第 17 章 EXPLAIN执行计划详解

📖 本篇简介：EXPLAIN 是 MySQL 给开发者的"查询说明书"——它告诉你一条 SQL 会怎么被执行、走没走索引、扫了多少行。本篇假设你已经会写多表 JOIN、知道索引的基本概念。学完之后，你将能读懂 EXPLAIN 输出的每一列、判断一条 SQL 是好是坏、定位慢查询的瓶颈。本示例基于 MySQL 8.0。

目录

• 1 EXPLAIN 的作用
• 2 输出列总览
• 3 id 与 select_type
• 4 table 与 type 访问类型
• 5 possible_keys key key_len
• 6 rows 与 filtered
• 7 Extra 额外信息
• 8 真实案例解读
• 9 执行计划速查表
• 10 常见坑与注意事项
• 11 练习题

1 EXPLAIN 的作用

一条 SQL 在你按下回车之前，MySQL 的优化器会先想好"怎么执行最快"。但优化器的想法你看不见，就只能靠 EXPLAIN 让它把执行计划亮出来。

-- 本示例基于 MySQL 8.0
-- 在任意 SELECT 前面加 EXPLAIN，就不会真正执行查询，只展示计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- MySQL 8.0 还支持 EXPLAIN ANALYZE，真正执行并给出每一步耗时
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

看到 EXPLAIN 的输出，你要回答三个问题：

1. 走索引了吗？（看 key 列）

2. 扫了多少行？（看 rows 列）

3. 有没有额外的临时表/文件排序？（看 Extra 列）

这三个问题对应着慢查询最常见的三个病因：没索引、扫描行数太大、排序分组没走索引。后面几节会逐列拆开讲，看完你就能对着一张执行计划表说出"这条 SQL 慢在哪、该怎么改"。

2 输出列总览

在 8.0 里，EXPLAIN 后面可以接 FORMAT=TRADITIONAL（默认表格）、FORMAT=JSON、FORMAT=TREE。无论哪种格式，核心信息都是那 12 列，只是展示形式不同。

EXPLAIN 默认输出 12 列，每一列都重要：

| 列名 | 含义 | 重点看什么 |

|---|---|---|

| id | 查询序号 | 越大越先执行 |

| select_type | 查询类型 | SIMPLE/PRIMARY/SUBQUERY 等 |

| table | 这一行查的是哪张表 | - |

| type | 访问类型 | 最重要，性能好坏看它 |

| possible_keys | 可能用到的索引 | 候选 |

| key | 实际用的索引 | 最重要 |

| key_len | 用了索引多少字节 | 判断联合索引用了几列 |

| ref | 索引比较的来源 | 常量/哪个字段 |

| rows | 预估扫描行数 | 越小越好 |

| filtered | 过滤后的百分比 | - |

| Extra | 额外信息 | 看是否有 Using filesort/temporary |

示例输出（简化）：
id  select_type  table   type   possible_keys  key        key_len  rows   Extra
1   SIMPLE       orders  ref    idx_user       idx_user   5        12     Using where

3 id 与 select_type

• id：每个 SELECT 的编号。id 不同时，id 大的先执行；id 相同时，从上往下执行。
• select_type：查询的类型。

| select_type | 含义 |

|---|---|

| SIMPLE | 简单查询，不含子查询和 UNION |

| PRIMARY | 复杂查询里最外层的那个 SELECT |

| SUBQUERY | 子查询里的第一个 SELECT |

| DERIVED | 派生表（FROM 子句里的子查询） |

| UNION | UNION 里的第二个及以后 SELECT |

| UNION RESULT | UNION 的结果集 |

实际看执行计划时，先看 id 列判断执行顺序，再看 select_type 理解这条 SQL 的结构。多表 JOIN 时往往会出现多行，每一行代表一张表的访问方式，要从上到下读一遍。id 越大越先执行，id 相同则按出现顺序执行。

-- 这条 SQL 的 EXPLAIN 里会出现 PRIMARY + DERIVED
EXPLAIN SELECT * FROM (SELECT user_id, COUNT(*) c FROM orders GROUP BY user_id) t
WHERE t.c > 10;

4 table 与 type 访问类型

