EXPLAIN 深度解剖：从 type=ALL 到 Using index，彻底读懂执行计划

EXPLAIN 完全指南：一张图看懂 MySQL 执行计划

摘要：DBA 丢给你一个慢查询，你盯着 EXPLAIN 输出的一堆表格发呆——type=ALL 是什么意思？Extra 里的 Using filesort 严重吗？key_len=303 代表了什么？本文把 EXPLAIN 的每一列、每一个 Extra 提示都翻译成"人话"，配上大量图解和速查表，让你真正看懂执行计划，优化 SQL 不再盲目。

一、为什么需要 EXPLAIN？

MySQL 查询优化器会根据成本和规则生成一个执行计划（Execution Plan），决定：

• 用哪个索引？
• 怎么访问表？
• 多表连接的顺序是什么？

但优化器不是万能的，它有时也会选错索引。EXPLAIN 就是让你"偷看"优化器底牌的工具。

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

+—-+————-+——-+————+——+—————+———-+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+———-+———+——-+——+———-+——-+
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL |
+—-+————-+——-+————+——+—————+———-+———+——-+——+———-+——-+

这一行输出代表了对 s1 表的访问计划。接下来我们就逐列拆解。

二、EXPLAIN 输出全景图

先给一个"地图"，知道每一列是干嘛的：

EXPLAIN 输出就像一张"体检报告"

+——–+——————————————+
| type | 心脏（最关键）- 访问方法好坏决定生死 |
+——–+——————————————+
| key | 大脑 – 有没有用上索引 |
+——–+——————————————+
| rows | 体重 – 扫描行数越少越好 |
+——–+——————————————+
| Extra | 体检备注 – 有没有(filesort/temporary) |
+——–+——————————————+

三、id & select_type：谁在查？查什么？

3.1 table 列

EXPLAIN 的每一行记录都对应某个单表的访问方法。

— 单表查询：只有 1 行
EXPLAIN SELECT * FROM s1;
— table = s1

— 连接查询：2 行，s1 在前是驱动表，s2 在后是被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
— table = s1, s2

3.2 id 列

每个 SELECT 关键字对应一个唯一的 id。

连接查询中的 id：

SELECT * FROM s1 INNER JOIN s2;
|
+– id 都为 1（同一个 SELECT 里的多个表）
第1行 table=s1（驱动表）
第2行 table=s2（被驱动表）

子查询中的 id：

SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
|
+– 外层 SELECT id=1, table=s1
+– 子查询 id=2, table=s2

UNION 中的 id：

SELECT * FROM s1 UNION SELECT * FROM s2;
|
+– 第1个 SELECT id=1, table=s1
+– 第2个 SELECT id=2, table=s2
+– UNION 去重 id=NULL, table=<union1,2>（临时表）

注意：如果优化器把子查询改写成了连接查询，id 可能全部相同。

3.3 select_type 列

select_type 告诉你这行记录在整次查询中扮演什么角色：

select_type 关系图：

大查询
|
+– PRIMARY（最外层）
| +– table = s1
|
+– SUBQUERY（不相关子查询）
| +– 物化执行，只执行1次
| +– table = s2
|
+– DEPENDENT SUBQUERY（相关子查询）
| +– 依赖外层值，可能执行N次
| +– table = s2
|
+– UNION RESULT（去重临时表）
+– table = <union1,2>

实战要点：

• 看到 DEPENDENT SUBQUERY 要警惕——子查询可能被执行很多次
• 看到 MATERIALIZED 说明子查询被物化成了临时表
• 看到 DERIVED 说明 FROM 子查询被物化了

四、type：这是最重要的一列

type 列表示 MySQL 访问单表的方法。以下按从快到慢排列：

访问方法速度天梯：

system → 最快，表只有1条记录（MyISAM/Memory）
|
const → 主键/唯一索引等值匹配（最多1条）
|
eq_ref → 连接中，被驱动表用主键/唯一索引等值匹配
|
ref → 普通二级索引等值匹配
|
ref_or_null → ref + 还要找 NULL
|
index_merge → 多个索引合并（Intersection/Union）
|
unique_subquery → IN 子查询用主键等值匹配
|
index_subquery → IN 子查询用普通索引等值匹配
|
range → 索引范围扫描
|
index → 遍历整个二级索引（覆盖索引）
|
ALL → 全表扫描（最慢）
|
v
性能越来越差，ALL 是噩梦

