MySQL中EXPLAIN分析执行计划_Mysql

一、问题：没有 explain 时，你怎么知道一条 sql 是怎么执行的？

假设你写了一条 sql：

select * from orders where user_id = 100 and status = 'paid';

然后你发现这条 sql 执行很慢。现在你想优化它。

但问题是：你根本看不到 mysql 内部是怎么处理这条 sql 的。

你不知道：

它是直接通过索引定位到那几行数据，还是把整张表从头到尾扫了一遍？
你之前给 user_id 建的索引，这次查询到底有没有被用上？
如果有 3 张表 join，mysql 是先查 a 再关联 b，还是先查 b 再关联 a？
它预估要扫描多少行？有没有做额外的排序或创建临时表？

没有 explain 的弊端：

优化完全靠猜——你只能凭经验"觉得"这里该加个索引，但加完也不知道有没有生效。
索引建了白建——你创建了联合索引，但查询条件顺序不对，mysql 根本没走这个索引，你却不知道。
多表 join 是黑盒——你写了一个复杂的关联查询，但优化器可能选择了一个效率极低的执行顺序，你无从得知。
试错成本极高——改一次 sql、加一次索引、跑一次测试，循环往复，效率极低。

所以 mysql 需要一个工具，把优化器"最终决定的执行方案"暴露出来，让开发者看得见、能分析。

这就是 explain 的设计初衷。

二、explain 是什么？

explain 不会真正执行你的 sql（除了 explain analyze 这种特殊形式）。它只是让 mysql 的查询优化器生成执行计划，然后把计划的各个维度展示给你。

核心用法：

explain select * from users where id = 1;

mysql 会返回一张表格，每一行代表优化器对某个表（或某个步骤）的执行计划。

三、输出字段详解：每个字段解决什么问题？

字段	解决什么问题	重点关注
id	多表查询时，哪个步骤先执行、哪个后执行	id 相同从上到下执行，id 越大越先执行
select_type	这是什么类型的查询？简单查询？子查询？union？	simple（简单）、subquery（子查询）等
table	当前这一步操作的是哪张表	定位问题表
type	怎么访问数据？这是最重要的字段	见下方详细讲解
possible_keys	优化器觉得"可能能用"的索引有哪些	看有没有你期望的索引
key	优化器实际选了哪个索引	null 表示没走索引
key_len	实际使用了索引的多少字节	判断联合索引是否被完全利用
ref	索引是和常量比，还是和另一张表的列比	const 表示和固定值比
rows	优化器预估要扫描多少行	越小越好
filtered	经过 where 条件过滤后，预计剩下多少百分比的数据	百分比越高说明索引过滤效果好
extra	有没有额外的"坏操作"，比如排序、临时表	见下方详细讲解

四、type 字段：从"好"到"差"的完整谱系

type 告诉你 mysql 以什么方式访问数据。这是 explain 里最需要关注的字段。

type 值	含义	为什么好/坏
system	表只有一行数据（几乎见不到）	最优
const	通过主键或唯一索引，直接定位到一行	最优，只读一次
eq_ref	join 时，被驱动表通过主键/唯一索引关联	很好，每次只匹配一行
ref	通过普通索引（非唯一）等值查询	好，可能匹配多行
range	索引范围扫描，比如 between、>、<	较好，只扫描索引的一部分
index	全索引扫描——遍历整个索引树	较差，虽然比全表扫描快，但仍要遍历所有索引项
all	全表扫描——遍历整个数据文件	最差，性能瓶颈的常见原因

看到 type = all，基本意味着你的查询没有走索引，需要重点优化。

五、extra 字段：暴露"隐藏的性能杀手"

extra 列会告诉你优化器有没有做一些"额外的高成本操作"。

extra 值	含义	为什么需要注意
using index	覆盖索引——查询的列都在索引里，不需要回表查数据行	好事，减少了一次回表 io
using where	存储引擎把数据返回给 server 层后，server 再用 where 条件过滤	一般正常，但如果配合 type=all 就很差
using temporary	需要创建临时表来存储中间结果	坏事，常见于 group by 或 distinct 没有走索引
using filesort	需要额外的排序操作，而不是利用索引的有序性	坏事，常见于 order by 字段没有索引
using index condition	索引下推（icp）——把 where 的一部分判断下推到存储引擎层	好事，减少回表次数

看到 using temporary 或 using filesort，通常意味着你的索引设计或 sql 写法有问题。

六、实战分析：用 explain 诊断问题

案例 1：发现全表扫描

explain select * from users where name = 'alice';

输出：

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | simple      | users | all  | null          | null | null    | null | 10000 | using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

诊断：

type = all：全表扫描
key = null：一个索引都没走
rows = 10000：把一万行全扫了一遍

结论：需要给 name 字段加索引。

案例 2：验证索引是否生效

-- 给 name 加了索引后再次执行
explain select * from users where name = 'alice';

输出：

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | simple      | users | ref  | idx_name      | idx_name | 1023    | const |    1 | using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

诊断：

type = ref：走了普通索引的等值查询
key = idx_name：实际使用了你建的索引
rows = 1：只扫描了 1 行

结论：索引生效，优化成功。

案例 3：联合索引的最左前缀问题

-- 索引是：idx_age_name(age, name)
explain select * from users where name = 'alice';

输出：

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | simple      | users | all  | null          | null | null    | null | 10000 | using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

诊断：

索引 idx_age_name 在 possible_keys 里都没出现
type = all，全表扫描

为什么？因为联合索引要求"最左前缀"匹配。你的查询条件只有 name，缺少最左边的 age，所以 mysql 无法使用这个索引。

结论：查询条件必须包含联合索引的最左列，或者调整索引顺序为 idx_name_age。

案例 4：覆盖索引（不需要回表）

-- 索引是：idx_age_name(age, name)
explain select name from users where age = 20;

输出：

+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | simple      | users | ref  | idx_age_name  | idx_age_name | 5       | const |  100 | using index              |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+

诊断：

type = ref：走了索引
extra = using index：覆盖索引

为什么好？因为你要查的 name 也在索引 idx_age_name 里，mysql 直接读索引就能得到结果，不需要再根据索引里的指针回表去数据页查整行数据。减少了一次磁盘 io。

七、explain 的进阶用法

1. 看更详细的成本估算（mysql 5.7+）

explain format=json select * from users where id = 1;

输出 json 格式，包含更细粒度的成本信息。

2. 实际执行并看真实耗时（mysql 8.0.18+）

explain analyze select * from users where id = 1;

普通 explain 只是"预估"计划，而 explain analyze 会真正执行 sql，并告诉你每一步实际花了多少时间、读了多少行。

八、总结：设计的逻辑链条

步骤	设计逻辑
问题	sql 性能差，但优化器怎么执行是完全的黑盒
弊端	不知道有没有走索引、不知道扫描了多少行、不知道有没有额外排序或临时表，优化只能靠猜和试错
方案	explain 把优化器生成的执行计划以表格形式暴露出来
核心字段	type（怎么访问数据）、key（实际用了哪个索引）、rows（扫描行数）、extra（有没有额外的高成本操作）
判断逻辑	先看 type 是不是 all（全表扫描）→ 再看 key 是不是 null（没走索引）→ 再看 extra 有没有 using temporary / using filesort
结果	开发者能精准定位性能瓶颈，是缺索引、索引没用上、还是 sql 写法有问题