专栏首页SEian.G学习记录MySQL 8.0 新特性之Hash Join

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的时候执行计划以及查询时间如下:

测试执行结果如下:

有索引

无索引

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势在必行呀!

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEianG

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-09-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 8.0之hash join

    首先对于熟悉Oracle 的DBA 来说,hash join并不陌生,尤其涉及到多个表join时 执行计划出现 hash join ,一般来说hash join...

    用户1278550
  • MySQL 8.0 新特性:引人注目的哈希连接(Hash Join)

    blog.csdn.net/horses/article/details/102690076

    肉眼品世界
  • Python第十二章-多进程和多线程02-多线程

    MySQL被Sun收购后,搞了个过渡的6.0版本,没多久就下线了(有一次居然听说有人在线上用6.0版本,我惊得下巴都掉了)。被Oracle收购后,终于迎来了像样...

    不会飞的小鸟
  • Oracle放大招:MySQL 即将支持 Hash Join

    在刚刚OOW19会上的《python and mysql 8.0 document store》topic中,终于看到了MySQL即将在8.0.18中支持has...

    数据和云
  • MySQL 8.0与MariaDB 10.4,谁更易于填坑补锅?

    贺春旸,凡普金科DBA团队负责人,《MySQL管理之道:性能调优、高可用与监控》第一、二版作者,曾任职于中国移动飞信、安卓机锋网。致力于MariaDB、Mong...

    jeanron100
  • MySQL 的 join 功能弱爆了?

    关于MySQL 的 join,大家一定了解过很多它的“轶事趣闻”,比如两表 join 要小表驱动大表,阿里开发者规范禁止三张表以上的 join 操作,MySQL...

    程序员历小冰
  • MySQL 的 join 功能弱爆了?

    关于MySQL 的 join,大家一定了解过很多它的“轶事趣闻”,比如两表 join 要小表驱动大表,阿里开发者规范禁止三张表以上的 join 操作,MySQL...

    程序员历小冰
  • MYSQL What's new in 优化和执行 来自旧金山的问候

    以下内容采集自 2019年9月19日 San Francisco Oracle open 大会内容。主题 What’s New in MySQL Optim...

    AustinDatabases
  • Mysql 8.0 新增特性

    1. 数据字典 新增了事务型的数据字典,用来存储数据库对象信息 之前,字典数据是存储在元数据文件和非事务型表中的 2. 账号权限管理 添加了对 “角色” 的支持...

    dys
  • MySQL 8.0.20 正式发行(GA)

    MySQL 的最新版本 8.0.20 正式发行。与之前 8.0 的系列版本一样,这次的发行版,除了包含缺陷修复,也同样包括新功能。让我们快速浏览一下。

    爱可生开源社区
  • 如果要升级到MySQL 8.0,可以分几个阶段走

    最近在推一些业务迈入MySQL 8.0,对很多同学来说,好像差别不大,对于我们来说却是一个质的变化,光看版本就能够感受出来,5.7-> 8.0,所幸我们3年前...

    jeanron100
  • MySQL8.0.20 正式发行(GA)

    MySQL的最新版本8.0.20正式发行。与之前8.0的系列版本一样,这次的发行版,除了包含缺陷修复,也同样包扩新功能。让我们快速浏览一下。

    田帅萌
  • MySQL 8.0复制新特性

    截止2017年8月,MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使用常规复...

    wubx
  • MySQL 8.0复制新特性

    截止目前(2017年8月),MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使...

    wubx
  • MySQL 8.0新特性:InnoDB ReplicaSet

    InnoDB ReplicaSet在 MySQL 8.0.19 版本之后开始支持;本文将针对 InnoDB ReplicaSet这一新特性做一些测试,包括环境部...

    SEian.G
  • 新特性解读 | mysql 8.0 memcached api 新特性

    资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构...

    爱可生开源社区
  • MySQL和PostgreSQL在多表连接算法上的差异

    我们知道mysql没有hash join,也没有merge join,所以在连接的时候只有一种算法nest loop join,nl join使用驱动表的结果集...

    数据和云
  • MySQL5.7升级到8.0过程详解

    不知不觉,MySQL8.0已经发布好多个GA小版本了。目前互联网上也有很多关于MySQL8.0的内容了,MySQL8.0版本基本已到稳定期,相信很多小伙伴已经在...

    MySQL技术
  • MySQL 8.0 新特性之统计直方图

    原文链接:https://mysqlserverteam.com/histogram-statistics-in-mysql/

    沃趣科技

扫码关注云+社区

领取腾讯云代金券