一、概述

EXPLAIN 是MySQL中 分析SQL执行计划 的核心工具，它能模拟优化器执行SQL语句的过程，输出SQL的执行细节（如是否使用索引、表连接的方式、数据扫描范围等）。通过解读 EXPLAIN 的结果，我们可以快速定位SQL性能瓶颈所在，如全表扫描、索引失效、不合理的连接顺序等，从而针对性地进行优化。

　

二、EXPLAIN 的使用方式

在需要分析的SQL语句前直接加上 EXPLAIN 即可，支持 SELECT、DELETE、UPDATE、INSERT（部分版本）语句。

示例：

EXPLAIN SELECT u.id, u.name, o.order_no 
FROM user u 
LEFT JOIN order o ON u.id = o.user_id 
WHERE u.age > 25 AND u.status = 1;

执行后会输出一张包含 10+ 列的表格，每一列都代表 SQL 执行的关键属性。

　

三、EXPLAIN 输出列的核心解读（10大核心列）

不同MySQL版本的输出列可能略有差异，但核心列一致，以下是必须掌握的关键列及其含义：

列名	核心含义	关键取值及解读
id	SQL 中每个操作的执行顺序（编号）	- 相同 id：同一层级，按从上到下顺序执行（多表连接时体现连接顺序） - 不同 id：id 越大，优先级越高（子查询会生成更大的 id）
select_type	查询类型（区分简单查询、子查询、联合查询等）	- SIMPLE：简单查询（无子查询、无 UNION） - SUBQUERY：非关联子查询 - DERIVED：派生表（FROM 后的子查询） - UNION：UNION 后的第二个及以后的查询 - PRIMARY：最外层的查询
table	当前行正在访问的表名（或临时表/派生表的别名，如 ）	直接显示表名或别名，临时表通常以 <derivedN> 或 <unionN> 表示
type	访问类型（最核心的列之一，决定查询效率）	效率从高到低排序： system > const > eq_ref > ref > range > index > ALL
possible_keys	优化器认为可能使用的索引（但不一定实际使用）	显示索引名，若为NULL则表示没有可用索引
key	优化器实际使用的索引	若为NULL则表示未使用索引（可能需要优化）
key_len	实际使用的索引长度（字节），越长表示索引覆盖的字段越全	用于判断复合索引是否被充分利用（如复合索引 (a,b)，key_len 若只包含 a 的长度，则 b 未被使用）
ref	表示哪些列或常量被用来查找索引列上的值	- 如const：表示用常量匹配索引 - 如 user.age：表示用另一张表的 age 列匹配索引
rows	优化器预估需要扫描的行数（非精确值）	数值越小，查询效率通常越高（全表扫描时此值等于表的总记录数）
Extra	额外执行信息（包含大量优化关键线索）	- Using index：覆盖索引（无需回表，效率极高） - Using where：需要过滤数据（可能未用索引） - Using filesort：文件排序（非索引排序，效率低） - Using temporary：使用临时表（如 GROUP BY 未用索引，效率低）

　

四、核心列select_type（拆解查询结构）的深度解读

1、select_type的核心作用

（1）区分查询的复杂度

通过 select_type 可以快速判断当前查询是“简单查询”还是“复杂查询”（如包含子查询、联合查询等），从而针对性地分析性能瓶颈。例如：子查询可能导致临时表或多次扫描，而联合查询可能涉及结果集合并，这些都可能成为性能问题的来源。

（2）理解查询的执行层次

复杂查询中，select_type 结合 id 列可以清晰展示各部分的执行顺序和依赖关系。例如：子查询的select_type为SUBQUERY，且id大于外层查询，说明子查询会先执行，结果作为外层查询的输入。

（3）定位低效查询的源头

某些 select_type（如DERIVED派生表、UNION联合查询）往往伴随着临时表的创建或结果集的合并，这些操作本身可能消耗大量资源。通过 select_type 可以快速定位这些潜在的低效环节。

　

2、常见select_type及其场景分析

不同的 select_type 对应不同的查询结构，以下是典型类型及其对性能分析的意义：

