MySQL EXPLAIN详解

MySQL数据库是许多Web应用程序的底层支持，而查询性能的优化是确保系统高效运行的关键。在MySQL中，EXPLAIN是一项强大的工具，可帮助开发者深入了解查询语句的执行计划，从而更好地优化查询性能。本文将详细解析MySQL的EXPLAIN关键字，以揭开查询执行计划的面纱。

什么是EXPLAIN?

mysql官网文档：https://dev.mysql.com/doc/refman/8.0/en/explain.html

EXPLAIN是MySQL提供的一种查询优化工具，通过分析查询语句的执行计划，我们可以深入了解数据库引擎是如何处理查询的。执行EXPLAIN语句后，MySQL将返回一组关于查询执行的信息，其中包括访问表的方式、使用的索引、估计的行数等关键信息。

EXPLAIN示例

我们有四张表,bus_work_order_info工单表T1，sys_user用户表T2,bus_work_order_range发布省份表T3,bus_work_order_channel发布渠道表T5，T3,T5表的serial_id关联T1表的serial_id,T2表的user_name关联T1表的create_by,T1,T3,T5的字段serial_id都创建了索引，T2表的user_name创建了索引。执行以下sql:

EXPLAIN SELECTT1.serial_id,T1.top_flag,T1.top_time,T1.create_time,T2.nick_name,group_concat( T3.province_code ),group_concat( T5.channel_type ) 
FROMbus_work_order_info T1LEFT JOIN sys_user T2 ON T1.create_by = T2.user_nameLEFT JOIN bus_work_order_range T3 ON T1.serial_id = T3.serial_idLEFT JOIN bus_work_order_channel T5 ON T1.serial_id = T5.serial_id 
WHERET1.del_flag = '0' 
GROUP BYT1.serial_id 
ORDER BYT1.top_flag,T1.top_time DESC,T1.create_time DESC

输出结果为：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	T1	NULL	ALL	index_serial_id	NULL	NULL	NULL	1403	10.00	Using where; Using temporary; Using filesort
1	SIMPLE	T2	NULL	ref	index_user_name	index_user_name	122	cnpctest.T1.create_by	1	100.00	Using where
1	SIMPLE	T3	NULL	ref	index_serial_id	index_serial_id	259	cnpctest.T1.serial_id	1	100.00	NULL
1	SIMPLE	T5	NULL	ref	index_serial_id	index_serial_id	259	cnpctest.T1.serial_id	1	100.00	NULL

我们逐行解读下这个信息

第一个行（T1表）

type: ALL 表示全表扫描，没有使用索引。

rows: 1403 表示MySQL估计需要检索1403行。

filtered: 10.00 表示过滤效果较差。

Extra: Using where; Using temporary; Using filesort 表示使用了 WHERE 子句过滤，需要使用临时表进行排序。

第二个行（T2表）

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: Using where 表示使用了 WHERE 子句过滤。

第三个行（T3表）

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: NULL 表示无额外信息。

第四个行（T5表）

type: ref 表示使用了索引进行等值比较。

rows: 1 表示MySQL估计需要检索1行。

filtered: 100.00 表示过滤效果好。

Extra: NULL 表示无额外信息。

EXPLAIN输出的字段解析

执行EXPLAIN语句后，输出结果中的字段提供了关于查询执行计划的详细信息。以下是一些常见的输出字段及其含义：

id

查询的唯一标识符。对于复杂查询，可能会有多个id值，表示查询的执行顺序。

id的取值

单一查询：如果查询是简单的SELECT语句，id将为一个整数值，表示查询的顺序。

复合查询：对于包含子查询或UNION的复杂查询，id可能包含多个值，每个值代表查询计划中的一个步骤。

id值的排序

从1开始递增：对于单一查询，id值从1开始递增，依次标识查询中的每个步骤。

子查询和UNION：对于复合查询，id值的排序取决于执行计划中步骤的执行顺序。

id的含义

主查询的id为1：在复合查询中，主查询的id通常为1，表示最外层的查询。

子查询的id大于1：对于子查询，id的值大于1，用于表示子查询的执行顺序。

id的关联性

相同id值的关联子查询：在复合查询中，如果多个查询步骤有相同的id值，它们可能是关联的，表示它们在同一级别执行。

父子查询关系：id值较小的步骤通常是id值较大的步骤的父查询。

分析查询计划

顺序分析：观察id值的顺序，可以了解查询的执行顺序。

关联关系：通过id值的关联关系，可以理解查询计划中不同步骤之间的父子关系。

select_type

查询的类型，常见的有:

SIMPLE: 简单的SELECT查询，不包含子查询或UNION。
PRIMARY: 最外层的SELECT查询。
SUBQUERY: 子查询。
DERIVED: 衍生表，通过FROM子句中的子查询获得的表。

SIMPLE：简单查询

表示查询不包含子查询或UNION操作。

这是最基本的查询类型，不涉及复杂的操作。

PRIMARY：最外层查询

表示查询中的最外层SELECT语句。

在复杂查询中，最外层的SELECT语句的select_type值为PRIMARY。

