MySQL查询执行计划详解（EXPLAIN）

一、单表查询

访问方法/访问类型：

• const：通过主键值或唯一二级索引与一个常熟进行等值查询（不包括NULL），只会生成一条记录
• ref：普通二级索引与一个常数进行等值比较，可能生成多条记录
• ref_or_null：ref的前提下可以加上or key is null
• range：对应的扫描区间为若干个单点扫描区间或范围扫描区间（不包括负无穷到正无穷的范围）
• index：扫描区间为全表，但是可以在二级索引中扫描（因为二级索引每条记录占用空间更小，所以需要读的页更少）
• all：直接扫描全部的聚集索引记录
• …

默认情况下是从二级索引中读取到一条记录后，就会进行回表，而MRR优化可以先读取一部分二级索引记录，将它们的主键值排好序后再统一回表

索引合并：

• intersection索引合并：要求每个索引中获取到的二级索引记录都是按照主键值排序的
• union索引合并：要求每个索引中获取到的二级索引记录都是按照主键值排序的
• sort-union索引合并：先将从各个索引中扫描到的记录按照主键值排序，再按照union索引合并

没有sort-intersection，sort-union针对的是单独根据搜索条件从某个二级索引中获取的记录数比较少的场景。因为根据单个索引获取的记录比较少，但是用了or连接，导致还是需要进行全表查询，所以采用union优化，那么这种情况下记录比较少自然排序开销也不大

而intersection合并是因为单独某个索引查询出的记录比较多，进行回表次数比较多，所以才需要合并索引来减少需要回表的记录数。这种情况下单个索引查出的记录多，使用排序开销也比较大，所以没有sort-intersection

二、联表查询

内连接和外连接的区别：

• 无论是内连接还是外连接，凡是不符合where子句中过滤条件的记录都不会被加入到最后的结果集

对于外连接的驱动表中的记录来说，如果无法在被驱动表中匹配到on子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充

• 如果on子句放到内连接中，mysql会把它像where子句一样对待

由于被驱动表可能会被访问多次，因此可以为被驱动表建立合适的索引以加快查询速度

如果被驱动表非常大，多次访问被驱动表可能导致很多次的磁盘IO（因为默认情况下是驱动表没找到一条记录就去查询被驱动表，如果被驱动表很大，那么可能会导致被驱动表前面引入的页面在后面被淘汰了，那么驱动表下一条记录进行连接时又要重新去磁盘中加载），因此引入join buffer，一次性读取多条驱动表记录放入其中。join buffer只会放置查询列表中的列和过滤条件中的列。

三、统计数据收集

InnoDB提供了两种存储统计数据的方式，分别是永久性的存储统计数据和非永久性的存储统计数据（服务器关闭后清除）。

InnoDB默认以表为单位来收集和存储统计数据，可以通过在创建表时指定STATS_PERSISTENT属性来指定（1则存储在磁盘，0则存在内存，默认是存在磁盘）。这些统计数据实际上是存放在mysql系统数据库的innodb_table_stats和innodb_index_stats表中。

1、innodb_table_stats

• 数据库名
• 表名
• 最后更新时间
• 记录数（估计值，按照一定的算法从聚簇索引中选取几个叶子结点页面，统计每个页面中包含的记录数量，计算出一个页面平均包含的记录数量，再将其乘以全部叶子节点的数量）
• 聚簇索引占用的页面数量：需要统计聚集索引对应的叶子结点段和非叶子结点段分别占用的页面数量

• 从数据字典中找到表的各个索引对应的根页面位置（从数据字典页找到SYS_INDEXES表的根索引位置，之后即可从表中找到各个索引的跟页面位置）
• 从根页面的Page Header中找到叶子结点段和非叶子节点段对应的Segment Header（每个索引的根页面的Page Header部分都有Page_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP两个Segment Header）
• 从叶子结点段和非叶子结点段的Segment hader中找到这两个段对应的INODE Entry结构
• 从INODE Entry段找到段中所有零散页面的地址以及Free、NOT_FULL和FULL链表的基节点
• 直接统计零散的页面有多少，然后从FREE、NOT_FULL和FULL这三个链表的List Length字段中读取出该占用的区的数量。每个区占用64个页，因此可以统计整个段的占用的页面数量
• 当一个段的数据多于32个页面的时候，会以区为单位来申请空间，但是申请之后有一些页可能没有使用，但是统计时算进去了。所以实际的占用页面数量可能比这两个统计项的值要小一些

