Die Abfrage einer MySQL-Datenbank dauert eine gewisse Zeit

Question

Ich habe eine große Nachrichtendatenbank mit 24.000 Zeilen:

Zeigt die Zeilen 0–24 an (insgesamt 2455455 Zeilen, die Abfrage dauert 0,0006 Sekunden). 

Nachrichten, daher muss ich Konversationen schneller laden. Für Benutzer mit weniger Konversationen erfolgt das Laden wie folgt (Benutzer hat 3,2.000 Konversationen):

Zeile 0-24 anzeigen (insgesamt 3266 Zeilen, Abfrage dauert 0,0345 Sekunden) [id: 5009666... ​​​​- 4375619...]. 

Langsamerer Ladevorgang für Benutzer mit vielen Konversationen (Benutzer mit 40.000 Konversationen):

Zeile 0-24 anzeigen (insgesamt 40296 Zeilen, Abfrage dauert 5,1763 Sekunden) [id: 5021561... - 5015545...]. 

Ich verwende Indexschlüssel für diese Spalten:

id, to_id, from_id, time, seen

Datenbanktabelle:

CREATE TABLE `messages` (
  `id` int(255) NICHT NULL,
  `to_id` int(20) NICHT NULL,
  `from_id` int(20) NICHT NULL,
  „Nachricht“-Langtext NICHT NULL,
  „time“ double NICHT 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, „Hallo“, 1587581995.5222, 1);


ALTER TABLE „Nachrichten“.
  PRIMÄRSCHLÜSSEL („id“) HINZUFÜGEN,
  SCHLÜSSEL „time_idx“ („time“) HINZUFÜGEN,
  SCHLÜSSEL `from_idx` (`from_id`) HINZUFÜGEN,
  SCHLÜSSEL `to_idx` (`to_id`) HINZUFÜGEN,
  SCHLÜSSEL „seenx“ („seen“) HINZUFÜGEN,
  SCHLÜSSEL `idx` (`id`) HINZUFÜGEN;


ALTER TABLE „Nachrichten“.
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;

Ich verwende die folgende Abfrage:

SELECT
  *
AUS
  Mitteilungen,
  (
    WÄHLEN
      MAX(id) als letzte ID
    AUS
      Mitteilungen
    WO
      (
        message.to_id = '1' – die ID, mit der verglichen werden soll (die ID des angemeldeten Benutzers)
        ODER message.from_id = '1' – die ID, mit der verglichen werden soll (die ID des angemeldeten Benutzers)
      )
    GRUPPIERE NACH
      CONCAT(
        MINDESTENS(messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) als Gespräche
WO
  id = Gespräche.lastid
SORTIEREN NACH
  message.id DESC

Ich weiß nicht, wie ich es für Benutzer mit vielen Konversationen schneller machen kann, wenn ich die Datenbankstruktur neu erstellen soll.

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级。