Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化

一丶单表访问方法

mysql执行查询语句的方法叫做访问方法，同一语句使用不同的访问方法执行，查询结果都是一样的，但是不同的查询方法效率差距很大，mysql优化器会选择成本最低的访问方法，理解访问方法对我们理解索引有益处

1.const

查询可以通过主键或者唯一索引与常数进行等值比较来定位一条记录，这种访问方法被定位为const，如果唯一索引存在多列，那么需要多列都进行等值比较。（唯一索引不限制null元素的个数，所以is null并不会使用const访问方法）

2.ref

搜索条件为二级索引与常数进行等值比较的，形成的扫描区间为单点扫描区间（key='a' key是二级索引，需要扫描的区间是[a,a],这称为单点扫描区间）只需要定位到满足条件的第一条记录，然后沿着B+树叶子节点的指针向右查找直到不满足条件即可（也许需要回表，并不是将所有满足的数据从二级索引上拿到主键然后一起回表，而是每获取一条便立即回表）。这种访问方法称为ref.

同样二级索引允许存储null值，且不限制个数（唯一二级索引也不限制）但是为null的值放在B+树的最左侧，查找的流程任然一致，即使是key is null 也必须要进行单点扫描、

如果二级索引存在多列，并不需要多列都进行等值比较，但是要求最左连续的列进行等值比较（比如联合索引a,b,c,a=1 and c=2 可以使用ref，找到a=1向右并且索引下推过滤掉不满足c=2的记录，减少回表，但是如果是b=1 and c=2 这时候是无法使用ref的，因为联合索引是先按照a排序，再依次b，c。）

3.ref_or_null

查询条件是二级索引等值查询 or二级索引 is null,可以使用ref_or_null,这其实涉及到两个扫描区间[null,null],[等值,等值]执行流程和ref一样。

4.range

使用索引执行查询时，对应的扫描区间是若干单点区间，或者范围扫描区间，那么可以使用range(全表扫描不能算作range，单个单点扫描区间是ref而不是range）

5.index

我们知道二级索引需要存储索引列和主键，聚簇索引需要存储所有列和主键（以及隐藏列）所以二级索引大小远小于聚簇索引，且如果一个查询不需要进行回表，那么将直接利用二级索引进行全表扫描（索引小，意味着IO次数小）这种访问方法叫index

比如select 主键 from table where 无法走索引的条件，那么这时候不如扫描二级索引，其B+树叶子节点保存的是主键和索引列，每一页可以存放更多数据，减少IO次数，其中的主键也可以覆盖需要查询的主键

6.all

直接扫描所有的聚簇索引记录

二丶多范围读取MRR

上面我们说到回表，是每从二级索引中获取一条符合的数据都会到聚簇索引根据主键进行回表，但是二级索引中的主键是无序的，这导致每次执行回表操作都是随机IO，导致性能开销巨大，mysql为了优化这种随机IO，使用了MRR多范围读取，即先读取一部分二级索引，然后将主键值排序后再统一执行回标，将随机IO优化为顺序IO。

三丶索引合并

通常情况下，mysql只会为单个索引生成扫描区间，但是存在特殊情况，mysql可以为多个索引生成扫描区间，这种多个索引生成扫描区间来完成依次查询的方法称为索引合并

1.交集索引合并

select xxx ,xxx from table where key1=1 and key2=2 （key1和key2都是二级索引）mysql可以选择使用key1，也可以使用key2索引，获取符合的主键然后回表并过滤不符合的记录。也可以分别从key1索引中获取满足key1=1，从key2索引中获取key2=2的主键值，再获取二者交集最后进行回表，这样可与减少不必要的回表操作。

使用交集索引合并的话，要求通过二级索引查到的主键本身就是有序的，这样获取交集效率更高，并且减少了随机IO。

• 如果具有a,b两个普通索引,执行查询select * from table where a>1 and b=2 那么是无法进行交集索引合并的,因为a>1得到的主键并不是有序的，
• 同样联合索引q,w,e 普通索引r 执行select * from table where q=1 and w=2 and r=3也不可以使用交集索引合并，因为联合索引是依次根据q,w,e排序的，满足q=1 and w=2的数据主键并不是有序的。
• 普通索引a，主键为id，select * from a=1 and id>100这样的查询理论上是主键有序可与使用的，但是mysql会找到满足a=1且id>100的第一条记录，然后向右直到不符合条件的数据出现，这种情况也不需要使用交集索引合并

2.并集索引合并

select