MySQL执行单表查询语句的原理

一. 前言

MySQL将执行查询语句的方式称为访问方法（access method）或者访问类型。同一个查询语句可以使用不同的访问方法来执行，不同的执行方式花费的时间和内存可能会悬殊巨大，而MySQL会从众多执行方式中估算出时间和内存成本最低的一种，用来作为访问方法。这个过程在MySQL Server的一个优化器模块中，他会对一条查询语句的语法进行解析，然后生成一个所谓的执行计划。这个计划表明了应该使用那些索引进行查询（访问方法），表之间的连接顺序是啥样的等等之类。

二. 创建表（实例用作展示）

我们为这个single_table表建立1个聚簇索引和4个二级索引。

三. 访问方法类别

1. const

通过主键或者唯一二级索引列与一个常数进行等值比较时称为const。如果主键或者唯一二级索引的索引列由多个列构成，则只有在索引列中的每一个列都与常数进行等值比较时，这个const访问方法才有效果，因为只有这样才可以保证最多只有一条记录符合搜索记录。
例如：SELECT * FROM single_table WHERE key2 = 2345;

根据const的查询方式过程如下：

2. ref

搜索条件为普通二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法被称为ref。
例如：SELECT * FROM single_table WHERE key1 =‘asd’；
使用idx_key1，也就是普通的二级索引来查询，此时形成的单点扫描区间就是[‘asd’,‘asd’]，MySQL定位到key1='asd’条件的第一条记录，然后沿着记录所在的单向链表向后链表，直到某条记录不符合key1='asd’条件为止。由于查询列表是*，所以针对获取到的每一条二级索引记录，都需要根据该记录的id值进行回表操作。
采用二级索引来执行查询时，每获取一条二级索引记录，就会立刻对其执行回表操作，并不是将所有二级索引记录的主键值都集中完成之后再统一执行回表操作。
下图辅助理解：

对于普通的二级索引来说，通过索引列进行等值比较后可能会匹配到多条连续的二级索引记录，而不是像主键或者唯一二级索引那样最多只能匹配一条记录。所以ref访问方法比const在效率上差了一点。
注：
在二级索引列允许存储NULL值时，无论是普通的二级索引，还是唯一二级索引，他们的索引列并不限制NULL值的数量，所以在执行包含“KEY IS NULL”形式的搜索条件时，最多只能使用ref访问方法，而不能使用const访问方法。
对于索引列中包含多个列的二级索引来说，只要最左边连续的列是与常数进行等值比较，就可以用ref访问方法，如下：
SELECT * FROM single_table WHERE key_part1 = ‘asd’；
SELECT * FROM single_table WHERE key_part1 = ‘asd’ AND key_part2 = ‘shdh’；
SELECT * FROM single_table WHERE key_part1 = ‘asd’ AND key_part2 = ‘shdh’ AND key_part3=‘uieyr’；

3. ref_or_null

ref_or_null顾名思义就是在ref的基础上加上了or NULL，如下：
SELECT * FROM single_table WHERE key_part1 = ‘asd’ OR key1 IS NULL；
下图辅助理解：

4. range

MySQL中将使用错音执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方式称为range，仅包含一个单点扫描区间的访问方法不能称为range访问方法，扫描区间在负无穷到正无穷的访问方法也不能称为range访问方法。
例如：SELECT * FROM single_table WHERE key2 IN (1438,6789) OR (key2 >= 38 AND key2 <= 79);

5. index

MySQL将查询过程中不需要回表，直接扫描全部二级索引记录即可的查询方法称为index。
例如：SELECT key_part1,key_part2,key_part3 FROM single_table WHERE key_part2 = ‘asd’;
另外当通过全表扫描对使用InnoDB存储引擎的表执行查询时，如果添加了“ORDER BY 主键”的语句，那么该语句在执行时也会被人为地认为使用的是index访问方法。
例如： SELECT * FROM single_table ORDER BY id;

6. all

全表扫描

7. 索引合并

MySQL在一般情况下只会为单个索引生成扫描区间，但还存在特殊情况。在这些特殊情况下，MySQL也可能为多个索引生成扫描区间，这种一次性使用多个索引来完成一次查询的执行方法称为index merge（索引合并）。
具体索引合并有以下三种。

7.1 Intersection索引合并

举例：SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;
这个查询语句中key1和key3都是索引，普通的查询方法，要么是以key1为索引条件来查询有，也就是设定单点扫描区间[‘a’,‘a’]，由于key1不是唯一索引，更不是主键，所以采用的访问方法就是ref，沿着idx_key1的索引在对应区间中定位[‘a’,‘a’]，找到对应的id值，然后进行回表得到完整的用户记录，再对用户记录中的key3判断是否符合等于字符‘b’。要么是以key3为索引条件，过程和key2一致，此处不多做讲述。
索引合并的方式就是，同时使用idx_key1和idx_key3执行查询。也就是在idx_key1中扫描key1值在[‘a’,‘a’]区间中的二级索引记录，同时在idx_key3中扫描key3值在[‘b’,‘b’]区间中的二级索引记录，然后从这两者的操作结果中找出id列值相同的记录（即找出它们共有的id值）。然后再根据这些共有的id值执行回表操作，并且要求两个索引的扫描区间的二级索引记录都是按照主键值排序的，如果没有排好序则不可以使用Intersection方式来查询。

