MySQL-6、单表访问方法

 前言

前面介绍了MySQL表空间相关的内容。包括区、段、碎片区，还有一些不同的页类型的作用。

（如果没有看前面五篇文章，不建议看此篇文章）

  传送门：

MySQL-1、InnoDB行格式

MySQL-2、InnoDB数据页

MySQL-3、索引

MySQL-4、B+树索引的使用

MySQL-5、InnoDB的表空间

访问方法的概念

我们平时写的最多的就是查询语句，本质上是一种声明式的语法，是MySQL规定的语法。同一个查询语句可以使用多种不同的访问方法来执行，虽然最后的结果是一样的，但是不同的执行方式，所耗费的时间成本差距是很大的。

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

const

const翻译过来就是常量，意思就是常量级别的访问方法 。

通过主键或者唯一二级索引列来定位一条记录的访问方法就是const级别（常量级别代价是可以忽略不计的）。不过这种访问方法只能在主键或唯一二级索引列与一个值进行等值比较才有效。因为采用等值查询，才可以保证最多只有一条记录符合搜索条件。

由于唯一二级索引列不限制NULL值的数量，当进行NULL查询时，就可能访问到多条记录，也就不会常采用const级别。

ref

通过二级索引列进行等值比较，形成的单点扫描区间，这种查询的访问方法称为ref。

这里再啰嗦以下，采用二级索引来执行查询操作时，每获取一条二级索引记录，就会立刻对其执行回表操作，而不是将所有二级索引记录的主键值都收集起来之后再统一执行回表操作。（这里说的回表的前提是，要查询的字段数据，在聚簇索引里才有）

使用二级索引查询，还有两点情况：

• 二级索引列允许为NULL时，无论是普通二级索引，还是唯一二级索引。在执行 二级索引列 IS NULL查询时，最多只能使用ref访问方法。
• 如果是联合索引（也是二级索引），只要最左边连续的列进行等值比较，就可以才用ref访问方法。如果最左边的列不全是等值比较的话，那就不能用ref。

ref_or_null

ref_or_null从名字上看，就是在ref的基础上，再判断为NULL的情况。实际上也就是这样。

通过二级索引列进行等值比较，形成的单点扫描区间，同时还有 OR 二级索引列 IS NULL的条件，这种查询的访问方法称为ref_or_null。形成的扫描区间就是 ['值','值']和[NULL,NULL]。

ref_or_null访问方法只是比ref访问方法多扫描了一些值为NULL的二级索引记录。

（值为NULL的记录会被放在索引的最左边）

range

range从名字上也可以看出，是范围级别。通过使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围是范围扫描区间，这种访问方法 称为 range。

这种扫描区间不能叫做 range，那就是(-∞,+∞)，这种是全部扫描。zong

index

查询的列和筛选条件都是索引列，不需要回表，称为index。也称为索引覆盖。

all

all没有什么可说的，就是全表扫描。

索引合并

一般情况下，MySQL执行一条sql，只会为单个索引生成扫描区间，但是也有一些特殊情况。在这些特殊情况下，MySQL会为多个索引生成扫描区间。MySQL把这种使用多个索引来完成一次查询 的执行方法称为 索引合并（index merge）。共有3种特殊情况，说特殊情况前，先把之前的表结构搬过来。

create table demo_table
(id int not null auto_increment,key1 varchar(50),key2 int,key3 varchar(100),key_part1 varchar(100),key_part2 varchar(100),key_part3 varchar(100),des varchar(255),primary key(id),key idx_key(key1),unique key uk_key2(key2),key idx_key3(key3),key idx_key_part(key_part1,key_part2,key_part3)
)

该表iid为自增长主键，key1和key3为普通索引，key2为唯一索引，key_part1,key_part2,key_part3为联合索引。

1.Intersection 索引合并

现在有下面一条sql：

select * from demo_table where key1 = 'a' and key3 = 'c';

了解之前B+树使用的文章知道，会有三种执行方式来减少扫描数据。

• 第一种，全表扫描。。（有索引，这肯定不用）
• 第二种 ，使用key1的二级索引，对应的扫描区间就是[a,a]。然后每获取一条二级索引记录，回表查询其他字段数据。最后再判断key3 是否等于 c，依次循环。直到找到第一条不符合key1 = 'a'的数据为止。
• 第三种，使用key3的二级索引，对应的扫描区间就是[b,b]。然后每获取一条二级索引记录，回表查询其他字段数据。最后再判断key1 是否等于 a，依次循环。直到找到第一条不符合key3 = 'c'的数据为止。

