公司網站突然變慢，資料庫直接滿載！淺談資料表的 EXPLAIN 語法與索引

今天上班途中，業務部門向 RD 部門回報公司網站突然掛掉了。

經主管告知，資料庫 Server 的 CPU 使用率達到 100%，所以應該是有查詢過程卡住。

請 MIS 協助開啟資料庫的 Slow Query Log 之後，發現有一個查詢語句執行時間長達 33 秒，甚至一度高達 88 秒。

The Slow Query Log 是資料庫中的功能，當有語句查詢時間超過設定的值時，這個查詢語句就會被寫進 Log (紀錄檔案) 中，方便日後查找效能問題。

這個執行時間長達 88 秒的語句大概是長這樣 (範例)。

SET timestamp=1603683274; 

SELECT `id`, 
       `title`, 
       `show_date`, 
       `title`
FROM   `reports` 
       INNER JOIN `report_categories` 
               ON `reports`.`report_uuid` = `report_categories`.`report_uuid` 
WHERE  `status` = '1' 
       AND `report_categories`.`id` = '17' 
        OR `report_categories`.`id` = '61' 
ORDER  BY `show_date` DESC 
LIMIT  20 offset 0; 

從查詢語句上來看，就是把報告分類資料表中 id 為 17 與 61 的所有關聯報告撈出，並且報告必須是上架的狀態。

身為菜雞工程師的小弟我，還真的看不太出來哪邊怪怪的，於是我向同事求助。

同事在看完語句之後，請我在 SELECT 前面加一句 EXPLAIN，所以語句變成這樣。

SET timestamp=1603683274; 

# 加上這一行
EXPLAIN

SELECT `id`, 
       `title`, 
       `show_date`, 
       `title`
FROM   `reports` 
       INNER JOIN `report_categories` 
               ON `reports`.`report_uuid` = `report_categories`.`report_uuid` 
WHERE  `status` = '1' 
       AND `report_categories`.`id` = '17' 
        OR `report_categories`.`id` = '61' 
ORDER  BY `show_date` DESC 
LIMIT  20 offset 0; 

這時候重新查詢語句，就會發現查詢結果改變，只有兩行出現 (省略部分欄位)。

同事解釋 EXPLAIN 這個語法，可以用來分析語句的執行效能，查看是否有使用索引或是全表掃描。下表解釋每個欄位的意思。

眼尖的同事，發現這兩張表之間沒有任何索引是一件很奇怪的事情，因此幫忙進行測試。

發現 `reports`.`report_uuid` 這個欄位，加上索引後可以大幅度的降低查詢時間。

資料庫中的索引，是建立一種用於資料檢索的查找表，以加快搜尋的速度。索引有助於加快 SELECT 和 WHERE 語法的查詢，但它會減慢資料寫入的速度。

因為 `reports`.`report_uuid` 這個欄位是使用大寫英文與數字組合的亂數，如果不加入索引，查詢就會被拖慢。

同事表明如果資料庫滿載的話，可以從索引這邊下手，當然索引也必須視情況加入，不能亂加。

感謝大神同事的幫忙，小弟我受益良多，特此寫一篇部落格文章紀錄。

參考資料

• mysql explain 詳解
• 一張圖徹底搞定 explain