MYSQL EXPLAIN执行计划详解

一、定义

        mysql查询优化器在基于成本和规则对一条查询语句进行优化后，会生成一条执行计划，这个执行计划展示了接下来执行查询的具体方式，比如多表连接的顺序是什么，采用什么访问方法来具体查询每个表等。设计mysql的大叔贴心地提供explain语句，可以让我们查看某个查询语句的具体执行计划。

        除了select开头的查询语句，其余的delete,insert,update,replace语句前面都可以加上explain这个词，用来查看这些语句的执行计划。

二、执行计划输出的各列详解

执行以下sql语句：

select * from user where id=101868

1、table

        无论我们的查询语句多复杂，里面包含了多少个表，到最后也是对每个表进行单表访问，所以mysql大叔规定：explain语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表该表的表名。

 2、id

        查询语句中每出现一个select关键词，设计mysql的大叔就会为它分类一个唯一的id值，这个id值就是explain输出的第一列。

3、select_type 

        select_type有以下值：

• SIMPLE：查询语句中不包含union或者没有只查询的查询都算做SIMPLE类型。
• PRIMARY：对于包含union,union all或者子查询的大查询来说，他是有多个小查询组成的；其中最左边那个查询的select_type值就是PRIMARY。
• UNION：对于包含union或者union all的大查询来说，它们是由几个小查询组成的；其中除了最左边的那个小查询以外，其余的小查询select_type就是UNION.
• UNION RESULT：mysql 选择使用临时表来完成union查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT.
• SUBQUERY：如果包含子查询的查询语句不能够转为对应的半链接形式，并且该子查询是不相关的子查询，而却查询优化器决定将该子查询物化的方案来执行子查询时，该子查询的第一个select关键词代表的那个查询的select_type就是subquery。
• DEPENDENT SUBQUERY：如果包含的子查询的查询语句不能够转化为对应的半链接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询的第一个select关键词代表的那个查询的select_type就是dependent subquery。
• DEPENDEN UNION：在包含union或者union al的大查询中，如果各个小查询都依赖外层查询，则除了最左边的那个小查询之外，其余的小查询的select_type值就是dependent union。
• DERIVED：在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询的select_type就是derived。
• MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询，该子查询对应的select_type属性就是materialized。

4、 partitions

        如果您的表是分区表（Partitioned Table），那么 EXPLAIN 输出中的 "partitions" 列将会显示有关查询涉及的分区的信息。这对于理解查询是如何在分区表上执行的非常有用。它可以告诉您查询是否仅涉及一个或多个特定分区，以及哪些分区被使用。

        通常情况下，"partitions" 列会显示一个包含分区信息的字符串，其中描述了查询所涉及的分区。例如，它可能显示涉及的分区范围或者具体的分区标识符，这取决于查询的情况。

 5、type

        优化器决定索引最终选择哪一种访问方法，访问方法有：system,const,ref,eq_ref,ref_or_null,range,index等等。

 6、possible_keys和key

        在explain语句输出的执行计划中，possible_keys列表式在某个查询语句中，对某个表执行查询时可能用到的索引有哪些；key表式实际用到的索引有哪些。

7、key_len 

        当我们决定使用某个索引来执行查询时，首先要搞清楚对应的扫描区间，以及形成该扫描区间的边界条件是什么，我们看看以下的雨具：

select * from s1 where key1 > 'a' and key1 < 'b'

        很显然，在使用idx_key1索引执行查询时，对应的扫描区间是（'a','b'）,形成改扫描区间的条件就是key1>a AND key1<b。当然，这个结论是我们根据经验得出的，在一些情况下，我们希望从执行计划中直接可以看出形成扫描区间的边界条件是什么，这时候执行计划的key_len列就派上用场了。上诉语句的执行计划以下：

        执行计划的key_len列的值是82，这个82是怎么来的呢？原来设计mysql的大叔为边界条件中包含的列都维护了一个key_len值。该key_len值有下面3部分组成：

• 该列的实际数据最多占用的存储空间长度。对于固定长度类型的列来说，比方说对于int类型的列来说，该列实际数据最多占用的存储空间长度就是4字节（当然，对于int类型的列来说，不管存什么数据，实际数据占用的存储空间长度都是4字节）。对于使用边长类型的列来说，比方说对于使用utf8字符集，类型为varchar(100)的列来说，该列的实际数据最多占用的存储空间长度就是utf8字符集中表示一个字符最多占用的字节数*该类型组多可以存储的字符数的积，也就是3*100=300字节。
• 如果该列可以存储null，则key_len值在该列的实际数据最多占用的存储空间长度的基础上再加1字节。
• 对于使用变长类型的列来说，都会有2字节的空间来存储该变列的实际数据占用的存储空间长度，key_len值还要在原先的基础上再加2字节。

        这样的话，我们再来分析一下上述查询中的key_len值是怎么计算出来的。

1. key1列的类型是varchar(20)，使用的字符集是utf8mb4，所以该列的实际数据最多占用的存储空间长度就是20*4=80字节。
2. key1列不可以存储NULL，所以没有额外的1字节。
3. key1是边长类型的列，key_len值在基础上再加2，最终得到的也就是82。

说明形成扫描区间的搜索条件只包含key1列这一列。设计该列的搜索条件是key1>a and key1<b，这个搜索条件就是形成范围区间的边界条件。

8、ref 

        当访问方法是const，eq_ref，ref，ref_or_null，unique，subquery，index_subquery中的某一个时，ref暂时的就是与索引进行等值匹配的东西是啥。

9、rows

        在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估计行数。如果使用索引来执行查询时，执行计划的rows值就代表预计扫描的索引记录数。

10、filtered

• 基本含义：filtered 值表示在表扫描过程中，满足查询条件的行占总行数的百分比。
• 范围：filtered 的值是一个百分比（0 到 100 之间的数字）。例如，一个 filtered 值为 50 表示查询条件过滤掉了 50% 的行，即有 50% 的行满足查询条件。

11、Extra

• Using index: 表示查询使用了覆盖索引（Covering Index）。这意味着查询只需要读取索引而不需要访问表的实际数据行，可以提高查询性能。
• Using where: 表示查询使用了 WHERE 子句中的条件进行过滤。这意味着在读取索引或表的数据时，还需要进一步检查每一行是否满足 WHERE 条件。
• Using temporary: 表示在查询执行过程中需要创建临时表来存储中间结果。这通常发生在排序、分组或连接等操作中，可能会增加额外的内存和磁盘开销。
• Using filesort: 表示查询在排序过程中需要进行文件排序，即将结果保存到临时文件中进行排序操作。这种情况下通常也会同时出现 Using temporary。
• Using join buffer (Block Nested Loop): 表示在连接操作中使用了连接缓冲区（Join Buffer），它用于存储连接操作的中间结果。这种情况下通常也会同时出现 Using join buffer (Block Nested Loop)。
• Using index condition: 表示查询使用了索引条件过滤（Index Condition Pushdown）。这意味着部分过滤操作被推入到了索引访问层，减少了需要加载到内存中的数据量，提高了性能。
• Using sort_union(): 表示使用了排序合并优化算法，对多个子查询的排序结果进行合并。
• Using intersect(): 表示使用了交集优化算法，对多个子查询的结果进行交集运算。
• Using index for group-by: 表示在分组操作中使用了索引，避免了全表扫描的开销。
• Using index for window: 表示在窗口函数操作中使用了索引，避免了全表扫描的开销。