MySQL数据库查询需要耗费一定时间

Question

我有一个大的消息数据库，有2.4万行：显示行0-24（共2455455行，查询耗时0.0006秒）。消息，所以我需要更快地加载对话，对于有较少对话的用户，加载如下（用户有3.2k对话）：显示行0-24（共3266行，查询耗时0.0345秒）[id：5009666...-4375619...]。对于有大量对话的用户，加载较慢（用户有40k对话）：显示行0-24（共40296行，查询耗时5.1763秒

P粉710478990 · Answer

嗯，也许你可以尝试为你的表添加索引：https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#:~:text=Creating%20Indexes&text=The%20statement%20to%20create%20index,the%20index%20must%20be%20distinct。确保按照你查询的行添加组合索引。

如果这样做没有改善你的查询时间，那么应该改进查询。

P粉020085599 · Answer

注意：

使用UNION而不是OR（见下文）
存在冗余键。PRIMARY KEY是一个键，所以删除KEY(id)
不要盲目地为每个列创建索引；而是使用查询来确定哪些索引，特别是复合索引，实际上是有用的。
在GROUP BY和ORDER BY中，CONCAT是不必要的，可能会适得其反。
对于INT类型，长度字段被忽略。你拥有的是20亿个值的限制。（对于seen来说，这是过度的，假设它只有0或1？）
使用新的语法：JOIN..ON。
如果seen只是true/false，那么删除它的索引。（或者向我展示你认为会从中受益的查询。）

CONCAT-LEAST-GREATEST - 这是为了构造一个“friends_id”？也许你真正想要一个“conversation_id”？目前，两个用户永远不会有多个“conversation”，对吗？

如果确实需要，为conversation_id创建一个新列。（目前，GROUP BY是低效的。）下面的代码消除了对这样一个id的需求。

( SELECT lastid FROM (
    ( SELECT from_id, MAX(id) AS lastid FROM messages
           WHERE to_id = ? GROUP BY from_id )
    UNION DISTINCT
    ( SELECT to_id,   MAX(id) AS lastid FROM messages 
           WHERE from_id = ? GROUP BY to_id )
                     ) AS x
) AS conversations

并且拥有这些“covering”和“composite”索引：

INDEX(to_id, from_id, id)
INDEX(from_id, to_id, id)

删除KEY(to_id)，KEY(from_id)，因为我的新索引可以处理这两个索引的所有其他任务。

我认为这具有相同的效果，但运行速度更快。

将它们组合起来：

SELECT  *
    FROM (
            ( SELECT from_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages
                  WHERE to_id = ? GROUP BY from_id )
            UNION ALL
            ( SELECT to_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages 
                  WHERE from_id = ? GROUP BY to_id )
         ) AS latest
    JOIN  messages  ON messages.id = latest.lastid
    ORDER BY  messages.id DESC

(加上这两个索引)

更多

我曾经错误地认为UNION DISTINCT可以替代对conversation_id的需求。但事实并非如此。我立即看到了一些解决方案：

添加一个conversation_id并使用它进行去重。（同时，我将UNION DISTINCT更改为UNION ALL，使查询稍微加快而不改变结果。）
将我的查询结果放入一个临时表中，其中包含（from_id，to_id，latestid）；然后使用你的CONCAT-LEAST-GREATEST技巧来去重对话；最后再将其与messages表进行JOIN，以获取其他列。
这种临时表技术使编写和调试更容易。我的第三个建议只是将这些部分组合到一个（难以阅读的）查询中，嵌套的Select语句深度为3级。