select_type	含义	典型场景示例	分析价值
SIMPLE	简单查询（无子查询、无 UNION）	SELECT * FROM user WHERE id = 1;	结构最简单，性能问题通常集中在索引或表扫描（如type: ALL）。
PRIMARY	最外层的查询（复杂查询中）	外层查询SELECT * FROM (子查询) 中的外层部分	标识查询的“主入口”，需关注它与内部子查询的交互效率（如子查询结果是否过大）。
SUBQUERY	非关联子查询（内层独立查询）	SELECT * FROM user WHERE age > (SELECT AVG(age) FROM user);	子查询会独立执行一次，结果作为常量传入外层。若子查询返回结果大，可能导致外层扫描效率低。
DERIVED	派生表（FROM 后的子查询）	SELECT * FROM (SELECT id FROM user WHERE status=1) AS t WHERE t.id > 100;	派生表会生成临时表，若数据量大，临时表的创建和扫描会成为瓶颈（可优化为 JOIN）。
UNION	UNION 后的第二个及以后的查询	SELECT id FROM user WHERE status=1 UNION SELECT id FROM user WHERE status=2;	UNION 会合并结果并去重（需创建临时表），若用UNION ALL（不去重）可避免临时表。
UNION RESULT	UNION 结果集的合并	上述 UNION 查询中，最终合并结果的部分（通常表名显示为 <union1,2>）	标识结果合并环节，若涉及大量数据，需关注排序和去重的开销。

　

3、实战案例

（1）场景

查询“购买过商品A或商品B的用户 ID”，原始SQL使用UNION

EXPLAIN
SELECT user_id FROM orders WHERE product_id = 1  -- 商品A
UNION
SELECT user_id FROM orders WHERE product_id = 2; -- 商品B

（2）EXPLAIN输出关键信息

第一条查询的select_type: SIMPLE，第二条的select_type: UNION；

第三条（合并结果）的select_type: UNION RESULT，Extra: Using temporary（临时表去重）。

（3）问题分析

UNION会创建临时表存储结果并去重，若订单表数据量大，临时表操作耗时高。

（4）优化方案

用OR替代UNION（需确保 product_id 有索引）：

EXPLAIN
SELECT DISTINCT user_id FROM orders WHERE product_id = 1 OR product_id = 2;

（5）优化后

select_type: SIMPLE，无临时表，查询效率提升（前提是索引有效）。

　

五、核心列type（判断是否有效利用索引）的深度解读

1、type 列的核心作用

（1）判断访问效率等级

type的值直接对应数据访问的“成本”，高效的type（如const、eq_ref）意味着 MySQL 能快速定位数据，而低效的type（如ALL）则意味着需要遍历大量无关数据，性能瓶颈往往源于此。

（2）定位优化方向

通过type可以快速判断查询是否有效利用了索引——如果type是ALL（全表扫描）但表数据量很大，通常需要通过加索引、优化查询条件来提升type等级。

（3）验证索引有效性

即使建了索引，若type未达到预期（如本应是range却成了ALL），可能是索引设计不合理（如联合索引顺序错误）或查询条件不匹配（如索引列用了函数）。

　

2、type列的具体值（按效率从高到低排序）

类型	含义描述	示例
system	表中只有一行数据（如系统表），效率最高（极少出现）。	-
const	通过主键或唯一索引匹配单行数据（如 WHERE id = 1），效率极高。	EXPLAIN SELECT * FROM user WHERE id = 100;（id 是主键，type 为 const）。
eq_ref	多表连接时，被连接表的每行通过唯一索引/主键匹配（如 LEFT JOIN 中被连接表的关联字段是主键）。	user（主键 id）与 order（主键 id，外键 user_id 关联 user.id）连接，order 表的 type 为 eq_ref。
ref	通过非唯一索引匹配多行数据（如 WHERE name = '张三'，name 是普通索引）。	EXPLAIN SELECT * FROM user WHERE name = '张三';（name 是普通索引，type 为 ref）。
range	通过索引范围查询（如 BETWEEN、IN、>、< 等），只扫描索引的某一段。	EXPLAIN SELECT * FROM user WHERE age BETWEEN 20 AND 30;（age 是索引，type 为 range）。
index	扫描整个索引树（而非数据行），比全表扫描快（但仍需扫描全部索引）。	EXPLAIN SELECT name FROM user;（name 是索引，无需回表，但需扫描整个 name 索引）。
ALL	全表扫描（遍历整个表找数据），效率极低，必须避免（通常是未建索引或索引失效导致）。	EXPLAIN SELECT * FROM user WHERE age = 25;（age 未建索引，type 为 ALL）。

　

