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 SELECT
T1.serial_id,
T1.top_flag,
T1.top_time,
T1.create_time,
T2.nick_name,
group_concat( T3.province_code ),
group_concat( T5.channel_type )
FROM
bus_work_order_info T1
LEFT JOIN sys_user T2 ON T1.create_by = T2.user_name
LEFT JOIN bus_work_order_range T3 ON T1.serial_id = T3.serial_id
LEFT JOIN bus_work_order_channel T5 ON T1.serial_id = T5.serial_id
WHERE
T1.del_flag = '0'
GROUP BY
T1.serial_id
ORDER BY
T1.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字段将显示查询中涉及的分区的名称。
- ALL
如果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数据库的潜力，为应用程序提供更出色的性能。
标签：EXPLAIN,查询,索引,字段,详解,MySQL,NULL,id From： https://blog.51cto.com/xiuji/8694945