聊聊BLOCK NESTED LOOP JOIN

昨天谈到MYSQL数据库与PG区别的时候重点聊了一些关于HASH JOIN的问题，复杂的多表关联查询的性能问题一直也是Mysql的一个痛点。Mysql 8.0.18以后才开始支持并不完整的hash join，说是不完整，是因为Mysql 8.0目前只实现了一部分其他数据库中Hash join的功能，而不是全部，其Hash join存在一些比其他数据库更多的限制，比如说最要命的是不能使用索引。

在8.0.18以前的MYSQL版本中无法使用HASH JOIN，在使用HASH JOIN的时候不能使用索引。这让很多复杂的大表关联的SQL语句十分受伤。那么，在没有Hash是Join的情况下，Mysql如何解决这个问题的呢？其实Mysql 8.0.18的Hash Join主要目的是用于替换一种之前解决大数据量的JOIN的方法Block Nested Loop Join的。

可能很多朋友都没有关注过表连接是如何实现的，从MYSQL 5.5开始，Mysql的用户一直使用nested loop作为表连接的主要方式。我们把NESTED LOOP的算法用一组简单的中文写出来：

第一步：查找外表O，找到符合条件的所有记录Ro;

第二步：对于Ro中的每一条记录，做循环操作（NESTED LOOP）；

第三步：根据Ro的每一条记录的关联键值，扫描内表I，找到符合条件的记录；

上面的操作省略了一些步骤，不过足以说明NESTED LOOP的关键算法。大家可能可以看出来，如果外表O中符合扫描条件的记录比较少，而且内表I与O的关联条件上有比较好的索引，那么这种连接方式是十分高效的。

不过有些情况下，这种连接成本十分高。比如第一种情况，如果内表I比较大，而且关联条件上没有索引，那么这种连接方式要多次全表扫描大表I，成本就十分高了。另外一种情况下，如果外表O上满足条件的记录数特别多，比如有1万行，那么我们就要做一万次循环，才能完成JOIN操作。如果上面两种情况同时出现，那么这条SQL就是灾难了。

Oracle DBA可能也遇到过这种情况，某条SQL平时很快，突然变得十分慢了，这种情况很多时候就与NESTED LOOP有关。Oracle在RBO时代的时候，也只能使用NESTED LOOP来处理这样的表连接。自从有了CBO，情况就不同了。CBO在处理上面两种情况的时候，不会再使用NESTED LOOP，而会选择另外一种连接方式-hash join。Hash join先会找一张结果集较小的表，把这张表的连接字段首先生成一张HASH表，然后再扫描产生较大结果集的表，对符合扫描条件的每条记录使用HASH函数探测HASH表，判断是否满足连接条件。这样对于这张大的外表，只需要扫描一遍就可以完成连接了。

当Mysql没有HASH JOIN的时候，怎么来优化这种情况呢？从Mysql 5.7开始，出现了一种改良版的Nested Loop Join算法，这就是Block Nested Loop Join。这个算法说起来也十分简单，首先还是要选择一张较小的外表O，扫描O的记录，批量取出连接关键字的值，放入一个内存缓冲区中去，当缓冲区写满后，将这一批键值交给扫描引擎，去扫描内表I,从而完成连接操作。

如果在I上没有合适的索引，那么如果O上的符合条件的记录有Bo个缓冲区，那么I表需要被扫描Bo次，如果每个BUFFER可以存储10个键值，那么扫描I表的次数可以减少为1/10，从而大大减少扫描的次数。

上图是在网上找到的一个关于Block Nested Loop Join的算法描述。实际上BNLJ要复杂的多。比如说内表I特别大，同时表的关联字段上有索引，那么Mysql可以通过索引来扫描，这时候要使用一个算法：Batched Key Access Algorithms（BKA）。通俗来说，就是批量键值访问。BKA会将需要扫描的一组键值组织成一个BUFFER，然后一次性交给Multi-Range Read (MRR)扫描引擎去做批量键值扫描，从而降低索引扫描的成本。不过在成本估算的时候，MYSQL仍然给这种MRR扫描较为悲观的估算，所以在缺省的配置下，MYSQL，并不能很好的估算出BNLJ的成本，从而选择使用BNLJ。如果我们要使用BKA，那么Mysql的官方文档上建议如下设置：

在Mysql 5.7中,mrr和mrr_cost_based缺省都是on,因此为了在BNLJ中使用BKA，需要关闭mrr_cost_based。batched_key_access缺省是Off也必须打开。在Mysql里，BNLJ不仅仅可以支持内连接，也可以支持外连接和半连接，使用场景还是覆盖比较全面的。

实际上在Mysql已经推出Hash Join的今天，通过BNLJ去优化SQL的性能，在某些场景下还是有价值的。除了一些无法使用Hash Join的场景，有一些可以使用Hash Join的场景中，BNLJ也有可能有更好的效果。比如内表十分巨大，具有较好的索引选择性，通过索引返回的数据集的比例较小，而外表的总数据量在数千，此时使用BNLJ的效果可能会好于Hash Join。

综上所述，BNLJ虽然可以解决一部分NESTE LOOP JOIN成本过高的问题，不过仍然比HASH JOIN的开销要大得多。在大多数情况下，效率也要低于HASH JOIN。不过利用BNLJ仍然在很多场合下可以有效的提升MYSQL多表连接的效率，对于在MYSQL中优化SQL性能还是十分有用的。