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 Indexes&text=The statement to create index,the index must be distinct。確保按照你查詢的行添加組合索引。

如果這樣做沒有改善你的查詢時間，那麼應該改進查詢。

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