MySQL EXPLAIN 解析

什么是 EXPLAIN？

EXPLAIN 是 MySQL 的一个关键字，用于获取 MySQL 如何执行一条 SELECT 语句的详细信息。它通过模拟执行（或实际执行，在 MySQL 8.0.18+ 的某些情况下）来展示查询的执行计划（Query Execution Plan, QEP），而不是真正返回查询结果。

通过分析 EXPLAIN 的输出，你可以：

查看表之间的连接顺序和类型。
判断是否使用了索引，以及使用了哪些索引。
估算需要扫描的数据行数。
发现潜在的性能瓶颈（如全表扫描、临时表、文件排序等）。
为优化查询提供明确的指导方向。

如何使用 EXPLAIN？

使用方法非常简单，直接在你要分析的 SELECT 语句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON 即可。

-- 最基本的使用方式
EXPLAIN SELECT * FROM your_table WHERE id = 1;

-- 查看更详细的 JSON 格式信息（推荐，信息更全）
EXPLAIN FORMAT=JSON SELECT * FROM your_table WHERE id = 1;

-- 也可以用于分析连接查询
EXPLAIN SELECT a.*, b.name
FROM orders a
JOIN users b ON a.user_id = b.id
WHERE a.amount > 100;

详解 EXPLAIN 的输出列（核心）

EXPLAIN 的标准输出是一个表格，包含以下至关重要的列。我们逐一来解读。

列名	描述
id	查询中每个 SELECT 子句的唯一标识符
select_type	SELECT 查询的类型
table	当前行正在访问的表名
partitions	匹配的分区信息
type	【极其重要】表的访问类型（连接类型），性能好坏的关键指标
possible_keys	查询可能使用到的索引
key	查询实际决定使用的索引
key_len	使用的索引字段的长度
ref	哪些列或常量被用于与索引列进行比较
rows	【重要】MySQL 预估为了找到所需的行而需要读取的行数（估算值）
filtered	表示存储引擎返回的数据在服务器层过滤后，剩余的行数所占的百分比（MySQL 5.7+）
Extra	【重要】额外的信息，包含不适合在其他列显示但非常重要的信息

id

含义：一个数字，表示 SELECT 在查询中的执行顺序。
规则：
- id 相同：执行顺序从上到下（如多个表连接）。
- id 不同：如果是子查询，id 会递增。id 值越大，优先级越高，越先执行。
- id 为 NULL：通常表示这是一个结果集合并（UNION）的语句。

select_type

含义：查询的类型，主要用于区分普通查询、子查询、联合查询等。
常见值：
- SIMPLE：最简单的查询，不包含子查询或 UNION。
- PRIMARY：查询中若包含任何复杂的子部分，最外层的 SELECT 被标记为 PRIMARY。
- SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询。
- DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生表），MySQL 会递归执行这些子查询，把结果放在临时表中。
- UNION：UNION 中的第二个或后面的 SELECT 语句。
- UNION RESULT：从 UNION 表获取结果的 SELECT。

type 【性能核心指标】

这是判断查询性能最关键的一列。它显示了连接使用了哪种类型，从好到坏依次是：

system：表只有一行记录（等于系统表），这是 const 类型的特例，性能最好。
const：通过索引一次就找到了。用于比较 主键索引 或 唯一索引 的所有列与常量值的情况。速度非常快，因为它只读取一次。
- EXPLAIN SELECT * FROM users WHERE id = 1; （id 是主键）
eq_ref：在连接查询时，使用主键或 唯一非空索引 进行关联。对于来自前表的每一行，从当前表中只能找到一行与之匹配。这是除了 system 和 const 之外最好的连接类型。
- 常见于 ... ON a.primary_key = b.unique_key ...
ref：使用 普通索引（Non-Unique Index）进行查找。对于来自前表的每一行，从当前表中可能找到多个匹配的行。
- EXPLAIN SELECT * FROM users WHERE age = 30; （age 字段上有普通索引）
range：只检索给定范围的行，使用一个索引来选择行。BETWEEN, IN, >, < 等操作就是典型的范围扫描。
index：全索引扫描（Full Index Scan）。与 ALL 类似，但不同的是它只遍历索引树，而不是数据行。这通常比 ALL 快，因为索引文件通常比数据文件小。
- EXPLAIN SELECT id FROM users; （id 是主键，索引覆盖）
ALL：全表扫描（Full Table Scan）。性能最差，表示 MySQL 需要扫描整张表来找到匹配的行。必须优化！通常是通过增加索引来避免。

目标：在优化时，至少要保证查询达到 range 级别，最好能达到 ref 或以上。

possible_keys & key

possible_keys：指出 MySQL 可能会使用哪些索引来查找该表中的行。如果为 NULL，则没有相关的索引。
key：显示 MySQL 实际决定使用的索引。如果为 NULL，则没有使用索引。
注意：possible_keys 列出的索引不一定在实际中被使用。MySQL 会基于查询成本和统计信息选择它认为最优的索引。

rows

含义：MySQL 预估为了找到所需的行而需要检查的行数。这是一个估算值，但非常能反映性能成本。数值越小越好。

Extra 【包含重要警告信息】

这一列包含了很多额外信息，很多都直接指出了性能问题：

Using index：非常好。表示查询使用了 覆盖索引（Covering Index），即所有需要的数据都可以从索引中获取，而无需回表读取数据行。
Using where：表示 MySQL 服务器在接收到存储引擎返回的行后，使用了 WHERE 条件来进一步过滤。
Using temporary：非常不好。表示 MySQL 需要使用临时表来存储结果集，常见于排序 (ORDER BY) 和分组查询 (GROUP BY)，尤其是分组字段没有索引时。
Using filesort：不好。表示 MySQL 无法使用索引来完成排序，需要额外的排序操作。这个操作可能在内存或磁盘上完成，取决于数据量大小。filesort 是非常耗性能的操作。
Using join buffer (Block Nested Loop)：表示连接查询时，被驱动表没有使用索引，需要用到连接缓冲区。
Impossible WHERE：WHERE 子句的条件永远为 false，MySQL 无法获取任何行。

总结

养成习惯：对任何不确定性能的查询，都使用 EXPLAIN 进行分析。
关注核心列：重点观察 type、rows 和 Extra 这三列。
追求更好的 type：目标是 range，争取 ref，避免 ALL。
减少 rows：通过索引让 MySQL 需要扫描的行数尽可能少。
警惕 Extra 中的坏信号：如 Using temporary 和 Using filesort，并想办法优化它们（通常是调整索引或改写查询）。
使用 EXPLAIN FORMAT=JSON：当需要更详细的信息（如成本信息）时，使用 JSON 格式，它会提供比表格形式更深入的分析数据。