当前位置：首页 > news >正文

MySql EXPLAIN 详解

news 2025/9/17 21:15:12

1、EXPLAIN介绍

EXPLAIN语句提供MySQL如何执行语句的信息。EXPLAIN返回SELECT语句中使用的每个表的信息并列出一行运行数据。它是按照MySQL在处理语句时读取表的顺序列出并输出到一个表格中。

2、查询示例

2.1、【explain + 表名】显示的是这个表的表结构。

2.2、【explain + select语句】显示的是这个语句执行时的信息。

3、explain 列详细介绍

3.1、explain 表

列名	JSON 名称	含义
`id`	`select_id`	`SELECT` 标识符
`select_type`	`None`	`SELECT` 类型
`table`	table_name	输出行的表
`partitions`	partitions	匹配的分区
`type`	access_type	联接类型
`possible_keys`	possible_keys	可能选择的索引
`key`	key	实际选择的索引
`key_len`	key_length	所选键的长度
`ref`	ref	列与索引的比较
`rows`	rows	估计要检查的行数
`filtered`	filtered	按表条件筛选的行百分比
`Extra`	None	其他信息

3.2、列名：id

id列是一个有顺序的编号，表示查询中每个SELECT子句的唯一标识符。在复杂查询中，可能会有多个SELECT语句，id值用于确定这些语句的执行顺序。一般来说，id值越大，执行优先级越高，越先执行；如果id值相同，则按照从上到下的顺序执行；如果id值为NULL，则表示这是一个结果集，通常来自UNION操作‌。

3.3、列名：select_type

SELECT 的类型，可以是下表中所示的任何类型。

`select_type 类型`	JSON 名称	含义
`SIMPLE`	None	简单SELECT：不涉及UNION或子查询的简单查询。
`PRIMARY`	None	最外层SELECT：查询中最外层的SELECT语句。在包含子查询或UNION的复杂查询中，最外层的操作会被标记为此类型。
`UNION`	None	UNION中的非首条SELECT：在UNION操作中，除第一个SELECT之外的后续SELECT语句。
`DEPENDENT UNION`	`dependent` (`true`)	依赖外部查询的UNION：UNION中第二个及以后的SELECT语句，并且依赖于外部查询的结果。性能警告：通常意味着效率较低，因为要对外部查询的每一行都执行一次。
`UNION RESULT`	`union_result`	UNION的结果集：表示从UNION查询的匿名临时表中获取结果的SELECT。
`SUBQUERY`	None	（非相关）子查询：子查询中的第一个SELECT，并且不依赖于外部查询的结果。通常子查询可以独立执行，效率尚可。
`DEPENDENT SUBQUERY`	`dependent` (`true`)	依赖外部查询的子查询：子查询中的第一个SELECT，并且依赖于外部查询的结果。性能警告：必须为外部查询的每一行都执行一次，效率极低，是需要优化的重点。
`DERIVED`	None	派生表：在FROM子句中包含的子查询（也称为派生表），MySQL会将其结果物化为一个临时表供外部查询使用。性能提示：在MySQL 8.0中，优化器会尝试合并派生表以提升性能。
`DEPENDENT DERIVED`	`dependent` (`true`)	依赖外部查询的派生表：派生表依赖于另一个表（通常是外部查询）的数据。
`MATERIALIZED`	`materialized_from_subquery`	物化子查询：优化器选择将子查询的结果物化（即实际填充到一个临时表中），然后通过连接此临时表来执行查询。这通常用于优化 IN (subquery) 等场景。
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	不可缓存子查询：子查询的结果无法被缓存，必须为外部查询的每一行都重新计算。性能警告：效率非常低，通常因为子查询中包含 volatile 函数（如 RAND(), NOW()）或用户变量。
`UNCACHEABLE UNION`	`cacheable` (`false`)	不可缓存的UNION：属于不可缓存子查询（UNCACHEABLE SUBQUERY）中的UNION操作。

建议：

性能友好型：
- SIMPLE, PRIMARY, UNION：通常是高效查询的基础组成部分。
- SUBQUERY (非相关)：可以独立执行一次，效率尚可。
- MATERIALIZED：优化器的一种优化策略，用空间（临时表）换时间，有时能极大提升性能。

需要重点关注和优化：
- DEPENDENT SUBQUERY：头号性能杀手。因为它对外部查询的每一行都会执行一次，如果外部查询返回1万行，这个子查询就会执行1万次。应极力尝试用 JOIN 重写。
- UNCACHEABLE SUBQUERY：另一个性能杀手。同样需要多次执行，且无法缓存结果。
- DEPENDENT UNION：结合了 DEPENDENT 和 UNION 的双重开销，效率很低。
- DERIVED：在旧版本（如5.7）中，创建临时表可能带来较大开销。虽然在8.0中得到了优化（派生表合并），但大量数据时仍需注意。

【3.1】和【3.3】中的JSON名称使用【EXPLAIN FORMAT=JSON + 查询语句】时就可以看见，如下图：

3.4、列名：table

输出行所指向的表的名称。这也可以是以下值之一：

可以是实际的表名、视图名。
可以是 <derivedN> 格式，其中 N 是id值，表示该行访问的是id为N的查询产生的派生表。
可以是 <unionM,N> 格式，表示该行访问的是id为M和N的查询UNION后产生的临时表。

3.5、列名：partitions

查询将从其中匹配记录的分区。对于非分区表，该值为NULL。

3.6、列名：type

连接类型，它表示了MySQL决定如何查找表中的行。