4.1 优秀区（system/const/eq_ref/ref）

— const：主键等值匹配
EXPLAIN SELECT * FROM s1 WHERE id = 5;
— type=const, rows=1

— eq_ref：连接中被驱动表用主键匹配
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
— s1: type=ALL（驱动表）
— s2: type=eq_ref（被驱动表用主键等值匹配）

— ref：普通索引等值匹配
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
— type=ref, key=idx_key1

4.2 及格区（range/index_merge）

— range：索引范围查询
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
— type=range

— index_merge：多个索引取并集
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
— type=index_merge, Extra=Using union(idx_key1,idx_key3)

4.3 警戒区（index/ALL）

— index：遍历二级索引全部记录（覆盖索引时）
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
— type=index, key=idx_key_part
— 虽然遍历索引，但至少比全表扫描快

— ALL：全表扫描！需要优化！
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
— type=ALL, possible_keys=NULL
— 说明 common_field 没有索引，只能扫全表

type 优化建议：

看到 type=ALL：
→ 给 WHERE 条件列加索引
→ 检查是否 SELECT * 导致无法覆盖索引

看到 type=index：
→ 检查是否可以用更好的索引变成 range/ref
→ 确认是否真的没有更好的选择

争取做到：
等值查询 → const/ref
范围查询 → range
连接查询 → eq_ref/ref

五、key 系列：索引用上了吗？

5.1 possible_keys vs key

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
— possible_keys = idx_key1, idx_key3
— key = idx_key3 ← 优化器选择了 idx_key3
— 因为等值匹配通常比范围匹配成本更低

5.2 key_len：用了几个索引列？

key_len 表示使用的索引记录最大长度，它的计算规则：

key_len 计算规则：

定长类型（如 INT）：
INT NOT NULL → 4 字节
INT NULL → 5 字节（多1字节标记NULL）

变长类型（如 VARCHAR(100) utf8）：
最大存储空间 = 100 × 3 = 300 字节
允许 NULL → +1 字节
变长字段 → +2 字节（存储实际长度）
总计 = 303 字节

key_len 最重要的作用：判断联合索引用了几个列

— 联合索引 idx_key_part(key_part1, key_part2, key_part3)

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
— key_len = 303 → 只用了第1列

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
— key_len = 606 → 用了第1、2列

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
— key_len = 909 → 用了全部3列

联合索引使用示意图：

idx_key_part (key_part1, key_part2, key_part3)
+———-+———-+———-+
| 列1 | 列2 | 列3 |
+———-+———-+———-+
| | |
v v v
WHERE key_part1='a' → key_len=303（用了1列）
|
v
WHERE key_part1='a' AND key_part2='b' → key_len=606（用了2列）
|
v
WHERE … AND key_part3='c' → key_len=909（用了3列）

5.3 ref：和谁做等值匹配？

当 type 为 const/eq_ref/ref/ref_or_null 时，ref 列展示等值匹配的对象：

— ref = const
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

— ref = xiaohaizi.s1.id（连接查询）
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

— ref = func（函数）
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

六、rows & filtered：要扫多少行？

6.1 rows：预估扫描行数

rows 是优化器估算的扫描行数：

• 全表扫描时 = 表中估计的总行数
• 索引查询时 = 索引范围区间内的估计行数

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
— rows = 266
— 说明优化器估计满足 key1>'z' 的记录约 266 条

rows 越小越好。如果 rows 很大（比如上万），而实际只需要几条记录，说明索引选择可能有问题。

6.2 filtered：过滤比例

filtered 表示在 rows 条记录中，有多少比例满足其余的条件。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
— rows = 266（满足 key1>'z' 的估计有266条）
— filtered = 10.00
— 意思是：在这266条中，估计只有 10% 满足 common_field='a'
— 最终大约 266 × 10% = 26.6 条记录

filtered 在连接查询中的作用：

驱动表 s1：
rows = 9688（全表扫描）
filtered = 10.00
→ 扇出 = 9688 × 10% = 968.8 ≈ 969 次被驱动表访问

被驱动表 s2：
要被访问约 969 次！这就是为什么被驱动表一定要有索引。

