MySQL之EXPLAIN

1. EXPLAIN简介

通过使用EXPLAIN可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈。EXPLAIN可以得到以下结果：

• 表的读取顺序；
• 数据读取操作的操作类型；
• 哪些索引可以使用；
• 哪些索引被实际使用；
• 表之间的引用；
• 每张表有多少行被优化器查询；
  比如执行以下sql会返回字段结果：
  可以看到返回有这些字段：
  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

2. id列

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一列的值，并且id的顺序是按SELECT出现的顺序增长的，id列越大执行优先级越高，id相同则从上往下执行，id 为 NULL最后执行。

2.1 单个SELECT

比如下边这个查询中只有一个SELECT关键字，所以EXPLAIN的结果中也就只有一条id列为1的记录。

2.2 连接查询

对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。

2.3 包含子查询

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值。
但是这里需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划。
可以看到，虽然查询语句是一个子查询，但是执行计划中 base_category2和base_category3表对应的记录的 id值全部是1，这就表明了查询优化器将子查询转换为了连接查询。

2.4 包含UNION\UNION ALL子句

对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，不过还是有点儿特别。 这个语句的执行计划的第三条记录是因为UNION子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，MySQL使用的是内部的临时表。UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1,2>的临时表，就是执行计划第三条记录的table列的名称，id为3表明这个临时表是为了合并两个查询的结果集而创建的。
跟UNION对比起来UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录。

3. select_type列

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句。MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为：select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，select_type取值如下：

• SIMPLE：简单的SELECT查询，不使用union及子查询；
• PRIMARY：最外层的SELECT查询；
• UNION：UNION中的第二个或随后的SELECT查询，不依赖于外部查询的结果集；
• UNION RESULT：UNION结果集；
• SUBQUERY：子查询中的第一个SELECT查询,不依赖于外部查询的结果集；
• DERIVED：用于FROM子句里有子查询的情况，MySQL会递归执行这些子查询，把结果放在临时表里；

3.1 SIMPLE

简单的select查询，查询中不包含子查询或者UNION。
连接查询也算是SIMPLE类型。

3.2 PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type值就是PRIMARY。 从结果中可以看到，最左边的小查询SELECT t1.id FROM base_category3对应的是执行计划中的第一条记录，它的select_type值就是PRIMARY。

3.3 UNION

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的查询的select_type值就是UNION。

3.4 UNION RESULT

MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT:

3.5 SUBQUERY

包含在SELECT中的子查询，不在FROM子句中。

3.6 DERIVED

包含在FROM子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的select_type是DERIVED，说明该子查询是以派生表的方式执行的。id为1的记录代表外层查询，注意看它的table列显示的是<derived2>，表示该查询是针对将派生表之后的表进行查询的。

4. table列

不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行单表访问的，MySQL规定 EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。
连接查询的执行计划中有两条记录，这两条记录的table列分别是base_category2和base_category3。

5. partitions列

和分区表有关，一般情况下查询语句的执行计划的partitions列的值都是NULL。

6. type列

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法或访问类型，其中的type列就表明了这个访问方法或访问类型是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > range > index > ALL, 一般来说，得保证查询至少达到range级别，最好能达到ref。

6.1 system级别

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如：MyISAM、Memory，那么对该表的访问方法就是system。
如果改成使用InnoDB存储引擎，type的值就是all。

6.2 const级别

根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。因为只匹配一行数据，所以很快。
B+ Tree 叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树矮胖，所以这样根据主键值定位一条记录的速度很快。

6.3 eq_ref级别

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较，则对该被驱动表的访问方法就是：eq_ref。
从执行计划的结果中可以看出，MySQL打算将t2作为驱动表，t1作为被驱动表，重点关注t1的访问方法是eq_ref，表明在访问t1表的时候可以通过主键的等值匹配来进行访问。

驱动表与被驱动表

例如A表和B表join连接查询，如果通过A表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到B表中查询数据，然后合并结果。那么我们称A表为驱动表，B表为被驱动表。

6.4 ref级别

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
本质上也是一种索引访问，它返回所有匹配某个单独值的行，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体。
对于这个查询可以选择全表扫描来逐一对比搜索条件是否满足要求，也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。
由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。
如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。
对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const要差些，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的，如果匹配的二级索引记录太多那么回表的成本就太大了。
对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法。

