MySQL EXPLAIN详解

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

mysql_explain.jpg

什么是EXPLAIN?

mysql官网文档：dev.mysql.com/doc/refman/…

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

输出结果为：

我们逐行解读下这个信息

• 第一个行（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数据库的潜力，为应用程序提供更出色的性能。