本篇是介绍MySQL执行计划的铺垫，今天终于想好了该如何组织这部分内容，先是大致介绍查询的实现，再由此引出执行计划。

概述

我们日常的查询，基本可以分为三类:

1. 单表查询

2. 子查询

3. 连接查询

这三种可以组合，也可以分开，上面的顺序也是我们学习SQL的顺序，我们下面介绍其实现，也是按照上面这种顺序。看本篇之前建议先看这个本篇的前几篇:

• MySQL优化学习手札(一)
• MySQL优化学习手札(二)
• MySQL优化学习手札(三)

当然如果你对MySQL的B+树索引比较熟悉也可以不看。

单表访问方法

我们写了一个单表查询的语句，MySQL是如何获取我们查询语句所对应的记录的呢：

SELECT * FROM Student WHERE ID = 1;

我们忽略语法解析、连接建立这些步骤，这些都搞定了，那么MySQL该如何定位记录呢？MySQL中执行查询的方式一共有以下两种:

• 全表扫描(一条记录一条记录的去比较)
• 使用索引进行查询，索引也有不同的类型，所以就算是使用索引进行查询，也分为几种不同的情况:
• 针对主键或唯一二级索引的等值查询
• 针对普通二级索引的等值查询
• 针对索引列的范围查询
• 直接扫描整个索引

在MySQL中执行查询语句的方式称之为访问方法或者访问类型。

通过主键列等值匹配来定位记录-const

SELECT * FROM Student WHERE ID = 1; ID是主键

Id是Student这张表的主键，这里让我们再回忆一下MySQL存储数据的基本结构:

• InnoDB将数据划分为若干页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB
• InnoDB存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引，聚簇索引的叶子结点包含完整的用户记录。
• 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子结点，其余的是内结点。所有的用户记录都存储在B+树的叶子结点。所有的目录项记录都存储在内结点
• 我们也可以为自己感兴趣的列建立二级索引，二级索引的叶子结点包含的用户记录由索引列+主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
• B+树的每层结点都是按照索引列值的从小到达的顺序排序而组成了双向链表，而每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到达的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列的值相同，再按照联合索引后边的列进行排序。

也就是ID这一列是聚簇索引，同时按ID进行排序，所以这个相当快，可以先用定位到目录项地行为这一列位于哪个数据页，定位到之后，再用二分查找定位这条记录在哪个位置。现在让我们为Student再加上一列name，并为该列建立唯一索引，我们去执行如下查询:

select  * from student where name ='aa'

也同聚簇索引类似，但是name列由于是非聚簇索引列，叶子结点没有完整的记录，定位到name= ‘aa’这条记录后，还用用这条记录对应的主键去主键索引列去查完整的记录。即便有回表的代价，MySQL的开发人员仍然认为这种查询方式是非常快的，将这种访问方法定义为: const,也就是常数级别。但如果查询NULL值，情况就又有所不同:

select * from  student where name is null

因为唯一索引并不限制NULL值的数量，所以上面的查询语句可能会访问到多条记录。

ref

用唯一索引列去查找NULL值，会查询到多行，这种情况和用非唯一索引列去匹配记录类似，查询步骤和用唯一索引列去查询是一样的，定位这条记录在哪个数据页，然后到具体的页里面去匹配记录，由于我们是select * , 所以还要回表查询。如果匹配的记录比较少，回表的代价还是比较低的，MySQL就更倾向于使用索引+回表的方式来查找，采用二级索引进行等值查询记录的方式，MySQL将其定义为ref。

但对于某个包含多个索引列的二级索引列来说，只要最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法。至于为什么是可能原因在于还是成本的衡量，如果你是select * ， 这就要回表。如果查询的记录比较多，让MySQL觉得与其索引列+回表还不如直接扫描全表的话。

现在让我们再为Student再加: age, sex，同时为age、sex建一个普通的索引。如果我们查询的语句写成了下面这样:

select * from student where age = '18' and sex >  '女'

这种访问方法在MySQL中的查询级别就不是ref，原因在于对sex这一列使用的是范围查询。

ref_or_null

根据普通索引进行匹配，但同时查找该索引列为null的值，像下面这样:

select * from  student where name = 'aa' and  name is  null

这种查询级别在MySQL中我们称之为ref_or_null.

range-范围查询

我们日常的开发中范围查询也是高频出现，像下面这样:

select * from student where age in ('96','18') OR (age >= 28 and key <= 79);

对于MySQL来说执行这个查询有两种选择，省事速度慢自然是全表扫描，当然也可以使用二级索引+回表的方式。上面的条件是一个搜索范围，在MySQL中将这种查询级别定义为range。

index

select sex,age from student where sex = '男'

age和sex组成的联合索引中，age在前，sex和age上都有索引，那对于MySQL来说就有两种选择，一种是用sex上的索引+回表查出age。另一种是遍历sex和age对应的联合索引，这种效率事实上更高，因为非主键索引只存储了主键列和索引列，数据页会更小，也不需要回表，代价更小。这种查询级别在MySQL中被定义为index。

all

如其名，扫描全表。

该如何回表

前面我们唠叨了回表这个词，事实上行对于不同的查询场景也有不同的回表策略。

