MySQL 優化器問題排查實戰：EXPLAIN 執行計畫、ANALYZE TABLE、FORCE INDEX、系統變數調優

MySQL 基於成本的優化器 (Cost-Based Optimizer)

優化器根據統計資訊估算各執行計畫成本，選擇最低成本路徑。當統計資訊不準確/缺失時，優化器可能做出次優決策。

四大診斷與干預工具

1. EXPLAIN：查看執行計畫 (核心診斷工具) ⭐

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.status = 1;

關鍵輸出欄位解讀：

2. ANALYZE TABLE：更新索引統計資訊

ANALYZE TABLE t1, t2;
-- 或全部資料庫
ANALYZE TABLE db1.table1, db1.table2, db2.table3;

• 重新掃描索引頁面，更新 cardinality (基數統計)
• 鎖表時間短 (僅讀取鎖)，可線上執行
• 大表建議低峰期執行

3. INDEX HINT：強制/忽略索引

-- 強制使用指定索引
SELECT * FROM t1 FORCE INDEX (idx_status) WHERE status = 1;

-- 建議使用 (優化器可選擇其他)
SELECT * FROM t1 USE INDEX (idx_status) WHERE status = 1;

-- 忽略指定索引
SELECT * FROM t1 IGNORE INDEX (idx_bad) WHERE status = 1;

⚠️ 慎用 FORCE INDEX：資料分布變化可能導致強制索引反而變慢，建議配合監控定期評估。

4. 系統變數調優：影響優化器決策

實戰優化案例

-- 原始慢查詢
SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

-- EXPLAIN 顯示：type=ALL, rows=5000000, Extra=Using where; Using filesort

-- 方案一：建立複合索引 (WHERE + ORDER BY)
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at DESC);

-- 方案二：若 user_id 選擇性極高，單欄索引配合 LIMIT 下推 (MySQL 5.6+)
CREATE INDEX idx_user_id ON orders (user_id);

-- 方案三：覆蓋索引 (僅查詢部分欄位)
CREATE INDEX idx_cover ON orders (user_id, status, created_at DESC, order_id, amount);
SELECT order_id, amount FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;
-- Extra: Using index (無需回表)

優化器陷阱避坑指南

• ❌ 盲目加索引：寫入效能下降、磁碟空間暴增、優化器選擇困惑
• ❌ 永久 FORCE INDEX：資料分布變化導致效能倒退
• ❌ 忽略統計資訊更新：大量寫入後必須 ANALYZE TABLE
• ✅ 定期 pt-query-digest 分析慢查詢日誌
• ✅ 建立 optimizer_trace 基準對比
• ✅ 測試環境模擬生產資料量驗證執行計畫