MySQL 8.0 hash join

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等等。

发表评论