sql优化(2)-单表访问方法

单表访问方法

MySQL 是怎么执行单表查询的(就是 FROM 子句后面只有一个表)。一般对sql的性能查询可以用explian+要查询的sql语句。
对于type属性就是表的访问方法。

什么是访问方法

类比使用各种地图 App 来查找到某个地方的路线， 如果搜索从北京西站到北京站的路线，地图 App 会给出多种路线供选择，其中的花费的钱和时间都不相同，无论采用哪一种路线，最终的目标都是从北京西站到北京站。我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉 MySQL 要获取的数据符合哪些规则 ，至于 MySQL 是如何把查询结果搞出来的则是 MySQL 内部解决的事情。

**把 MySQL 执行查询语句的方式称为访问方法（access method ）或者访问类型 。**同一个查询语句可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是不同的执行方式花费的时间成本可能差距甚大。就像是从北京西站到北京站，可以坐公交车去，还可以骑共享单车去，当然也可以走着去。

使用单索引的访问方法

先展开说访问方法之前，先用创建一张举例用的demo表

CREATE TABLE table_demo ( 
	id INT NOT NULL AUTO_INCREMENT, 
	keyl VARCHAR(lOO) , 
	key2 INT, 
	key3 VARCHAR(100) , 
	key_partl VARCHAR(100) , 
	key_part2 VARCHAR(10Q) , 
	key_part3 VARCHAR(100) , 
	common_field VλRCHAR(lOO) , 
	PRIMARY  KEY (id) , 
    KEY idx_keyl (keyl),
    UNIQUE KEY uk_key2 (key2) , 
    key idx_key3 (key3),
    KEY idx_part(key_partl, key_part2, keY_part3) 
)

为这个 table_demo 表建立1个聚簇索引4个二级索引，分别是：

• 为 id 列建立的聚簇索引;
• 为 key1 列建立的 idx_key1 二级索引
• 为 key2 列建立的 uk_key2 唯一索引
• 为 key3 列建立的 idx_key3 二级索引
• 为key_part1、key_part2、 key_part3列建立联合索引

假设往这个表插入10万条随机数据。然后进行下面这些查询操作：

const

查询1：

select * from table_demo where  key2=1438;

这个查询的执行可以分为下面两步：

1. 使用唯一索引 uk_key2, 在索引对应的 B+ 树索引中，根据 key2列与常数的等值比较条件定位到一条二级索引记录(二级索引会记录包含 key2值和 对应的主键id值 )
2. 然后再根据该记录的 id 值到聚簇索引（即主键索引）中获取到完整的用户记录

这种通过主键或者唯一索引列与常数的等值比较来定位条记录是特别快的，所以把这种通过主键或者非空唯一索号列来定位一条记录的访问方法定义为 const (意思是常数级别的，代价是可以忽略不计的) 。不过这种 const 访问方法只能在主键列或者唯一二级索引列与一个常数进行等值比较时才有效，如果主键或者唯一二索引的索引列由多个列构成，则只有在索引列中的每个列都与常数进行等值比较时，这个const 访问方法才有效〈这是因为只有在该索引的每个列都采用等值比较时，才可以保证最多只有一条记录符合搜索条件)。

对于唯一二级索引列来说 在查询列为 NULL 值时，情况比较特殊。比如下面这样

select * from table_demo where  key2 is null;

因为唯一二级索引列并不限制 NUL 值的数量 所以上述语句可能访问到多条记录，也就是说上面这个语句不可以使用 const 访问方法来执行。

ref

查询2：

select * from table_demo where  key1 = 'abc';

对于这个查询，当然可以选择全表扫描的方式来执行，不过也可以使用 idx_keyl 来执行，此时对应的扫描区间就是[ ‘abc’, ‘abc’] ，这是个单点扫描区间 我们可以定位到 keyl = ‘abc’ 条件的第一条记录，然后沿辑记录所在的单向链表向后扫描，直到某条记录不符合 key1 = ‘abc’ 条件为止。由于查询列表是 *，因此针对获取到的每 二级索引记录，都需要根据该记录的 id 值执行回表操作， 到聚簇索引中获取到完整的用户记录后再发送给客户端。

