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

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

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