type 列是判断查询性能的核心指标，从好到差依次是：

| type | 含义 | 性能 |

|---|---|---|

| system | 表只有一行 | 最好 |

| const | 主键/唯一索引等值匹配，最多一行 | 很好 |

| eq_ref | JOIN 时用主键/唯一索引匹配 | 很好 |

| ref | 普通索引等值匹配 | 好 |

| range | 索引范围扫描（>、<、IN、BETWEEN） | 一般 |

| index | 扫整棵索引树 | 差 |

| ALL | 全表扫描 | 最差，必须优化 |

经验法则：至少要达到 range，最好 ref/const；看到 ALL 就要警惕，看到 index 也要想办法优化。

把 type 列当成一个信号灯：绿（const/ref/range）可以放行，黄（index）需要观察，红（ALL）必须立刻处理。DBA review 慢 SQL 时，第一件事就是把所有 type=ALL 的语句挑出来。

-- const：主键等值查
EXPLAIN SELECT * FROM orders WHERE id = 12345;

-- ref：普通索引等值查
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- range：索引范围查
EXPLAIN SELECT * FROM orders WHERE create_time > '2026-01-01';

-- ALL：没用上索引，全表扫（user_id 没建索引时）
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

5 possible_keys key key_len

• possible_keys：优化器认为"可能能用"的索引列表。
• key：优化器"实际选"的索引。NULL 表示没走索引。
• key_len：这条查询用了索引的前多少字节，用来判断联合索引用了几列。

