EXPLAIN 深度解析：輸出列含義、連接類型優劣、Extra 信息解讀、子查詢優化、實戰調優案例
EXPLAIN 是 MySQL 性能調優的「聽診器」，揭示優化器如何執行查詢：掃表還是走索引、連接順序、臨時表、文件排序等。本文系統講解所有輸出列、連接類型層級、Extra 關鍵信息、各類子查詢類型，並提供讀懂執行計劃的實戰方法論。
一、基本語法
EXPLAIN SELECT * FROM t3 WHERE id = 3952602;
EXPLAIN SELECT * FROM (SELECT * FROM t3 WHERE id=3952602) a;
二、輸出列完整解讀
| 列名 | 含義 | 關鍵判讀點 |
|---|
id | 查詢序列號，標識執行順序。值越大越先執行（從內向外）。相同 id 按順序執行。 | 關注最大 id 先執行，派生表/子查詢 id 較大 |
select_type | 查詢類型，見下表詳細分類 | 識別 SIMPLE/PRIMARY/UNION/SUBQUERY/DERIVED 等 |
table | 當前行涉及的表（或派生表 <derivedN>） | derivedN 表示第 N 步產生的臨時表 |
type | 核心列：訪問策略/連接類型，優劣見下表 | 目標：const/eq_ref/ref/range；避免 ALL/index |
possible_keys | 可能用到的索引集合 | 為 NULL 表示無索引可用，需檢查 WHERE 條件列 |
key | 實際選用的索引 | 為 NULL 表示全表掃描；可用 FORCE/USE/IGNORE INDEX 干預 |
key_len | 使用索引的字節長度 | 越短越好（不損失精度）；組合索引可判斷用了幾列 |
ref | 與索引比較的引用（常數、列名、函數） | const 表示常數等值查詢 |
rows | 預估掃描行數（統計信息估算） | 越小越好；與實際行數偏差大時需 ANALYZE TABLE |
Extra | 額外執行信息，見下表關鍵值 | 關注 Using filesort、Using temporary、Using index |
三、select_type 完整分類
| 類型 | 含義 | 典型場景 |
|---|
SIMPLE | 簡單查詢，無 UNION/子查詢 | SELECT * FROM t WHERE id=1 |
PRIMARY | 最外層查詢 | 包含子查詢/派生表的主查詢 |
UNION | UNION 中第二或後續 SELECT | SELECT 1 UNION SELECT 2 第二行 |
DEPENDENT UNION | UNION 中依賴外部查詢的 SELECT | SELECT * FROM t WHERE id IN (SELECT ... UNION SELECT ...) |
UNION RESULT | UNION 合併結果 | 顯示 NULL id 行 |
SUBQUERY | 子查詢中第一個 SELECT | SELECT * FROM t WHERE id = (SELECT ...) |
DEPENDENT SUBQUERY | 依賴外部查詢的子查詢 | SELECT * FROM t WHERE id IN (SELECT ...) |
DERIVED | FROM 子句中的派生表（子查詢） | SELECT * FROM (SELECT ...) |
四、type 連接類型優劣排序（最優→最差）
- system：表僅一行（const 特例）
- const：主鍵/唯一索引等值查詢，最多一行，極快
- eq_ref：唯一索引/主鍵連接，每行匹配一行，最佳連接
- ref：非唯一索引/最左前綴連接，匹配少量行則好
- ref_or_null：ref + 額外檢查 NULL
- index_merge：索引合併優化（Using union/intersect/sort_union）
- unique_subquery：IN 子查詢主鍵優化替換
- index_subquery：IN 子查詢非唯一索引優化
- range：索引范圍掃描（>、<、>=、<=、BETWEEN、IN）
- index：全索引掃描（比 ALL 快，索引通常小於數據）
- ALL：全表掃描，**最差**，需加索引優化
五、Extra 關鍵信息解讀
| 值 | 含義 | 優化方向 |
|---|
Using index | 覆蓋索引：僅訪問索引樹，不回表 | 優秀，設計覆蓋索引可獲取 |
Using where | WHERE 過濾（Server 層而非存儲引擎層） | 正常；若 type=ALL 需加索引 |
Using temporary | 需創建臨時表（常見 ORDER BY/GROUP BY 列不一致） | 需優化：調整索引順序、避免臨時表 |
Using filesort | 需文件排序（無索引支持 ORDER BY） | 需優化：添加排序列索引 |
Using index condition | ICP (Index Condition Pushdown) 下推 | MySQL 5.6+ 優化特性 |
Using join buffer | 使用連接緩存（Block Nested Loop / Batched Key Access） | 大表連接優化 |
Impossible WHERE | WHERE 條件永假（如 id=1 AND id=2） | 優化器直接返回空 |
Distinct | 找到第一匹配行後停止掃描 | 正常優化 |
Not exists | LEFT JOIN 優化：找到匹配行後停止 | 正常優化 |
六、實戰讀懂執行計劃方法論
- 看 id 順序： 從最大 id 開始，理解執行流向（子查詢→派生表→主查詢）。
- 看 type： 核心指標。是否 ALL/index？是否 const/eq_ref/ref/range？
- 看 key/key_len： 是否用上預期索引？組合索引用了幾列？
- 看 rows： 預估掃描行數是否合理？偏大需分析統計信息。
- 看 Extra： 是否出現
Using temporary、Using filesort、Using index？
- 對比 possible_keys 與 key： 為何優化器沒選最優索引？統計信息過舊？條件未命中最左前綴？
七、經典調優案例
案例 1：隱式類型轉換導致索引失效
-- 表 t: id VARCHAR(20) 有索引
EXPLAIN SELECT * FROM t WHERE id = 123; -- type=ALL，隱式轉換
EXPLAIN SELECT * FROM t WHERE id = '123'; -- type=const，走索引
案例 2：OR 條件打破索引
EXPLAIN SELECT * FROM t WHERE a=1 OR b=2; -- 可能 type=ALL
-- 優化：UNION ALL 分開查詢再合併，各自走索引
案例 3：ORDER BY 文件排序
EXPLAIN SELECT * FROM t WHERE a=1 ORDER BY b; -- Using filesort
-- 優化：創建組合索引 (a,b) 實現索引排序
總結
讀懂 EXPLAIN 是 SQL 調優的核心技能。重點關注 type（訪問策略）、key（實際索引）、rows（掃描規模）、Extra（臨時表/排序/覆蓋索引）。結合 SHOW INDEX、ANALYZE TABLE、OPTIMIZE TABLE、EXPLAIN FORMAT=JSON（MySQL 5.6+）深入分析，方能精準定位並解決性能瓶頸。
