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秒）[id：5021561... - 5015545...]。

我对这些列使用索引键：

id，to_id，from_id，time，seen

数据库表：

CREATE TABLE `messages` (
  `id` int(255) NOT NULL,
  `to_id` int(20) NOT NULL,
  `from_id` int(20) NOT NULL,
  `message` longtext NOT NULL,
  `time` double NOT NULL,
  `seen` int(2) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALUES
(2, 6001, 2, 'Hi there', 1587581995.5222, 1);


ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `time_idx` (`time`),
  ADD KEY `from_idx` (`from_id`),
  ADD KEY `to_idx` (`to_id`),
  ADD KEY `seenx` (`seen`),
  ADD KEY `idx` (`id`);


ALTER TABLE `messages`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;

我使用以下查询：

SELECT
  *
FROM
  messages,
  (
    SELECT
      MAX(id) as lastid
    FROM
      messages
    WHERE
      (
        messages.to_id = '1' -- 与之比较的ID（已登录用户的ID）
        OR messages.from_id = '1' -- 与之比较的ID（已登录用户的ID）
      )
    GROUP BY
      CONCAT(
        LEAST(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) as conversations
WHERE
  id = conversations.lastid
ORDER BY
  messages.id DESC

我不知道如何使具有大量对话的用户更快，我是否应该重新创建数据库结构。

Answer 1

嗯，也许你可以尝试为你的表添加索引：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。 确保按照你查询的行添加组合索引。

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

Answer 2

注意：

• 使用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级。