MySQL 5.0 查詢優化器原理與 EXPLAIN 實戰分析

優化器核心目標

盡可能使用索引、用最嚴格索引消除最多行、讓最終查找匹配行而非排除行。

EXPLAIN 實戰：看穿執行計畫

基本用法

mysql> 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

優化器識別 WHERE 0 無匹配行，直接返回 Impossible WHERE，完全不掃描表。

優化器決策邏輯：索引選擇

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

假設：

• col1 索引匹配 900 行
• col2 索引匹配 300 行
• 聯合匹配 30 行

優化器會先測試 col2（300 行 → 30 行，失敗 270 次），而非 col1（900 行 → 30 行，失敗 870 次），減少 I/O 與 CPU 開銷。

協助優化器的兩大原則

1. 比較相同資料型別的欄位

• INT vs BIGINT、CHAR(10) vs VARCHAR(10) 視為相容
• CHAR(10) vs CHAR(12) 不相容，導致索引失效
• 必要時 ALTER TABLE 統一型別

2. 讓索引欄位在比較表達式中獨立

• ❌ WHERE YEAR(date_col) = 2023（函數導致全表掃描）
• ✅ WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'（索引生效）
• ❌ WHERE col1 + 1 = 100
• ✅ WHERE col1 = 99

EXPLAIN 關鍵欄位解讀

實戰優化清單

1. 針對 type=ALL 的查詢建立索引
2. 消除 Using filesort、Using temporary
3. 組合索引遵循「最左前綴」原則
4. 避免 SELECT *，僅查詢所需欄位
5. 大表分頁優化：延遲關聯或游標分頁