• 情况一, 查询用到了两个索引列，该如何回表:

select  *  from student where age > '50'  and name = '张三'

age 和 name 都是索引列,  该使用哪个索引呢，MySQL优化器一般会根据Student的统计数据来判断到底使用哪个条件对应的二级索引中查询扫描的行数会更少。然后将从二级所以呢中查询的结果经过回表得到完整的用户记录，再根据另一个条件过滤记录。一般情况来说，等值查找比范围匹配需要扫描的范围更少，这里假设查询优化器使用name列进行查找。所以对于上面的语句，查询步骤如下:

根据name = ‘张三’去name对应的索引上去查找对应二级索引的记录。

然后回表二级索引对应的主键去聚簇索引中找到对应的完整的用户记录，再根据age > ‘50’进行过滤。

• 情况三:   有的搜索条件无法使用索引

我们再为Student 添加一个card的字段，然后执行下面的查询:

select * from student where age > 30 and card = '001'

对于这种情况，card上面没有索引，MySQL会倾向于使用age上的索引然后回表查出记录，再用card =  ‘001’进行过滤。

那如果是or呢？

select * from student where age > 30 or card = '001'

这种情况就没有办法用到age上的索引，因为age索引上就只有索引列和主键，MySQL就可能会进行扫描。

索引合并

MySQL在一般情况下执行一个查询是最多只会用到单个二级所以呢，有一般就会有特殊情况，在一些特殊情况可能在一个查询中使用到多个二级索引，使用到多个二级索引的这种情况，在MySQL中被称为：index merge.

Intersection合并

下面的讨论中暂时移除age身上的单独索引列，仅保留age、sex的联合索引。

Intersection 意为交集，简单的理解就是一个查询可以使用多个二级索引，将多个二级索引中查询到的结果取交集:

select * from  Student  where age = '18'and sex = '男' and name = '张三'

age 和 name 上都有索引，那么执行上面的语句，就有两种执行方案供MySQL所选择(不要提全表):

• 选择一个条件去对应的索引列上去查找记录，然后回表，用另一个条件过滤。
• age索引列去查找记录，name索引列查找记录。由于所以存储的有主键列，这两个结果集求交集。

那哪种访问方式成本比较低呢，一般情况下MySQL更倾向于选择读取多个二级索引的方式，因为读取二级索引是顺序I/O, 回表是随机I/O.

所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，这种情况回表的损耗可能就要比访问多个索引更高。

下面两个查询就不能使用Intersection索引合并:

select * from Student where  age = '18' and sex = '男' and name > 'zhangsan'

我们分析一下为什么这种情况为什么就不能使用索引合并求交集，原因在于时间复杂度的问题，age是范围匹配(age 和 sex建立联合索引,)，扫描到的主键列未必有序，有序集合求交集的时间复杂度O(n)，无序集合求交集的时间复杂度为O(n^2).

下面的查询是一样的原因，不能用到索引合并:

select * from Student where  age = '18'  and name =  'zhangsan'

联合索引age相同，按照sex进行排序，但是仅根据age这一列得到的记录主键可能还是无序的，所以也无法用到索引合并。

主键列可以是范围匹配的查询:

select  * from Student where id > 1 and name = '张三'

上面不是说了范围匹配无法用到索引合并吗？但是索引存储了索引列和主键，我们甚至可以认为这个只用到了name这一列，然后再回表。如个只用name列再回表的代价大于主键列和name列使用索引列合并的代价，那么MySQL就可能倾向于使用id列和name进行索引合并。

Union合并

有求交集，就有求并集，这是一对双生子。MySQL在某些特定去年高考下才可能会使用到Union索引合并:

• 情况一: 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

select * from student where name = 'a' or (age = '18' and sex = '男')

下面两个查询就不能进行Union索引

select * from student where name > 'a' or (age = '18' and sex = '男')
select * from student where name = 'a' or age = '18'

原因还是和排序有关，两个集合进行排序。

• 情况二: 主键列可以是范围匹配
• 情况三：使用Intersection 索引合并的搜索条件

可以理解为两个交集的并。

Sort-Union合并

Union索引合并的使用条件有点苛刻，必须保证各个二级索引列再进行等值匹配的条件下才可能被用到。

select * from Student  where name > 'a' and age < '25'

上面这个SQL语句就无法用到Union排序，原因在于从name和age这两个列查到的主键值不是排好序的，但如果排序的代价不高呢，MySQL在代价不高的情况下会如下执行:

• 先根据条件name > ‘a’ 取值，然后根据主键进行排序
• 再根据age < ‘25’ 取值，然后根据主键进行排序。

拍好序剩下的操作就和Union索引合并方式就一样的。

索引合并的注意事项

我们先为card属性添加一个普通索引：

select * from student where name = '张三' and card = '001'

这个查询之所以可能用到索引合并的原因在于，name 和 card 是两个索引，两个列要是一个索引就不用MySQL来合并了。这样就不用读两棵B+树了。

写在最后

本篇其实也是看本文的参考资料，用自己的思路梳理了一下，做的学习笔记。

参考资料

• 《MySQL 是怎样运行的：从根儿上理解 MySQL》 小孩子煮