理解 MySQL 8 的 Hash Joins

• 原文标题：Understanding Hash Joins in MySQL 8
• 原文链接： https://www.percona.com/blog/2019/10/30/understanding-hash-joins-in-mysql-8/
• 作者：Tibor Korocz
• 译者：2014BDuck
• 翻译时间：2019-12-23
• 译注：dalao 们多来博客走走~

在 MySQL 8.0.18 中有个新功能叫 Hash Joins。我打算研究一下它是如何运作的和在什么场景下它能够帮到我们。你可以在这里了解它的底层原理。

更上层的解释：如果使用 join 查询，它会基于其中一个表在内存构建一个哈希表，然后一行一行读另一个表，计算其哈希值到内存哈希表中进行查找。

很好，但性能上带给我们什么好处呢？

首先，它只会在没有索引的字段上生效，所以它是个实时的表扫描。通常我们不推荐在没有索引的列上 join 查询，因为这很慢。这种情况下 Hash Joins 就有它的优势，因为它用的是内存哈希表而不是嵌套循环（ Nested Loop ）。

让我们来做些测试。首先创建如下表：

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

我向两个表都插入了 131072 行随机数据。

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 131072   |
+----------+

测试 1 - Hash Joins

基于没有索引的表 c2 执行 Join 查询。

mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=1728488704)
-> Table scan on t2 (cost=0.01 rows=131472)
-> Hash
-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

我们使用explain format=tree来查看 Hash Join 是否生效，默认情况下(explain)会误导说这会是个嵌套循环。我已经提了bug report，在工单中你可以看到开发者回复：

解决方法就是不要用传统的EXPLAIN

因此在旧的 explain 中这不会被修复，我们要使用新的查询方式。

回到语句上，我们可以看到它使用 Hash Join 了，但性能有多快？

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (0.73 sec)

17000000 多行数据，0.73 秒。看起来很不错。

测试 2 - 非 Hash Joins

我们现在用优化器的开关或 hint 关掉 Hash Join。

mysql> select /*+ NO_HASH_JOIN (t1,t2) */ count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (13 min 36.36 sec)

同样的查询使用了超过 13 分钟。非常大的差距，可以看到 Hash Join 性能提升明显。

测试 3 - 索引 Join

让我们来创建索引，看看基于索引的 join 速度如何。

create index idx_c2 on t1(c2);
create index idx_c2 on t2(c2);

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (2.63 sec)

2.6 秒。在这个测试用例中 Hash Join 比基于索引的 Join 还要快。

然而，我可以在索引可用的情况下，通过ignore index强制优化器使用 Hash Join：

mysql> explain format=tree select count(*) from t1 ignore index (idx_c2) join t2 ignore index (idx_c2) on t1.c2 = t2.c2 where t1.c2=t2.c2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=17336898)
-> Table scan on t2 (cost=0.00 rows=131472)
-> Hash
-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

我在想即使索引存在的情况下，优化器也能够根据提示使用 Hash Join，这样我们就不需要在各种表上ignore index了。我已经提了feature request。

然而，如果你有认真阅读我提的bug report，你会看到 MySQL 的开发者有表明这可能是不必要的：

块嵌套循环（ Block Nested-Loop ）在某些情况下完全不会起作用，这时提示（ hint ）会被忽略。

这意味着他们在未来可能打算移除块钱套循环 Join，使用 Hash Join 代替。

限制

我们可以看到 Hash Join 很强大，但也有些限制：

• 我提到过它只在没有索引的列上其作用（或者需要手动忽略索引）
• 只有 Join 条件是“=”的情况才会生效
• LEFT JOIN和RIGHT JOIN不生效

我还期望看到 Hash Join 使用次数的统计，所以我又提了另一个 feature request

总结

Hash Join 是个很强大的 join 查询方式，我们应该重点关注它，未来如果有更多 Feature 我也不会感到惊讶。理论上，它应该也能用来做LEFT JOIN和RIGHT JOIN，我们在 bug report 的评论里面也能看到 Oracle 在未来也打算使用 Hash Join。