MySQL EXPLAIN 深度解析：從輸出列到執行計劃優化的實戰指南

EXPLAIN 基礎用法

-- 基本語法
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 扩展信息 (MySQL 5.6+)
EXPLAIN EXTENDED SELECT * FROM users WHERE status = 1;
SHOW WARNINGS;  -- 查看優化後的查詢重寫

-- 格式化輸出 (MySQL 5.7+)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 1;
-- 或
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE status = 1;  -- 8.0.16+

輸出列詳細解讀

type 訪問類型性能分級（從優到劣）

1. system：表僅 1 行（系統表），const 特例
2. const：主鍵/唯一索引查找常數，WHERE pk = 1，最快
3. eq_ref：聯合查詢中，前表每行在後表匹配**唯一行**（主鍵/唯一索引），最優 JOIN
4. ref：非唯一索引查找，或唯一索引**前綴**查找，WHERE idx_col = const
5. ref_or_null：ref + NULL 值查找，WHERE idx_col = const OR idx_col IS NULL
6. index_merge：索引合併 (Intersection/Union/Sort-Union)，多索引掃描合併
7. range：索引範圍掃描，>, <, >=, <=, IN(), BETWEEN
8. index：全索引掃描 (掃描索引樹)，比 ALL 快（索引通常小於數據）
9. ALL：全表掃描，**最差**，大表必須優化

Extra 關鍵值深度解析

其他 Extra 值

• Distinct：找到第一匹配行後停止（LIMIT 1 效果）
• Not exists：LEFT JOIN 優化，匹配到即停止
• Range checked for each record (index map: N)：無好索引，每行嘗試選索引，**極慢**
• Impossible WHERE：WHERE 條件永假，優化器直接返回空
• Select tables optimized away：聚合函數優化，如 COUNT(*) MyISAM 直接讀統計

實戰優化案例

案例 1：消除 Using filesort

-- 原查詢
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY created_at DESC;
-- type: ref, key: idx_user_id, Extra: Using where; Using filesort

-- 優化：創建聯合索引 (where列, order by列)
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- 再次 EXPLAIN
-- type: ref, key: idx_user_created, Extra: Using where  (filesort 消失！)

案例 2：實現覆蓋索引

-- 原查詢
EXPLAIN SELECT id, name, email FROM users WHERE status = 1;
-- type: ref, key: idx_status, Extra: Using where  (需回表查 name, email)

-- 優化：聯合索引包含 SELECT 列
ALTER TABLE users ADD INDEX idx_status_name_email (status, name, email);

-- 再次 EXPLAIN
-- type: ref, key: idx_status_name_email, Extra: Using index  (覆蓋索引！)

案例 3：優化 GROUP BY 臨時表

-- 原查詢
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: Using temporary; Using filesort

-- 優化：利用索引順序
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
-- 或強制使用索引
EXPLAIN SELECT user_id, COUNT(*) FROM orders FORCE INDEX (idx_user_id) GROUP BY user_id;
-- Extra: Using index (無 temporary/filesort)

JSON 格式輸出解析 (MySQL 5.7+)

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 1;

輸出包含：

• query_block：查詢塊信息
• table_access：表訪問詳情（成本估算、索引選擇理由）
• cost_info：read_cost, eval_cost, prefix_cost, data_read_per_join
• used_columns：實際使用列（判斷覆蓋索引）
• attached_condition：附加條件下推信息

優化器追蹤

SET SESSION optimizer_trace = 'enabled=on';
SELECT * FROM users WHERE status = 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 可見：索引評估成本、範圍優化器決策、JOIN 順序枚舉、條件下推細節

避坑指南

總結

EXPLAIN 是 SQL 調優的「聽診器」。核心讀懂三要素：

1. type：訪問路徑，**拒絕 ALL、index、range 過大**，追求 const/eq_ref/ref
2. key/key_len/ref：索引使用情況，**聯合索引前綴原則、覆蓋索引設計**
3. Extra：執行細節，**消除 Using temporary/filesort，爭取 Using index/index condition**

配合 FORMAT=JSON、optimizer_trace、Handler_read% 多維驗證，從「看懂計劃」進化到「預判優化器決策」，才是調優高手的必經之路。