MySQL 查詢優化器工作原理深度解析：索引選擇、常數折疊、EXPLAIN 實戰

優化器核心目標

MySQL 查詢優化器（Query Optimizer）單一核心目標：**以最小代價找到符合條件的行**。核心策略：盡可能使用索引，並選擇最嚴格（選擇性最高）的索引，儘早過濾掉不符合條件的行。

常數折疊與不可能條件檢測

-- 優化器識別 WHERE 0 永假，直接返回空結果，不掃描表
EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE

索引選擇邏輯：選擇性計算

假設查詢：

SELECT col3 FROM mytable
WHERE col1 = 'value1' AND col2 = 'value2';
-- col1、col2 均有索引

優化器估算：

• col1 = 'value1' 匹配 900 行（選擇性 900/總行數）
• col2 = 'value2' 匹配 300 行（選擇性較高）
• 聯合匹配 30 行

決策：優先用 col2 索引讀取 300 行，再回表過濾 col1，僅 270 次失敗；若先用 col1 則 870 次失敗。優化器選擇代價更小的 col2 索引。

輔助優化器的最佳實踐

1. 統一比較欄位數據類型：INT vs BIGINT、CHAR(10) vs VARCHAR(10) 視為相容，但 CHAR(10) vs CHAR(12) 會阻礙索引使用

   -- 惡例：隱式轉換導致索引失效
   WHERE varchar_col = 123;  -- 數值與字串比較
   -- 良例：統一類型
   WHERE varchar_col = '123';

2. 收集統計資訊：ANALYZE TABLE tbl_name; 更新索引基數，幫助優化器準確估算 rows
3. 使用 EXPLAIN 驗證：關注 type（目標 ref/eq_ref/range）、key（實際用索引）、rows（預估掃描行）、Extra（Using index/where/temporary/filesort）
4. 必要時強制索引：FORCE INDEX (idx_name)、IGNORE INDEX (idx_name)、USE INDEX (idx_name)（謹慎使用，優化器通常更聰明）

EXPLAIN 關鍵欄位深度解讀

優化器限制與人工干預場景

1. 範圍優化器：IN 列表過長（> range_optimizer_max_mem_size）會退化為全表掃描
2. 關聯順序：優化器單向動態規劃搜尋最優 JOIN 順序，表數 > optimizer_search_depth（預設 62）時改用貪婪演算法
3. 直方圖統計：8.0+ 支援 ANALYZE TABLE ... UPDATE HISTOGRAM 解決非均勻分布估算偏差

延伸閱讀

• MySQL 無重複查詢的實現
• MySQL 中 SELECT 命令的特殊用法
• 使用 Limit 參數實現 MySQL 查詢優化