背景

MySQL 基於成本的優化器 (Cost-Based Optimizer) 在統計信息缺失或不準時，可能選擇錯誤執行計劃 (如全表掃描代替索引、錯誤連接順序)。

四大排查工具

1. EXPLAIN — 窺探執行計劃

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

重點關注列：

• type：ALL(全表掃描) → index → range → ref → eq_ref → const → system (優到劣)。
• key：實際使用的索引，NULL 表示無索引可用。
• rows：預估掃描行數，越小越好。
• Extra：Using where、Using index(覆蓋索引)、Using temporary、Using filesort (需優化)。

2. ANALYZE TABLE — 更新索引統計信息

ANALYZE TABLE t1, t2;

重新掃描索引頁面更新 INFORMATION_SCHEMA.STATISTICS 中的基數統計，解決優化器「以訛傳訛」問題。InnoDB 為隨機採樣 (默認 8 頁)，MyISAM 為全表掃描。**大表執行較慢，建議低峰期運行**。

3. FORCE INDEX / USE INDEX / IGNORE INDEX — 強制/建議/忽略索引

-- 強制使用指定索引 (優化器認為表掃描更貴時生效)
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：數據分佈變化可能導致強制索引反而更慢，建議配合 OPTIMIZER_SWITCH 或 OPTIMIZER_HINTS (8.0+) 更精細控制。

4. 調節優化器系統變量

-- 假設鍵查找成本極低，提高表掃描代價估算
SET GLOBAL max_seeks_for_key = 1000;
-- 或啟動參數 --max-seeks-for-key=1000

告訴優化器：「任何索引查找不超過 1000 次」，傾向選擇索引而非全表掃描。其他關鍵變量：

• optimizer_switch：細粒度開關 (如 mrr=on、batched_key_access=off 等)。
• join_buffer_size、sort_buffer_size：影響 JOIN/排序性能。

實戰排查清單

1. EXPLAIN 確認是否走索引、連接順序、是否產生臨時表/文件排序。
2. SHOW INDEX FROM tbl 確認索引定義與基數。
3. ANALYZE TABLE 刷新統計信息。
4. 嘗試 FORCE INDEX 驗證索引是否真優於全表掃描。
5. 調整 optimizer_switch 或升級 MySQL 版本 (優化器持續改進)。
6. 根治：補充缺失索引、重寫 SQL (避免隱式轉換、OR 條件、函數包裹列)。