MySQL EXPLAIN 執行計劃完整解讀指南：欄位含義、優化判讀與實戰案例

前言

EXPLAIN 是 MySQL 效能調校的「聽診器」。透過分析執行計劃，我們能洞悉優化器如何存取資料、使用何種索引、是否產生臨時表或檔案排序。本文系統解析 EXPLAIN 12 個輸出欄位含義、type 存取類型層級、Extra 關鍵提示、EXPLAIN ANALYZE (8.0.18+) 實際執行統計，並附實戰判讀邏輯與優化決策樹。

一、EXPLAIN 基本語法與變體

-- 傳統格式 (表格)
EXPLAIN SELECT * FROM users WHERE id = 1;

-- JSON 格式 (8.0+，含更多成本資訊)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- 實際執行統計 (8.0.18+，真實耗時、回傳行數、迴圈次數)
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 追蹤優化器決策 (5.6+，需開啟 optimizer_trace)
SET SESSION optimizer_trace="enabled=on";
SELECT * FROM users WHERE id = 1;
SELECT * FROM information_schema.optimizer_trace;

二、12 個標準輸出欄位詳細解析

三、type 存取類型層級 (由優到劣)

四、Extra 關鍵值對照表 (優化紅綠燈)

五、EXPLAIN ANALYZE 實戰優勢 (8.0.18+)

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123 ORDER BY created_at DESC LIMIT 10;

-- 輸出關鍵實際值 (TREE 格式)
-> Limit: 10 row(s)  (actual time=0.5..1.2 rows=10 loops=1)
    -> Sort: orders.created_at DESC  (actual time=0.5..1.1 rows=1000 loops=1)
        -> Filter: (orders.user_id = 123)  (actual time=0.1..0.8 rows=1000 loops=1)
            -> Index range scan on orders using idx_user_id  (actual time=0.05..0.3 rows=1000 loops=1)
                (cost=100 rows=1000)

核心優勢：actual time (啟動/結束毫秒)、actual rows (實際行數)、loops (迴圈次數)、cost (優化器成本)。**直接對比 estimated rows vs actual rows 發現統計資訊偏差**。

六、實戰判讀邏輯與優化決策樹

步驟 1：看 type → 定性

• ALL → **全表掃描**，必須加索引或改寫 SQL。
• index → **全索引掃描**，若非覆蓋索引則需加 WHERE 條件限制範圍。
• range/ref/eq_ref/const → **索引生效**，進入步驟 2。

步驟 2：看 key/key_len/ref → 定準

• key=NULL → 索引未命中，檢查隱式轉型、函數包裹、最左前綴違反。
• key_len 過短 → 複合索引未用全欄，檢查 WHERE/JOIN/ORDER BY 順序。
• ref 為 func 或 NULL → 可能有函數/運算導致索引失效。

步驟 3：看 rows/filtered → 定量

• rows 巨大 (>10萬) → 掃描成本高，需縮小範圍或分區。
• filtered 低 (<10%) → 過濾性差，考慮複合索引包含過濾欄位。

步驟 4：看 Extra → 定紅綠燈

• 🔴 Using filesort / Using temporary / Using join buffer → **立即優化**，參考對照表方向。
• 🟢 Using index / Using index condition / Not exists → **良好模式**，保持。
• 🟡 Using where → 常態，若配合 ref 且 rows 小可接受。

步驟 5：多表 JOIN 看 id/table 順序 → 定驅動表

• id 相同、table 順序 = JOIN 順序 (驅動表 → 被驅動表)。
• 驅動表應為**結果集最小、過濾性最強**的表 (通常有高選擇性 WHERE 條件)。
• 被驅動表**必須有 JOIN 條件索引** (ref/eq_ref)。

七、常見誤區與避坑

八、總結：EXPLAIN 速查卡片

┌─────────────────────────────────────────────────────────────────┐
│ EXPLAIN 判讀三步走                                               │
├─────────────────────────────────────────────────────────────────┤
│ 1️⃣ type:  是否 ALL/index?  → 是 → 加索引/改寫                   │
│ 2️⃣ key:     是否 NULL?      → 是 → 檢查隱式轉型/函數/最左前綴   │
│ 3️⃣ Extra:   是否 🔴 filesort/temporary/join_buffer? → 是 → 優化 │
│         是否 🟢 Using index? → 否 → 嘗試覆蓋索引                 │
├─────────────────────────────────────────────────────────────────┤
│ 關鍵公式: 總成本 ≈ Σ(rows_i × filtered_i/100)                   │
│ 目標: type ≥ ref, key ≠ NULL, rows 最小, Extra 綠燈             │
└─────────────────────────────────────────────────────────────────┘