七、Extra：藏在细节里的优化密码

Extra 列是"体检备注"，包含了很多关键的优化信息。

7.1 看到就开心的（好事）

Using index（覆盖索引）

EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
— Extra = Using index
— 查询列表和条件都只涉及 key1 列，idx_key1 索引已经包含了所有需要的数据

覆盖索引 vs 非覆盖索引：

覆盖索引（Using index）：
idx_key1 B+ 树
+———-+
| key1='a' | → 直接返回，不需要回表！
+———-+

非覆盖索引：
idx_key1 B+ 树 聚簇索引
+———-+ +———-+
| key1='a' | –回表–> | 完整记录 |
+———-+ +———-+

Using index condition（索引条件下推 ICP）

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
— Extra = Using index condition

正常情况下，key1 LIKE '%b'（通配符开头）不能走索引。但有了索引条件下推：

有无 ICP 的对比：

无 ICP（老版本）：
1. 找到 key1>'z' 的二级索引记录
2. 立刻回表 → 把完整记录给 server 层
3. server 层判断 LIKE '%b' 是否成立
问题：不符合条件的记录也回表了，浪费 I/O

有 ICP（新版本）：
1. 找到 key1>'z' 的二级索引记录
2. 在存储引擎层先判断 key1 LIKE '%b' 是否成立
3. 不成立 → 直接跳过，不回表！
4. 成立 → 再回表
好处：减少大量回表 I/O

7.2 需要警惕的（坏事）

Using filesort（文件排序）

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
— Extra = Using filesort
— common_field 没有索引，只能把数据读到内存/磁盘再排序

文件排序原理：

没有索引时排序：
1. 读取所有符合条件的记录
2. 在内存（或磁盘临时文件）中按 ORDER BY 列排序
3. 返回排序后的结果

内存足够 → 内存排序（快一些）
数据太多 → 磁盘排序（非常慢）

优化方案：
→ 给 ORDER BY 列加索引
→ 或让 ORDER BY 的列恰好是查询索引的最左列

Using temporary（使用临时表）

EXPLAIN SELECT DISTINCT common_field FROM s1;
— Extra = Using temporary

EXPLAIN SELECT common_field, COUNT(*) FROM s1 GROUP BY common_field;
— Extra = Using temporary; Using filesort

临时表产生场景：

DISTINCT：需要临时表去重
GROUP BY：需要临时表分组
UNION： 需要临时表合并去重（UNION ALL 不需要）

优化方案：
→ 给 DISTINCT/GROUP BY 列加索引
→ 用 UNION ALL 代替 UNION（如果允许重复）
→ GROUP BY 时加上 ORDER BY NULL 避免 filesort

优化 GROUP BY 示例：

— 默认会排序，导致 Using filesort
EXPLAIN SELECT common_field, COUNT(*) FROM s1 GROUP BY common_field;
— Extra = Using temporary; Using filesort

— 显式取消排序，只保留 temporary
EXPLAIN SELECT common_field, COUNT(*) FROM s1 GROUP BY common_field ORDER BY NULL;
— Extra = Using temporary

— 更优：给 GROUP BY 列加索引
EXPLAIN SELECT key1, COUNT(*) FROM s1 GROUP BY key1;
— Extra = Using index（索引直接搞定，无需临时表）

7.3 连接查询相关的

Using join buffer

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
— s2 的 Extra = Using where; Using join buffer (Block Nested Loop)
— 说明 s2.common_field 没有索引，MySQL 用内存块缓存驱动表记录来减少被驱动表扫描

7.4 索引合并相关的

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND key3 = 'a';
— Extra = Using intersect(idx_key3,idx_key1); Using where

7.5 子查询优化相关的

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
— s2: Extra = Using where; Start temporary
— s1: Extra = End temporary

7.6 其他值得注意的

Using where 的两种情况：

情况1：全表扫描时的过滤
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
— type=ALL, Extra=Using where
— 所有记录读到 server 层，逐条判断 common_field='a'

情况2：索引查询后还需要过滤其他条件
EXPLAIN SELECT * FROM s1 WHERE key1='a' AND common_field='a';
— type=ref, Extra=Using where
— 用 idx_key1 找到记录后回表，在 server 层判断 common_field='a'