6.5 range级别

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在where语句中出现了 between、<、>、in等的查询。
这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

这种利用索引(聚簇索引、二级索引)进行范围匹配的访问方法称之为：range。

6.6 index级别

可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是：index。

6.7 ALL级别

最熟悉的全表扫描，将遍历全表以找到匹配的行。ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询。

7. possible_keys列和key列

在EXPLAIN语句输出的执行计划中，possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。 上述执行计划的 possible_keys 列的值表示该查询可能使用到idx_name_age_position,idx_name两个索引，然后key列的值是idx_name_age_position，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 idx_name_age_position来执行查询比较划算。

8. key_len列

key_len列，表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，计算方式如下：

• 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，如：某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100x3=300个字节；
• 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节；
• 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度；
  由于id列的类型是int，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是4。 对于可变长度的索引列来说：
  由于name列的类型是VARCHAR(24)，所以该列实际最多占用的存储空间就是24*3字节，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是74。
  key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

数据类型	所占字节数
字符串	char(n)：n字节长度； varchar(n):2字节存储字符串长度，如果是utf8，则长度3n+2
数值类型	tinyint：1字节；smallint：2字节； int：4字节；bigint：8字节
时间类型	date：3字节；timestamp：4字节； datetime：8字节

9. ref列

当使用索引列等值查询时，与索引列进行等值匹配的对象信息；
可以看到ref列的值是const，表明在使用主键索引执行查询时，与id列作等值匹配的对象是一个常数。 可以看到对被驱动表t1的访问方法是eg_ref，而对应的ref列的值是gmall.t2.category2_id，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，与t2表的id作等值匹配的对象就是gmall.t2.category2_id列，格式：数据库名称.表名.字段。

10. rows列

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

11. filtered列

某个表经过搜索条件过滤后剩余记录条数的百分比； 从执行计划的 key 列中可以看出来，该查询使用PRIMARY索引来执行查询，从rows列可以看出满足id<100的记录有100条。执行计划的filtered列就代表查询优化器预测在这100条记录中，有多少条记录满足其余的搜索条件，也就是age<30这个条件的百分比。此处filtered列的值是33.0，说明查询优化器预测在100条记录中有33.33%的记录满足age<30这个条件。

12. Extra列

Extra列是用来说明一些额外信息的，可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

12.1 Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用。

12.2 Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示。该属性与Using index相反，查询的列并没有被索引覆盖。

12.3 Using index condition

表示有些搜索条件中虽然出现了索引列，却不能使用到索引，但是可以使用了索引条件下推特性。 其中的oi.consignee_tel >'135'可以使用到索引，但是consignee_tel LIKE '%6573'却无法使用到索引，在8.0以前版本的MySQL中，是按照下边步骤来执行这个查询的：

1. 先根据oi.consignee_tel >'135'这个条件，从二级索引order_info_consignee_tel_IDX中获取到对应的二级索引记录；
2. 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合consignee_tel LIKE '%6573'这个条件，将符合条件的记录加入到最后的结果集；
   虽然consignee_tel LIKE '%6573'不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了 consignee_tel列，新版本MySQL把上边的步骤改进了一下。
   使用了索引条件下推：
   先根据oi.consignee_tel >'135'这个条件，定位到二级索引order_info_consignee_tel_IDX中对应的二级索引记录；
   对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足consignee_tel LIKE '%6573'这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表；
   对于满足consignee_tel LIKE '%6573'这个条件的二级索引记录执行回表操作，回表操作其实是一个随机IO比较耗时；
   所以上述修改可以省去很多回表操作的成本，这个改进称之为索引条件下推。
   如果在查询语句的执行过程中将要使用索引条件下推这个特性，在Extra列中将会显示Using index condition。

12.4 Using temporary

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如在执行许多包含DISTINCT、GROUPBY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary。
上边的GROUP BY的执行计划的Extra列不仅仅包含Using temporary提示，还包含Using filesort提示，可是查询语句中明明没有写ORDER BY子句，这是因为MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句。
如果不想为包含GROUP BY子句的查询进行排序，需要显式的写上ORDER BY NULL。

12.5 Using filesort

排序的时候，排序无法使用到索引只能在内存或者磁盘中进行排序

12.6 Using join buffer(Block Nested Loop)

表示在执行连接查询时，被驱动表不能有效地利用索引加快访问速度，而是使用内存块来加快查询