MySQL 8.0 新特性之哈希连接（Hash Join）

mysql 开发组于 2019 年 10 月 14 日 正式发布了 mysql 8.0.18 ga 版本，带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持 hash join 方式了。我们先来看看官方的描述：

mysql 实现了用于内连接查询的 hash join 方式。例如，从 mysql 8.0.18 开始以下查询可以使用 hash join 进行连接查询：

select * 
  from t1 
  join t2 
    on t1.c1=t2.c1;

hash join 不需要索引的支持。大多数情况下，hash join 比之前的 block nested-loop 算法在没有索引时的等值连接更加高效。使用以下语句创建三张测试表：

create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);
create table t3 (c1 int, c2 int);

使用explain format=tree命令可以看到执行计划中的 hash join，例如：

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)

必须使用 explain 命令的 format=tree 选项才能看到节点中的 hash join。另外，explain analyze命令也可以显示 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)

从以上输出同样可以看出，包含多个等值连接条件的查询也可以（会）使用多个 hash join 连接。

但是，如果任何连接语句（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>

此时，将会采用性能更慢的 block nested loop 连接算法。这与 mysql 8.0.18 之前版本中没有索引时的情况一样：

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)

hash join 连接同样适用于不指定查询条件时的笛卡尔积（cartesian product），例如：

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 的方法：

在全局或者会话级别设置服务器系统变量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 选项。默认为 hash_join=on。

在语句级别为特定的连接指定优化器提示 hash_join 或者 no_hash_join。

可以通过系统变量 join_buffer_size 控制 hash join 允许使用的内存数量；hash join 不会使用超过该变量设置的内存数量。如果 hash join 所需的内存超过该阈值，mysql 将会在磁盘中执行操作。需要注意的是，如果 hash join 无法在内存中完成，并且打开的文件数量超过系统变量 open_files_limit 的值，连接操作可能会失败。为了解决这个问题，可以使用以下方法之一：

增加 join_buffer_size 的值，确保 hash join 可以在内存中完成。

增加 open_files_limit 的值。

接下来他们比较一下 hash join 和 block nested loop 的性能，首先分别为 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 秒。所以在我们这个场景中的测试结果如下：

hash join（无索引）	block nested loop（无索引）	block nested loop（有索引）
12.98 s	未返回	19.56 s

再增加一个 oracle 12c 中无索引时 hash join 结果：1.282 s。

再增加一个 postgresql 11.5 中无索引时 hash join 结果：6.234 s。

再增加一个 sql 2017 中无索引时 hash join 结果：5.207 s。

总结

以上所述是小编给大家介绍的mysql 8.0 新特性之哈希连接（hash join）,希望对大家有所帮助