前言
EXPLAIN 是 MySQL 查詢優化的核心工具，它揭示了優化器如何執行 SQL 語句。讀懂 EXPLAIN 輸出，等於擁有了「X 光眼」，能精準定位全表掃描、索引失效、臨時表、檔案排序等效能殺手。本文系統整理各欄位含義、連接類型優劣、Extra 關鍵資訊，並提供實戰優化清單。
EXPLAIN 輸出欄位完整解讀
| 欄位 | 含義 | 關鍵判讀重點 |
|---|
id |
SELECT 識別符，查詢執行順序標識 |
id 相同按順序執行；id 越大越優先；NULL 為結果集合併 |
select_type |
查詢類型 |
SIMPLE 無子查詢/UNION；PRIMARY 最外層；SUBQUERY 子查詢首個 SELECT；DERIVED FROM 子查詢（臨時表）；UNION 第二個起；UNION RESULT UNION 結果集 |
table |
當前行存取的表（或衍生表別名） |
<derivedN> 表示衍生表；<subqueryN> 表示子查詢 |
partitions |
匹配的分區（分區表才顯示） |
檢查分區裁剪是否生效 |
type |
核心：存取策略/連接類型，由優到劣 |
詳見下表「連接類型優劣階梯」 |
possible_keys |
優化器理論上可用的索引 |
為 NULL 表示無索引可用，需檢查 WHERE/JOIN 條件欄位 |
key |
實際選用的索引 |
為 NULL 表示無索引；可用 FORCE/USE/IGNORE INDEX 干預 |
key_len |
實際使用的索引長度（位元組） |
越短越好（前提不失精度）；可推斷複合索引用了幾個欄位 |
ref |
與索引比較的參考值（常數、欄位、函數） |
const 表示常數比較；func 表示函數/運算式 |
rows |
優化器預估需掃描的行數 |
越小越好；結合 filtered 估算實際檢索行數 |
filtered |
按表條件過濾後的行百分比 (5.7+) |
100 表示無額外過濾；低值意味著大量回表後被丟棄 |
Extra |
關鍵額外資訊，優化重點 |
詳見下表「Extra 關鍵字含義」 |
連接類型優劣階梯（由優到劣）
| 類型 | 說明 | 典型場景 | 效能評級 |
|---|
system | 表僅有一行（系統表） | 極少見 | ⭐⭐⭐⭐⭐ |
const | 主鍵/唯一索引等值查詢，最多一行匹配 | WHERE id=1 | ⭐⭐⭐⭐⭐ |
eq_ref | 唯一索引/主鍵連接，前表每行匹配後表一行 | JOIN ON pk = pk | ⭐⭐⭐⭐ |
ref | 非唯一索引等值查詢/連接，前表每行匹配多行 | WHERE idx_col=val、JOIN ON idx = val | ⭐⭐⭐ |
ref_or_null | 類似 ref，額外搜尋 NULL 值 | WHERE idx_col=val OR idx_col IS NULL | ⭐⭐⭐ |
index_merge | 索引合併優化（多個索引交集/聯集） | WHERE idx1=1 OR idx2=2 | ⭐⭐ |
range | 索引範圍掃描（>, <, >=, <=, BETWEEN, IN, LIKE 'prefix%') | WHERE id > 100 | ⭐⭐ |
index | 全索引掃描（比 ALL 好，因索引通常較小） | SELECT 索引覆蓋欄位、ORDER BY 索引欄 | ⭐ |
ALL | 全表掃描，最差 | 無索引、索引失效、範圍過大 | ⚠️ 避免 |
Extra 關鍵字含義與優化對策
| Extra 值 | 含義 | 嚴重度 | 優化方向 |
|---|
Using index | 覆蓋索引，僅讀取索引樹無需回表 | ✅ 極佳 | 維持現狀，適當建立覆蓋索引 |
Using where | Server 層過濾（存儲引擎返回後再過濾） | ⚠️ 視情況 | 若 rows 大，嘗試下推條件到索引 |
Using index condition | ICP（Index Condition Pushdown），條件下推到存儲引擎 | ✅ 良好 | 5.6+ 自動開啟，善用複合索引 |
Using temporary | 建立臨時表（通常 ORDER BY/GROUP BY 欄位不一致） | ❌ 差 | 調整索引順序、避免跨表排序、增加 SQL_SMALL_RESULT |
Using filesort | 需額外排序操作（無索引支援 ORDER BY） | ❌ 差 | 建立符合排序順序的索引、限制結果集大小 |
Range checked for each record | 無理想索引，對前表每行重新評估索引選擇 | ❌ 極差 | 建立合適複合索引、重寫查詢 |
Impossible WHERE | WHERE 條件永假，優化器直接返回空結果 | ℹ️ 提示 | 檢查條件邏輯錯誤 |
Select tables optimized away | 聚合查詢直接用索引完成（如 MIN/MAX） | ✅ 極佳 | 無需優化 |
Distinct | 找到第一匹配行後停止掃描 | ✅ 良好 | 配合 LIMIT 1 使用 |
Not exists | LEFT JOIN 優化，找到匹配行後不再搜尋內表 | ✅ 良好 | 正常 LEFT JOIN 行為 |
實戰優化檢查清單
- 鎖定
type 為 ref 以上：出現 ALL、index 必須優化。
- 檢查
key 是否為預期索引：若為 NULL 或非預期，用 FORCE INDEX 強制或調整索引結構。
- 關注
rows × filtered/100：預估實際處理行數，超過萬行需警惕。
- 消除
Using temporary / Using filesort：調整索引順序覆蓋 ORDER BY/GROUP BY。
- 追求
Using index（覆蓋索引）：SELECT 欄位盡量包含在複合索引中。
- 警惕
index_merge：通常意味著缺乏合適複合索引，考慮建立聯合索引替代。
- 子查詢優化：
DEPENDENT SUBQUERY 往往可改寫為 JOIN 或 EXISTS。
- 分區表檢查
partitions：確認分區裁剪生效，避免全分區掃描。
進階技巧
1. 格式化輸出
EXPLAIN FORMAT=JSON SELECT ...; -- 5.6.5+ 支援，含成本資訊
EXPLAIN FORMAT=TREE SELECT ...; -- 8.0.16+ 樹狀結構，直觀展示執行順序
2. 配合 ANALYZE 實際執行統計
EXPLAIN ANALYZE SELECT ...; -- 8.0.18+ 實際執行並顯示耗時、迴圈次數
3. 追蹤優化器決策
SET optimizer_trace="enabled=on";
SELECT ...;
SELECT * FROM information_schema.optimizer_trace;
總結
讀懂 EXPLAIN 是 MySQL 效能調優的入場磚，更是進階必修課。重點記住：type 越靠前越好、key 必須命中、rows 越少越好、Extra 避開 temporary/filesort、追求 Using index。養成「寫 SQL 必跑 EXPLAIN」的習慣，90% 的慢查詢都能在開發階段被攔截解決。
