MySQL 5.0 查詢優化器工作原理與索引優化實戰

MySQL 5.0 查詢優化器工作原理與索引優化實戰

MySQL 查詢優化器的核心目標：儘可能使用索引，並用最嚴格的索引過濾掉最多不符合條件的行。理解其決策邏輯，能指導我們編寫高性能 SQL。

一、 優化器如何「偷懶」：不可能的 WHERE

SELECT * FROM tbl_name WHERE 0;

優化器識別 WHERE 0 永假，直接返回空結果，完全不掃描表。用 EXPLAIN 驗證：

EXPLAIN SELECT * FROM tbl_name WHERE 0\G
-- Extra: Impossible WHERE
-- table: NULL, type: NULL, rows: NULL

二、 優化器選擇索引順序的邏輯

假設查詢：

SELECT col3 FROM mytable
WHERE col1 = 'value1' AND col2 = 'value2';

索引 col1 匹配 900 行，col2 匹配 300 行，交集 30 行。
優化器會先應用選擇性最高（匹配行數最少）的索引col2，只需檢查 300 行找到 30 行，失敗 270 次；若先用 col1 需檢 900 行，失敗 870 次。原則：先過濾掉最多行的條件優先執行。

三、 協助優化器的兩大編碼準則

1. 比較列類型保持一致

• INT vs BIGINT 不同；CHAR(10) vs VARCHAR(10) 兼容，但 vs CHAR(12) 不同。
• 類型不一致會導致隱式轉換，索引失效。用 ALTER TABLE 統一列類型。

2. 讓索引列在比較表達式中「獨立」

反例：WHERE mycol * 2 < 4 → 每行必須計算 mycol*2，索引不可用。
正例：WHERE mycol < 4 / 2 → 常量折疊為 2，索引可用。
原則：索引列不參與函數調用、算術運算，保持「裸露」在比較運算符左側。

四、 實戰建議

• 為高選擇性列建立索引（唯一值佔比高）。
• 組合索引遵循「最左前綴」與「高選擇性在前」。
• 避免 SELECT *，減少回表開銷。
• 定期 ANALYZE TABLE 更新統計信息，讓優化器決策更準確。
• 用 EXPLAIN 檢查執行計劃，關注 type、key、rows、Extra。