MySQL EXPLAIN 执行计划完全指南：从入门到优化

1. 引言：你的 SQL 需要一张“X 光片”

当身体不舒服时，医生会让你拍张 X 光片——它能清晰显示骨骼有没有错位、哪里发炎了。在数据库世界里，当一个 SQL 查询运行缓慢时，你也需要一张“X 光片”来诊断问题。这张“X 光片”就是 EXPLAIN。它不会真正执行你的查询，而是告诉你 MySQL 打算怎么执行它：用哪个索引、扫描多少行、要不要排序……通过这些信息，你能精准找到性能瓶颈，而不是盲目加索引或瞎猜。

今天，我们就从零开始，彻底搞懂 MySQL 的 EXPLAIN 命令，学会读懂执行计划，掌握 SQL 优化的基本功。

---

2. 前置知识：理解执行计划必须掌握的基础概念

在查看“X 光片”之前，你得先知道正常的人体骨骼长什么样。同样，看懂 EXPLAIN 之前，你需要先理解几个关键概念。

2.1 索引——数据库的“目录”

想象一本没有目录的书，你要找一个特定内容，只能一页页翻（全表扫描）。有了目录（索引），你就能快速定位到目标页码。MySQL 的索引也是同样的道理，它是一棵 B+ 树，能帮你快速找到数据行。

• 聚簇索引：主键索引，叶子节点存的是整行数据。
• 二级索引：普通索引，叶子节点存的是主键值，需要再通过主键回表才能拿到完整数据。
• 覆盖索引：如果查询的所有列都包含在二级索引中，就不需要回表，性能极佳。

2.2 全表扫描——性能杀手

如果 MySQL 找不到合适的索引，它就只能 全表扫描（ALL）——把整张表从头到尾读一遍。数据量小的时候还好，一旦表有上百万行，全表扫描就会变成“慢查询”的元凶。

2.3 MySQL 优化器——决策者

你写一个 SQL，MySQL 会有一个叫 优化器 的组件，根据表的统计信息（行数、索引分布、数据选择性等）来决定用哪个索引、表的连接顺序、用什么算法连接。EXPLAIN 输出的就是这个决策的结果。

注意：统计信息不是实时更新的，可能需要手动运行 ANALYZE TABLE 来刷新，否则优化器可能基于过期的信息做出错误选择。

2.4 访问路径与连接算法

• 单表访问：MySQL 可以选择全表扫描、索引扫描（走索引树）、范围扫描（如 WHERE id > 100）、唯一索引等值查询等。
• 多表连接：
  • Nested Loop Join：嵌套循环，驱动表每扫一行，就去被驱动表查一次（需要索引）。
  • Hash Join（MySQL 8.0+）：等值连接且无索引时，会构建哈希表加速。
  • Sort Merge Join：通常用于排序后的数据连接，MySQL 较少用。

---

3. EXPLAIN 是什么？

EXPLAIN 是 MySQL 提供的一条诊断命令，你只需要在要分析的 SQL 前加上 EXPLAIN，MySQL 就会返回该语句的执行计划——也就是优化器打算怎么执行它。它不会真的执行你的 SQL，所以对生产环境没有影响（EXPLAIN ANALYZE 除外）。

执行计划会告诉你：

• 访问表的方式（用哪个索引？还是全表扫描？）
• 多表连接的顺序和算法
• 预估需要扫描多少行数据
• 是否使用了临时表、文件排序等

这些信息正是我们优化 SQL 的“地图”。

---

4. 如何使用 EXPLAIN？

语法极其简单，在 SELECT、UPDATE、DELETE 等语句前加上 EXPLAIN 即可：

EXPLAIN SELECT * FROM users WHERE country = 'USA' AND age > 30;

如果需要更详细的输出，可以用 FORMAT=JSON：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE country = 'USA' AND age > 30;

MySQL 8.0.18 以后还支持 EXPLAIN ANALYZE，它会真正执行 SQL，并输出实际执行时间和成本（生产环境慎用）。

---

5. EXPLAIN 输出字段详解（核心章节）

执行 EXPLAIN 后，你会得到一个表格，包含十几个列。我们挑最重要的逐一解读。

5.1 id：查询标识符

• 表示 SELECT 子句的执行顺序。
• id 相同：按 table 顺序执行。
• id 越大：优先级越高，越先执行。
• id 为 NULL：表示结果行，如 UNION 的汇总结果。