3、实际分析中的type应用场景

type列的分析需结合 表数据量、索引设计、查询条件 综合判断，以下是3个典型实战场景：

场景1、从ALL（全表扫描）优化为ref（索引查找）

（1）问题SQL

EXPLAIN SELECT id, username FROM user WHERE age = 25;

查询“年龄为25岁的用户列表”，表user有10万行数据，age未建索引。

（2）EXPLAIN结果：

type = ALL（全表扫描），rows = 100000（预计扫描10万行），执行耗时约1.2秒。

（3）优化思路：

age是查询条件，且查询频率高，为其建立普通索引：

CREATE INDEX idx_user_age ON user(age);

（4）优化后EXPLAIN结果：

type = ref，rows = 500（预计扫描500行，符合25岁的用户数量），执行耗时降至0.01秒。

分析：type 从ALL提升到ref，本质是从“遍历全表”变为“通过索引快速定位符合条件的行”，效率提升百倍。

　

场景2、从range（范围扫描）优化为eq_ref（精确匹配）

（1）问题SQL

多表连接查询“用户及其最新订单”，order表的 user_id 是普通索引，create_time 是普通索引。

EXPLAIN
SELECT u.username, o.order_no 
FROM user u 
JOIN `order` o ON u.id = o.user_id 
WHERE o.create_time = (SELECT MAX(create_time) FROM `order` WHERE user_id = u.id);

（2）EXPLAIN结果

order表的type = range（子查询中通过 user_id范围扫描找最大时间），rows = 10（每个用户平均扫描10条订单），1000个用户查询耗时约0.8秒。

（3）优化思路

order表的查询核心是“按user_id找最新create_time”，可建立联合唯一索引 idx_user_create (user_id, create_time DESC)（create_time降序，确保第一条就是最新订单）。

CREATE UNIQUE INDEX idx_user_create ON `order`(user_id, create_time DESC);

（4）优化后EXPLAIN结果

order表的 type = eq_ref（通过联合索引精确匹配user_id，且直接取第一条最新记录），rows = 1，1000个用户查询耗时降至0.05秒。

分析：type 从 range 提升到 eq_ref，是因为联合索引将“范围查找”变为“精确匹配+有序取数”，减少了扫描行数。

　

场景3、警惕index（全索引扫描）的隐性低效

（1）问题SQL

EXPLAIN SELECT username, email FROM user;

查询“所有用户的用户名和邮箱”，user表有20万行，有一个普通索引 idx_username (username)（仅包含username字段）。

（2）EXPLAIN结果

type = index（全索引扫描，遍历idx_username索引），但Extra列显示 Using index; Using filesort（需回表取 email，且排序），执行耗时约0.5秒。

（3）误区

有人认为type = index 比 ALL好，就无需优化，但此处index效率仍低——因为索引只包含username，需要“回表”（通过索引中的主键去表中查email），产生大量随机IO。

（4）优化思路

建立覆盖索引（包含查询所需的所有字段）：idx_username_email (username, email)，使查询无需回表。

CREATE INDEX idx_username_email ON user(username, email);

（5）优化后EXPLAIN结果

type = index（仍为全索引扫描），但Extra列显示 Using index（仅扫描索引，无需回表），执行耗时降至0.08秒。

（6）分析

type虽仍是index，但通过覆盖索引消除了回表，效率大幅提升。这说明：即使type等级不高，也可通过索引优化进一步提升性能。

　

4、type分析的关键注意事项

（1）不追求“最优”，只追求“合适”