SUBQUERY：子查询

表示在查询中包含的子查询。

子查询可以是嵌套在主查询中的查询操作。

DERIVED：衍生表

表示从FROM子句中的子查询获取的衍生表。

在查询中创建了一个临时表，通常发生在包含GROUP BY或DISTINCT的查询中。

UNION：UNION操作

表示在查询中使用了UNION或UNION ALL操作。

多个查询结果集合并的情况。

UNION RESULT：UNION结果

表示合并了多个查询结果的最终结果。

在包含UNION的查询中，表示最终的合并步骤。

SUBQUERY N：带编号的子查询

表示查询中的第N个子查询，N为正整数。

在包含多个子查询的查询中，通过编号区分不同的子查询。

DEPENDENT SUBQUERY：依赖的子查询

表示子查询的执行依赖于外部查询的结果。

子查询的每次执行都依赖于外部查询的结果。

UNCACHEABLE SUBQUERY：不可缓存的子查询

表示子查询的结果不能被缓存。

子查询包含了不可缓存的元素，例如用户变量或RAND()函数。

DEPENDENT UNION：依赖的UNION

表示UNION中的某个查询依赖于外部查询的结果。

与DEPENDENT SUBQUERY类似，但用于UNION操作。

UNCACHEABLE UNION：不可缓存的UNION

表示UNION中的某个查询结果不能被缓存。

与UNCACHEABLE SUBQUERY类似，但用于UNION操作。

table

涉及的表，包括表名或子查询的别名。

表名

如果查询操作直接涉及表，table字段将显示表的名称。

子查询别名

如果查询中包含子查询，table字段可能显示子查询的别名。

子查询的表别名

在包含子查询的情况下，如果子查询中使用了表别名，table字段将显示子查询的表别名。

衍生表的别名

如果查询中使用了DERIVED，表示创建了一个衍生表（通常是从FROM子句中的子查询中获取的），table字段将显示衍生表的别名。

partitions

查询中涉及的分区（如果有）的信息。分区是MySQL中一种对表进行水平分割的技术，可以根据某个列的值将表数据划分为不同的分区。

分区名称

如果查询涉及分区表，并且分区列上有索引，partitions字段将显示查询中涉及的分区的名称。

如果partitions字段的取值是"ALL"，表示查询操作涉及表的所有分区。这可能是因为查询条件没有涉及到分区列，或者分区列上没有索引。

NULL

如果partitions字段的取值是NULL，表示表不是分区表，或者查询不涉及分区表。针对非分区表或者没有涉及分区列的查询，partitions字段通常是NULL。

type

访问表的方式，可能是const、eq_ref、range、index、all等。

const：常量

表示通过索引一次就找到了，常用于等值比较。例如，在主键或唯一索引上进行等值查询。

eq_ref：唯一索引查找

类似于const，区别在于使用的是非唯一索引。例如，在使用主键或唯一索引进行连接查询时。

ref：非唯一索引查找

表示使用非唯一索引进行等值比较。例如，通过普通索引查找某个值。

range：范围查找

表示使用索引进行范围查找，通常出现在带有BETWEEN、IN、>,<等条件的查询中。例如，WHERE column_name BETWEEN 10 AND 20。

index：全索引扫描

表示查询会遍历整个索引，而不是表中的实际行数。这可能是因为查询的列没有被索引覆盖，或者查询不使用索引而进行全表扫描。

ALL：全表扫描

表示查询将对整个表进行扫描，而不使用索引。这是效率最低的情况，尽量避免在大表上出现type为ALL的情况。

system：系统表

表示查询将对系统表进行扫描，通常是一些固定的小表。

NULL：无效的或不可用的索引

表示没有使用索引，通常是因为相关的索引不可用或不适用。

possible_keys

在查询中可能用于加速数据检索的索引。

多个索引

如果查询中有多个可以使用的索引，它们将以逗号分隔列在possible_keys字段中。

NULL值

如果possible_keys字段的值为NULL，表示在查询中没有找到可以使用的索引。这可能导致查询执行时需要全表扫描，影响性能。

覆盖索引

如果查询的列在某个索引中全部包含，这个索引可能成为覆盖索引。覆盖索引可以提高性能，因为它不需要回表查找实际的行数据。

key

在查询执行中实际使用的索引。

NULL值

如果key字段的值是NULL，表示在查询中没有使用索引。这可能导致查询执行时需要全表扫描，影响性能。

具体索引名称

如果key字段的值是一个具体的索引名称，表示在查询中使用了这个索引。索引名称将与表的索引名称一致。

覆盖索引

如果key字段使用了索引，并且在Extra字段中显示了Using index，表示使用了覆盖索引。覆盖索引指的是查询所需的数据都包含在索引中，无需回表查找实际的行数据，通常提高性能。

多个索引

如果查询中有多个可以使用的索引，它们将以逗号分隔列在key字段中。这表示MySQL优化器可能在多个索引中选择一个用于查询执行。

key_len

使用的索引的长度。

key_len的单位

key_len表示索引的长度，以字节为单位。

key_len值为1的情况

