公司網站突然變慢,資料庫直接滿載!淺談資料表的 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; 

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

idselect_typetablepartitionstypepossible_keyskey
1reports NULLNULL
1report_cate… NULLNULL

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

idColumnsJSON NameMeaning
1idselect_id每個select子句的標識id
2select_typeNoneselect語句的類型
3tabletable_name當前表名
4partitionspartitions匹配的分區
5typeaccess_type當前表內訪問方式 join type
6possible_keyspossible_keys可能使用到的索引
7keykey經過優化器評估最終使用的索引
8key_lenkey_length使用到的索引長度
9refref引用到的上一個表的列
10rowsrowsrows_examined,要得到最終記錄索要掃描經過的記錄數
11filteredfiltered按表條件過濾行的百分比
12ExtraNone額外的信息說明

眼尖的同事,發現這兩張表之間沒有任何索引是一件很奇怪的事情,因此幫忙進行測試
發現 `reports`.`report_uuid` 這個欄位,加上索引後可以大幅度的降低查詢時間

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

因為 `reports`.`report_uuid` 這個欄位是使用大寫英文與數字組合的亂數如果不加入索引,查詢就會被拖慢
雖然資料表設計也是怪怪的(為什麼要用這種亂數的 uuid 呢…)

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

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

參考資料:
mysql explain 詳解
一張圖徹底搞定 explain

sharkHead

PHP 與 Python 菜雞工程師,覺得前端有趣,但是無奈沒有慧根
目前沒有任何評論~