八、JSON 格式执行计划：看见成本数字

标准 EXPLAIN 看不到成本，加上 FORMAT=JSON 就能看到优化器的"账单"。

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\\G

{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3197.16" // ← 整个查询的总成本
},
"nested_loop": [
{
"table": {
"table_name": "s1",
"access_type": "ALL", // 全表扫描
"rows_examined_per_scan": 9688, // 一次扫描约 9688 行
"rows_produced_per_join": 968, // 扇出约 968 条
"filtered": "10.00",
"cost_info": {
"read_cost": "1840.84", // I/O 成本 + 部分 CPU 成本
"eval_cost": "193.76", // 检测记录成本
"prefix_cost": "2034.60" // ← 单表查询 s1 的总成本
}
}
},
{
"table": {
"table_name": "s2",
"access_type": "ref", // 索引等值匹配
"key": "idx_key2",
"rows_examined_per_scan": 1,
"cost_info": {
"prefix_cost": "3197.16" // ← 整个连接查询的总成本
}
}
}
]
}
}

JSON 执行计划中的成本计算：

s1 表成本：
read_cost + eval_cost = prefix_cost
1840.84 + 193.76 = 2034.60

s2 表成本（被驱动表，访问多次）：
prefix_cost 包含：s1 单次成本 + s1扇出 × s2单次成本
2034.60 + (968 × 1.2) ≈ 3197.16

重点看 prefix_cost：

• 驱动表的 prefix_cost = 该表单次访问成本
• 被驱动表的 prefix_cost = 整个连接查询的累计成本

九、SHOW WARNINGS：优化器重写了什么？

执行完 EXPLAIN 后，紧接着执行 SHOW WARNINGS，可以看到优化器重写完的 SQL：

EXPLAIN SELECT * FROM s1 LEFT JOIN s2
ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

SHOW WARNINGS\\G

Message: /* select#1 */
select `s1`.`key1`, `s2`.`key1`
from `xiaohaizi`.`s1` join `xiaohaizi`.`s2` — ← LEFT JOIN 变成了 JOIN！
where ((`s1`.`key1` = `s2`.`key1`)
and (`s2`.`common_field` is not null))

SHOW WARNINGS 的价值：

你写的 SQL 优化器实际执行的 SQL
————- ———————
LEFT JOIN → JOIN（外连接被消除）
IN (子查询) → SEMI JOIN / 物化表连接
各种条件 → 常量传递、等值传递后的简化版

注意：Message 里的语句是"类似"重写后的结果，
不是标准 SQL，不能直接执行，仅供参考。

十、EXPLAIN 实战速查手册

10.1 type 优化优先级

看到 type = ? 时的反应：

system / const / eq_ref → 优秀，保持！
ref / ref_or_null → 良好，可以接受
range → 中等，看 rows 大小
index → 警戒，检查是否能优化为 range
ALL → 危险！必须优化！

10.2 Extra 危险信号速查

10.3 连接查询检查清单

连接查询优化 checklist：

□ 被驱动表的连接列是否有索引？（争取 eq_ref/ref）
□ 驱动表的 rows × filtered 是否过大？
□ 是否出现了 Using join buffer？（被驱动表没索引）
□ 是否可以外转内？（WHERE 有被驱动表的非空条件）

10.4 完整 EXPLAIN 分析流程

拿到 EXPLAIN 结果后的分析步骤：

Step 1: 看 type
→ ALL？立刻考虑加索引
→ index？检查是否能用更好的索引

Step 2: 看 key
→ NULL？没用索引，找原因
→ 有值？确认是不是期望的索引

Step 3: 看 key_len
→ 联合索引时，判断是否用了足够的列

Step 4: 看 rows
→ 数值是否和预期差距很大？
→ 连接查询中驱动表 rows × filtered 是否太大？

Step 5: 看 Extra
→ Using filesort？优化排序
→ Using temporary？优化 GROUP BY/DISTINCT
→ Using index？好事，覆盖索引生效
→ Using index condition？好事，ICP 生效

延伸阅读：

• MySQL 官方文档：EXPLAIN Output Format
• 《高性能 MySQL》第 5 章：创建高性能的索引
• 本文配套博客：MySQL 查询优化器的双重人格：成本计算与查询重写