学习访问方法，首先以一张表为例，建表语句如下：
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;访问方法
使用explain语句就可以看到该语句采用了什么访问方法，在结果的ref字段处
const
查询条件是等式，且结果一定只有一个值的时候，匹配到就是这种方法，例如以下查询：
SELECT * FROM single_table WHERE id = 1438;
SELECT * FROM single_table WHERE key2 = 3841;可见，只有主键和UNIQUE KEY才会触发这种场景
看着这个方法名字像是常数，深入分析其索引的原理，有两种：
条件是主键，直接通过聚簇索引找到对应值
条件是UNIQUE KEY，通过二级索引找到唯一值，拿到其主键，回表从聚簇索引找到唯一值
因此const是查询速度最快的访问方法
ref
查询条件是等式，但结果不一定只有一个值，匹配到这种方法，例如：
SELECT * FROM single_table WHERE key1 = 'abc';在这种查询场景下，使用二级索引key1再回表，key1的值不唯一，回表是随机I/O，这时引擎会判断回表的消耗与直接遍历聚簇索引后再内存比较的消耗，如果回表代价较低，就会采用ref方法
因为这种场景涉及到随机I/O，因此它的性能低于const
同时需要注意以下场景：
对
key is NULL条件的查询最多使用ref，因为允许为NULL的列，NULL不一定是唯一的对于联合索引，自左查询的索引列均为等值，才能尽量使用到ref，而出现了非等值比较，就不能用ref了，例如：
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';ref_or_null
对于不唯一的等值+null的匹配，使用的就是这种方法
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;range
例如以下这种场景：
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);多值/范围匹配使用的就是range方法
看上去好像key2 IN (1438, 6328) 不是等值的吗，实际上这跟前面ref是不同的，ref是一个值，有多条记录，而这里是多个值，比如IN语句和范围匹配，实际上都是查找多个点
而这种场景在二级索引树中，实际上是一个范围内的叶子节点，以及多个单点，也就是一个区间，写成[38, 79]U[1438]U[6328]
因此名如其意，就是范围匹配
对于 B+ 树索引来说，只要索引列和常数使用 = 、 <=> 、 IN 、 NOT IN 、 IS NULL 、 IS NOT NULL 、 > 、 < 、 >= 、 <= 、 BETWEEN 、 != （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的区间 。
而多条件，例如key2 > 100 and key2 > 200这种则会合并为一个区间，这一点也是符合数学思维的
index
该名称看上去是索引，与ref有区别，ref是使用二级索引并且需要回表，index是遍历二级索引，不需要回表，例如以下：
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';因此可以看出，使用index的场景是：
条件无法匹配索引，key_part2是联合索引的一部分，且非最左列，因此匹配不上
查询内容仅包括索引列
在这种场景下，遍历二级索引成本要比遍历聚簇索引小，因为数据量少，因此仅遍历二级索引即可，不需要回表，这种就是index匹配方法
all
最慢且最大的开销，全表扫描
访问方法的性能
一般访问方法性能排序是const>ref>range>index>all
但是在日常工作中很难实现const排序，一般优化到ref和range就很好了，最次优化到index，使用all的话数据量大起来性能就会很差了
引擎匹配访问方法的机制
单索引机制
一般只能使用一个索引进行匹配，引擎会判定使用哪个索引效率更高，例如：
SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;这里where有两个条件，包括key1的等值匹配，和key2的范围匹配，分别对应两个二级索引
这时候，key1是ref，key2是range，肯定优先匹配const，即选择idex_key1索引代表的二级索引树匹配数据，然后在内存中执行key2 > 1000 的过滤条件
一般情况下，这是最常见的匹配机制
主动放弃索引的机制
有时使用索引无法带来正向效果，则放弃使用索引，例如：
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';这两个查询案例，案例1是取交集，即key2 > 100 条件一定生效，可用匹配二级索引，而案例2取并集，即key2 > 100 不满足也行，这与扫全表没区别了，因此就没必要匹配索引了
有一个简单的判定方法：因为一般只使用一个索引树，因此可以假设匹配到某个二级索引，并将其他条件全部判定为TRUE，简化语句，例如：
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，则语句变为：
SELECT * FROM single_table WHERE
(key1 > 'xyz' AND TRUE ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE )) ;这里把key1 LIKE '%suf' 化为TRUE是因为该语句不符合字符串的最左前缀原则，因此也匹配不到索引
进一步简化为：
SELECT * FROM single_table WHERE
(key1 > 'xyz') OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 > 'zzz') ;其中(key1 < 'abc' AND key1 > 'lmn') 该条件永远无法满足，因此进一步简化为
SELECT * FROM single_table WHERE
(key1 > 'xyz') OR
(key1 > 'zzz') ;原来是一个range方法，取并集，即(key1 > 'xyz') ，则引擎会使用key1的二级索引，然后再在内存中做筛选
如果引擎选择key2的索引，简化方法一样，最终结果是TRUE，即使用key2的二级索引跟全表扫描没啥区别，那就不会选择这个索引了
索引合并
在很少情况下，引擎会使用多个索引，这个情况要符合索引合并的条件
intersection合并
又叫交集合并，即在条件全都是二级索引列等值查询条件下且匹配联合索引的全部列的情况下，或主键允许范围匹配其他二级索引列全都是等值匹配的条件下，可以进行索引合并
两个场景，分别看：
场景1：and查询条件下，查询条件均为二级索引列，且全为等值匹配，且联合索引要涉及全部索引列
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';这个符合，key1和key3是单列的索引，都是等值匹配
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';这个符合，key1是单列的，另外三个是联合索引，且全部涉及
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';这个不符合，因为联合索引没有匹配全部列
场景2：and查询条件下，主键列范围匹配，其他二级索引列全为等值匹配，且联合索引涉及全部索引列
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a'这个符合，因为id是主键，可以范围匹配，key1是单列的索引，使用等值匹配
为什么intersection合并可以使用到多个索引列？
因为当满足场景1或场景2，先看其中的二级索引，查到的结果一定都是按主键排序的，然后再去其中匹配结果，实际上就是在两个顺序列表中取交集，原来是一道双指针算法题，复杂度应该是O(n)级别的
例如：
结果集1：5、1、3；
结果集2：4、3、2；
怎么取交集，做算法题都知道要先排序
这也是为什么联合索引要全列匹配，因为不全列匹配，得到的结果不是按主键排序的，而主键可以范围匹配，因为主键自己就是按自己排序的
union合并
又叫并集合并，与intersection合并类似，在条件全都是二级索引列等值查询条件下且匹配联合索引的全部列的情况下，或主键允许范围匹配其他二级索引列全都是等值匹配的条件下，可以进行索引合并，同时更宽容的一点是，条件是交集合并，也可以触发并集合并
场景1：or查询条件下，查询条件均为二级索引列，且全为等值匹配，且联合索引要涉及全部索引列
SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b'AND key_part3 = 'c');这个可以交集合并，但key1换成范围匹配，或联合索引缺列，就无法合并
显然，把联合索引这个整体打开（例如三列都用OR），也不能union合并
场景2：or查询条件下，主键列范围匹配，其他二级索引列全为等值匹配，且联合索引涉及全部索引列
SELECT * FROM single_table WHERE id > 100 OR key1 = 'a'这个也符合条件
场景3：条件符合交集合并
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 ='c' OR (key1 = 'a' AND key3 = 'b');这个语句中OR左边三个AND可以看作整体是交集合并，右边括号括起来了，也是整体，也可以交集合并，因此整体可以并集合并
而并集合并的原理与交集合并类似，都是两个有序的数组，取并集，当然好取了
sort-union合并
这是一种特殊情况，针对二级索引全都是非等值匹配
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'这个场景，key1和key3虽然都不是等值匹配，但是有时引擎乐意为了结果加一步排序
即按key1的二级索引查到结果，再将结果按主键排序一次
再按key3的二级索引查到结果，再将结果按主键排序一次
这样得到的两个数组，进行union，因为多了两次sort，因此这种场景就是sort-union合并
显然，这种场景必须是数据量比较少，如果数据太多，引擎还不如直接回表。同时，没有sort-intersection合并
索引合并的启发
为什么要索引合并，还不是因为匹配到两个索引树，引擎已经在想办法尽量优化查询，减少回表了，既然是这样，开发时应该将往往绑定在一起查询的条件修改为联合索引，而不是两个单独的索引，从而提升性能
ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);索引下推
前面已经了解过，如果是联合索引，必须是左列完全排序，右列才能继续匹配索引，因此在左列使用LIKE匹配字符串的情况下，右列就很难匹配了，比如以下例子：
有一个USER表，联合索引是（name, age）
现在想查询：select * from tuser where name like '张%' and age=10;
因为name like '张%' 是没有对字符串完全排序的，因此age在数据集中就是无序的，其实就是前面看过的联合索引半失效场景2
还记得MySQL是分引擎层和Server层的，这种情况下，查询在引擎层，然后根据查询结果回表，然后所有数据交给server层排序和筛选，即内存操作
而在MySQL5.6+版本，通过索引下推ICP可以减少回表次数
set optimizer_switch="index_condition_pushdown=on";
开启索引下推后，引擎层在根据name like '张%' 查到结果集后，会根据age=10再做一次筛选，然后再去回表。而不是直接全交给server层做。这样可以减少回表次数
评论区