5.2 select_type：查询类型

值	含义
SIMPLE	简单查询（无子查询、无 UNION）
PRIMARY	最外层查询
SUBQUERY	子查询中的第一个 SELECT
DERIVED	派生表（FROM 子句中的子查询结果）
UNION	UNION 中第二个及之后的 SELECT
UNION RESULT	UNION 的结果

5.3 table：当前步骤访问的表

可能是表名、别名，或者 <derivedN>（N 为派生表 id）、<unionM,N> 等临时表标识。

5.4 type：访问类型（性能关键）

这是 EXPLAIN 最重要的列之一，描述了 MySQL 如何查找数据行。性能从优到劣排序如下：

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

类型	说明	例子
NULL	不用访问表和索引，直接返回结果（如 SELECT 1）	罕见
system	系统表，仅一行	极少见
const	主键或唯一索引等值查询，最多返回一行	WHERE id = 1
eq_ref	连接查询时，被驱动表使用主键或唯一索引	ON t1.id = t2.id（t2 表用主键）
ref	普通索引等值查询，可能返回多行	WHERE name = 'Alice'
range	索引范围扫描，如 >、<、BETWEEN、IN	WHERE age BETWEEN 20 AND 30
index	全索引扫描（扫描整个索引树）	通常比全表扫描稍好，但仍需警惕
ALL	全表扫描	性能杀手，尽量避免

补充：eq_ref 和 ref 的区别
eq_ref 意味着被驱动表的连接字段是唯一索引（通常是主键），驱动表的一行只匹配被驱动表的一行。而 ref 是普通索引，驱动表的一行可能匹配被驱动表的多行。eq_ref 通常比 ref 更快，因为只需要查找一次。

5.5 possible_keys 与 key

• possible_keys：可能用到的索引列表（优化器考虑过的索引）。
• key：实际使用的索引。如果为 NULL，表示没有使用索引。

为什么优化器可能不用你期望的索引？ 可能是索引列被函数操作了（如 WHERE DATE(create_time)），或者数据量太小优化器认为全表更快，或者统计信息过旧。

5.6 key_len：使用的索引长度（字节）

表示 MySQL 实际使用了索引中多少个字节。对于复合索引，这个值可以判断是否用到了全部索引列。例如，一个复合索引 (name, age)，name 是 varchar(50) 字符集 utf8mb4（每字符最多 4 字节），可为 NULL，则 name 部分的 key_len ≈ 50×4 + 1（NULL 标志）+ 2（变长长度）= 203 字节。如果 key_len 只有 203，说明只用了 name 列，age 列没用上。如果用了两列，则 key_len 会加上 age 字段的长度（例如 int 为 4 字节，可为 NULL 再加 1，总计约 208 字节）。通过这个值可以验证索引是否被充分使用。

5.7 ref：与 key 比较的列或常量

显示哪些列或常量与 key 列中的索引进行比较。例如，ref 可能是 const（使用常量），也可能是 db.table.column。

5.8 rows：预估扫描行数（非常重要）

优化器预估的需要读取的行数。这是一个预估值，不是精确值，但可以作为衡量查询效率的关键指标。rows 越小越好。

5.9 filtered：过滤比例

表示存储引擎返回的数据经过 WHERE 条件过滤后，剩余行数的百分比预估。例如 rows=1000, filtered=10%，意味着最终返回约 100 行。通常与 rows 结合看，如果 filtered 很低，可能说明索引选择性不够好。

5.10 Extra：额外信息（含大量性能提示）

这一列经常包含关键的性能信息，需要特别关注：

取值	含义	建议
Using index	使用了覆盖索引，无需回表	✅ 好！
Using where	服务器层在存储引擎返回行后进行了过滤	通常正常，但若伴随 Using filesort 需注意
Using temporary	使用了临时表（常见于 GROUP BY、DISTINCT、ORDER BY 不同列等）	⚠️ 警惕，尤其数据量大时，会占用内存和磁盘
Using filesort	使用了文件排序（内存或磁盘排序）	⚠️ 性能杀手！尽量通过索引避免排序
Using join buffer	连接时使用了缓冲区（通常 JOIN 无索引导致）	⚠️ 需为连接条件加索引
Select tables optimized away	优化器确定只需访问索引即可返回结果（如 MIN(id)）	✅ 极致优化