并非所有查询都需要 const/eq_ref——若表只有10行数据，ALL（全表扫描）可能比 ref（索引查找）更高效（索引本身有维护成本）。只有当表数据量较大（通常万级以上）时，type 等级才需重点优化。

（2）结合 rows 和 Extra 综合判断

type是“定性”指标，rows（预计扫描行数）是“定量”指标 —— 例如 type = range 但 rows = 10万，仍可能低效；同时需关注 Extra 中的 Using filesort（文件排序）、Using temporary（临时表）等“坏指标”，它们可能抵消 type 的优势。

（3）避免“索引失效”导致type降级

即使建了索引，若查询条件中对索引列使用了函数（如 DATE(create_time) = '2024-01-01'）、隐式类型转换（如 id = '100'，id 是int型），会导致索引失效，type 可能从 ref 降级为 ALL。

　

5、type分析的总结

type列是EXPLAIN分析中的“效率晴雨表”，其核心价值在于快速定位数据访问的瓶颈类型。实际优化中，应优先将ALL（全表扫描）、index（全索引扫描）等低效类型，通过“建合适的索引”“优化查询条件”提升为ref、range等高效类型；同时结合rows、Extra等列，避免只看type而忽略其他性能损耗点，最终实现 SQL 执行效率的最大化。

　

六、核心列Extra（识别性能杀手）的深度解读

1、Extra 列的核心作用

（1）暴露隐性性能损耗

比如 Using temporary（临时表）、Using filesort（文件排序）等，这些操作会极大增加 IO 和 CPU 开销，是慢 SQL 的常见根源。

（2）验证索引有效性

通过 Using index（覆盖索引）确认索引是否被 “完美利用”，或通过 Using where 区分 “索引过滤” 和 “全表过滤”。

（3）揭示执行逻辑细节

比如是否走了索引下推（Using index condition）、是否有分区过滤（Using partition）等，帮助理解 MySQL 优化器的决策逻辑。

　

2、Extra 常见取值分类与实际分析场景

根据对性能的影响，Extra 取值可分为 “理想型”（高效）、“警惕型”（有损耗）、“危险型”（严重瓶颈） 三类，下面结合实际场景说明如何分析。

（1）理想型：代表查询执行高效，无需优化

这类取值说明 MySQL 以最优方式执行查询，无额外性能损耗。

Extra 取值	含义	实际分析价值
Using index	触发“覆盖索引”（Covering Index）：查询所需字段全部在索引中，无需回表查主键聚簇索引。	这是索引利用的“最优状态”，说明索引设计精准匹配查询需求。 例：select id, name from user where age=20，若索引为 idx_age_name(id,age,name)，则会显示 Using index。
Using index condition	触发“索引下推”（Index Condition Pushdown, ICP）：存储引擎层直接用索引过滤条件，减少回表数据量。	说明 MySQL 启用了优化特性，避免了“先回表再过滤”的冗余操作，尤其对联合索引优化明显。 例：select * from user where age>20 and name like '张%'，若索引为 idx_age_name(age,name)，会显示此值（存储引擎先过滤 age>20，再过滤 name）。
Using where	仅用 WHERE 条件过滤数据，且过滤是在“索引查找后”或“全表扫描后”（需结合 type 判断）。	若 type 是 range/ref，则是“索引过滤后再筛选”（高效）；若 type 是 ALL，则是“全表扫描后筛选”（低效）。

（2）警惕型：存在性能损耗，需评估优化必要性

这类取值代表存在额外开销，但损耗程度取决于数据量，需结合实际场景判断是否优化。

