MySQL EXPLAIN 完整指南 (上)：核心欄位解讀與執行順序

EXPLAIN 是 SQL 調優的核心工具。本文為三部分系列第一篇，深度解析 id、select_type、table、type 四大核心欄位。

基本語法

EXPLAIN SELECT * FROM t3 WHERE id = 3952602;
-- 或
EXPLAIN FORMAT=JSON SELECT ...  -- 8.0+ JSON 格式更詳盡

輸出欄位總覽

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

1. id：查詢執行順序標識

• 數字越大越先執行 (由內向外)
• 相同 id：從上到下順序執行
• NULL：結果集合併階段 (如 UNION RESULT)

巢狀子查詢執行順序範例

EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t3 WHERE id=3952602) a) b;

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1  | PRIMARY     |  | system | NULL              | NULL    | NULL    | NULL | 1    |       |
| 2  | DERIVED     |  | system | NULL              | NULL    | NULL    | NULL | 1    |       |
| 3  | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      | 1    |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

執行順序：id=3 → id=2 → id=1 (由內向外)

2. select_type：查詢類型分類

類型	含義	觸發場景
SIMPLE	簡單查詢，無 UNION/子查詢	SELECT * FROM t WHERE id=1
PRIMARY	最外層查詢	包含子查詢/UNION 的最外層 SELECT
UNION	UNION 中第 2 個及以後的 SELECT	SELECT 1 UNION SELECT 2
DEPENDENT UNION	UNION 子查詢依賴外部查詢	SELECT * FROM t WHERE id IN (SELECT ... UNION SELECT ...)
UNION RESULT	UNION 合併結果集	id 為 NULL 的行
SUBQUERY	子查詢中的第一個 SELECT	SELECT * FROM t WHERE id = (SELECT ...)
DEPENDENT SUBQUERY	子查詢依賴外部查詢 (相關子查詢)	SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE t2.x = t1.x)
DERIVED	衍生表 (FROM 子句子查詢)	SELECT * FROM (SELECT * FROM t) a
MATERIALIZED	物化子查詢 (5.7+ 優化)	IN/EXISTS 子查詢被物化為臨時表
UNCACHEABLE SUBQUERY	不可緩存的子查詢 (含不確定函數)	WHERE id = (SELECT UUID())

各類型實戰範例

UNION / UNION RESULT

EXPLAIN SELECT * FROM t3 WHERE id=1 UNION ALL SELECT * FROM t3;
+----+--------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type  | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+--------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1  | PRIMARY      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const | 1    |       |
| 2  | UNION        | t3    | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       |
| NULL| UNION RESULT | | ALL | NULL              | NULL    | NULL    | NULL  | NULL |       |
+----+--------------+-------+-------+-------------------+---------+---------+-------+------+-------+

DEPENDENT SUBQUERY (相關子查詢，效能殺手)

EXPLAIN SELECT id FROM t3 WHERE id IN (SELECT id FROM t3 WHERE id=1);
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type        | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra                    |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
| 1  | PRIMARY            | t3    | index | NULL              | PRIMARY | 4       | NULL  | 1000 | Using where; Using index |
| 2  | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const | 1    | Using index              |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+

優化：改寫為 JOIN 或 EXISTS，避免逐行執行子查詢。

DERIVED (衍生表)

EXPLAIN SELECT * FROM (SELECT * FROM t3 WHERE id=1) a;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1  | PRIMARY     |  | system | NULL              | NULL    | NULL    | NULL | 1    |       |
| 2  | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      | 1    |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

5.7+ 會嘗試將 DERIVED 合併到外層 (derived_merge)，8.0 預設開啟。

3. table：涉及的表/衍生表

• 實體表名：t3
• 衍生表：<derivedN> (N 為 id)
• UNION 結果：<unionM,N>
• 子查詢快取：<subqueryN>

4. type：連接類型/存取策略 (最關鍵欄位)

從最優到最差：

type	含義	典型場景	效能
system	表僅一行 (system table)	常數表	⭐⭐⭐⭐⭐
const	主鍵/唯一索引等值查詢，最多一行	WHERE id=1	⭐⭐⭐⭐⭐
eq_ref	唯一索引掃描，JOIN 時每行匹配一行	PK/FK 關聯	⭐⭐⭐⭐
ref	非唯一索引等值查詢	WHERE idx_col=1	⭐⭐⭐
ref_or_null	ref + NULL 值查詢	WHERE col=1 OR col IS NULL	⭐⭐⭐
index_merge	索引合併 (多索引 OR 條件)	WHERE a=1 OR b=2	⭐⭐
unique_subquery	IN 子查詢主鍵查詢	WHERE id IN (SELECT pk FROM ...)	⭐⭐⭐
index_subquery	IN 子查詢索引查詢	WHERE col IN (SELECT idx_col FROM ...)	⭐⭐
range	索引範圍掃描	>, >=, <, <=, BETWEEN, IN	⭐⭐
index	全索引掃描 (不讀數據文件)	SELECT idx_col FROM t	⭐
ALL	全表掃描 (最差)	無索引或索引失效	⭐ (避免)

type 關鍵判讀

• 目標：至少達到 ref 級別，最好 eq_ref/const
• ALL = 全表掃描，大表必須優化
• index = 掃描全索引，比 ALL 略好但仍慢
• range = 範圍查詢，視範圍大小決定效能

下篇預告

下一篇將深度解析 possible_keys、key、key_len、ref、rows、Extra 欄位，以及 JSON 格式輸出解讀。