为什么 Using filesort 是性能杀手？
当 MySQL 无法利用索引排序时，它会把数据读取出来，在内存或磁盘上进行排序操作。数据量大时，可能产生临时文件，导致大量 I/O 和 CPU 消耗。通过创建合适的索引（使 ORDER BY 列顺序与索引一致）可以避免。

为什么 Using temporary 是性能杀手？
临时表可能创建在内存或磁盘上，数据量大时会非常慢。通常由 GROUP BY、DISTINCT 或某些子查询导致。可以通过优化索引或改写查询来避免。

---

6. 实战优化案例：一步步优化慢查询

6.1 原始查询

SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'London' AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY o.order_date DESC;

6.2 分析 EXPLAIN 结果（问题诊断）

假设我们在优化前执行 EXPLAIN，可能看到：

优化前执行计划

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	c	ALL	NULL	NULL	10000	Using where
1	SIMPLE	o	ref	idx_customer_id	idx_customer_id	200	Using where; Using filesort

• customers 表 type=ALL：全表扫描找 city='London'，扫描 1 万行。
• orders 表 type=ref：用 customer_id 索引，但 rows 200 表示每个客户可能有 200 个订单。
• Extra 有 Using filesort：因为排序字段 o.order_date 没索引，需要临时排序。

6.3 优化步骤

1. 为 customers.city 加索引：避免全表扫描。
2. 为 orders 建复合索引 (customer_id, order_date)：
   • 先通过 customer_id 快速定位到该客户的订单。
   • 索引中 order_date 已排序，范围查找 BETWEEN 高效，且无需额外排序（Using filesort 消失）。
3. 如果查询只需部分字段（如 o.id, o.order_date, c.name），可考虑覆盖索引：在 orders 索引中把需要的列也包含进去（MySQL 8.0 支持索引包含列）。

6.4 优化后的 EXPLAIN

优化后执行计划

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	c	ref	idx_city	idx_city	10	Using index
1	SIMPLE	o	range	idx_customer_date	idx_customer_date	50	Using where; Using index condition

• customers 表 type=ref：使用 city 索引，只扫约 10 行。
• orders 表 type=range：使用复合索引进行范围扫描，rows 大幅减少。
• Extra 没有了 filesort，可能还出现了 Using index condition（索引条件下推，进一步优化）。

---

7. 使用 EXPLAIN 的常见误区与注意事项

7.1 误区：EXPLAIN 结果是精确值

rows 是预估值，不是实际扫描行数。它与实际值可能有偏差，尤其是统计信息过旧时。可以执行 ANALYZE TABLE 更新统计信息。

7.2 误区：用了索引就一定快

type=index（全索引扫描）可能比全表扫描还慢，因为索引树可能比表数据还大。type=index 通常表示扫描了整个索引，效率依然不高。

7.3 误区：只看 type 不看 Extra

有时 type=ref 看起来不错，但 Extra 里有 Using filesort，仍然是性能瓶颈。必须结合多个字段综合判断。

7.4 其他注意事项

• 最左前缀原则：复合索引 (a, b, c)，如果查询条件只涉及 b 和 c，则无法使用该索引。
• 函数操作列：WHERE DATE(create_time) = '2023-01-01' 会让索引失效，应改写为 create_time >= '2023-01-01' AND create_time < '2023-01-02'。
• 数据类型不匹配：字符串列用数字查询，可能无法使用索引（如 WHERE str_col = 123）。
• OR 条件：如果 OR 两侧的列不是同一个索引，可能无法使用索引，可用 UNION 优化。

---

8. 总结与最佳实践

• EXPLAIN 是 SQL 优化的起点，它把优化器的决策摆在你面前，让你不再盲目调优。
• 核心关注点：type（访问类型）、key（实际索引）、rows（预估行数）、Extra（额外信息）。
• 性能信号：尽量避免 ALL、index、Using filesort、Using temporary。
• 优化本质：通过合理索引，让查询走 ref、range，减少 rows，避免排序和临时表。
• 不要过度优化：如果表很小（几千行），全表扫描可能比走索引更快，优化器自己会选。EXPLAIN 只是工具，最终要结合业务和数据量权衡。
• 定期维护统计信息：确保优化器决策的准确性。

下次遇到慢查询，别急着加索引，先用 EXPLAIN 拍张“X 光片”，看看内部执行逻辑，再对症下药。优化之旅，从读懂执行计划开始！