首頁  >  問答  >  主體

MySQL資料庫查詢需要耗費一定時間

<p>我有一個大的消息資料庫,有2.4萬行:</p> <pre class="brush:php;toolbar:false;">顯示行0-24(共2455455行,查詢耗時0.0006秒)。 </pre> <p>訊息,所以我需要更快地載入對話,對於有較少對話的用戶,載入如下(用戶有3.2k對話):</p> <pre class="brush:php;toolbar:false;">顯示行0-24(共3266行,查詢耗時0.0345秒)[id:5009666... - 4375619...]。 </pre> <p>對於有大量對話的用戶,載入較慢(用戶有40k對話):</p> <pre class="brush:php;toolbar:false;">顯示行0-24(共40296行,查詢耗時5.1763秒)[id:5021561... - 5015545...]。 </pre> <p>我對這些欄位使用索引鍵:</p> <pre class="brush:php;toolbar:false;">id,to_id,from_id,time,seen</pre> <p>資料庫表:</p> <pre class="brush:php;toolbar:false;">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;</pre> <p>我使用以下查詢:</p> <pre class="brush:php;toolbar:false;">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</pre> <p>我不知道如何讓具有大量對話的用戶更快,我是否應該重新建立資料庫結構。 </p>
P粉764836448P粉764836448437 天前592

全部回覆(2)我來回復

  • P粉710478990

    P粉7104789902023-09-01 21:08:35

    嗯,也許你可以嘗試為你的表添加索引: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。 確保按照你查詢的行添加組合索引。

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

    回覆
    0
  • P粉020085599

    P粉0200855992023-09-01 14:23:17

    注意:

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

    回覆
    0
  • 取消回覆