mysql运行原理笔记-explain

EXPLAIN详解

EXPLAIN语句输出各个列的作用

执行计划输出中各列详解

table

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

id

查询语句中每出现一个SELECT关键字，都会为它分配一个唯一的id值，这个id值就是EXPALIN输出的第一列。
对于连接查询来说，一个SELECT关键字后面的FROM子句中可以跟随多个表。在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。但是需要注意的是，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值都是相同的；出现在前面的表表示驱动表，出现在后面的表表示被驱动表。
对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字。所以在包含子查询的语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值。
对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是一样的，不过会存在一个临时表，id为NULL表名这个临时表是为了合并两个查询的结果集而创建的。
与UNION比起来，UNION ALL就不需要对最终的结果集进行去重。它只是单纯地把多个查询结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询计划中，就没有那个id为NULL的记录。

select_type

一条大的查询语句里面可以包含多个SELECT关键字，每个SELECT关键字代表一个小的查询语句。而每个SELECT语句的FROM子句中都可以包含若干张表（这些表用来进行连接查询），每一张表都对应着执行计划输出中的一条记录。对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

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

type

完整的访问方法有system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

• system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，那么对该表的访问方法就是system。
• const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。
• eq_ref：执行连接查询时，如果被驱动表是通过主键或者不允许存储NULL值得唯一二级索引等值匹配的方式进行访问的，则对该驱动表的访问方法就是eq_ref。
• ref：当通过普通的二级索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法可能是ref。
• fulltext：全文索引
• ref_or_null：当对普通二级索引列进行等值匹配且该索引列的值也可以是NULL值时，对表的访问方法就可能是ref_or_null
• index_merge：一般情况下只会为单个索引生成扫描空间，在某些场景下可以使用Intersection Union Sort-Union这3种索引合并的方式来执行索引。
• unique_subquery：类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery针对的是一些包含IN子查询的查询语句。
• index_subquery：与unique_subquery类似，只不过在访问子查询中的表时使用的是普通的索引。
• range：如果使用索引获取某些单点扫描区间的记录，那么就可能使用到range访问方法。
• index：当可以使用索引覆盖，但需要扫描全部索引的记录时，该表的访问方法就是index。

强调一下，对于使用InnoDB存储引擎来说，二级索引叶子节点的记录只包含索引列和主键列的值，而聚簇索引叶子节点中包含用户定义的全部列以及一些隐藏列。所以扫描全部二级索引记录的代价比扫描全部聚簇索引记录的代价更低一些。

• ALL：最熟悉的全表扫描。

一般来说，这些访问方法的性能按照我们介绍的顺序依次变差。

possible_keys和key

在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些；key列表示实际用到的索引有哪些。
另外需要注意的一点是，possible_keys列中的值并不是越多越好，可以使用的索引越多，查询优化器在计算优化器在计算查询成本时花费的时间就越长。如果可以的话，尽量删除那些用不到的索引。

key_len

key_len值由下面3部分组成

• 该列的实际数据最多占用的存储空间长度。
• 如果该列可以存储NULL值，则key_len值在该列的实际数据最多占用的存储空间长度的基础上再加1字节。
• 对于使用变长类型的列来说，都会有2字节的空间来存储该变列的实际数据占用的存储空间的长度，key_len值还要在原先的基础上加2字节。

ref

当访问方法是const eq_ref ref ref_or_null unique_subquery index_subquery中的其中一个时，ref列展示的就是与索引列进行等值匹配的东西是啥。

rows

在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估计行数。如果使用索引来执行查询，执行计划的rows列就代表预计扫描的索引记录行数。
你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
如果rows列的值是266，这就意味着查询优化器在分析完使用idx_key1执行查询的成本之后，觉得满足key1>'z’条件的记录只有266条。

filtered

在分析连接查询的成本时，提出过一个condition filtering的概念，这个概念就是MySQL在计算驱动表扇出时采用的一个策略。

• 如果使用全表扫描的方式来执行单表查询，那么计算驱动表扇出时需要估计出满足全部搜索条件的记录到底有多少条。
• 如果使用索引来执行单表扫描，那么计算驱动表扇出时需要估计出在满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。
  filtered代表rows结果满足其余查询条件的百分比。

Extra

顾名思义，Extra列是用来说明一些额外信息的。

• No tables used：当查询语句中没有FROM子句时将会提示额外的信息。
• Impossible WHERE：查询语句的WHERE子句永远为FALSE时将会提示该额外信息。
• No matching min/max row：当查询列表处有MIN或者MAX聚集函数，但是并没有记录符合WHERE子句中的搜索条件时，将会提示该额外信息。
• Using index：使用覆盖索引执行查询时，Extra列将会提示该额外信息。
• Using index condition：有些搜索条件中虽然出现了索引列，但却不能充当边界条件来形成扫描空间，也就是不能用来减少需要扫描的记录数量，将会提示该额外信息。

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