例如下方：
SELECT * FROM single_table WHERE key1>‘a’ AND key3 = ‘b’;
这个查询语句就无法使用Intersection索引合并来查询，因为他的二级索引查询结果注定是无序，就看key1>'a’的条件，他的单点扫描区间是a到正无穷。
SELECT * FROM single_table WHERE key1 = ‘a’ AND key_part1 = ‘a’;
这个sql也无法用Intersection索引合并来查询，因为key_part1是联合索引，他的二级索引列是是先按照key_part1进行排序的，然后key_part1相同的情况下，使用key_paty2进行排序，在key_part2值相同的情况下，使用key_paty3进行排序，所以当使用key_part1 = 'a’的时候，查询出来的二级索引记录并不是按照主键值来排序的。
SELECT * FROM single_table WHERE key1 = ‘a’ AND id > 9000;
这个sql就可以正常使用索引合并来查询，首先key1='a’的查询结果一定是按照id值顺序的，其次id>9000更是按照id值排序的了，所以两个条件都符合。

总结一下就是：普通二级索引（非组合索引）的查询条件需要是等值比较，主键索引可以是范围查询，所有索引条件都满足查询结果值是按照主键值排序的就可以使用Intersection索引合并方式进行查询。

说回SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = 'b’的例子，假设idx_key1的扫描区间[‘a’,‘a’]中二级索引记录的id值是排好序的，且顺序为1,3，5；idx_key3的扫描区间[‘b’,‘b’]中二级索引记录的id值也是排好序的，且顺序为2，3，4，那么这个查询在使用Intersection索引合并来执行时，过程如下：

• 先从idx_key1索引的扫描区间[‘a’,‘a’]中取出第一条二级索引记录，该记录的主键值为1。然后从idx_key3索引的扫描区间[‘b’,‘b’]中取出第一条二级索引记录，该记录的主键值为2。因为1<2，所以直接把从idx_key1索引中取出的那条主键值为1的二级索引记录丢弃。
• 接着继续从idx_key1索引的扫描区间[‘a’,‘a’]中取出下一条二级索引记录，该记录的主键值为3。步骤1中从idx_key3索引的扫描区间[‘b’,‘b’]中取出的二级索引记录的主键值为2。因为3>2，所以直接把步骤1中从idx_key3索引的扫描区间[‘b’,‘b’]中取出的主键值为2的那条二级索引记录丢弃。
• 接着继续从idx_key3索引的扫描区间[‘b’,‘b’]中取出下一条二级索引记录，该记录的主键值为3.步骤2中从idx_key1索引的扫描区间[‘a’,‘a’]中取出的二级索引记录的主键值为3.因为3=3，也就意味着获取主键交集成功，然后根据该主键值执行回表操作，获取到完整的用户记录后将其发送给客户端。
• 接着从idx_key1索引的扫描区间[‘a’,‘a’]中取出下一条二级索引记录，该记录的主键值为5。然后从idx_key3索引的扫描区间[‘b’,‘b’]中取出下一条二级索引记录，该记录的主键值为4。因为5>4，所以直接把从idx_key3索引的扫描区间[‘b’,‘b’]中取出的那条主键值为4的二级索引记录丢弃。
• 接着从idx_key3索引的扫描区间[‘b’,‘b’]中取出下一条符合条件的二级索引记录，发现没有了，然后结束查询。

7.2 Union索引合并

举例：SELECT * FROM single_table WHERE key1 = ‘a’ OR key3 = ‘b’;
这个SQL因为用的是or连接，所以两个索引的单点扫描区间都是负无穷到正无穷，基本等同于需要全表扫描。使用Union索引合并方式来查询，在idx_key1中扫描key1值在[‘a’,‘a’]区间中的二级索引记录，同时在idx_key3中扫描key3值在[‘b’,‘b’]区间中的二级索引记录，然后从这两者的操作结果中去重，再根据驱虫后的id值进行回表操作得到完整数据。关于这一点他是和Intersection反过来的，Intersection是从索引查询结果中去交集，而Union是去重，因为这两个本身就是一个是交集索引合并，一个是并集索引合并。同样Union索引合并查询方法也要求每个二级索引发的查询结果是按照主键排序的。

如下实例：
SELECT * FROM single_table WHERE key1>‘a’ OR key3 = ‘b’;
无法使用Union索引合并方式来执行，因为key1的条件不满足二级索引值是按照主键值排序的。

SELECT * FROM single_table WHERE key1 = ‘a’ OR key_part1 = ‘a’;
这个也无法使用，和Intersection一样，key_paty1是组合索引，相反这样就可以了，SELECT * FROM single_table WHERE key1 = ‘a’ OR (key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’);

7.3 Sort-Union索引合并

举例：SELECT * FROM single_table WHERE key1 < ‘a’ OR key3 > ‘b’;
Sort-Union索引合并执行这个查询时，过程如下：

• 先根据key1 < 'a’的条件从idx_key1二级索引中获取二级索引记录，并将获取到的二级索引记录的主键值进行排序；

• 再根据key3>'z’条件从idx_key3二级索引中 获取二级索引记录，并将获取到的二级索引记录的主键值进行排序；

• 因为上述两个二级索引主键值都是排好序的，所以剩下的操作就 Union索引合并方式一样了。

这种将各个索引中扫描到的记录的主键值进行排序，再按照执行Union索引合并的方式执行查询就是Sort-Union索引合并。

本文部分摘抄来自《MySQL是怎样运行的》——小孩子4919