MySQL 8.0 hash join

MySQL, 数据库 / 作者：
MySQL 8.0.18 版本增加了一个新的特性hash join，关于hash join，通常其执行过程如下，首先基于join操作的一个表，在内存中创建一个对应的hash表，然后再一行一行的读另外一张表，通过计算哈希值，查找内存中的哈希表。
这种方式真的能带来很大的性能提升吗？

hash join仅仅在join的字段上没有索引时才起作用，在此之前，我们不建议在没有索引的字段上做join操作，因为通常中这种操作会执行的很慢，但是有了hash join，它能够创建一个内存中的hash 表，代替之前的nested loop，使得没有索引的等值join性能提升很多。

通过示例来看下性能有多大提升。

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

c2字段没有索引，看下hash 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仍然会显示nested loop，这种情况容易产生误解。

测试一，hash join

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

实际执行时，hash join一共耗时0.73秒。

测试二，非hash join
通过禁用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分36秒。

测试三，基于索引的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.63秒。

结果对比：

  • hash join：0.73秒
  • 非hash join： 13分36.36秒
  • 基于索引的join：2.63秒

从结果对比可以看出，hash join执行性能确实提升很多。

配置hash join功能是否开启：

  • optimizer_switch 中的 hash_join=on/off，默认为on
  • sql语句中指定HASH_JOIN或者NO_HASH_JOIN

限制：

  • hash join只能在没有索引的字段上有效
  • hash join只在等值join条件中有效
  • hash join不能用于left join和right join

最后：

hash join 是一个比较强大的join选项，也许这仅仅是一个开头，在不久的将来，基于它可能会延伸出更多更强大的特性，比如支持left join和right join等等。

发表评论