MySQL 慢查詢優化實戰：從慢查詢日誌分析到索引優化全指南

前言：為什麼需要關注慢查詢？

在資料庫效能調優中，查詢效能問題佔據了絕大多數比例。與 Oracle 等商業資料庫自帶優化建議不同，MySQL 需要我們主動開啟並分析慢查詢日誌（Slow Query Log）來發現問題。預設情況下，慢查詢日誌以檔案形式儲存，這是大多數生產環境採用的方式。

一、 慢查詢日誌長什麼樣？

首先看一段典型的日誌記錄：

Time Id Command Argument
# Time: 141010 9:33:57
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 0.000342 Lock_time: 0.000142 Rows_sent: 3 Rows_examined: 3
use test;
SET timestamp=1412904837;
select * from t;

日誌結構清晰：第一行記錄時間戳，第二行記錄執行用戶與主機，第三行記錄核心效能指標——查詢耗時、鎖等待時間、返回行數與掃描行數，隨後是具體的 SQL 語句。若想了解 CSV 格式下各欄位的詳細含義，可執行 SHOW CREATE TABLE mysql.slow_log; 查看表結構定義。

二、 核心參數配置與開啟

1. 查看當前慢查詢設定

mysql> show variables like '%slow_query%';

關鍵輸出包括 slow_query_log（是否開啟）與 slow_query_log_file（日誌檔案路徑）。

2. 什麼查詢會被記錄？

MySQL 記錄兩類查詢：未使用索引的查詢，以及 超過指定時間且掃描行數超過閾值的查詢。相關參數如下：

• 未使用索引記錄開關：log_queries_not_using_indexes（ON/OFF），log_throttle_queries_not_using_indexes（每分鐘記錄上限，0 為不限制）。
• 長查詢時間閾值：long_query_time（預設 10 秒，建議設為 0.1~1 秒）。
• 最小掃描行數閾值：min_examined_row_limit（預設 0，不限制）。

3. 動態開啟慢查詢日誌

set global long_query_time=0.1;
set global log_queries_not_using_indexes=on;
set global slow_query_log = on;

注意：long_query_time 需根據業務 SLA 設定；開啟「無索引記錄」需配合流控參數防止日誌暴增影響效能。

三、 慢查詢日誌分析工具

面對海量日誌，必須借助工具快速定位「最值得優化」的 SQL。

1. 官方工具：mysqldumpslow

Perl 腳本，輸出包含執行次數、最大/總/平均耗時、鎖等待、返回/掃描行數、用戶及 SQL 文本。常用參數：

• -s 排序方式：c（次數）、r（返回行數）、t（耗時）
• -t n 顯示前 N 條

./mysqldumpslow -s t -t 1 /usr/local/mysql/data/cloudlu-slow.log

2. 進階工具：mysqlsla、pt-query-digest

同為 Perl 腳本，提供更豐富的統計維度：執行次數佔比、時間分佈（含 95% 分位）、鎖等待分析、行統計、可生成報表並持久化分析結果。建議生產環境優先使用 pt-query-digest。

四、 定位慢 SQL 後的深度優化：EXPLAIN 實戰

鎖定問題 SQL 後，使用 EXPLAIN 或 DESC 查看執行計劃：

mysql> explain select * from test.t \G

輸出重點關注三大指標：

1. type：ALL 表示全表掃描，效率最低，必須優化。
2. key：實際使用的索引及長度；NULL 代表未用到索引。
3. Extra：避免出現 Using filesort、Using temporary，尤其關注 ORDER BY、GROUP BY 場景。

五、 避坑指南：優化後的持續監控

SQL 優化是雙刃劍。例如新增索引雖加速查詢，但會增加存儲佔用、拖慢寫入（INSERT/UPDATE/DELETE）。在「寫多讀少」系統中可能適得其反。原則：優化後務必持續監控，防止引入新瓶頸。