其实还有一种可能，之前我们说过，二级索引会根据列值进行排序，如果列值相同，会根据主键值再排序。上面的sql就符合这种条件。

• 第四种，同时使用key1的二级索引和key3的二级索引。key1索引的扫描区间是[a,a]，key3的扫描区间是[b,b]，然后在两者的操作结果中找出主键值相同的记录（就是共有的主键值）。然后根据共有的主键值回表，去聚簇索引中查找完整记录。

使用Intersection 索引合并有个必要条件，从使用的每个索引中获取到的二级索引记录都是按照主键值排序的。为什么要有这个必要条件呢，主要从以下两点：

• 从两个有序的集合中取交集比从两个无序的集合中取交集要容易的多。
• 如果获取到的主键值是有序的，则根据主键值进行回表时就不再是进行单纯的随机I/O，从而提高效率。

下面大致说下第四种方式的执行过程：

• 假设key1的扫描区间[a,a]，其中主键值是 2，6，9，假设key3的扫描区间[b,b]，对应的主键值是 3，6，15。
• 先从key1的扫描区间取出第一条记录，该记录主键值为1。然后从key3的扫描区间取出一条记录，该记录主键值为3。因为 2 <  3，所以直接丢弃key1中取出的第一条记录。
• 接着继续从key1的扫描区间取出第二条记录，该记录主键值为5。然后根据上一步骤中key3的扫描区间取出第一条记录进行比较，该记录主键值为3。因为 6 > 3，所以直接丢弃key3中取出的第一条记录。
• 接着从key3的扫描区间取出第二条记录，该记录主键值为6。然后根据上一步骤中key1的扫描区间取出第二条记录。因为 6 = 6，也就意味着取交集成功，然后进行回表，将回表获取到的完整数据发送给客户端。（这个客户端不是真正的我们用户，而是MySQL的程序，最后将所有符合的数据，一起返回给用户）
• 同理依次执行上面的步骤，直到将所有扫描区间遍历完毕。

下面看下不能用Intersection 索引合并的sql

select * from demo_table where key1 = 'a' and key_part1 = 'b';

key1的二级索引记录是按照主键值排序的，但是key_part1是聚合索引，显示按照key_part1排序，然后按照key_part排序，然后按照key_part3排序。key_part1的扫描区间中的主键值不是有序的。

2.Union索引合并

Intersection 索引合并就是把相同的主键值取交集。有交集就有并集，Union索引合并就是取并集。同理我们看下面的sql

select * from demo_table where key1 = 'a' or key3 = 'b';

参照上面Intersection 索引合并，上面sql也有三种执行方案。分别是使用key1二级索引、key3二级索引、全表扫描。当然也有第四种，那就是Union索引合并方案。

可以同时使用key1二级索引和key3二级索引来执行查询，在key1二级索引的扫描区间[a,a]，在key3二级索引的扫描区间[b,b]。然后根据两个记录主键值的集合去重，最后根据去重后的主键值集合回表，这样重复的主键值只会回表一次。

同理使用Union索引合并也有一个必要条件：从使用的每个索引中获取到的二级索引记录都是按照主键值排序的。原因同上。

3.Sort-Union索引合并

上面两种索引合并的前提条件都是二级索引下的主键值，必须是有序的。Sort-Union索引合并就是解决如果是无序的一种情况，我们看下面的sql

select * from demo_table where key1 > 'a' or key3 < 'y';

这种情况，二级索引下的主键值肯定是无序的。但是我们又想使用索引合并，那该怎么办呢？这就用到了Sort-Union，看字面意思，就是排序+合并。真是的也是这样的，先将key1二级索引和key3二级索引查出来后，分别对查询的主键值进行排序，然后将两个主键值集合进行合并。最后是回表操作。所以与Union索引合并的区别就是，合并前，会进行一次排序操作。

不知道大家会不会有个疑问，为什么没有Sort-Intersection??

原因如下：

• Union 的目标是合并结果集，所以排序后的合并是有效的优化。
• Intersection 的目标是找到结果集的共同元素，重点在于匹配，而不是合并顺序。因为排序好了之后还是会继续检索后面的数据是否有交集。Intersection 索引合并有序是为了在回表的时候减少随机I/O。

总结：

MySQL的访问方法，根据性能排序：const > ref > ref_or_null > range > index > all。

我们怎么知道这条sql的执行方法是const、ref、range、index、还是全表扫描呢。又怎么知道这条sql用到了索引合并，用到的具体是哪种索引合并呢。在后面会具体写一篇关于MySQL EXPLAIN用法的文章，后面再做介绍。