由于普通二级索引并不限制列值的唯一性，所以位于扫描区间 ‘abc’ 中的 级索引记录可能有多条， 此时使用二级索引执行查询的代价就取决于该扫描区间中的记录条数。如果该扫描区间中的记录较少 ，则回表操作的代价还是比较低的。把这种"搜索条件为二级索引列与常数进行得值比较，形成的扫描区为单点扫描区间，采用二级索引来

执行查询"的访问方法称为 ref 。

对于普通的二级索引来说 ，通过索引列进行等值比较后可能会匹配到多条连续的二级索引记录，而不是像主键或者唯一二级索引那样最多只能匹配一条记录.所以这种 ref 访问方法比 const 差了那么一点。

另外二级索引列允许存储 NULL 值时，无论是普通的二级索引 ，还是唯一二级索引 ，它们的索引列并不 限制NULL值的数量，所以在执行包 “key IS NULL” 形式的搜索条件的查询时，最多只能使用 ref 访问方法 而不能使用 const 访问方法。

对于联合索引，按照最左匹配原则，只要最左边连续的列与常数进行等值比较，就可以采用ref 访问方法。如key_part1、key_part2、 key_part3列建成联合索引：

select * from table_demo where  key_part1 = 'a';
select * from table_demo where  key_part1 = 'a' and key_part2='b';
select * from table_demo where  key_part1 = 'a' and key_part2='b' and key_part3='c';

如果索引列中最左边连续的列不全部是等值比较的话，它的访问方法就不能称为 ref 了。

ref_or_null

查询3：

select * from table_demo where  key1 = 'abc' or key1 is null;

当查询3 使用二级索引而不是全表扫描的方式执行该查询时，对应的扫描区间就是 [NULL, NULL] 以及 [‘abc’ ‘abc’] 。像这样不仅需要找出某个二级索引列的值等于某个常数的记录，而且还需要把该列中值为 NULL 的记录也找出来的查询所使用的方法就叫 ref_or_null 。

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

range

查询4：

select * from table_demo where  key2 in (1438,1567) or (key2>1 and key2<100)

在对索引列与某一个常数进府等值比较时，才会使用到前文介绍的几种访问方法 。面对更为复杂的搜索条件如查询2，如果使用uk_key2索引形成的所对应的扫描区间为(1,100)、[1438,1438]、[1567,567]。把"使用索引执行查询时， 对应 扫描区间为若干个单点扫描区区 或者范围扫描区间"的访问方法称为 range (仅包含一个单点扫描区间的 方法不能 称为 range 访问方法，扫描区间为(-∞，+∞)的访问方法也不能称为 range 访问方法)。

index

查询5：

select key_part2, keY_part3 from table_demo where  key_part2='abc'

对于查询5，如果使用联合索引idx_key_part ，因为key_part2不是联合索引idx_key_part 的索引列中最左边的列，所以无法通过联合索引形成合适的扫描区间来减少扫描记录数据，从而无法使用ref或者range访问方法来执行这个语句。但是这个查询恰好符合下面这两个条件：

1. 它的查询列只有 key_part2, keY_part3 这2个列，而联合索引 idx_key_part 的索引值又包含这两个列
2. 搜索条件中只有key_part2列，这个列也包含在索引idx_key_part中

也就是说，我们可以直接遍历 idx_key_part 索引的所有二级索引记录， 针对获取到每一条二级索引记录，都判断 key_part2 = ‘abc’ 条件是否成立。如果成立，就从中读取出 key_part2、key_part3 这2个列的值并将它们发送给客户端。很显然 在这种使用 idx_key_part 索引执行上述查询的情况下，对应 扫锚区间就是(-∞ +∞) 。

由于二级索引记录比聚簇索记录小得多(聚簇索引记录要存储用户定义的所有列以及隐藏列，而二级索引记录只需要存放索引列和主键) ，而且这个过程不用执行回表操作，所以直接扫描全部的二级索引记录比直接扫描全部的聚簇索引记录的成本要小很多。把这种扫描全部二级索引记录的访问方法称为 index 方法。

