一、认识 EXPLAIN：MySQL SQL 调优的核心分析工具
在 SQL 调优过程中，EXPLAIN 可以说是不可或缺的核心工具。其本质是 MySQL 内置的执行计划分析器，能够直观展示 SQL 语句的详细执行流程。通过 EXPLAIN，我们可以清晰地了解 MySQL 查询优化器如何执行该 SQL——包括表的读取顺序、索引使用情况、预估扫描行数等关键指标。掌握这些信息后，定位并优化性能瓶颈将不再无从下手。
需要说明的是，本文内容主要适用于 MySQL 5.7 及以上版本，以及 8.0 版本。其中 EXPLAIN ANALYZE 功能需要 MySQL 8.0.18+，Hash Join 特性需要 MySQL 8.0.20+。
二、EXPLAIN 基本语法与用法
2.1 标准用法
EXPLAIN SELECT * FROM table_name WHERE condition;
使用方式极为简便，只需在待分析的 SQL 语句前加上 EXPLAIN 关键字即可。
2.2 支持的语句类型
需要注意的是，EXPLAIN 不仅适用于 SELECT 查询，以下类型的语句同样可以进行分析：
SELECTDELETEINSERTREPLACEUPDATE
2.3 MySQL 8.0+ 新增用法
-- 实际执行并分析耗时（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25; -- JSON 格式输出（包含成本模型数据） EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;
EXPLAIN ANALYZE 是 MySQL 8.0 引入的重磅特性，它会实际执行 SQL 并返回真实耗时与扫描行数，相比普通 EXPLAIN 的估算值更具参考价值。
三、EXPLAIN 输出字段详细解读
跑完 EXPLAIN 后，MySQL 会返回一个结果集，包含下面这些列：
字段 | 含义 |
id | 查询标识符，表示执行顺序 |
select_type | 查询类型（SIMPLE、PRIMARY、SUBQUERY 等） |
table | 访问的表名 |
partitions | 匹配的分区（MySQL 5.7+） |
type | 访问类型（ALL、index、range、ref、eq_ref 等） |
possible_keys | 可能使用的索引 |
key | 实际使用的索引 |
key_len | 使用的索引长度 |
ref | 与索引比较的列或常量 |
rows | 估算需要扫描的行数 |
filtered | 按条件过滤后剩余行的百分比（MySQL 5.7+） |
Extra | 额外信息（非常重要） |
在众多字段中，最值得重点关注的是 type、key、rows 和 Extra 四列。接下来我们将逐一深入解析。
四、核心字段深度解析与优化指南
4.1 id 列 - 执行顺序标识
规则：
- id 相同：从上往下顺序执行
- id 不同：id 值越大，优先级越高，越先执行
- id 为 NULL：最后执行（通常是 UNION 结果合并）
-- 示例：子查询 EXPLAIN SELECT * FROM test1 WHERE id IN (SELECT id FROM test2); -- 结果中 id=2 的子查询会先执行，id=1 的主查询后执行
简而言之，id 列反映了查询的执行路线图。
4.2 select_type 列 - 查询类型
类型 | 说明 |
SIMPLE | 简单查询，不包含子查询或 UNION |
PRIMARY | 最外层查询 |
SUBQUERY | SELECT 或 WHERE 中的子查询 |
DERIVED | FROM 中的子查询（派生表） |
UNION | UNION 中的第二个及后续查询 |
UNION RESULT | UNION 结果合并 |
日常开发中最常见的是 SIMPLE 类型，但当 SQL 包含子查询或 UNION 时，其他类型便会发挥作用。
4.3 type 列 - 访问类型（性能关键）
type 列是 EXPLAIN 输出中最关键的字段之一，直接表明 MySQL 如何定位目标数据行。不同访问类型性能差异显著，从最优到最差排序如下：
类型 | 说明 |
system | 不进行磁盘IO,查询系统表,仅仅返回一条数据 |
const | 通过主键或唯一索引一次就找到 |
eq_ref | 连接查询中，被驱动表使用主键/唯一索引等值匹配 |
ref | 使用普通索引等值匹配 |
range | 索引范围扫描（BETWEEN、IN、>、< 等） |
index | 遍历整颗索引树,比ALL快一些,因为索引文件要比数据文件小 |
ALL | 全表扫描 |
优化建议：上线前检查一下，至少得保证 range 级别，最好能达到 ref 或 eq_ref。如果看到 type=ALL，那基本就是告警信号，说明查询在走全表扫描。
4.4 Extra 列 - 额外信息
这是藏得最深的“情报站”。很多麻烦都是从 Extra 里暴露出来的：
值 | 含义 | 优化建议 |
Using index | 使用覆盖索引 | 理想状态，无需回表 |
Using where | 使用 WHERE 过滤（全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中） | 正常情况 |
Using filesort | 使用外部排序（无法利用索引排序） | 需要优化，考虑添加索引 |
Using temporary | 使用临时表来存储结果集，常见于排序和分组查询 | 常见于 GROUP BY / ORDER BY，需优化 |
Using join buffer | 使用连接缓存 | 连接条件未使用索引 |
Impossible WHERE | WHERE 条件永远为 false | 检查逻辑 |
Select tables optimized away | 优化器确定最多返回一行 | 无需优化 |
特别需要警惕的是 Using filesort 和 Using temporary，一旦出现这两个提示，通常意味着索引设计或 SQL 编写存在隐患，亟需优化。
五、实战案例详解
5.1 单表查询分析
-- 表结构：users(id, age, score, name, address) -- 索引：idx_age_score_name(age, score, name) EXPLAIN SELECT * FROM users WHERE age = 25;
结果分析：
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | ref | idx_age_score_name | idx_age_score_name | 5 | const | 12 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
结果解读：
type=ref：使用了普通索引等值匹配，性能良好key=idx_age_score_name：实际启用了联合索引Extra=Using index：实现了覆盖索引，无需回表查询rows=12：仅扫描 12 行，查询非常高效
5.2 连接查询分析
EXPLAIN SELECT * FROM test1 t1 INNER JOIN test2 t2 ON t1.id = t2.id;
关键观察点：
- 注意识别驱动表——通常 rows 较小的表作为驱动表性能更优
- 被驱动表的 type 应达到
eq_ref，说明使用了主键或唯一索引进行匹配
5.3 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25\G
输出示例：
*************************** 1. row *************************** EXPLAIN: -> Covering index lookup on users using idx_age_score_name (age=25) (cost=1.52 rows=12) (actual time=0.0272..0.0344 rows=12 loops=1)
核心优势：
- 直接展示实际执行时间（actual time）
- 显示实际返回行数（rows）
- 数据真实可靠，比普通 EXPLAIN 的估算值更具说服力
六、常见 SQL 优化场景与解决方案
6.1 避免全表扫描（type = ALL）
问题诊断：
- 查询条件列没有索引
- 查询写入了函数导致索引失效
- 多表 JOIN 时驱动表选错了
优化方法：
-- 错误：函数包装导致索引失效 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 正确：改写为范围查询 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
开发者常犯的错误是对索引列使用函数，导致索引失效。将其改写为范围查询后，性能往往能得到显著提升。
6.2 消除文件排序（Using filesort）
-- 添加合适的索引避免 filesort CREATE INDEX idx_age_name ON users(age, name); -- 查询同时满足 WHERE 和 ORDER BY SELECT * FROM users WHERE age > 20 ORDER BY age, name;
文件排序意味着查询无法借助索引完成排序，需在内存或磁盘中额外进行排序操作。通过创建合适的联合索引，即可有效避免文件排序。
6.3 利用覆盖索引
-- 索引：idx_age_name(age, name) -- ✅ 覆盖索引查询（Extra = Using index） EXPLAIN SELECT age, name FROM users WHERE age = 25; -- ❌ 非覆盖索引（需要回表查询） EXPLAIN SELECT * FROM users WHERE age = 25;
如果查询仅需 age 和 name 两列，且索引已包含它们，则查询可直接在索引树完成，无需回表，效率大幅提升。
七、EXPLAIN 的局限性
不过，EXPLAIN 也不是万能的，它有一些内在限制需要了解：
- 无法提供触发器、存储过程等内部执行信息
- 不考虑查询缓存等各类缓存机制
- 不显示 MySQL 执行查询时内部所做的优化
- 部分统计信息为估算值，并非精确数据
- 标准 EXPLAIN 不会实际执行 SQL（EXPLAIN ANALYZE 除外）
因此，EXPLAIN 是一个优秀的分析工具，但最终的性能判断仍需结合实际执行情况综合考量。
八、总结
检查项 | 优化目标 |
type | 至少达到 range，最好 ref 或 eq_ref |
key | 确保实际使用了索引 |
rows | 越小越好 |
Extra | 避免出现 Using filesort、Using temporary |
覆盖索引 | 尽量让 Extra 显示 Using index |
掌握 EXPLAIN 是 SQL 优化的基本功和入门必修课。遇到慢 SQL 时，先执行 EXPLAIN，检查 type 是否低于 range、Extra 是否出现 filesort 或 temporary，然后根据这些线索优化索引及 SQL 写法。熟练运用这套流程后，解决性能瓶颈将变得更加高效。
