• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL 8.0 新特性之Hash Join

MySQL SEian.G 3年前 (2021-08-31) 1851次浏览 已收录 0个评论
文章目录[隐藏]

上周在公司做了针对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 8.0 新特性之Hash Join

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

MySQL 8.0 新特性之Hash Join

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

MySQL 8.0 新特性之Hash Join

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

MySQL 8.0 新特性之Hash Join

测试执行结果如下:

有索引 无索引
MySQL5.7 6 min 26.00 s 3小时都未返回结果
MySQL8.0 2 min 42.11 s 50.28s

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

小结

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


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 8.0 新特性之Hash Join
喜欢 (5)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址