MySQL 8.0发布,你熟悉又陌生的Hash Join?
昨天下午在查资料的时候,无意间点到了MySQL的官网。发现MySQL发布了一个新版本。
Mysql这个数据库有没有人不熟悉?不用的?没有吧。
2019年末,MySQL发布的8.0.18 GA版本,带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持Hash Join。
还是老样子,建议英文好的同学直接看这里: https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
关于MySQL Hash Join的特性介绍:
- 1、对于大数据量的表关联,HJ(Hash Join)速度将明显比NL(Nested Loop)快很多
- 2、在内存中处理
- 3、必要情况下,会使用磁盘空间
- 4、用于内连接,可扩展到外连接、半连接和反连接
- 5、替换查询计划中的Block Nested Loop
- 6、可以通过HINT强制SQL走HJ或者NL
有的同学可能已经懵逼了。什么是Hash Join?什么是NL?HINT又是什么鬼?
第一部分先做一个简单的科普
首先,在多表联合查询的时候,如果我们查看它的执行计划,就会发现里面有多表之间的连接方式。多表之间的连接有三种方式:Nested Loops,Hash Join 和 Sort Merge Join。
肯定有人说,阿里巴巴规范上都说了,并发情况下不能用多表查询。你有多大并发?任何一个系统的后台都会用到多表联合查询。
Hash Join 在Spark 和 Flink的SQL部分进行Join的时候都会被用到,之前我们发过一篇文章:
[Spark SQL Join的三种实现方式]。
Hash Join散列连接是CBO做大数据集连接时的常用方式,而且通常适合大小表之间进行Join。一般来说,使用小表利用连接键(JOIN KEY)在内存中建立散列表,将列数据存储到hash列表中,然后扫描较大的表,同样对JOIN KEY进行HASH后探测散列表,找出与散列表匹配的行。
有的同学又懵逼了。CBO是什么?这里我们就不展开了,简单的说CBO是一种SQL优化方式,它会根据真实的数据情况,评估执行计划,选择代价最小的执行计划。
什么是执行计划?百度去吧...[黑人问号脸]
那什么是Nested Loops?简单的说就是两层循环,用第一张表做Outter Loop,第二张表做Inner Loop,Outter Loop的每一条记录跟Inner Loop的记录作比较,找出符合条件的数据。当然Nested Loops有多种情况。我们举个最简单的例子,伪代码如下:
for (r in R) { for (s in S) { if (r satisfy condition s) { output <r, s>; } } }
什么是Hint?Hint这个英文单词是提示的意思。简单的说,Hint特别像我们在开发代码时候的注释,代码中的注释是提示开发者或者其他人这段代码的意思。那么这个Hint在SQL中会起到特殊的作用,是对数据库的提示,表示希望数据库按照我的提示进行执行。这里就不举例了。
书归正文,Hash Join在新版MySQL中如何使用?
我们直接用官网的例子。
假设我们有三张表如下:
CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT);
有一个简单的表关联查询:
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
我们使用EXPLAIN FORMAT=TREE命令可以看到上面SQL的执行计划:
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON t1.c1=t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1)
我们看到关键词Inner hash join则代表这条SQL使用了Hash Join。
此外,多个表之间使用等值连接的的查询也会进行这种优化。例如以下查询:
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);
通过EXPLAIN FORMAT=TREE命令的输出进行查看,我们同时可以发现非等值连接的条件会在最后变成过滤器。
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) -> JOIN t3 -> ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1) -> Table scan on t3 (cost=0.35 rows=1) -> Hash -> Filter: (t1.c2 < t2.c2) (cost=0.70 rows=1) -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1)
从上面的日志也能看到 如果你的SQL包含多个等值连接,那么MySQL会使用多个Hash Join。
但是,注意啦!如果你的SQL中on条件中不是等值连接,那么不会采用Hash Join。
例如:
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** EXPLAIN: <not executable by iterator executor>
我们EXPLAIN一下看看:
mysql> EXPLAIN -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using join buffer (Block Nested Loop) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using join buffer (Block Nested Loop)
看到了吧,MySQL这时候就会选择Nested Loop。
笛卡尔积查询也同样可以使用HJ:
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> WHERE t1.c2 > 50\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Filter: (t1.c2 > 50) (cost=0.35 rows=1) -> Table scan on t1 (cost=0.35 rows=1)
注意看重点!
默认配置时,MySQL 会尽可能的使用Hash Join。 同时也提供了两种控制是否使用Hash Join的方法。 比如我就是不喜欢HJ,我就喜欢NL的龟速Join,然后项目经理让优化时候再打开HJ查询岂不是美滋滋?
两种方式任选其一:
- 全局设置服务器系统变量hash_join=on
- 在SQL中使用Hint指定 HASH_JOIN 或者 NO_HASH_JOIN
更为牛逼的是,HJ自身不受索引的影响,意思就是即使没有进行索引优化,HJ依然速度很快。
下面是我找了一个网上其他人的测试,展示一下HJ的强大。
首先分别为 t1、t2 和 t3 生成 1000000 条记录:
set join_buffer_size=2097152000; SET @@cte_max_recursion_depth = 99999999; INSERT INTO t1 -- INSERT INTO t2 -- INSERT INTO t3 WITH RECURSIVE t AS ( SELECT 1 AS c1, 1 AS c2 UNION ALL SELECT t.c1 + 1, t.c1 * 2 FROM t WHERE t.c1 < 1000000 ) SELECT * FROM t;
没有索引情况下的 hash join:
mysql> EXPLAIN ANALYZE -> SELECT COUNT(*) -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(0) (actual time=22993.098..22993.099 rows=1 loops=1) -> Inner hash join (t3.c1 = t1.c1) (cost=9952535443663536.00 rows=9952435908880402) (actual time=14489.176..21737.032 rows=1000000 loops=1) -> Table scan on t3 (cost=0.00 rows=998412) (actual time=0.103..3973.892 rows=1000000 loops=1) -> Hash -> Inner hash join (t2.c1 = t1.c1) (cost=99682753413.67 rows=99682653660) (actual time=5663.592..12236.984 rows=1000000 loops=1) -> Table scan on t2 (cost=0.01 rows=998412) (actual time=0.067..3364.105 rows=1000000 loops=1) -> Hash -> Table scan on t1 (cost=100539.40 rows=998412) (actual time=0.133..3395.799 rows=1000000 loops=1) 1 row in set (23.22 sec) mysql> SELECT COUNT(*) -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 = t3.c1); +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (12.98 sec)
实际运行花费了 12.98 秒。这个时候如果使用 block nested loop:
mysql> EXPLAIN FORMAT=TREE -> SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*) -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: <not executable by iterator executor> 1 row in set (0.00 sec) SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*) FROM t1 JOIN t2 ON (t1.c1 = t2.c1) JOIN t3 ON (t2.c1 = t3.c1);
EXPLAIN 显示无法使用 hash join。查询跑了几十分钟也没有出结果,其中一个 CPU 使用率到了 100%;因为一直在执行嵌套循环(1000000 的 3 次方)。
再看有索引时的 block nested loop 方法,增加索引:
mysql> CREATE index idx1 ON t1(c1); Query OK, 0 rows affected (7.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE index idx2 ON t2(c1); Query OK, 0 rows affected (6.77 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE index idx3 ON t3(c1); Query OK, 0 rows affected (7.23 sec) Records: 0 Duplicates: 0 Warnings: 0
查看执行计划并运行相同的查询语句:
mysql> EXPLAIN ANALYZE -> SELECT COUNT(*) -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(0) (actual time=47684.034..47684.035 rows=1 loops=1) -> Nested loop inner join (cost=2295573.22 rows=998412) (actual time=0.116..46363.599 rows=1000000 loops=1) -> Nested loop inner join (cost=1198056.31 rows=998412) (actual time=0.087..25788.696 rows=1000000 loops=1) -> Filter: (t1.c1 is not null) (cost=100539.40 rows=998412) (actual time=0.050..5557.847 rows=1000000 loops=1) -> Index scan on t1 using idx1 (cost=100539.40 rows=998412) (actual time=0.043..3253.769 rows=1000000 loops=1) -> Index lookup on t2 using idx2 (c1=t1.c1) (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000) -> Index lookup on t3 using idx3 (c1=t1.c1) (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000) 1 row in set (47.68 sec) mysql> SELECT COUNT(*) -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1) -> JOIN t3 -> ON (t2.c1 = t3.c1); +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (19.56 sec)
实际运行花费了 19.56 秒。所以在我们这个场景中的测试结果如下:
再增加一个 Oracle 12c 中无索引时 Hash Join 结果:1.282 s。 再增加一个 PostgreSQL 11.5 中无索引时 Hash Join 结果:6.234 s。 再增加一个 SQL Server 2017 中无索引时 Hash Join 结果:5.207 s。
看到 Hash Join的强大了吧?你学到了吗?
声明:本号所有文章除特殊注明,都为原创,公众号读者拥有优先阅读权,未经作者本人允许不得转载,否则追究侵权责任。
关注我的公众号,后台回复【JAVAPDF】获取200页面试题! 5万人关注的大数据成神之路,不来了解一下吗? 5万人关注的大数据成神之路,真的不来了解一下吗? 5万人关注的大数据成神之路,确定真的不来了解一下吗?
欢迎您关注《大数据成神之路》
</not></not></r,>
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Atom 1.43.0 发布,改进各种语法高亮
Atom 1.43.0发布了。Atom 是 GitHub 专门为程序员推出的一个跨平台文本编辑器。具有简洁和直观的图形用户界面,并有很多有趣的特点:支持 CSS、HTML 与 JavaScript 等网页编程语言,支持宏、自动完成分屏功能,集成了文件管理器。 此版本显著变化包括: #20041- 改进 PHP 语法高亮,包括闭包内的三元运算符、ereg 函数和注释 #20122- 改进返回引用和返回类型函数的PHP 语法高亮 #20088- 通过在 @extend 语句中将 '-' 识别为类选择器的一部分,改进 SCSS 语法高亮 #20099- 修复选项卡不是编辑器时的查找和替换崩溃 #19832-修复了阻止由 git worktree 创建的目录被识别为 git 存储库的问题 #20134- 支持网格属性,改善 CSS 语法突出显示 #20156- 新增支持 JavaScript forEach 代码段 #20173- 新增支持 JavaScript 大整型数字高亮 #20196- 更新旧版 TextMate 语法以匹配microsoft/vscode@e6abf47 atom/g...
- 下一篇
MySQL新特性之哈希连接
概述 很长一段时间,MySQL 执行 连接 的唯一算法是 嵌套循环算法 ( nested loop algorithm) 的变体 ,但是 嵌套循环算法 在某些场景下非常低效,也是 MySQL 一直被诟病的一个问题。 随着 MySQL 8.0.18 的发布,MySQL Server 可以使用哈希连接(hash join),这篇文章将会简单介绍下哈希连接如何实现,看看在 MySQL 中它是如何工作的,何时使用它,有什么限制。 哈希连接简介 什么是哈希连接? 哈希连接是一种用于关系型数据库中的连接算法,只能用于有等连接条件的连接中(on a.b = c.b)。它通常比 嵌套循环 算法 更高效(探测端非常非常小除外),尤其是在没有命中索引的情况下。 简单来说,哈希连接算法就是先把一张小表加载到内存哈希表里,然后遍历大表的数据,逐行去哈希表中匹配符合条件的数据,返回到客户端。 (哈希表只是示例,方面理解,实际 hash 的 key 是连接的值,value 是数据行链表) 通常将 哈希连接 分为两个阶段,构建阶段(build phase)和探测阶段(probe phase)。在构建阶段,先选择合适...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
-
Docker使用Oracle官方镜像安装(12C,18C,19C)
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS8编译安装MySQL8.0.19
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- MySQL8.0.19开启GTID主从同步CentOS8
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
推荐阅读
最新文章
- SpringBoot2全家桶,快速入门学习开发网站教程
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- MySQL8.0.19开启GTID主从同步CentOS8
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- SpringBoot2整合Redis,开启缓存,提高访问速度
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果