MySQL 慢查詢分析優化全流程：開啟日誌、工具分析、EXPLAIN 實戰、索引優化

慢查詢是資料庫效能殺手。建立「開啟日誌 → 定期分析 → EXPLAIN 驗證 → 索引優化 → 效果驗證」閉環，系統性解決效能問題。

第一步：開啟慢查詢日誌

動態開啟 (無需重啟，重啟失效)

-- 查看當前設定
SHOW VARIABLES LIKE 'long_query_time';   -- 預設 10 秒
SHOW VARIABLES LIKE 'slow_query_log';    -- OFF/ON
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 設定 1 秒為慢查詢門檻
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

持久化配置 (my.cnf/my.ini)

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  -- 記錄未用索引查詢 (慎開，量大)
min_examined_row_limit = 100       -- 忽略掃描行數 < 100 的查詢

第二步：mysqldumpslow 分析工具實戰

# 基本用法
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# -s 排序方式: c=計數, t=時間, l=鎖定時間, r=返回行數 (加 a 為倒序: at, ac...)
# -t 顯示前 N 筆
# -g 正則過濾 (不區分大小寫)

# 實戰常用組合
# 1. 最常執行的 10 條
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 2. 總耗時最長的 10 條
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 3. 返回行數最多的 10 條 (可能造成網路/記憶體壓力)
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 4. 含 LEFT JOIN 的慢查詢
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

# 5. 特定表相關
mysqldumpslow -s t -t 20 -g "orders" /var/log/mysql/slow.log

第三步：EXPLAIN 實戰分析單條 SQL

執行計畫關鍵欄位

欄位	關鍵值	含義
id	數字	SELECT 識別號，越大越先執行
select_type	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION	查詢類型
table	表名/衍生表別名	當前行操作的表
partitions	分區名	分區表才有
type	system/const/eq_ref/ref/range/index/ALL	**最關鍵**：訪問類型，越前越好
possible_keys	索引名列表	理論可用索引
key	索引名	**實際使用索引**
key_len	位元組	索引鍵長度，判斷複合索引用了幾列
ref	const/函數/欄位	與索引比較的值來源
rows	數字	預估掃描行數 (越小越好)
filtered	百分比	WHERE 條件過濾後剩餘比例
Extra	Using index/Using where/Using temporary/Using filesort	**關鍵細節**

實戰分析範例

EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 AND status = 'paid' 
ORDER BY created_at DESC LIMIT 20;

-- 理想輸出:
-- type: ref (或 eq_ref)
-- key: idx_user_status_created (user_id, status, created_at)
-- rows: 20
-- Extra: Using where; Using index (覆蓋索引)

第四步：索引優化實戰模式

模式 1：WHERE + ORDER BY + LIMIT → 複合索引

-- 查詢
SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20;

-- 索引 (等值在前、排序在後)
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);
-- MySQL 8.0 支援降序索引，5.7 忽略 DESC 但仍可用於排序

模式 2：覆蓋索引避免回表

-- 查詢只需 id, order_no, created_at
SELECT id, order_no, created_at FROM orders WHERE user_id = 100;

-- 索引包含所有查詢欄位
CREATE INDEX idx_cover ON orders(user_id, id, order_no, created_at);
-- Extra: Using index

模式 3：範圍查詢索引列順序

-- 查詢: WHERE created_at > '2024-01-01' AND status = 'paid'
-- 索引: (status, created_at) ✓  (等值在前)
-- 索引: (created_at, status) ✗  (範圍在前，status 無法用上)

模式 4：LIKE 前綴匹配用索引

WHERE name LIKE '張%'   -- 可用索引
WHERE name LIKE '%張%'  -- 全表掃描，考慮全文索引或 ES

第五步：驗證優化效果

-- 1. 再次 EXPLAIN 確認 type、key、rows 改善
-- 2. 實際執行時間對比
SET profiling = 1;
-- 執行優化前/後 SQL
SHOW PROFILES;
-- 3. 慢查詢日誌確認不再出現
-- 4. 監控指標: Slow_queries、Innodb_rows_read、QPS

自動化分析工具推薦

• pt-query-digest (Percona Toolkit)：專業慢查詢分析報表
• MySQL Enterprise Monitor：官方監控
• Prometheus + Grafana + mysqld_exporter：開源監控棧
• pgBadger 類工具：針對 PostgreSQL

慢查詢治理 SOP

1. 每日跑 mysqldumpslow -s t -t 20 產出 Top 20
2. 每週 DBA 審核 Top 50，制定優化計畫
3. 上線前必跑 EXPLAIN，CI/CD 整合 EXPLAIN ANALYZE (MySQL 8.0.18+)
4. 建立索引變更審批流程，避免過度索引
5. 定期 ANALYZE TABLE 更新統計資訊