深入理解InnoDB（8）—单表访问_Gogo的专栏文章_微信文章

1. 访问方法

MySQL把执行查询语句的方式称之为访问方法或者访问类型。而访问方法大致分为两类

全表扫描
索引

而进行细分的话可以分为以下几类（为了方便说明，先建一个表）

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_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
复制代码

1.1 const

SELECT * FROM single_table WHERE id = 1438;

SELECT * FROM single_table WHERE key2 = 3841;
复制代码

例如上面两个sql语句，条件都是主键或者唯一二级索引列与常量进行比较。

第一个sql语句的执行，直接就是在主键的聚簇索引中，根据主键值进行匹配
第二个sql语句，先要在对应的唯一二级索引中，根据查询的索引列进行一次匹配，查到主键id后，再回表查出所有的数据

因为主键和唯一二级索引，他们的索引列都是唯一的，因此查找的速度是非常快的，所以这种访问方法叫const，意思就是常数级别的查询速度.（注意：当索引列的查询条件为空时，访问方法并不能算const，因为可能有多条记录为空）

1.2 ref

而ref的访问方式类似于唯一二级索引的访问方式，都是先在二级索引上进行匹配，找到主键id后，再回表查询。但是，因为普通二级索引列的值并不是唯一的，因此可能查询到的是多条记录，再进行多次回表。

SELECT * FROM single_table WHERE key1 = 'abc';
复制代码

例如上面的sql语句，因为key1是普通二级索引，可能存在多个key1='abc'的记录，因此在查询时，需要对多个记录进行回表

1.3 ref_or_null

这个与ref类似，只是多了对于空值的匹配

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;
复制代码

例如上面的sql语句，是在key1这个二级索引上，查询key1 = 'abc'和key1为空的主键id，然后再进行回表

1.4 range

而range访问方法，意思就是根据范围进行查询，前面的查询都是匹配单个常数值的，而range是查询一个范围的。

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

例如上面的sql语句，只是在之前查询key2 IN (1438, 6328)的基础上，将满足79>=key2>=38的主键id也找出来，同样进行回表

1.5 index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
复制代码

key_part2 = 'abc'这个条件并不满足最左匹配，但是key_part1, key_part2, key_part3 属于一个联合索引idx_key_part，因此这个查询是在idx_key_part索引的叶子节点中，一个一个匹配key_part2 = 'abc'，直接返回索引列即可，并不需要回表。

index的意思就是直接在索引中匹配查询。

1.6 all

而all就是全表扫描

2.注意事项

2.1 一般情况下只能利用单个二级索引执行查询

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;
复制代码

查询优化器会根据single_table表的统计数据，判断key1 = 'abc' 和 key2 > 1000两个条件，哪个索引查询的条数少，就选择到哪个索引的B+树上查。

例如在上面的例子中，优化器选择的是key1 = 'abc'，那么查询到满足key1 = 'abc' 的主键后，就会回表查询，并且以 key2 > 1000为条件进行过滤

2.2 明确range访问方法使用的范围区间

2.2.1 所有搜索条件都可以使用某个索引的情况

例如：

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
复制代码

因为条件都是来自同一个索引的范围查询，因此只要将对应的范围进行集合的运算即可

2.2.2 有的搜索条件无法使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
复制代码

因为common_field = 'abc'无法使用到索引，因此范围搜索只需要关注key2 > 100这个范围即可，回表的时候再根据common_field = 'abc'过滤一次，就可以保证选出来的记录满足 key2 > 100 AND common_field = 'abc'。

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
复制代码

但是，当条件换成or以后，就无法使用key2的索引了，因为查询出了key2 > 100的记录以后，没有办法也在key2的索引中查出common_field = 'abc'的记录

2.2.3 复杂搜索条件下找出范围匹配的区间

SELECT * FROM single_table WHERE 
        (key1 > 'xyz' AND key2 = 748 ) OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;
复制代码

主要使用到key1和key2 两个索引，因此对他们进行分别的查找的区间

对于key1

先将不属于key1的条件替换为true

(key1 > 'xyz' AND TRUE ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
复制代码

替换掉永远为TRUE或FALSE的条件

因为符合key1 < 'abc' AND key1 > 'lmn'永远为FALSE，所以上边的搜索条件可以被写成这样：

(key1 > 'xyz') OR (key1 > 'zzz')
复制代码

合并范围

key1 > xyz
复制代码

对于key2

先将不属于key1的条件替换为true

(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
复制代码

再次化简，可以推出最后条件是true，也就是说需要搜索全部的key2索引

2.3 索引合并

之前第一点注意事项说过：一般情况下只能利用单个二级索引执行查询，而在特殊情况下也可能在一个查询中使用到多个二级索引。因此把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

2.3.1 Intersection合并

Intersection（交集），意味着对多个二级索引的结果找交集

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
复制代码

执行过程

在key1这个二级索引上找key1 = 'a'
在key3这个二级索引中找key3 = 'b'
因为二级索引查出来的记录格式是索引列加主键，因此直接对他们查出来的主键作一个交集再回表即可

为什么不直接查到二级索引key1的记录，然后在回表的过程中，过滤掉不满足key3的条件呢？

因为如果在key1上面查出来的记录条数很多的话，那么就要进行很多次的回表，而这种回表属于随机I/O，非常耗时，而选择两次的二级索引查找，属于顺序I/O，有更好的性能。

使用Intersection的情况：

二级索引只能是等值匹配

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';
复制代码

第一条sql语句可以正常使用
第二条使用了范围查询
第三条没有使用联合索引的所有列

主键列可以是范围匹配

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';
复制代码

因为二级索引下，索引列是已经排好序的了，而如果出现了索引列全部相同的记录，那么将按照主键值进行排序，而使用了排好序的主键来求交集，就非常简单了，因为对于两个已经排好序的主键序列，求他们交集的时间复杂度是o（n）。

这也回答了第一种情况，为什么只能是等值匹配，因为只有等值匹配的情况下，索引列记录相同的几条记录才能按照主键的顺序排好。

上面的两种情况只是使用Intersection的必要条件。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

2.3.2 Union合并

Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。 使用Union的情况：

二级索引只能是等值匹配(与Intersection相同)
主键列可以是范围匹配(与Intersection相同)
使用Intersection索引合并的搜索条件

例如：

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');
复制代码

1.先按key1 = 'a' AND key3 = 'b'，使用 Intersection得到一个交集

2.再使用 key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'，得到一个集合

3.再对上面求到的两个集合求并集。然后进行回表

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

2.3.3 Sort-Union合并

Union的条件太苛刻了，只有等值匹配才能使用到。

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
复制代码

而对于这样的范围查询就无法使用了，而对于上面的sql就可能执行Sort-Union索引合并。

1.按照key1 < 'a'查出所有记录，并且排好序 2.按照key3 > 'z'查出所有记录，并且排好序 3.因为两个集合的主键都是排好序的了，因此直接按Union那样做就行了

为什么没有Sort-Intersection？ 因为Union是在单独根据搜索条件从某个二级索引中获取的记录数比较少的情况下使用的，而这种情况下记录数少，排序的性能损耗小。而Intersection的情况恰恰相反，它是针对查询出的记录数多，如果排序对于性能的损耗就很大了

2.3.4 联合索引注意事项

联合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
复制代码

像这样的sql语句，需要使用到两个单独的索引，倒不如直接做成联合索引，既可以少一颗b+树，还不能做集合运算。

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);
复制代码