• 其他索引占用的页面数量：需要统计其他索引对应的叶子结点段和非叶子结点段

2、innodb_index_stats

• 数据库名
• 表名
• 索引名
• 最后更新时间
• 统计项的名称

• n_leaf_page：该索引的叶子结点实际占用多少页面
• size：该索引共占用多少页面（包括已经分配给叶子结点段或非叶子结点段但是尚未使用的页面）
• n_diff_pfxNN：表示对应索引列不重复的值有多少

• 对应的统计项的值
• 为生成统计数据而采样的页面数量：在计算某些索引列中包含多少个不重复的值时，需要对一些叶子结点页面进行采样
• 对应的统计项的描述

3、更新策略

• 开启innodb_stats_auto_recalc：默认是打开的，决定了服务器是否自动重新计算统计数据。每个表都维护了一个变量，记录着对该表进行增删改的记录条数，**如果发生改动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新计算一次统计数据。**这个过程是异步执行的。

• InnoDB默认以表为单位来收集和存储统计数据，我们也可以单独为某个表设置是否自动重新计算统计数据的属性

• 手动调用analyze table语句来更新统计信息，这个过程是同步的。

innodb_table_stats和innodb_index_stats和普通的表一样，也可以进行增删改查操作。修改之后需要使用flush table xx语句使优化器重新加载更改后的数据。

四、成本计算

MySQL中的执行成本是由两方面组成的：

1. IO成本：读取一个页面花费的成本默认是1.0
2. CPU成本：读取以及检测一条记录是否符合搜索条件的成本默认是0.2

1、单表查询成本

1. 找出所有可能使用的索引，即possible keys

2. 计算全表扫描的代价

1. 聚簇索引占用的页面数
2. 表中的记录数
3. 成本=聚集索引页面数*1+表中记录数*0.2**（这些信息保存在上一部分讲到的统计数据表中）**

3.计算使用不同索引执行查询的代价

• 扫描区间数量（无论扫描区间占用了多少页面，都认为读取一个扫描区间的IO成本和读取一个页面的IO成本是相同的）

• 对于in而言，每一个值则代表一个区间（单点扫描区间），如果大于等于200个记录，则根据索引的cardinality属性（非重复记录数量）和rows来得出一条记录平均的重复记录数，从而估算出这些区间大概有多少条记录；如果少于两百个区间则按下面的方式去计算记录条数

• 需要回表的记录数：页的头部中记录了该页当前有多少记录

• 找出最左的记录和最右的记录
• 如果两个记录像个小于十个页面，则可以精确的计算出记录数目
• 如果超过十个页面，则读取十个页面，计算页面平均包含多少记录，然后用平均值乘最左和最右记录之间的页面数量即可

• 左右记录间的页面数量则根据左右记录的父节点进行查询，其对应的目录项记录之间隔着鸡条记录，则隔着多少页面
• 如果左右记录之间的页面太多，导致左右记录不在一个父节点，那么则递归，再到父节点的父节点去计算

• 成本=（区间数量+回表记录数）*1+二级索引记录数*0.2+聚集索引记录数*0.2（二级索引记录数和聚集索引记录数其实就是回表记录数，计算的是读取和判断这条记录的开销）

• 对比，找出成本最低的代价

2、连接查询成本

• 单次查询驱动表的成本
• 多次查询被驱动表的成本（具体多少次取决于驱动标查询后的结果集有多少条记录）

多次查询被驱动表的成本等于单次查询驱动表后的结果集数量乘以单次查询被驱动表的成本，也就是说驱动表的扇出或者被驱动表的查询成本越小，查询的开销越小。为了减小访问被驱动表的成本，应该尽量在被驱动表的连接列上面建立索引。

对于外连接而言，成本=单次访问驱动表的成本+驱动表扇出值（启发式预测） * 单词访问被驱动表的成本

对于内连接而言，因为驱动表和被驱动表的位置时可以互换的，所以需要计算不同表当驱动表时的成本，从而选择更优的一项