如果key_len的值是1，表示使用的是单列索引，索引列的长度为1个字节。

多列索引的情况

如果使用的是多列索引，key_len将显示这些列长度之和。例如，如果有一个包含两个INT类型列的索引，且这两列的长度分别为4个字节，那么key_len将显示8。

字符串类型的索引

如果索引包含字符串类型的列，key_len将根据字符集和存储引擎的不同而有所不同。例如，UTF-8字符集的字符串索引可能需要多个字节来表示一个字符。

ref

关于索引的使用方式和关联条件的信息。

ref值的含义

ref字段的值指示了连接表时所使用的索引，通常与关联条件中的列有关。如果没有连接操作，ref字段可能显示NULL。

多个ref值

如果查询中有多个连接条件，ref字段的值将以逗号分隔，分别对应不同的连接条件。

const值

如果ref的值为const，表示使用的是常量值进行连接，通常出现在使用常量进行等值连接的情况下。例如，在连接时使用了类似WHERE column_name = 10的条件。

复杂条件的情况

如果连接条件比较复杂，ref字段可能显示多个值，反映了不同连接条件的情况。这可能涉及到多个索引和多个关联条件。

rows

查询执行时估计的检索行数。

rows的含义

rows字段表示在执行查询时，MySQL估计需要检索的行数。这是一个估计值，不是实际的查询结果行数。

单表查询

在单表查询中，rows表示预计从表中检索的行数。

多表查询

在多表连接查询中，rows表示联接操作后预计返回的行数。

对于联接操作，rows的值可能会受到连接条件、索引的影响。

索引使用：

当type字段显示为const、eq_ref、ref、range等时，rows通常是相对准确的估计。

当type字段显示为ALL时，rows可能是一个较大的值，表示将对整个表进行扫描。

优化器统计信息：

MySQL优化器根据表的统计信息和索引信息来估计rows的值。

通过ANALYZE TABLE命令可以更新表的统计信息，有助于优化器更准确地估计行数。

filtered

在查询过程中过滤的行的百分比

filtered的取值范围

filtered的值是一个百分比，范围从0到100。0表示没有任何过滤，100表示所有的行都被过滤。

filtered的含义

filtered表示通过使用索引和条件进行过滤后，最终返回的行数相对于扫描行数的百分比。这个值越高，表示查询条件和索引更有效地过滤了不符合条件的行。

单表查询

在单表查询中，filtered表示使用索引和条件过滤掉的行的百分比。

多表查询

在多表连接查询中，filtered表示在连接操作后使用索引和条件过滤掉的行的百分比。

NULL值

如果filtered的值是NULL，表示MySQL无法估计或优化器没有使用统计信息。

统计信息的重要性

filtered的值受到表统计信息的影响，通过ANALYZE TABLE命令可以更新表的统计信息，有助于优化器更准确地估计过滤效果。

Extra

查询执行计划的一些额外信息，如Using index、Using where、Using temporary等。这个字段可以包含多个标志，每个标志之间以分号（;）分隔。

Using where

表示MySQL将在存储引擎层使用WHERE条件来过滤行。这意味着在检索出的结果集中，可能会有额外的条件过滤。

Using index

表示MySQL将使用覆盖索引，即查询可以通过索引的信息满足WHERE条件和选择列表，而无需回表查找实际的行数据。

Using temporary

表示MySQL在查询过程中需要创建一个临时表，通常发生在排序或分组操作时。

Using filesort

表示MySQL将对结果进行文件排序，通常发生在没有使用索引的ORDER BY操作中。

Range checked for each record

表示MySQL在索引范围扫描时需要对每一条记录进行额外的检查，通常出现在范围查询时。

Full scan on NULL key

表示MySQL执行了全表扫描，但只考虑了索引列为NULL的行。

Backward index scan

表示MySQL将反向扫描索引，通常发生在ORDER BY DESC操作中。

Distinct

表示MySQL将对结果进行DISTINCT操作，去除重复的行。

Fulltext index

表示MySQL使用了全文索引。

Filesort on disk

表示MySQL执行文件排序，并且结果集大到不能在内存中完成排序，因此需要使用磁盘临时文件。

如何利用EXPLAIN优化查询

查看访问类型

通过type字段了解访问表的方式，避免全表扫描，优先选择索引访问。

检查索引使用情况

确保查询中涉及的字段有适当的索引，避免不必要的索引扫描。

关注Extra字段

注意额外信息，如Using where、Using temporary等，有助于理解查询执行的额外开销。

避免不必要的数据检索

通过分析rows字段，可以了解估计的数据行数，避免不必要的大量数据检索。

总结

通过深入了解MySQL的EXPLAIN工具，开发者可以更好地理解查询语句的执行计划，从而有针对性地进行优化。通过选择合适的索引、避免全表扫描、关注查询执行的额外开销，我们可以提高数据库查询性能，确保系统的高效运行。

EXPLAIN是MySQL查询优化的得力助手，通过善于利用这一工具，我们能够更好地挖掘MySQL数据库的潜力，为应用程序提供更出色的性能。