首页 / MYSQL / MySQL 8.0发布，你熟悉又陌生的Hash Join？

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 8.0发布，你熟悉又陌生的Hash Join？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10171字，纯文字阅读大概需要15分钟。

内容图文

昨天下午在查资料的时候，无意间点到了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 秒。所以在我们这个场景中的测试结果如下：

MySQL 8.0发布，你熟悉又陌生的Hash Join？ - 文章图片

再增加一个 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万人关注的大数据成神之路，确定真的不来了解一下吗？

欢迎您关注《大数据成神之路》

MySQL 8.0发布，你熟悉又陌生的Hash Join？ - 文章图片

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

标签：伪代码   半连接   支持   就是   aggregate   内存   显示   can   time   

本文系统来源：https://www.cnblogs.com/importbigdata/p/12199161.html

内容总结

以上是互联网集市为您收集整理的MySQL 8.0发布，你熟悉又陌生的Hash Join？全部内容，希望文章能够帮你解决MySQL 8.0发布，你熟悉又陌生的Hash Join？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/518328.html

来源：【匿名】