计算不同连接顺序时（如三表关联，则有6种连接顺序），有如下方式减小计算查询成本的性能消耗：

1. 提前结束某种连接顺序的成本评估（如果其已经大于当前最小成本）
2. 提供系统变量optimizer_search_depth，如果连接表的个数小于该值，就穷举分析，否则只对数量与optimizer_search_depth值相同的表进行穷举分析。
3. 不满足启发式规则的连接不进行分析

所以列中不重复的值的数量对于MySQL优化器十分重要，通过它可以计算在索引列中一个值平均重复多少行，它的应用场景主要有两个：

• 单表查询中的单点扫描区间太多：当in语句对应的单点扫描区间太多时，采用index dive的方式直接访问B+树索引来统计就记录的数量就太耗费性能了，所以直接依赖统计数据中一个值平均重复多少行来计算单点扫描区间对应的记录数量
• 在执行连接查询时，如果被驱动表的列有索引，则可以使用ref访问方法来查询被驱动表。而在优化器生成执行计划时，查询并没有真正执行，也就是对于select * from t1 join t2 on t1.col = t2.key where语句中，t1.col的值是不确定的，所以不能使用index dive的方式直接访问B+树索引来统计记录数量，只能依赖统计数据中一个值平均重复多少行来计算记录数量

如何对待null值有三种方式：

1. 认为所有的null值都相等，如果某个索引列的null值很多，会使得查询哟花旗认为某个列中一个值的平均重复次数特别多，因此倾向于不适用索引查询
2. 认为所有的null值都是不相等的
3. 直接忽略null值

五、基于规则的优化

我们编写的查询语句的搜索条件本质上是表达式，有些表达式可能比较复杂无法高效执行，MySQL优化器会为我们简化这些表达式。

• 移除不必要的括号
• 常量传递：如a=5 and b>a化简为a=5 and b>5
• 移除没用的条件：恒为true或false的表达式
• 表达式计算
• 如果在外连接查询中，指定的where子句中包含被驱动表的列不会NULL值的条件（如果被驱动表的某个属性等于xxx，或者被驱动表某个属性is not null，这种情况称之为控制拒绝。那么这种情况下外连接和内连接是等价的，在这种情况下优化器可以将外连接转换为内连接，从而评估表的不同连接顺序的成本，选出成本最低的那个连接顺序来执行查询
• …

六、EXPALIN

MySQL查询优化器在基于成本和规则对一条查询语句进行优化后，会生成一个执行计划，这个执行计划展示了接下来执行查询的具体方式，比如多表连接的顺序是什么，采用什么方法来具体查询每个表等。我们可以通过EXPLAIN语句来查看某个查询语句的具体执行计划。

EXPLAIN语句会输出如下的列：

1.id：在一个大的查询语句中，每个select语句对应一个的id，如果采用联表查询虽然会产生多条记录，但是它们的id是相同的（出现在前面的为驱动表，出现在后面的为被驱动表）

• 如果使用union子句（用于合并查询结果并去重）连接两个查询语句，那么还会出现第三条记录，其实际上是MySQL使用的内部临时表，其id为null
• 如果union all则不会产生，因为不需要去重，直接返回即可

2.select_type：select关键字对应的查询的类型

• SIMPLE：查询语句中不包含union或者子查询的查询都为SIMPLE类型
• PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询而言，其是由多个小查询组成的，其中最左边的那个查询为PRIMARY
• UNION：对于包含UNION和UNION ALL的查询，除了最左边的那个小查询，其它都为UNION
• UNION RESULT：UNION产生的临时表的类型为UNION RESULT
• SUBQUERY：不相关子查询的第一个select关键字代表的那个查询为SUBQUERY类型，这种子查询只需要执行一遍
• DEPENDENT SUBQUERY：相关子查询的第一个select关键字代表的那个查询，这种子查询可能需要执行多次
• DEPENDENT UNION：在包含UNION和UNION ALL的大查询中，如果各个小查询都依赖于外层查询，则除了最左边的那个小查询外其他的小查询为DEPENDENT UNION
• DERIVED：在包含派生表的查询中，派生表对应的子查询就是DERIVED类型

3.table：表名

4.partitions：匹配的分区信息。对于未分区的表，值为null

5.type：针对单表的访问方法

