join流程分类

Index Nested-Loop Join

分析语句select * from t1 straight_join t2 on (t1.a=t2.a);

上述语句中t2的a字段存在索引，则join流程如下

  • 从表 t1 中读入一行数据 R；
  • 从数据行 R 中，取出 a 字段到表 t2 里去查找；
  • 取出表 t2 中满足条件的行(使用索引)，跟 R 组成一行，作为结果集的一部分；
  • 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为Index Nested-Loop Join，简称 NLJ。

Simple Nested-Loop Join

分析语句select * from t1 straight_join t2 on (t1.a=t2.b);

上述语句中t2的b字段不存在索引，则join流程如下

  • 从表 t1 中读入一行数据 R；
  • 从数据行 R 中，取出 a 字段到表 t2 里去查找；
  • 取出表 t2 中满足条件的行(没有索引，全表扫描)，跟 R 组成一行，作为结果集的一部分；
  • 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

这个过程需要遍历t1和t2，扫描的行数是t1和t2的总行数乘积，效率太低。上述流程我们称之为Simple Nested-Loop Join

Block Nested-Loop Join

分析语句select * from t1 straight_join t2 on (t1.a=t2.b);

上述语句中t2的b字段不存在索引，则join流程如下

  • 把表 t1 的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表 t1 放入了内存；
  • 扫描表 t2，把表 t2 中的每一行取出来，跟join_buffer中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此针对表的扫描行数是t1和t2表的行数之和。由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做遍历来判断，总共需要的操作次数依旧是t1和t2的总行数乘积，但后面的部分是在内存中做的判断，因此效率更高。

注意如果放不下表 t1 的所有数据话，则会分段放入join_buffer然后聚集结果返回。

BLJ缺点

  • 可能会多次扫描被驱动表，占用磁盘 IO 资源；
  • 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
  • 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

能不能使用 join 语句？

  • 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  • 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

  • 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
  • 如果是 Block Nested-Loop Join 算法：
    • 在 join_buffer_size 足够大的时候，是一样的；
    • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

当然了，这里需要说明下，什么叫作“小表”。在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join优化

Multi-Range Read

MRR:这个优化的主要目的是尽量使用顺序读盘。

介绍 InnoDB 的索引结构时，提到了"回表"的概念。回表是指，InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据的过程。如果随着 a 的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。因为大多数的数据都是按照主键递增顺序插入得到的，所以可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

MRR流程

  • 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中;
  • 将 read_rnd_buffer 中的 id 进行递增排序；
  • 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

MRR 能够提升性能的核心在于，查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

理解了 MRR 性能提升的原理，我们就能理解 MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。该算法实质通过MRR将相关键值排序后再去批量顺序搜索。

如果要使用 BKA 优化算法的话，需要在执行 SQL 语句之前，先设置set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on

NLJ优化

开启BKA算法使用设置，则 NLJ 算法优化执行的逻辑过程如下：

  • 从驱动表 t1，一行行地取出 a 的值，放到join_buffer
  • 在join_buffer中做排序
  • 批量传给t2搜索相关行做join
  • 如果t1取出的数据一次性放不下，那就分批量取，重复2-3的步骤

BLJ优化

BLJ的缺陷如上文所述，具体如何优化的主要思路还是尽肯能的利用被驱动表的索引

  • 被驱动表针对相关键值做索引
  • 如果被驱动表数据太多，不适合建立索引，则通过构建被驱动表的相关临时表来优化，将数据使用临时表集中后再构建索引，从而达到使用BKA算法的优化功能