其中key1>'z’可以用来形成扫描区间，但是key1 LIKE '%a’却不能。
我们知道，MySQL服务器程序其实分为server层和存储引擎层。在没有索引条件下推特性之前，server层在生成执行计划后，是按照下面的步骤来执行这个查询的。
步骤1、server层首先调用存储引擎的接口定位到满足key1>'z’条件的第一条二级索引记录。
步骤2、存储引擎根据B+树索引快速定位到这条二级索引记录后，根据二级索引记录的主键值进行回表操作，将完整的用户记录返给server层。
步骤3、server层再判断其他的搜索条件是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后想存储引擎层要下一条记录。
步骤4、由于每条记录都有一个next record属性，根据该属性可以快速定位到符合key1>'z’条件的下一条二级索引记录，然后再执行回表操作，将完整的用户记录返回给server层。然后重复步骤3，直到将索引idx_key1的扫描区间(‘z’,+∞)内的所有记录都扫描过为止。
这里面有个问题，虽然key1 LIKE '%a’不能用于充当边界条件来减少需要扫描的二级索引记录的数量，但这个搜索条件毕竟只涉及key1列，而key1列是包含在索引idx_key1中的。所以改进了上面的步骤
步骤1、server层首先调用存储引擎的接口定位到满足key1>'z’条件的第一条二级索引记录。
步骤2、存储引擎根据B+树索引快速定位到这条二级索引记录后，不着急执行回表操作，而是先判断下所有关于idx_key1索引列中包含的列的条件是否成立，也就是key1>‘z’ AND key1 LIKE '%a’是否成立。如果这些条件不成立，则直接跳过该二级索引的记录，然后去找下一条二级索引记录；如果这些条件成立，则执行回表操作，将完整的用户记录返回给server层。
步骤3、server层再判断其他的搜索条件是否成立。如果成立则将结果返回给客户端；否则跳过该条记录，然后向存储引擎要下一条记录。
步骤4、由于每条记录都有一个next_record属性，根据该属性可以快速定位到要符合key1>'z’条件的下一条二级索引记录。还是不着急进行回表操作，先判断一下所有关于idx_key1索引中包含的列的条件是否成立。如果这些条件不成立，则直接跳过该二级索引记录，然后去找下一条二级索引记录。如果这些条件成立，则执行回表操作，将完整的用户记录返回给server层。然后重复步骤3，直到将索引idx_key1的扫描区间(‘z’,+∞)内的所有记录都扫描过为止。
每次执行回表操作时，都需要将一个聚簇索引页面加载到内存中。这比较耗时，所以尽管上述修改只改进了一点点，但是可以省去好多回表操作的成本。MySQL把这个改进称为索引条件下推（Index Condition Pushdown）。
如果在查询语句的执行过程中使用索引条件下推特性，在Extra列中将会显示Using index condition。
另外还需要注意的一点是，索引条件下推特性只是为了在扫描某个扫描区间的二级索引记录时，尽可能减少回表操作的次数，从而减少了IO操作。而对于聚簇索引而言，它不需要回表，它本身就包含全部的列，也起不到减少IO操作的作用。所以规定下推特性只适用于二级索引。

• Using where：当某个搜索条件需要在server层进行判断时，在Extra列中会提示Using where
• Using join buffer：在连接查询的执行过程中，当被驱动表不能有效地利用索引加快访问速度时，MySQL一般会为其分配一块名为连接缓冲区的内存块来加快查询速度。也就是使用基于块的嵌套循环算法来执行连接查询。
• Using intersect、Using union和Using sort_union：如果执行计划的Extra列出现了Using intersect提示，说明准备使用Intersection索引合并的方式执行查询。
• Zero limit：当Limit子句的参数为0时，表示压根不打算从表中读出任何记录，此时将会提示该额外信息。
• Using filesort：在有些情况下，当对结果集中的记录进行排序时，是可以使用到索引的。
• Using temporary：在需要查询过程中，MYSQL可能会借助临时表来完成一些功能。
• Start\End temporary：查询优化器会优先尝试将IN查询转换成半连接，而半连接又有好多种执行策略。当执行策略为Duplicate Weedout时，也就是通过建立临时表来为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示。
• LooseScan：在将IN子查询转为半连接时，如果采用的是LooseScan执行策略，驱动表查询执行计划的Extra列将显示LooseScan提示。
• FirstMatch：在将IN子查询转为半连接时，如果采用的是FirstMatch执行策略，驱动表查询执行计划的Extra列将显示FirstMatch提示。