MySQL单表的访问方法和连接的原理

1.单表的访问方法概念

MySQL的SQL查询语句对表的访问或者是查询可以有多种方式，每种方式执行的成本、花费的时间大不一样，MySQL定义了很多种表的访问方式或者叫访问类型来。

1.1 const 访问类型

通过主键索引或者唯一二级索引和常数值进行查询的时候因为目标结果是唯一确定的，MySQL认为这种查询效率十分高，成本消耗几乎忽略不计，这种访问方式定义为常数级访问const。

1.2 ref 访问类型

通过二级索引和常数值进行查询的时候，扫描区间是一个单点区间，MySQL讲这种查询方式定义为ref访问类型。

1.3 ref_or_null 访问类型

这种类型是ref的一种特殊存在，区别是它多了一些扫描Null值的扫描区间。

1.4 range 访问类型

如果查询条件对应的不是一个单点区间，而是一个范围区间，那么MySQL定义这种访问方式是range范围访问类型。

1.5 index 访问类型

通过扫码全部二级索引树获取数据的方式定义为index访问类型。索引树里包含了要查询的列，通过遍历索引即可找到结果。

1.6 all 访问类型

遍历整颗主键索引B+Tree。

2.索引合并

MySQL绝大部分情况下一个查询只能用到一个索引，但是某些特殊场景MySQL做了优化，可以利用多个索引来提升查询效率。

2.1 Intersection 索引合并/交集合并

SELECT * FROM user WHERE a = 'a' AND b = 'b'，如果a和b列都是二级索引，在一般情况下MySQL只会选择其中之一来进行查询。但是如果a和b这俩二级索引里面所对应的主键ID是有序的，那么MySQL可以通过交集合并的方式利用a索引匹配到结果集，然后利用b索引匹配到结果集，然后求交集，就是最后的正确结果。
1.之所以要求每个二级索引对应的主键ID也必须有序，是因为有序集合的交集运算更快。
2.还有一个原因就是如果主键ID是有序的，那么回表的时候将是顺序IO。

2.2 Union 索引合并

SELECT * FROM user WHERE a = 'a' OR b = 'b'，和交集合并类似，如果两个二级索引所对应的主键ID是有序的，那么这种SQL就会优化为Union索引合并，通过两个索引的并集去重得到最终的结果。

2.3 Sort-Union 索引合并

SELECT * FROM user WHERE a > 'a' OR b < 'b'，这种场景就不能使用Union索引合并，但是可以通过对每个二级索引匹配后的主键ID排序，最后来合并去重两个结果集。
相当于比Union多了一步对主键ID的排序工作。

2.4 为什么MySQL没有Sort-Intersection

Sort-Union索引合并本来适用于结果集比较少的场景才会优化，而Intersection索引合并本身适用于结果集较大的优化，如果推出了Sort-Intersection，那么排序就会带来影响。
MariaDB实现了Sort-Intersection。

3.连接的原理

3.1 驱动表和被驱动表

     - 第一个需要查询的表称之为驱动表，驱动表的每一条记录都要去被驱动表进行查询匹配。
     - 每获取一条驱动表记录，都要立刻从被驱动表中寻找匹配的记录。
     - Left Join：选取左边的表为驱动表。
     - Right Join：选取右边的表为驱动表。

3.2 嵌套循环连接

对于驱动表只访问一次，被驱动表访问多次的情况称之为嵌套循环连接。这是最笨拙的一种方式，每遍历到一条驱动表记录，就会去被驱动表检索，往复循环。

3.3 索引加快连接速度

被驱动表由于要每次都要遍历查询，如果被驱动表的连接条件时索引列，则可以加快检索的速度。

3.4 基于块的嵌套循环连接

由于普通的嵌套循环连接对被驱动表要大量的IO操作，为了减少这样的操作，MySQL设计了一个叫Join Buffer的连接缓冲区，这个缓冲区的作用如下：
在连接开始的时候，MySQL申请了一块固定大小的Buffer空间在内存中，先将驱动表的多条记录加载到内存，然后开始遍历被驱动表，将被驱动表的每一条记录都和Buffer里面的驱动表记录进行匹配，减少了磁盘IO操作，整个过程咋内存中进行。
Join Buffer的大小可以通过变量join_buffer_size设置，默认是256KB，最小128字节。