类型	含义	性能优先级（数字越小性能越好）
system	该表只有一行（等于系统表）。这是const连接类型的一种特殊情况。	1
const	表最多有一个匹配行，在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器的其余部分视为常量。Const表非常快，因为它们只被读取一次。	2
eq_ref	在JOIN查询中，使用主键或唯一索引作为连接条件。对于来自前一个表的每一行组合，从当前表中读取一行。是除 system 和 const 之外最好的连接类型。	3
ref	对于来自前一个表的每个行组合，将从这个表中读取具有匹配索引值的所有行。如果连接只使用键的最左边的前缀，或者键不是PRIMARY key或UNIQUE索引（换句话说，如果连接不能根据键值选择单行），则使用ref。如果所使用的键只匹配几行，则这是一个很好的连接类型。简单来说就是使用非唯一索引进行等值扫描。	4
fulltext	连接是使用FULLTEXT索引执行的。	9
ref_or_null	这种连接类型类似于ref，但是MySQL会对包含NULL值的行进行额外的搜索。这种连接类型优化最常用于解析子查询。	7
index_merge	此连接类型表明使用了索引合并优化。在这种情况下，输出行的键列包含所使用的索引列表，key_len包含所使用索引的最长键部分列表。	6（一般情况下）
unique_subquery	这种类型替换了以下形式的一些IN子查询的eq_ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) Unique_subquery只是一个索引查找函数，它完全取代了子查询以提高效率。	8
index_subquery	这种连接类型类似于unique_subquery。它取代了IN子查询，但它适用于以下形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)	8
range	只检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。key_len包含使用的最长的键部分。这种类型的ref列为NULL。当键列与常量进行比较时，关键操作符 =、<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE 或 IN（）操作符中的任何一个。	5
index	除了扫描索引树之外，索引连接类型与ALL相同。这有两种情况： 1、如果索引是一个覆盖查询的索引，并且可以用来满足表中所需的所有数据，则只扫描索引树。仅索引扫描通常比ALL更快，因为索引的大小通常小于表数据的大小。 2、执行全表扫描时，使用从索引读取数据，按索引顺序查找数据行。使用索引不会出现在Extra列中。全索引扫描。遍历整个索引树来查找数据。虽然避免了全表扫描，但通常仍然很慢。Extra列如果出现 Using index，则表示是覆盖索引，性能会好很多。	10
ALL	全表扫描。性能最差，通常意味着没有合适的索引可用，必须检查表中的每一行。这是必须要优化的信号。	11

3.7、列名：possible_keys

显示查询可能会使用哪些索引来查找行。基于查询条件和表结构分析得出。如果此列为 NULL，并不意味着没有索引可用，可能只是需要检查表结构和 WHERE 条件。

3.8、列名：key

显示查询实际决定使用的索引。这是优化器从 possible_keys 中选择的、成本最低的索引。如果为 NULL，则表示优化器决定不使用任何索引。注意：有可能 key 列显示的索引并不在 possible_keys 列表中，这表明可能使用了覆盖索引。

3.9、列名：key_len

key_len 列表示MySQL决定使用的键的长度。key_len 的值使您能够确定MySQL实际使用索引的哪些部分。如果键列为 NULL，则 key_len 列也为 NULL。由于键存储格式的原因，可以为 NULL 的列的键长度比 NOT NULL 列的键长度大1。

3.10、列名：ref

ref 列显示哪些列或常量与 key 列中指定的索引进行比较，以便从表中选择行。如果值是 func，则使用的值是某个函数的结果。

3.11、列名：rows

rows 列表示MySQL认为执行查询必须检查的行数。对于InnoDB表，这个数字是一个估计值，可能并不总是准确的。

3.12、列名：filtered

筛选的列表示按表条件筛选的表行的估计百分比。最大值是100，这意味着没有对行进行过滤。理想值是100%，表示返回的数据完全符合WHERE条件。数值越低，需要读取的无关数据越多。从100开始递减的值表示过滤量在增加。Rows显示检查的估计行数，Rows × filtered显示与下表连接的行数。例如，如果rows为1000，filtered为50.00(50%)，则要与下表连接的行数为1000 × 50% = 500。

3.13、列名：Extra

这一列包含关于MySQL如何解析查询的附加信息，通常包含了非常关键的优化线索。没有单独的JSON属性对应于Extra列。

常见值	含义
Using index (覆盖索引)	查询所需的所有数据都可以在索引中找到，无需回表读取数据行。这是非常好的性能信号。
Using where	服务器层在存储引擎返回行之后，又使用了WHERE条件进行过滤。如果 type 是 index 或 ALL，出现这个值通常不是好兆头。
Using temporary	MySQL为了执行查询，必须创建一张临时表来保存中间结果。这通常发生在 GROUP BY 和 ORDER BY 子句涉及不同列的查询中。需要优化。
Using filesort	MySQL无法使用索引来完成排序操作，需要在内存或磁盘上进行额外的排序。需要优化，考虑为 ORDER BY 子句添加索引。
Using join buffer (Block Nested Loop)	表示JOIN操作没有使用索引，需要用到连接缓冲区。需要优化，考虑为JOIN条件添加索引。
Select tables optimized away	使用了一些聚合函数（如 MIN()/MAX()）来访问某个索引列，优化器知道可以直接从索引中获取值而无需遍历整个索引。

其他详细值请查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

为了更直观地理解，我们可以将它们放入一个更完整的性能梯队中：

性能等级	访问类型 (type)
最优	system > const > eq_ref > ref > range
中等	index_merge (有时) > ref_or_null > index_subquery > unique_subquery
特殊	fulltext (为特定场景设计)
需优化	index (全索引扫描)
必须优化	ALL (全表扫描)