-- 假设有联合索引 idx_user_status(user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;
-- key_len 较大：两列都用上了

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- key_len 较小：只用了第一列 user_id（最左前缀）

key_len 怎么估算：

| 字段类型 | 字节数 |

|---|---|

| BIGINT | 8 |

| INT | 4 |

| VARCHAR(n) utf8mb4 | n*4 + 2（变长） |

| CHAR(n) utf8mb4 | n*4 |

| 可 NULL | 多 1 字节 |

6 rows 与 filtered

• rows：优化器预估这条查询要扫多少行。越小越好。
• filtered：扫描的行里，有多少百分比能满足 WHERE 条件。

rows × filtered% 大致是最终需要回表处理的行数。如果 rows 是 100 万、filtered 是 1%，那就要回表处理 1 万行，性能就差。

实际调优时，这两列要和 key 列一起看：走了索引但 rows 巨大，说明索引选择性差（比如索引列大部分值都相同）；rows 小但 filtered 极低，说明索引只过滤了一部分，剩下的还要在 server 层慢慢筛。

7 Extra 额外信息

Extra 列里出现的关键字决定了这条 SQL 是"清爽"还是"有坑"：

| Extra | 含义 | 好坏 |

|---|---|---|

| Using index | 覆盖索引，不用回表 | 好 |

| Using where | 服务器层额外过滤 | 一般 |

| Using index condition | 索引下推 ICP | 较好 |

| Using filesort | 额外排序 | 要优化 |

| Using temporary | 用了临时表 | 要优化 |

| Using join buffer | JOIN 没走索引 | 要优化 |

| Impossible WHERE | WHERE 恒假 | - |

-- 出现 Using filesort：ORDER BY 没走索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1 ORDER BY create_time DESC;

-- 出现 Using temporary：GROUP BY 没走索引，建了临时表
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;

8 真实案例解读

假设有表 orders(id, user_id, status, amount, create_time)，索引 idx_user(user_id)、idx_status(status)。

-- 案例 1：走索引的好查询
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- type=ref, key=idx_user, rows=20, Extra=Using where
-- 解读：走了 user_id 索引，扫 20 行，OK

-- 案例 2：没走索引的坏查询
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- type=ALL, key=NULL, rows=1000000
-- 解读：全表扫 100 万行，必须给 amount 建索引或改写 SQL

-- 案例 3：联合索引最左前缀
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 2;
-- 假设有 idx_user_status(user_id, status)：type=ref, key=idx_user_status
-- 如果 WHERE 只写 status = 2：key=NULL（最左前缀不满足）

下面用 Python + sqlite3 演示 EXPLAIN 的输出结构（sqlite3 的 EXPLAIN 与 MySQL 列不同，但能看到"执行计划是一段结构化计划"这个本质），真实输出贴出：

# 用 sqlite3 的 EXPLAIN QUERY PLAN 看执行计划结构
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t(id INT PRIMARY KEY, a INT, b INT)")
conn.execute("CREATE INDEX idx_a ON t(a)")

# 走索引
for row in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM t WHERE a = 5"):
    print(row)
print("---")
# 全表扫
for row in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM t WHERE b = 5"):
    print(row)

真实运行输出（Python 3.14 实跑）：

(2, 0, 0, 'SEARCH t USING INDEX idx_a (a=?)')
(0, 0, 0, 'SEARCH t USING INDEX idx_a (a=?)')
---
(0, 0, 0, 'SCAN t')

可以看到：sqlite3 直接告诉我们 a=5 走了 idx_a，而 b=5 是 SCAN t 全表扫——和 MySQL EXPLAIN 的判断逻辑一致。

8.1 FORMAT=TREE 的直观输出

MySQL 8.0 支持 FORMAT=TREE，把执行计划画成一棵树，比表格更直观：

EXPLAIN FORMAT=TREE
SELECT o.id, u.name
FROM orders o JOIN user u ON o.user_id = u.id
WHERE o.status = 1;

典型输出长这样（示意）：

-> Nested loop inner join  (cost=...)
    -> Filter: (o.status = 1)  (cost=...)
        -> Index scan on o using idx_status
    -> Single-row index lookup on u using PRIMARY (id=o.user_id)

从这棵树能一眼看出：先按 status 索引扫 orders，再对每行用主键回 user 表。这就是 JOIN 的执行过程。

8.2 完整案例：一条慢查询的优化前后

原始 SQL：查某用户最近的订单，带用户昵称。

-- 慢 SQL：orders 表 100 万行，user_id 无索引
EXPLAIN SELECT o.*, u.name
FROM orders o JOIN user u ON o.user_id = u.id
WHERE u.name = '张三';

优化前 EXPLAIN：orders 全表扫（type=ALL，rows=100万），再 JOIN user。优化方法：给 orders.user_id 建索引。

-- 优化：给 JOIN 字段建索引
ALTER TABLE orders ADD INDEX idx_userid (user_id);

-- 优化后 EXPLAIN：orders 走 idx_userid（type=ref，rows=20），user 走主键
EXPLAIN SELECT o.*, u.name
FROM orders o JOIN user u ON o.user_id = u.id
WHERE o.user_id IN (SELECT id FROM user WHERE name='张三');

优化前后扫描行数从 100 万降到 20，性能提升几个数量级。

8.3 type=ref 与 eq_ref 的区别

两者都是索引等值匹配，区别在 JOIN 场景：

| type | 出现场景 | 含义 |

|---|---|---|

| eq_ref | 被驱动表 JOIN | 驱动表的每行，被驱动表用主键/唯一索引精确匹配一行 |

| ref | 普通索引等值 | 普通二级索引匹配，可能匹配多行 |

eq_ref 比 ref 更好，因为被驱动表每次只查一行。

9 执行计划速查表

| type 值 | 从好到差 | 结论 |

|---|---|---|

| const / eq_ref | ★★★★★ | 完美 |

| ref | ★★★★ | 好 |

| range | ★★★ | 可以接受 |

| index | ★★ | 警惕 |

| ALL | ★ | 必须优化 |

| Extra 关键字 | 含义 | 要不要优化 |

|---|---|---|

| Using index | 覆盖索引 | 不用 |

| Using where | 服务器过滤 | 一般不用 |

| Using index condition | 索引下推 | 不用 |

| Using filesort | 额外排序 | 要 |

| Using temporary | 临时表 | 要 |

| 现象 | 大概率原因 |

|---|---|

| key=NULL | 没索引或索引失效 |

| rows 巨大 | 没走索引或选择性差 |

| Using filesort | ORDER BY 列没走索引 |

| Using temporary | GROUP BY/DISTINCT 没走索引 |

9.1 EXPLAIN 的三种用法

| 写法 | 作用 | 是否真正执行 |

|---|---|---|

| EXPLAIN SELECT ... | 看执行计划 | 否 |

| EXPLAIN FORMAT=JSON SELECT ... | 输出 JSON，更详细（含代价） | 否 |

| EXPLAIN ANALYZE SELECT ... | 真正执行，给每步真实耗时 | 是（8.0.18+） |

-- JSON 格式能看到优化器估算的代价（cost_info）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1001;

-- 真正执行并打印每一步耗时（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

9.2 type 列的完整排序记忆法

按性能从好到差，一句话记忆：系统常量，等值范围，索引全表。

system > const > eq_ref > ref > range > index > ALL

看到 ref 以上基本放心；range 是及格线；index 和 ALL 要立刻动手优化。

9.3 key_len 的实战计算

表有 idx(a_int, b_varchar)，其中 a_int INT NOT NULL，b_varchar VARCHAR(10) utf8mb4：

• 只查 a_int = 1：key_len = 4（INT 4 字节，NOT NULL 不加 NULL 标记）；
• 查 a_int = 1 AND b_varchar = 'x'：key_len = 4 + 10*4 + 2 = 46（VARCHAR 变长要 2 字节长度）。

-- 用 key_len 验证最左前缀是否真的生效
EXPLAIN SELECT * FROM t WHERE a_int = 1 AND b_varchar = 'x';
-- 看 key_len，若 ≈46 说明两列都用上了；若只有 4 说明 b_varchar 没用上

9.4 用 EXPLAIN 反推优化方向

拿到一条慢 SQL，按这个顺序问自己：

1. type 是不是 ALL/index？是 → 缺索引或索引失效；

2. key 是不是 NULL？是 → 看 WHERE 字段有没有索引；

3. rows 是不是几十万上百万？是 → 索引选择性差或没覆盖；

4. Extra 有没有 filesort/temporary？有 → ORDER BY/GROUP BY 没走索引。

这四问基本能定位 80% 的慢查询。

剩下 20% 的疑难杂症，往往藏在 type 看似正常但 rows 异常大、或者优化器选错索引的情况。这时候配合 ANALYZE TABLE 刷新统计信息、甚至用 FORCE INDEX 强制走指定索引对比执行效果，才能彻底定位。

记住：EXPLAIN 是医生的听诊器，不是处方本身。它告诉你问题在哪，开什么药（建什么索引、怎么改 SQL）还要结合业务判断。

下一篇我们会把 EXPLAIN 的结论落到实处，专门讲索引怎么设计才能让执行计划越来越好。

10 常见坑与注意事项

坑 1：possible_keys 有值但 key=NULL。

说明有候选索引，但优化器算下来觉得走索引比全表扫还慢（比如表特别小，或索引选择性太差）。小表无所谓；大表出现这种情况要分析统计信息是否过期。

坑 2：rows 是预估值，不是精确值。

优化器基于统计信息估算，跟真实行数可能差很多。统计信息过期会导致选错索引，必要时 ANALYZE TABLE orders; 重新统计。

坑 3：EXPLAIN 不真正执行 SQL。

EXPLAIN SELECT ... 不会真的查数据，所以你看不到真实耗时。想看真实耗时，用 MySQL 8.0 的 EXPLAIN ANALYZE。

坑 4：把 type=index 当成"走了索引很安全"。

type=index 是整棵索引树扫描，比全表扫好不了多少（还要回表），尤其是联合索引的二级索引，扫一遍也是几十万行。看到它要想办法加 WHERE 条件改成 ref/range。

坑 5：JOIN 的 EXPLAIN 里看到 Using join buffer。

说明被驱动表的 JOIN 字段没索引，每次都全表扫一遍。这是经典慢查询来源，必须给被驱动表的 JOIN 字段建索引。

坑 6：只看 type 不看 rows。

有些查询 type 是 ref，但 rows 几十万（索引选择性差），一样慢。type 和 rows 要一起看：type 再好，rows 太大也不行。

坑 7：EXPLAIN 里出现了索引却没生效。

可能是优化器评估后主动放弃（key=NULL），也可能是加了 FORCE INDEX 强制。遇到"建了索引却不走"，先跑 ANALYZE TABLE 刷新统计信息再看。

11 练习题

下面这些题覆盖了本篇的核心列，建议先自己想答案再看折叠块里的参考答案。

练习 1：EXPLAIN 输出里 type=ALL 表示什么？为什么要避免？

<details>

<summary>参考答案</summary>

type=ALL 表示全表扫描，即逐行扫描整张表。表大时会扫几十万甚至几百万行，CPU 和 IO 都很高，是慢查询的头号来源。应给 WHERE/JOIN 字段建合适的索引，把 type 提升到 range/ref 以上。

</details>

练习 2：Extra 列出现 Using filesort 和 Using temporary 分别意味着什么？怎么优化？

<details>

<summary>参考答案</summary>

Using filesort：ORDER BY 的字段没用到索引，MySQL 额外做了一次排序。优化：给 ORDER BY 字段建索引，或让 WHERE 条件与 ORDER BY 共用联合索引。

Using temporary：GROUP BY/DISTINCT/UNION 用了临时表。优化：给 GROUP BY 字段建索引，让分组直接走有序索引，避免建临时表。

</details>

练习 3：key_len 有什么用？怎么用它判断联合索引用了几列？

<details>

<summary>参考答案</summary>

key_len 显示这条查询实际用了索引的前多少字节。联合索引 (a INT, b VARCHAR(10))：如果 WHERE 只用 a，key_len≈4；如果 a 和 b 都用，key_len≈4+10*4+2=46。通过对比 key_len 和索引总字节，就能判断是否用到了索引的全部列，验证最左前缀。

</details>

练习 4：一条 SQL 的 EXPLAIN 显示 rows=1000000，filtered=10，意味着什么？

<details>

<summary>参考答案</summary>

rows 是预估扫描行数 100 万，filtered=10 表示其中约 10%（10 万行）满足 WHERE 条件需要回表处理。这是典型的大扫描 + 低过滤，性能差，应该优化索引让 rows 降下来。

</details>

练习 5：possible_keys 有 idx_user，但 key=NULL，可能是什么原因？

<details>

<summary>参考答案</summary>

优化器评估后认为走这个索引比全表扫还慢，常见原因：表特别小（全表扫更快）；索引选择性太差（区分度低，走索引要大量回表）；统计信息过期导致优化器误判。大表出现这种情况可以 ANALYZE TABLE 重新统计，或 force index 强制走索引对比。

</details>

练习 6：EXPLAIN ANALYZE 和 EXPLAIN 有什么区别？什么时候用？

<details>

<summary>参考答案</summary>

EXPLAIN 只展示优化器预估的执行计划，不真正执行，看不到真实耗时；EXPLAIN ANALYZE（MySQL 8.0.18+）会真正执行查询，并打印每一步的实际行数和真实耗时。当怀疑优化器预估不准、想定位具体哪一步慢时，用 EXPLAIN ANALYZE。

</details>

练习 7：JOIN 查询里 type=eq_ref 是什么意思？为什么它比 ref 更好？

<details>

<summary>参考答案</summary>

eq_ref 出现在被驱动表上，表示驱动表每来一行，被驱动表用主键或唯一索引精确匹配一行（最多一行）。它比 ref 好是因为 ref 是普通二级索引等值匹配，可能匹配多行；eq_ref 每次只查一行，IO 更确定、更省。

</details>

练习 8：一条 SQL 的 EXPLAIN 显示 Extra 里同时有 "Using filesort" 和 "Using temporary"，说明什么？怎么优化？

<details>

<summary>参考答案</summary>

说明这条查询既建了临时表（GROUP BY/DISTINCT）又做了额外排序（ORDER BY），是典型的"没走索引的分组+排序"。优化：让 GROUP BY 和 ORDER BY 共用同一个联合索引，利用索引的有序性同时完成分组和排序，消灭临时表和 filesort。

</details>

本篇小结：看执行计划就盯三件事——type 有没有退化到 ALL、key 选没选对索引、Extra 有没有 filesort/temporary。三者都过关，这条 SQL 的执行计划才算健康。

>

小提示：rows 是预估值，统计信息过期时先 ANALYZE TABLE 再下结论。

下一篇预告：第 18 章 索引优化与覆盖索引