Extra 取值	含义	实际分析与优化思路
Using temporary	MySQL 为存储中间结果创建了内存临时表（若数据量大则转磁盘临时表）。	常见场景：GROUP BY/DISTINCT 未使用索引、ORDER BY 与 GROUP BY 字段不一致。 优化：创建包含 GROUP BY/ORDER BY 字段的联合索引，避免临时表。 例：select age from user group by age，若无 age 索引，会显示 Using temporary；添加 idx_age(age) 后消失。
Using filesort	MySQL 无法利用索引排序，需在内存/磁盘中进行额外排序（“文件排序”并非特指磁盘文件，内存满了才会写磁盘）。	最常见的慢 SQL 根源之一，尤其对大表排序时耗时极长。 场景：ORDER BY 字段未包含在索引中，或与索引顺序不一致。 优化：将 ORDER BY 字段加入联合索引，且保持索引顺序与 ORDER BY 一致（注意 ASC/DESC 需匹配）。 例：select id from user where age=20 order by name，若索引为 idx_age(age)，会显示 Using filesort；改为 idx_age_name(age,name) 后消失。
Using join buffer (hash join)	多表连接时，MySQL 为小表创建了连接缓冲区（Hash Join 专用）。	若缓冲区足够（join_buffer_size 配置合理），损耗较小；若缓冲区不足导致多次 IO，则需优化。 优化：1. 确保小表被驱动（MySQL 通常会自动选择小表，但可通过 STRAIGHT_JOIN 强制）；2. 适当调大 join_buffer_size（避免过大导致内存浪费）。

（3）危险型：代表严重性能瓶颈，必须优先优化

这类取值通常意味着查询执行效率极低，对大表几乎“致命”，需立即处理。

Extra 取值	含义	实际分析与优化思路
Using filesort; Using temporary	同时存在“文件排序”和“临时表”，是双重性能杀手。	常见于 GROUP BY + ORDER BY 且未使用索引的场景，对大表会导致查询耗时呈指数级增长。 例：select age, count(*) from user group by age order by count(*)，若无索引，会同时出现两者。 优化：若 count(*) 无法避免排序，可考虑预聚合（如用定时任务生成统计报表），或使用覆盖索引减少临时表/排序压力。
Using index for group-by	虽然用了索引，但 GROUP BY 操作仍需在索引上做额外处理（类似覆盖索引的变种，但效率略低于纯 Using index）。	通常无需紧急优化，但可检查是否能通过调整索引结构（如包含更多聚合字段）进一步提升效率。
Range checked for each record (index map: N)	MySQL 无法确定合适的索引，为每一行记录都尝试匹配索引（“逐行索引检查”），效率极低。	常见于 WHERE 条件中使用了不适合索引的表达式（如 age+1=20），或索引选择性太差。 优化：1. 避免在索引字段上做运算/函数（如改为 age=19）；2. 重建选择性更高的索引。

　

3、实际案例：通过Extra定位并优化慢SQL

假设我们有一张order表（100万条数据），结构如下：

CREATE TABLE `order` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `order_no` varchar(50) NOT NULL,
  `create_time` datetime NOT NULL,
  `amount` decimal(10,2) NOT NULL
);

　

场景1：查询用户的订单并按创建时间排序

（1）原始SQL

SELECT id, order_no FROM `order` WHERE user_id = 123 ORDER BY create_time DESC;

（2）EXPLAIN结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ALL	NULL	NULL	NULL	NULL	1000000	Using where; Using filesort

（3）分析

type=ALL（全表扫描），Extra=Using where; Using filesort（全表过滤后还要排序），性能极差。

（4）优化方案

创建 user_id + create_time 的联合索引（覆盖查询字段）：

CREATE INDEX idx_user_create ON `order`(user_id, create_time DESC);

（5）优化后 EXPLAIN 结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ref	idx_user_create	idx_user_create	8	const	10	Using index

（6）关键变化

type提升为ref（索引查找），Extra变为 Using index（覆盖索引，无排序、无回表），查询耗时从秒级降至毫秒级。

　

场景2：统计用户各日期的订单总金额

（1）原始 SQL

SELECT DATE(create_time) AS order_date, SUM(amount) 
FROM `order` 
WHERE user_id = 123 
GROUP BY order_date 
ORDER BY order_date DESC;

（2）EXPLAIN 结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ALL	NULL	NULL	NULL	NULL	1000000	Using where; Using temporary; Using filesort

（3）分析

同时出现 Using where（全表过滤）、Using temporary（临时表存分组结果）、Using filesort（排序分组结果），三重性能损耗。

（4）优化方案

创建包含 user_id + create_time + amount 的联合索引（覆盖分组、过滤、聚合字段）：

