MySQL的单表访问方法探讨

对于我们来说，Mysql我们平时用的最多的就是查询功能，偶尔需要对一些语句做出优化，但是我们如果连查询是怎样执行的都不知道的话，优化也就无从谈起了。
Mysql有一个称为优化器的模块，Mysql在对一条查询语句进行语法解析之后，就会将其交给优化器进行优化，优化器的结果就是生产一个所谓的执行计划。
我们接下来首先要看一下Mysql是怎样执行单表查询的：
首先创建一张single_table表：

CREATE TABLE single_table(
	ID INT NOT NULL AUTO_INCREMENT,
	KEY1 VARCHAR(100),
	KEY2 INT,
	KEY3 VARCHAR(100),
	KEY_PART1 VARCHAR(100),
	KEY_PART2 VARCHAR(100),
	KEY_PART3 VARCHAR(100),
	common_filed VARCHAR(100),
	PRIMARY KEY(id),
	KEY idx_key1(key1),
	UNIQUE KEY uk_key2(key2),
	KEY idx_key3(key3),
	KEY id_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8;

const

有时可以通过主键列来定位一条记录，比如以下这个查询：

SELECT *FROM SINGLE_TABLE WHERE ID =10;

MYSQL会直接利用主键值在聚簇索引中定位对应的用户记录。

与之类似，我们根据唯一二级索引来定位一条记录的速度也是很快的:

SELECT * FROM single_table WHERE key2 = 3831;

该查询执行过程分为两步：
1、在uk_key2对应的B+树种，根据key2列与常数的等值比较条件定位到一条二级索引记录。
2、再根据该记录的id值到聚簇索引中获取到完整的用户记录。

他们把这种通过主键或者唯一二级索引来定位一条记录的访问方法定义为const(常数级别)，，不过该const访问方法只能在主键列或者唯一二级索引列于一个常数进行比较时才有效。
当常数列为null时，不可以使用const方式访问。

ref

有时，我们需要将某个二级索引列与常数进行等值比较，比如：

SELECT * FROM single_table WHERE key1 = 'abc';

对于该查询，可以通过全表扫描的方式执行，也可以使用idx_key1来执行，此时对应的扫描区间是[‘abc’, ‘abc’] ， 也就是一个单点扫描区间。我们可以定位到key1 = 'abc’条件的第一条记录，然后沿着单向链表一直向后查找，直到某条记录不符合key1 = 'abc’的条件为止。由于查询列表是*，所以需要对获取到的每一条二级索引记录，都需要根据获取到的id进行回表操作，将聚簇索引叶子节点的数据返回到客户端。

搜索条件为普通二级索引与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法成为ref。
对于普通的二级索引来说，通过索引列进行等值比较后可能会匹配到多条连续的二级索引记录，而不像是主键或者唯一二级索引那样最多只能匹配一条记录。所以这种ref访问方法比const差了一点。

有两点需要注意：
1、在二级索引列允许存储NULL值的数量，所以在之心包含 "key is null "形式的搜索条件时，最多只能使用ref访问方法。
对于索引列种包含多个列的二级索引来说，只要最左边连续的列是与常数进行等值比较就可以采用ref访问方法。

ref_or_null

有时，不仅想找到某个二级索引列的值等于某个常数的记录，而且还想把该列中值为NULL的记录找到：

SELECT * FROM single_tan;e WHERE key1 = 'abc' OR key1 is NULL;

当时用二级索引而的方式执行该查询时，对应的扫描区间就是[NULL,NULL]和 [‘abc’,‘abc’]，此时执行这种类型的查询所使用的访问方法就称为ref_or_null。

注意：值为NULL的记录会被放在索引的最左边。

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2<= 79);

这时面对的搜索条件就很复杂，如果使用inx_key2执行该查询，那么对应的扫描区间是[1438,1438]、[6328,6328]以及[38,79]。所以在使用索引执行查询时，对应的扫描区间为若干个单点扫描区间，或者范围扫描区间的访问方法成为range。需要注意的是，仅包含一个单点扫描区间或者扫描区间为负无穷到正无穷的访问方法不能称为range访问方法。

index

来看下这个查询：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc' ;

由于key_part2并不是联合索引idx_key_part的索引列最左边的列，所以无法形成合适的范围区间来尖山需要扫描的记录数量，从而无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下面这两个条件：
1、他的查询列表只有key_part1, key_part2, key_part3这三个列，而索引idx_key_part又恰好包含这3个列；
2、搜索条件中只有key_part2列，这个列也包含在索引idx_key_part中。

也就是说，我们直接遍历id_key_part索引的所有二级索引记录，针对获取到的每一条二级索引记录，都判断key_part2='abc’的条件是否成立，如果成立，则取出key_part1, key_part2, key_part3返回到客户端。

二级索引记录比聚簇索引小得多，而且这个过程不需要回表，直接扫描二级索引记录比直接扫描全部的聚簇索引记录的成本要小得多。这种扫描方法被称为index访问方法。

注意：如果全表扫描时，添加了ORDER BY 主键的语句，那么该语句在执行时，会被人为地认定为index访问方法。

all

a最直接的查询方式就是全表扫描，使用全表扫描执行查询的访问方式称为all访问方法。

索引合并

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

1、intersection索引合并

SELECT * FROM SINGLE_TABLE WHERE KEY1 = 'a' AND key3 = 'b'; 

执行该条sql除了全表扫描外还有其他三种方法：

–方案一：使用idx_key1索引执行该查询，此时对应的扫描区间是[ ‘a’, ‘a’ ]，然后将满足区间的数据判断key3 = ‘b’ 是否满足条件，这里需要注意的一点是 ，其key1 =‘a’，也就意味着这些二级索引记录其实是按照主键值进行排序的。

–方案二：使用idx_key1索引执行该查询，此时对应的扫描区间是[ ‘b’, ‘b’ ]，然后将满足区间的数据判断key1 = ‘a’ 是否满足条件，这里需要注意的一点是 ，其key3 =‘b’，也就意味着这些二级索引记录其实是按照主键值进行排序的。

–方案三：同时使用idx_key1和idx_key3执行查询。也就是在idx_key1中扫描key1值在[ ‘a’, ‘a’ ]区间中的二级索引记录，同时在idx_key3中扫描key3值在[ ‘b’ , ‘b’ ]区间中的二级索引记录，然后从两者的操错结果中找出id列值相同的记录(即找出他们共有的id值)，再根据这些共有id执行回表操作。

而这个方案三就是所谓的Intersection索引合并。Intersection的意思是交集，其含义就是对不同索引扫描到的记录取交集。

2、Union索引合并

SELECT * FROM SINGLE_TABLE WHERE KEY1 = 'a' OR key3 = 'b'; 

如上这个查询，我们除了全表扫描外，只能是同时使用两个索引来执行查询，我们同时使用idx_key1和idx_key3来执行查询。有人就是在idx_key1中扫描key1值位于[ ‘a’ , ‘a’ ]区间中的二级索引记录，同时在idx_key3中扫描key3值位于 [ ‘b’ , ‘b’ ]区间中的二级索引记录，然后根据索引记录中的id在两者的结果中去重，再根据去重后的id值执行回表操作，这样重复的id值只需执行一次，这就是所谓的Union索引合并。其中文意思为并集，也就是不同索引扫描到的记录取并集。

3、Sort-Union索引合并

也就是将索引中扫描到的记录先按照主键值排好序，再使用Union索引合并方式进行合并。