MySQL 8.0发布，你熟悉又陌生的Hash Join？-低调大师

MySQL 8.0发布，你熟悉又陌生的Hash Join？

2020-01-15 557

昨天下午在查资料的时候，无意间点到了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&gt; EXPLAIN FORMAT=TREE
    -&gt; SELECT * 
    -&gt;     FROM t1 
    -&gt;     JOIN t2 
    -&gt;         ON t1.c1=t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
    -&gt; Table scan on t2  (cost=0.35 rows=1)
    -&gt; Hash
        -&gt; 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 &lt; t2.c2)
    JOIN t3 
        ON (t2.c1 = t3.c1);

通过EXPLAIN FORMAT=TREE命令的输出进行查看，我们同时可以发现非等值连接的条件会在最后变成过滤器。


mysql&gt; EXPLAIN FORMAT=TREE
    -&gt; SELECT * 
    -&gt;     FROM t1
    -&gt;     JOIN t2 
    -&gt;         ON (t1.c1 = t2.c1 AND t1.c2 &lt; t2.c2)
    -&gt;     JOIN t3 
    -&gt;         ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
    -&gt; Table scan on t3  (cost=0.35 rows=1)
    -&gt; Hash
        -&gt; Filter: (t1.c2 &lt; t2.c2)  (cost=0.70 rows=1)
            -&gt; Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -&gt; Table scan on t2  (cost=0.35 rows=1)
                -&gt; Hash
                    -&gt; Table scan on t1  (cost=0.35 rows=1)

从上面的日志也能看到如果你的SQL包含多个等值连接，那么MySQL会使用多个Hash Join。

但是，注意啦！如果你的SQL中on条件中不是等值连接，那么不会采用Hash Join。

例如：


mysql&gt; EXPLAIN FORMAT=TREE
    -&gt;     SELECT * 
    -&gt;         FROM t1
    -&gt;         JOIN t2 
    -&gt;             ON (t1.c1 = t2.c1)
    -&gt;         JOIN t3 
    -&gt;             ON (t2.c1 &lt; t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>

我们EXPLAIN一下看看：


mysql&gt; EXPLAIN
    -&gt;     SELECT * 
    -&gt;         FROM t1
    -&gt;         JOIN t2 
    -&gt;             ON (t1.c1 = t2.c1)
    -&gt;         JOIN t3 
    -&gt;             ON (t2.c1 &lt; 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&gt; EXPLAIN FORMAT=TREE
    -&gt; SELECT *
    -&gt;     FROM t1
    -&gt;     JOIN t2
    -&gt;     WHERE t1.c2 &gt; 50\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Inner hash join  (cost=0.70 rows=1)
    -&gt; Table scan on t2  (cost=0.35 rows=1)
    -&gt; Hash
        -&gt; Filter: (t1.c2 &gt; 50)  (cost=0.35 rows=1)
            -&gt; 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 &lt; 1000000
)
SELECT *
  FROM t;

没有索引情况下的 hash join：


mysql&gt; EXPLAIN ANALYZE
    -&gt; SELECT COUNT(*)
    -&gt;   FROM t1
    -&gt;   JOIN t2 
    -&gt;     ON (t1.c1 = t2.c1)
    -&gt;   JOIN t3 
    -&gt;     ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Aggregate: count(0)  (actual time=22993.098..22993.099 rows=1 loops=1)
    -&gt; Inner hash join (t3.c1 = t1.c1)  (cost=9952535443663536.00 rows=9952435908880402) (actual time=14489.176..21737.032 rows=1000000 loops=1)
        -&gt; Table scan on t3  (cost=0.00 rows=998412) (actual time=0.103..3973.892 rows=1000000 loops=1)
        -&gt; Hash
            -&gt; Inner hash join (t2.c1 = t1.c1)  (cost=99682753413.67 rows=99682653660) (actual time=5663.592..12236.984 rows=1000000 loops=1)
                -&gt; Table scan on t2  (cost=0.01 rows=998412) (actual time=0.067..3364.105 rows=1000000 loops=1)
                -&gt; Hash
                    -&gt; 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&gt; SELECT COUNT(*)
    -&gt;   FROM t1
    -&gt;   JOIN t2 
    -&gt;     ON (t1.c1 = t2.c1)
    -&gt;   JOIN t3 
    -&gt;     ON (t2.c1 = t3.c1);
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (12.98 sec)

实际运行花费了 12.98 秒。这个时候如果使用 block nested loop：


mysql&gt; EXPLAIN FORMAT=TREE
    -&gt; SELECT /*+  NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
    -&gt;   FROM t1
    -&gt;   JOIN t2 
    -&gt;     ON (t1.c1 = t2.c1)
    -&gt;   JOIN t3 
    -&gt;     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&gt; CREATE index idx1 ON t1(c1);
Query OK, 0 rows affected (7.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql&gt; CREATE index idx2 ON t2(c1);
Query OK, 0 rows affected (6.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql&gt; CREATE index idx3 ON t3(c1);
Query OK, 0 rows affected (7.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看执行计划并运行相同的查询语句：


mysql&gt; EXPLAIN ANALYZE
    -&gt; SELECT COUNT(*)
    -&gt;   FROM t1
    -&gt;   JOIN t2 
    -&gt;     ON (t1.c1 = t2.c1)
    -&gt;   JOIN t3 
    -&gt;     ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -&gt; Aggregate: count(0)  (actual time=47684.034..47684.035 rows=1 loops=1)
    -&gt; Nested loop inner join  (cost=2295573.22 rows=998412) (actual time=0.116..46363.599 rows=1000000 loops=1)
        -&gt; Nested loop inner join  (cost=1198056.31 rows=998412) (actual time=0.087..25788.696 rows=1000000 loops=1)
            -&gt; Filter: (t1.c1 is not null)  (cost=100539.40 rows=998412) (actual time=0.050..5557.847 rows=1000000 loops=1)
                -&gt; Index scan on t1 using idx1  (cost=100539.40 rows=998412) (actual time=0.043..3253.769 rows=1000000 loops=1)
            -&gt; Index lookup on t2 using idx2 (c1=t1.c1)  (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000)
        -&gt; 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&gt; SELECT COUNT(*)
    -&gt;   FROM t1
    -&gt;   JOIN t2 
    -&gt;     ON (t1.c1 = t2.c1)
    -&gt;   JOIN t3 
    -&gt;     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,>

微信关注我们

原文链接：https://my.oschina.net/wangzhiwubigdata/blog/3158788

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

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...

2020-01-16

742

概述很长一段时间，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）。在构建阶段，先选择合适...

2020-01-16

537

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。