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数据库的潜力,为应用程序提供更出色的性能。