另外，当通过全表扫描对使用 lnnoDB 存储引擎的表执行查询时, 如果添加了 “ORDER BY 主键” 的语句，那么该语句在执行时也会被人为地认定未使用的是 index 访问方法 。

select * from table_demo order by id ;

all

最直接的查询行方式就是全表扫描 ，对于 InnoDB 表来说就是直接扫描全部的聚簇索引记录，把这种使用全表扫描执行查询的访问方法称为 all 访问方法。

使用多索引的访问方法

MySQL 在" 一般情况下"只会为单个索引生成扫描区间，但还存在特殊情况 。这些特殊情况下，MySQL 也可能为多个索引生成扫描区间 。把这种使用多个索引来完查询的执行方法称为index_merge (索引合并)。 具体的索引合并方法有下面3种

Intersection 索引合并

查询6：

select * from table_demo  where key1='a' and key3='b';

对于查询6 ，除了使用全表扫描的方法，还可以使用 idx_key1索引和idx_key3索引，这分别对应着两个方案：

方案1：使用 idx_key1 索引执行该查询，此时对应的扫描区间就是 [‘a’，‘a’]，可 对于获取到的每条二级索引记录，根据它的 id 值执行回表操作后获取到完整的用户记录，再判断 key3 = ‘b’ 条件是否成立。对于满足 key1='a’的二级索引，其对应的主键值是按大小排序的。

方案2：使用 idx_key3 索引执行该查询，此时对应的扫描区间就是 [‘b’，‘b’]，可 对于获取到的每条二级索引记录，根据它的 id 值执行回表操作后获取到完整的用户记录，再判断 key1 = ‘a’ 条件是否成立。对于满足 key3='b’的二级索引，其对应的主键值是按大小排序的。

除了使用单个索引的方案1和方案2，还有以有方案3：

方案3：同时使用 idx_key1 和 idx_key3 执行查询。也就是在idx_key1中扫描key1值 在[‘a’，a’] 区间中的二级索引记录，同时在 idx key3 中扫描 key3 值在[‘b’，‘b’] 区间中的二级索引记录，然后从两者的操作结果中找出 主键列值相同的记录（即找它们共有的 id值），然后再据这些共有的主键值执行回表操作 （那些仅在单个扫描区间中包含的 id 值就不需要执行回表操作了 ，这样可能省下很多回表操作带来的开销）

这里的方案3就是所谓的 Intersection 索引合并。Intersection 的中文含义就是"交集"， Intersection 索引合并指的就是对从不同索引中扫描到的记录的主键值取交集，只为这些主键值执行回表操作 。

如果使用Intersection 索引合并的方式执行查询，并且每个使用到的索引都是二级索引的话， 则要求从每个索引中获取到的二级索引记录都是按照主键值排序的（补充一下：如果是主键索引，那么必然是按照主键排序的）。比如在上面的查询中，在 idx_key1 的 [‘a’ ，‘a’] 可扫描区间中的二级索引记录都是按照主键值排序的，在 idx_key3 的[‘b’， ‘b’] 扫描区间中的二级索引记录也都是按照主键值排序的。

为什么会要求从不同二级索引中获取到的二级索引记录都按照主键值排好序呢？这主要出于两方面的考虑

1、从两个有序集合中取交集比从两个无序集合中取交集要容易得多:

2、如果获取到的 id 值是有序排列的，则在根据这些 id 值执行回表操作时就不再是进行单纯的随机I/O（这些 id 值是有序的），从而会提高效率

如果使用索引获取的二级索引记录不是按照主键值排序的话，是不能使用 Intersection 索引合并 的方式执行的。

例如下面这些查询：

select * from table_demo  where key1>'a' and key3='b';
select * from table_demo  where key1='a' and key_part1='b';

Union 索引合并

查询7：

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