CREATE INDEX idx_user_create_amount ON `order`(user_id, create_time DESC, amount);

（5）优化后 EXPLAIN 结果

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ref	idx_user_create_amount	idx_user_create_amount	8	const	10	Using index; Using temporary

（6）关键变化

type变为 ref，Extra只剩 Using temporary（分组仍需临时表，但避免了全表扫描和文件排序），查询耗时从 5 秒降至 0.1 秒。若要进一步优化，可考虑预聚合（如每日定时计算用户的日期金额统计，存入中间表）。

　

4、Extra的分析总结

（1）优先处理“危险型”

一旦出现 Using filesort; Using temporary 或 Range checked for each record，必须立即优化，尤其是大表。

（2）追求“理想型”

以Using index（覆盖索引）和Using index condition（索引下推）为优化目标，这是最高效的执行方式。

（3）结合其他列综合判断

Extra 不能孤立看——比如Using where若搭配type=ALL是坏事，但搭配type=ref是好事；Using temporary若数据量小（rows少），损耗可接受，无需过度优化。

总之，Extra列是SQL调优中“见微知著”的关键，很多隐藏的性能瓶颈都能通过它精准定位。

　

七、实际场景：用 EXPLAIN 定位并优化 SQL

场景1：全表扫描导致查询缓慢

（1）问题 SQL

查询年龄大于 25 且状态为 1 的用户（age 和 status 均未建索引）。

EXPLAIN SELECT id, name FROM user WHERE age > 25 AND status = 1;

（2）EXPLAIN 关键输出

type: ALL（全表扫描）

possible_keys: NULL（无可用索引）

rows: 10000（预估扫描 1 万行）

（3）优化方案

建立复合索引 idx_age_status (age, status)（根据查询条件的过滤顺序）。

（4）优化后 EXPLAIN 输出

type: range（索引范围扫描）

key: idx_age_status（实际使用索引）

rows: 500（预估扫描行数大幅减少）

　

场景2：ORDER BY 导致文件排序

（1）问题 SQL

查询状态为 1 的用户，并按创建时间排序（status 有索引，但 create_time 无）

EXPLAIN SELECT id, name FROM user WHERE status = 1 ORDER BY create_time;

（2）EXPLAIN 关键输出

type: ref（使用了 status 索引）
Extra: Using where; Using filesort（存在文件排序）

（3）优化方案

建立联合索引 idx_status_create_time (status, create_time)（将过滤字段和排序字段组合，利用索引有序性避免排序）。

（4）优化后EXPLAIN输出

Extra: Using where; Using index（覆盖索引，无文件排序）

　

场景3：GROUP BY 导致临时表

（1）问题SQL

按用户所在城市分组，统计用户数（city 无索引）。

EXPLAIN SELECT city, COUNT(*) FROM user GROUP BY city;

（2）EXPLAIN 关键输出

type: ALL（全表扫描）
Extra: Using temporary; Using filesort（临时表 + 文件排序）

（3）优化方案

给 city 建立索引 idx_city (city)

（4）优化后EXPLAIN输出

type: index（扫描 city 索引）

Extra: Using index（覆盖索引，无临时表和排序）

　

八、使用 EXPLAIN 的常见误区

（1）只看 key 列，忽略 type

即使 key 显示使用了索引，若 type 是 index（全索引扫描），效率仍可能较低（需确认是否为范围查询）。

（2）相信 rows 是精确值

rows 是优化器的预估值，并非实际扫描行数，需结合 type 和 Extra 综合判断。

（3）忽略 Extra 中的 "Using filesort" 和 "Using temporary"

这两个是典型的性能杀手，即使使用了索引，也可能因排序/分组未利用索引而效率低下。

　

九、总结

EXPLAIN 是 SQL 调优的“显微镜”——它不直接优化 SQL，但能清晰暴露执行计划中的问题。

调优的核心思路是：

（１）通过 EXPLAIN 让 type 尽量靠近 ref / range，避免 ALL；

（２）让 Extra 避免 Using filesort / Using temporary，争取 Using index。

掌握EXPLAIN是成为MySQL优化高手的必备技能。