• system：如果表中只有一条记录且统计数据是精确的（如MyISAM和MEMORY存储引擎），则使用system
• const：通过主键或唯一二级索引与常数进行等值匹配
• eq_ref：执行连接查询时，如果被驱动表是通过主键或者不允许为null的唯一二级索引列等值匹配的方式访问的
• ref：通过普通的二级索引列与常量进行等值匹配
• ref_or_null：当对普通二级索引列进行等值匹配时且该索引列的值也可以为null时
• index_merge：一般情况下只会为单个索引生成扫描区间，在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询
• range：使用索引获取某些单点扫描区间的记录或者获取某个或某些范围扫描区间的记录
• index：使用可以使用索引覆盖，但是需要扫描全部的索引记录

• 比较特殊的一点是如果要根据主键对全表进行排序，那么type也是index

• all：全表扫描

6.possible_keys：可能用到的索引

7.key：实际使用的索引

• 比较特殊的是在使用index访问查询时，possible_keys列是空的，而key列展示了实际使用的索引

8.key_len：实际使用的索引长度，其由以下三个部分组成

• 该列实际数据最多占用的存储空间，如int则为4字节，采用变长字符集utf8的varchar(100)则占用3*100=300
• 如果可以存储NULL值则长度再加一
• 如果是变长类型则长度再加二（用于存储实际占用空间的长度）

• 执行计划是在server层生成的，不是针对具体某个存储引擎的功能，所以不会考虑到根据实际存储长度来决定占用一字节还是二字节，统一都是二字节

9.ref：当使用索引列等值查询时，与索引列进行等值匹配的对象的信息

• 当访问方式是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery时，ref列展示的就是与索引列进行等值匹配的东西是什么
• 取值可能是const（表示常数）或列名（联表查询时被驱动表为eq_ref，则会显示驱动表的列名）或func（表示函数）

10.rows：预估的需要读取的记录条数

11.filtered：针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比

• 对于单表查询而言没什么意义，我们更关注在联表查询中驱动表对应的执行计划的filtered值，将其与rows相乘则代表着驱动表的扇出值，也就是被驱动表需要被访问的次数

12.Extra：一些额外的信息

• no table used：没有from语句
• impossible where：查询语句的where子句永远为false
• no matching min/max row：当查询列表有min或者max聚集函数但是没有记录负荷where子句的搜索条件时
• using index：使用覆盖索引执行查询时
• using index condition：在存储引擎层执行搜索条件的判断（即索引条件下推）

对于查询语句select * from s1 where Key1 > 'z' and key1 like '%a';

在没有索引条件下推特性之前，server层在生成执行计划后，是按照下面的步骤来执行这个查询的：

1. server层调用存储引擎的接口定位到满足key1>'z’条件的第一条二级索引记录
2. 存储引擎根据B+树索引快速定位到这条耳机索引记录后，根据该二级索引记录的主键值进行回表操作，将完整的用户记录返回给server层
3. server层再判断其他的搜索条件是否成立，如果成立则将其发送给客户端，否则跳过该记录，向存储引擎要下一条记录
4. 由于每条记录都有一个next_record的属性，根据该属性可以快速定位到符合key1>'z’条件的下一条二级索引记录，然后执行回表操作，将完整的记录返回给server层。之后重复步骤3，直到将索引的扫描区间(‘z’, +∞）内的所有记录都扫描完为止

在这个语句中虽然key1 like '%z'不能用于充当边界条件来减少要扫描的二级索引记录的数量，但是key1列是包含在查询的索引中的，那我们完全可以在查询到之后先判断是否满足key1 like '%z'之后再进行回表，这就极大程度的省去了很多回表的操作。这就是索引条件下推，即将索引条件的判断下放到存储引擎来执行。

• using where：在server层进行搜索条件的判断，一般出现在判断条件没有索引的情况
• using filesort：无法使用索引（没有创建索引或需要大量数据进行回表成本更高的情况）进行排序时，则使用文件排序
• using temporary：MySQL 需要创建临时表来存储查询的结果，常见于包含order by、group by和distinct等子句的查询

using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表不能有效地利用索引加快访问速度时，MySQL一般会分配一块连接缓冲区地内存块来加快查询速度， 将驱动表读出来放到join buffer中，再遍历被驱动表与驱动表进行查询