MySQL数据库：explain执行计划详解

2022-09-02 13:08:25

一、执行计划：

执行计划是SQL语句经过查询分析器后得到的 抽象语法树 和 相关表的统计信息 作出的一个查询方案，这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果，所以可能会存在分析错误的情况，也就是存在执行计划并不是最优的情况。

通过explain关键字知道MySQL是如何执行SQL查询语句的，分析select 语句的性能瓶颈，从而改进我们的查询，explain的结果如下：

下面是有关各列的详细介绍，重要的有id、type、key、key_len、rows、extra：

1、id：

id列的编号就是select 的序列号，可以理解为SQL执行顺序的标识，有几个select 就有几个id。

  • （1）id值不同：id值越大优先级越高，越先被执行；
  • （2）id值相同：从上往下依次执行；
  • （3）id列为null：表示这是一个结果集，不需要使用它来进行查询。

2、select_type：

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询；

（1）simple：表示查询中不包括union操作或者子查询，位于最外层的查询的select_type即为simple，且只有一个；

explain select * from t3 where id=3952602;

（2）primary：需要union操作或者含有子查询的select，位于最外层的查询的select_type即为primary，且只有一个；

explain select * from (select * from t3 where id=3952602) a ;

（3）derived：from列表中出现的子查询，也叫做衍生表；mysql或者递归执行这些子查询，把结果放在临时表里。

explain select * from (select * from t3 where id=3952602) a ;

（4）subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery。

explain select * from t3 where id = (select id from t3 whereid=3952602 ) ；

（5）union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。

explain select * from t3 where id=3952602 union all select * from t3；

（6）union result：从union表获取结果的select ，因为它不需要参与查询，所以id字段为null。

explain select * from t3 where id=3952602 union all select * from t3;

（7）dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响；

（8）dependent subquery：与dependent union类似，子查询中的第一个SELECT，这个subquery的查询要受到外部表查询的影响。

3、table：

表示 explain 的一行正在访问哪个表。

  • （1）如果查询使用了别名，那么这里显示的是别名;
  • （2）如果不涉及对数据表的操作，那么这显示为null;
  • （3）如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生;
  • （4）如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4、type：

访问类型，即MySQL决定如何查找表中的行。依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type类型 都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一个索引。一般要求type为ref，范围查找需要达到 range。

（1）system：表中只有一条元组匹配（等于系统表），这是 const 类型的特例，平时不会出现，可以忽略不计。

（2）const：通过索引一次就找到了，表示使用主键索引或者唯一索引。

（3）eq_ref：主键或者唯一索引中的所有字段被用于连接使用，只会返回一行匹配的数据。简单的select查询语句不会出现这种情况。

（4）ref：普通索引扫描，可能返回多个符合查询条件的行。

（5）fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

（6）ref_or_null：与ref方法类似，只是增加了null值的比较。

（7）index_merge：表示查询使用了两个以上的索引，索引合并的优化方法，最后取交集或者并集，常见and ，or的条件使用了不同的索引。

（8）unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值；

（9）index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

（9）range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。

（10）index：索引全表扫描，把索引树从头到尾扫描一遍；

（11）all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

（12）NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

5、possible_keys：

显示查询可能使用到的索引。

6、key：

实际使用哪个索引来优化对该表的访问；select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

7、key_len：

实际上用于优化查询的索引长度，即索引中使用的字节数。通过这个值，可以计算出一个多列索引里实际使用了索引的哪写字段。key_len的计算规则：

字段类型索引长度
tinyint1字节
smallint2字节
int4字节
bigint8字节
date3字节
timestamp4字节
datetime8字节
char(n)

GBK编码：2n字节

utf8编码：3n字节

utf8mb4编码：4n字节

varchar(n)

GBK编码：（2n+2）字节

utf8编码：（3n+2）字节

utf8mb4编码：（4n+2）字节

 注意：

（1）如果字段允许为 NULL，需要额外1字节记录是否为 NULL

（2）索引的最大长度为767字节，当字符串过长时，mysql会做类似左前缀索引的处理，将前半部分的字符提取出来做索引。

（3）key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

（4）key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

8、ref：

显示哪个字段或者常量与key一起被使用。

（1）如果是使用的常量等值查询，这里会显示const。

（2）如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段。

（3）如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

9、rows：

表示MySQL根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。

10、extra：

这一列展示一些额外信息，同样十分重要。这个列可以显示的信息非常多，有几十种，常用的有：

类型

说明

using index

使用覆盖索引

using index condition查询的列未被索引覆盖，where筛选条件是索引的前导列

using where

查询的列未被索引覆盖，where筛选条件非索引的前导列

using index;using where

查询的列被索引覆盖，where筛选条件非索引的前导列

NULL

（既没有using index，也没有using where; using index，也没有using where）

查询的列未被索引覆盖，并且where筛选条件是索引的前导列。意味着用到了索引，但是部分字段未被索引覆盖，须通过“回表”来获取查询所需的字段

using temporary

用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，通常是因为group by的列上没有索引。也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了

using filesort

MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能

using join buffer

使用了连接缓存：
（1）Block Nested Loop：连接算法是块嵌套循环连接;

（2）Batched Key Access：连接算法是批量索引连接

Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了

impossible wherewhere子句的值总是false

distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

select tables optimized away

在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

11、filtered：

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

二、MySQL执行计划的局限性：

（1）EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；

（2）EXPLAIN不考虑各种Cache；

（3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；

（4）部分统计信息是估算的，并非精确值；

（5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。

三、案例分析：

执行顺序：

第一：（id = 4）：【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。

第二：（id = 3）：【select id, name from t1 where address = ‘11’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘11’ 通过复合索引idx_name_email_address就能检索到，所以type为index。

第三：（id = 2）：【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。

第四：（id = 1）：【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。

第五：（id = NULL）：【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。

  • 作者：张维鹏
  • 原文链接：https://blog.csdn.net/a745233700/article/details/84335453
    更新时间：2022-09-02 13:08:25