MySQL EXPLAIN用法实例深度详解!

作者：0xDevNull
EXPLAIN是MySQL提供的性能分析工具,用于查看SQL查询的执行计划,这篇文章主要介绍了MySQL EXPLAIN用法的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考下。

一、什么是 EXPLAIN
EXPLAIN 是 MySQL 提供的一个用于分析 SQL 语句执行计划的强大工具。通过它，我们可以了解 MySQL 查询优化器是如何执行 SQL 语句的，包括表的读取顺序、索引使用情况、扫描行数等关键信息，从而帮助我们定位和优化性能瓶颈。

版本说明：本文基于 MySQL 5.7+ 和 8.0+ 版本。EXPLAIN ANALYZE 和 Hash Join 特性需要 MySQL 8.0.18+ 和 8.0.20+。

二、基本语法

2.1 标准用法
1
EXPLAIN SELECT * FROM table_name WHERE condition;

2.2 支持的语句类型
EXPLAIN 支持以下语句：

SELECT
DELETE
INSERT
REPLACE
UPDATE

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 输出字段详解
执行 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

额外信息（非常重要）

四、核心字段深度解析

4.1 id 列 - 执行顺序标识
规则：

id 相同：从上往下顺序执行
id 不同：id 值越大，优先级越高，越先执行
id 为 NULL：最后执行（通常是 UNION 结果合并）

-- 示例：子查询
EXPLAIN SELECT * FROM test1 WHERE id IN (SELECT id FROM test2);
-- 结果中 id=2 的子查询会先执行，id=1 的主查询后执行

4.2 select_type 列 - 查询类型
类型

说明

SIMPLE

简单查询，不包含子查询或 UNION

PRIMARY

最外层查询

SUBQUERY

SELECT 或 WHERE 中的子查询

DERIVED

FROM 中的子查询（派生表）

UNION

UNION 中的第二个及后续查询

UNION RESULT

UNION 结果合并

4.3 type 列 - 访问类型（性能关键）
性能从优到劣排序：

类型

说明

system

不进行磁盘IO,查询系统表,仅仅返回一条数据
const

通过主键或唯一索引一次就找到

eq_ref

连接查询中，被驱动表使用主键/唯一索引等值匹配

ref

使用普通索引等值匹配

range

索引范围扫描（BETWEEN、IN、>、< 等）

index

遍历整颗索引树,比ALL快一些,因为索引文件要比数据文件小
ALL

全表扫描

优化建议：至少达到 range 级别，最好达到 ref 或 eq_ref。

4.4 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

优化器确定最多返回一行

无需优化

五、实战案例

5.1 单表查询分析
1
2
3
-- 表结构：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 的估算数据更可靠

六、常见优化场景

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;

七、EXPLAIN 的局限性
需要注意 EXPLAIN 的以下限制：

不会告诉你关于触发器、存储过程的信息
不考虑各种 Cache（查询缓存等）
不能显示 MySQL 在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
标准 EXPLAIN 不会真正执行 SQL（除 EXPLAIN ANALYZE 外）

八、总结
检查项

优化目标

type

至少达到 range，最好 ref 或 eq_ref

key

确保实际使用了索引

rows

越小越好

Extra

避免出现 Using filesort、Using temporary

覆盖索引

尽量让 Extra 显示 Using index

掌握 EXPLAIN 的使用是 SQL 性能优化的基础技能。通过分析执行计划，我们可以快速定位性能瓶颈，有针对性地进行索引优化和 SQL 改写。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；