是否可以仅使用 idx_key1或者 idx_key3索引来执行上述查询吗？以使用 idx_key1索引为例，使用 idx_key1索引执行查询7，对应的扫描区间就是 (-∞,+∞ ) ,相当于要获取所有的 idx_key1索引的每一条记录值，然后执行回表操作。

在这种情况下，除了全表扫描方法，还可以同时使用 idx_key1和idx key3 索引执行查询。也就是在 idx_key1 扫描 keyl 值位于 [‘a’, ‘a’] 区间中的二级索引记录， 同时在idx_key3 中扫描 key3 值位于[‘b’, ‘b’] 区问中的二级索引记录，然后根据二级索引记录的 主键值在两者的结果中进行去重，再根据去重后的 id 值执行回表操作，这样重复的 主键值只需回表一次。这方案就是所谓的 Union 索引合并。Union 的中文含义就是"并集"，Union 索引合并指的就是对从不同索引中扫描到的记录的主键值取并集，为这些主键值执行回表操作 。

如果使用 Union 合并的方式执行查询，并且每个使用到的索引都是二级索引的话，则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。比如在上面的查询中，在 idx_key1 的 [‘a’ ，‘a’] 可扫描区间中的二级索引记录都是按照主键值排序的，在 idx_key3 的[‘b’， ‘b’] 扫描区间中的二级索引记录也都是按照主键值排序的。

为什么会要求从不同二级索引中获取到的二级索引记录都按照主键值排好序呢？这主要出于两方面的考虑

1、从两个有序集合去重比从两个无序集合中执行去重要容易得多:

2、如果获取到的 id 值是有序排列的，则在根据这些 id 值执行回表操作时就不再是进行单纯的随机I/O（这些 id 值是有序的），从而会提高效率

如果使用索引获取的二级索引记录不是按照主键值排序的话，是不能使用 Intersection 索引合并 的方式执行的。

例如下面这些查询：

select * from table_demo  where key1>'a' or key3='b';
select * from table_demo  where key1='a' or key_part1='b';

Sort_Uinon 索引合并

Union 索引合并的使用条件太苛刻，它必须保证从各个索引中扫描到的记录的主键值是有序的。比如下面这个查询就无法使用 Union 索引合并:

查询8

select * from table_demo  where key1<'a' or key3>'z';

针对查询8，可以使用sort_union 索引合并访问方式，具体步骤如下：

• 先根据 key1 <‘a’ 条件从 idx_key1 二级索引中获二级索引记录，并将获取到的二级索引记录的主键值进行排序
• 再根据 key3 >‘z’ 条件从 idx_key3二级索引中获取二级索引记录，并将获取到的二级索引记录的主键值进行排序
• 因为这两个二级索引现在的主键值都是排好序的， 剩下的操作就与 Union 索引合并方式一样

把上面这种"先将从各个索引中扫描到的记录的主键值进行排序，再按照执行 Union 索引合并的方式执行查询"的方式称为Sort-Union 索引合并。很显然， Sort-Union 索引合并比 Union 合并多了一步对二级索引记录的主键值进行排序的过程。

为什么没有 Sort_Intersection 索引合并？

因为排序是一个很费时的操作，如果and条件使用范围查询，完全可以取其中一个索引，然后进行回表操作即可，进行排序后又取交集再回表，可能性能上不相上下甚至有所降低，但是or操作不一样，无法通过取其中一个索引再回表的操作，另一个条件可能全表都有，此时就要全表扫描，这样性能较差，不如两个索引都查然后排序一下再取并集，这样性能比全表扫描效果要好

索引合并比 Union 合并多了一步对二级索引记录的主键值进行排序的过程。

为什么没有 Sort_Intersection 索引合并？

因为排序是一个很费时的操作，如果and条件使用范围查询，完全可以取其中一个索引，然后进行回表操作即可，进行排序后又取交集再回表，可能性能上不相上下甚至有所降低，但是or操作不一样，无法通过取其中一个索引再回表的操作，另一个条件可能全表都有，此时就要全表扫描，这样性能较差，不如两个索引都查然后排序一下再取并集，这样性能比全表扫描效果要好