MySQL 8.0 新特性之Hash Join

上周在公司做了针对MySQL 8.0新特性相关的分享，提到MySQL 8.0新特性，不得不提到的就是HashJoin，MySQL一直被人诟病没有实现HashJoin，从8.0.18已经带上了这个功能，令人欣喜。有时候在想，MySQL为什么一直不支持HashJoin呢？可能是因为MySQL多用于简单的OLTP场景，并且在互联网应用居多，需求没那么紧急。另一方面在8.0.18之前，MySQL只支持Nest Loop Join算法，MySQL针对这个算法做了若干优化，实现了Block NestLoop Join，Index NestLoop Join等，有了这些优化，在一定程度上能缓解对HashJoin的迫切程度。本文会介绍HashJoin的原理以及在使用和不使用HashJoin的情况下，性能的差异。

在介绍HashJoin之前，先简单介绍下Block Nes tLoop Join算法和Index Nest Loop Join算法，这两种算法我们在查看SQL语句的执行计划的时候，经常会看到；

Block Nes tLoop Join算法采用了批量技术，即一次利用join_buffer_size缓存足够多的记录，每次遍历内表时，每条内表记录与这一批数据进行条件判断，这样就减少了扫描内表的次数，如果内表比较大，间接就缓解了IO的读压力。从算法角度来说，这是一个M*N的复杂度。

Index Nest Loop Join算法如果能对内表的join条件建立索引，那么对于外表的每条记录，无需再进行全表扫描内表，只需要一次Btree-Lookup即可；

HashJoin是基本思想是，将外表数据load到内存，并建立hash表，这样只需要遍历一遍内表，就可以完成join操作，输出匹配的记录。如果数据能全部load到内存当然好，逻辑也简单，一般称这种join为Classic Hash Join。如果数据不能全部load到内存，就需要分批load进内存，然后分批join。

详细的HashJoin的过程可参考MySQL官方博客：https://mysqlserverteam.com/hash-join-in-mysql-8/

从MYSQL 8.0.18开始，MYSQL实现了对于相等条件下的HashJoin，并且，join条件中无法使用任何索引，比如下面的语句：

当然，如果有一个或者多个索引可以适用于单表谓词，hash join也可以使用到。（官方文档原话为：A hash join can also be used when there are one or more indexes that can be used for single-table predicates.）

相对于Blocked Nested Loop Algorithm，简称BNL，hash join性能更高，并且两者的使用场景相同，所以从8.0.20开始，BNL已经被移除。使用hash join替代之。

通常在EXPLAIN的结果里面，在Extra列，会有如下描述：

Extra: Using where; Using join buffer (hash join) 说明使用到了hash join。

虽然hash join适用于等值join，但是MySQL 8.0.20及更高版本中，取消了对等条件的约束，可以全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join。，MySQL就可以使用到hash join来提升速度，比如下面的语句：

SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)  JOIN t3 ON (t2.c1 = t3.c1); 

该语句包含非等值的join条件

MySQL 5.7和MySQL 8.0版本性能对比

下面针对MySQL 5.7和MySQL 8.0版本在相同数据量，相同的Join sql语句，我们来对比一下在使用HashJoin和不使用HashJoin的性能差异

测试关联的两张表的数据量都在1000万左右

首选，我们来看一下执行计划的差异：

MySQL 5.7版本的执行计划以及查询时间如下：

MySQL 8.0版本不使用HashJoin的时候执行计划以及查询时间如下：

MySQL 8.0版本使用HashJoin的时候执行计划以及查询时间如下：

测试执行结果如下：

从测试结果可以看到，MySQL 8.0 在使用HashJoin的情况下相同SQL语句，查询时间不到1min的时间，对比其他情况，执行效率非常的快；

小结

MySQL8.0以后，Server层做了大量的优化。HashJoin的支持使得MySQL优化器有更多选择，SQL的执行路径也能做到更优，尤其是对于等值join的场景。虽然MySQL之前对于Join做过若干优化，比如NBLJ，INLJ等，但这些代替不了HashJoin的作用。一个好用的数据库就应该具备丰富的基础能力，利用优化器分析，结合具体的业务查询场景，更好的应用HashJoin，然后拿出对应的基础能力以最高效的方式响应请求，版本升级到MySQL 8.0势在必行呀！