mysql - 单表访问方法以及连接原理

10 单表访问方法

10.1 访问方法

1. const：通过 主键列或者唯一二级索引的等值判断 定位一条记录（一次回表）

   • eq_ref 是连表查询中通过主键或者唯一二级索引的等值判断定位一条记录

2. ref：通过普通二级索引的等值判断，形成单点扫描区间（区间大小=回表次数）

   • 唯一二级索引和普通二级索引都不会限制索引列为null的个数，null值索引会被放到索引最左边，所以is null条件的搜索最多能达到ref访问方法，不能使用const访问方法。

3. ref_or_null：索引列满足 = or is null条件，比ref多了值为null的二级索引记录（区间大小+null个数=回表次数）

4. range：使用索引执行查询，形成若干个单点扫描区间或者范围扫描区间（若干个区间大小之和=回表次数）

5. index：情形一 使用索引但不满足最左匹配原则 且搜索字段为索引列（不需要回表）-- 扫描二级索引记录；情形二 全表扫描添加order by 主键 – 直接返回聚簇索引记录

6. all：全表扫描

7. index_merge：索引合并

10.2 对回表的优化

MRR（Disk-sweep Multi-Range Read，多范围读取），取到主键后先不进行回表操作，存储在read_rnd_buffer中，等取到所有id或者read_rnd_buffer满后，对主键id进行排序，再回表取完整记录。

10.3 索引合并

概念：使用多个索引完成一次查询

1. intersection索引合并：对从不同索引中扫描到的记录的id值 取交集，只对这部分id进行回表操作，要求从二级索引中取到的记录按照id值排序。

   • 过程：同时扫描两个索引，边扫描边对比两个索引中取出记录的id值，相等回表，不等丢弃id值小的记录，继续扫描丢弃记录的索引

   • 聚簇索引由于记录是按id值排序的，所以聚簇索引的范围扫描区间 and 二级索引的等值扫描区间也能用intersection索引合并。

2. union索引合并：对不同索引中扫描到的记录id值去重后取并集，对这部分id进行回表操作，要求从二级索引中取到的记录按照id值排序。

   • 聚簇索引由于记录是按id值排序的，所以聚簇索引的范围扫描区间 or 二级索引的等值扫描区间也能用intersection索引合并。

3. sort-union索引合并：对从两个二级索引中取到的记录分别进行排序，再去重取并集。

   • 为什么没有sort-intersection？需要记录大量二级索引记录，代价较大。union索引合并本身就需要记录二级索引记录，只是多了两个排序操作。

11 连接原理

11.1 连接简介

1. 内连接：取交集

2. 外连接：在左连接、右连接中，当被驱动表中未找到满足on后连接条件的记录时，驱动表中的记录仍会记录到结果集中，对应的被驱动表记录用null填充

   • 左连接
   • 右连接

3. on + 连接条件，where + 过滤条件：on和where只在外连接是有区别

11.2 连接原理

1. 嵌套循环连接算法：在驱动表中每查询到一条符合条件的记录，就扫描一遍被驱动表，找到符合条件的记录，然后将组合后的记录返回给客户端，再以次循环，直至扫描完毕。驱动表只扫描一次，被驱动表扫描次数取决于驱动表中符合查询条件的记录数。

2. 使用索引加快连接速度：对被驱动表建索引，减少全表扫描速度。

3. 基于块的嵌套循环查询：基于可能出现的表过大的情况，为避免内存中同时放不下驱动表和被驱动表，需要在扫描时将俩表频繁读入到内存、移出到磁盘，造成I/O代价，设立 Join Buffer 缓冲区，用于记录若干条驱动表扫描结果（只包含查询列表列和查询条件列）。