MySQL 優化器原理與人工干預：EXPLAIN、ANALYZE、FORCE INDEX 實戰指南

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

MySQL 根據統計資訊估算各執行計畫成本，選擇最低成本路徑。但統計資訊可能過時、不準確，導致次優計畫。

四大干預工具

1. EXPLAIN — 觀察執行計畫

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

重點欄位：

• type：system > const > eq_ref > ref > range > index > ALL
• key：實際使用索引
• rows：預估掃描行數
• Extra：Using index、Using where、Using filesort、Using temporary

2. ANALYZE TABLE — 更新統計資訊

ANALYZE TABLE t1, t2;

• 重新掃描索引頁面，更新 INFORMATION_SCHEMA.STATISTICS 中的基數。
• InnoDB 為隨機抽樣（預設 20 頁），大表可能不準；MySQL 8.0.13+ 可用 SET GLOBAL innodb_stats_persistent_sample_pages = 100 提高精度。

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

-- 強制使用索引（優化器認為表掃描更貴時生效）
SELECT * FROM t1 FORCE INDEX (idx_col) JOIN t2 ON t1.col = t2.col;

-- 建議使用（優化器仍可選擇不採用）
SELECT * FROM t1 USE INDEX (idx_col) WHERE col > 100;

-- 忽略特定索引
SELECT * FROM t1 IGNORE INDEX (bad_idx) WHERE col = 'val';

4. 系統變數調整優化器行為

實戰排查流程

1. EXPLAIN 確認是否 type=ALL、key=NULL、rows 偏大。
2. ANALYZE TABLE 更新統計，再次 EXPLAIN。
3. 若仍不理想，嘗試 FORCE INDEX 驗證索引是否真能加速。
4. 調整 max_seeks_for_key 或 optimizer_switch 微調。
5. 根治：補齊缺失索引、重寫 SQL、調整 Schema 設計。

避坑提醒

• FORCE INDEX 非萬能：若統計資訊錯誤導致優化器誤判，強制可能反而更慢。
• 隱式轉換：WHERE varchar_col = 123 導致索引失效，EXPLAIN 會顯示 Using where 而非 Using index condition。
• 複合索引最左前綴：INDEX (a,b,c) 無法支援 WHERE b=1 或 WHERE a=1 AND c=2。