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

2023-08-10 10:37:04 236

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

MySQL实现了用于内连接查询的hashjoin方式。例如，从MySQL8.0.18开始以下查询可以使用hashjoin进行连接查询：

SELECT*
FROMt1
JOINt2
ONt1.c1=t2.c1;

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

CREATETABLEt1(c1INT,c2INT);
CREATETABLEt2(c1INT,c2INT);
CREATETABLEt3(c1INT,c2INT);

使用EXPLAINFORMAT=TREE命令可以看到执行计划中的hashjoin，例如：

mysql>EXPLAINFORMAT=TREE
->SELECT*
->FROMt1
->JOINt2
->ONt1.c1=t2.c1\G
***************************1.row***************************
EXPLAIN:->Innerhashjoin(t2.c1=t1.c1)(cost=0.70rows=1)
->Tablescanont2(cost=0.35rows=1)
->Hash
->Tablescanont1(cost=0.35rows=1)

必须使用EXPLAIN命令的FORMAT=TREE选项才能看到节点中的hashjoin。另外，EXPLAINANALYZE命令也可以显示hashjoin的使用信息。这也是该版本新增的一个功能。

多个表之间使用等值连接的的查询也会进行这种优化。例如以下查询：

SELECT*
FROMt1
JOINt2
ON(t1.c1=t2.c1ANDt1.c2
在以上示例中，任何其他非等值连接的条件将会在连接操作之后作为过滤器使用。可以通过EXPLAINFORMAT=TREE命令的输出进行查看：
mysql>EXPLAINFORMAT=TREE
->SELECT*
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1ANDt1.c2JOINt3
->ON(t2.c1=t3.c1)\G
***************************1.row***************************
EXPLAIN:->Innerhashjoin(t3.c1=t1.c1)(cost=1.05rows=1)
->Tablescanont3(cost=0.35rows=1)
->Hash
->Filter:(t1.c2Innerhashjoin(t2.c1=t1.c1)(cost=0.70rows=1)
->Tablescanont2(cost=0.35rows=1)
->Hash
->Tablescanont1(cost=0.35rows=1)
从以上输出同样可以看出，包含多个等值连接条件的查询也可以（会）使用多个hashjoin连接。
但是，如果任何连接语句（ON）中没有使用等值连接条件，将不会采用hashjoin连接方式。例如：
mysql>EXPLAINFORMAT=TREE
->SELECT*
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1
此时，将会采用性能更慢的blocknestedloop连接算法。这与MySQL8.0.18之前版本中没有索引时的情况一样：
mysql>EXPLAIN
->SELECT*
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1
Hashjoin连接同样适用于不指定查询条件时的笛卡尔积（Cartesianproduct），例如：
mysql>EXPLAINFORMAT=TREE
->SELECT*
->FROMt1
->JOINt2
->WHEREt1.c2>50\G
***************************1.row***************************
EXPLAIN:->Innerhashjoin(cost=0.70rows=1)
->Tablescanont2(cost=0.35rows=1)
->Hash
->Filter:(t1.c2>50)(cost=0.35rows=1)
->Tablescanont1(cost=0.35rows=1)
默认配置时，MySQL所有可能的情况下都会使用hashjoin。同时提供了两种控制是否使用hashjoin的方法：
在全局或者会话级别设置服务器系统变量optimizer_switch中的hash_join=on或者hash_join=off选项。默认为hash_join=on。
在语句级别为特定的连接指定优化器提示HASH_JOIN或者NO_HASH_JOIN。
可以通过系统变量join_buffer_size控制hashjoin允许使用的内存数量；hashjoin不会使用超过该变量设置的内存数量。如果hashjoin所需的内存超过该阈值，MySQL将会在磁盘中执行操作。需要注意的是，如果hashjoin无法在内存中完成，并且打开的文件数量超过系统变量open_files_limit的值，连接操作可能会失败。为了解决这个问题，可以使用以下方法之一：
增加join_buffer_size的值，确保hashjoin可以在内存中完成。
增加open_files_limit的值。
接下来他们比较一下hashjoin和blocknestedloop的性能，首先分别为t1、t2和t3生成1000000条记录：
setjoin_buffer_size=2097152000;
SET@@cte_max_recursion_depth=99999999;
INSERTINTOt1
--INSERTINTOt2
--INSERTINTOt3
WITHRECURSIVEtAS(
SELECT1ASc1,1ASc2
UNIONALL
SELECTt.c1+1,t.c1*2
FROMt
WHEREt.c1<1000000
)
SELECT*
FROMt;
没有索引情况下的hashjoin：
mysql>EXPLAINANALYZE
->SELECTCOUNT(*)
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1=t3.c1)\G
***************************1.row***************************
EXPLAIN:->Aggregate:count(0)(actualtime=22993.098..22993.099rows=1loops=1)
->Innerhashjoin(t3.c1=t1.c1)(cost=9952535443663536.00rows=9952435908880402)(actualtime=14489.176..21737.032rows=1000000loops=1)
->Tablescanont3(cost=0.00rows=998412)(actualtime=0.103..3973.892rows=1000000loops=1)
->Hash
->Innerhashjoin(t2.c1=t1.c1)(cost=99682753413.67rows=99682653660)(actualtime=5663.592..12236.984rows=1000000loops=1)
->Tablescanont2(cost=0.01rows=998412)(actualtime=0.067..3364.105rows=1000000loops=1)
->Hash
->Tablescanont1(cost=100539.40rows=998412)(actualtime=0.133..3395.799rows=1000000loops=1)

1rowinset(23.22sec)

mysql>SELECTCOUNT(*)
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1=t3.c1);
+----------+
|COUNT(*)|
+----------+
|1000000|
+----------+
1rowinset(12.98sec)
实际运行花费了12.98秒。这个时候如果使用blocknestedloop：
mysql>EXPLAINFORMAT=TREE
->SELECT/*+NO_HASH_JOIN(t1,t2,t3)*/COUNT(*)
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1=t3.c1)\G
***************************1.row***************************
EXPLAIN:

1rowinset(0.00sec)

SELECT/*+NO_HASH_JOIN(t1,t2,t3)*/COUNT(*)
FROMt1
JOINt2
ON(t1.c1=t2.c1)
JOINt3
ON(t2.c1=t3.c1);
EXPLAIN显示无法使用hashjoin。查询跑了几十分钟也没有出结果，其中一个CPU使用率到了100%；因为一直在执行嵌套循环（1000000的3次方）。
再看有索引时的blocknestedloop方法，增加索引：
mysql>CREATEindexidx1ONt1(c1);
QueryOK,0rowsaffected(7.39sec)
Records:0Duplicates:0Warnings:0
mysql>CREATEindexidx2ONt2(c1);
QueryOK,0rowsaffected(6.77sec)
Records:0Duplicates:0Warnings:0
mysql>CREATEindexidx3ONt3(c1);
QueryOK,0rowsaffected(7.23sec)
Records:0Duplicates:0Warnings:0
查看执行计划并运行相同的查询语句：
mysql>EXPLAINANALYZE
->SELECTCOUNT(*)
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1=t3.c1)\G
***************************1.row***************************
EXPLAIN:->Aggregate:count(0)(actualtime=47684.034..47684.035rows=1loops=1)
->Nestedloopinnerjoin(cost=2295573.22rows=998412)(actualtime=0.116..46363.599rows=1000000loops=1)
->Nestedloopinnerjoin(cost=1198056.31rows=998412)(actualtime=0.087..25788.696rows=1000000loops=1)
->Filter:(t1.c1isnotnull)(cost=100539.40rows=998412)(actualtime=0.050..5557.847rows=1000000loops=1)
->Indexscanont1usingidx1(cost=100539.40rows=998412)(actualtime=0.043..3253.769rows=1000000loops=1)
->Indexlookupont2usingidx2(c1=t1.c1)(cost=1.00rows=1)(actualtime=0.012..0.015rows=1loops=1000000)
->Indexlookupont3usingidx3(c1=t1.c1)(cost=1.00rows=1)(actualtime=0.012..0.015rows=1loops=1000000)

1rowinset(47.68sec)

mysql>SELECTCOUNT(*)
->FROMt1
->JOINt2
->ON(t1.c1=t2.c1)
->JOINt3
->ON(t2.c1=t3.c1);
+----------+
|COUNT(*)|
+----------+
|1000000|
+----------+
1rowinset(19.56sec)
实际运行花费了19.56秒。所以在我们这个场景中的测试结果如下：




HashJoin（无索引）
BlockNestedLoop（无索引）
BlockNestedLoop（有索引）




12.98s
未返回
19.56s




再增加一个Oracle12c中无索引时hashjoin结果：1.282s。
再增加一个PostgreSQL11.5中无索引时hashjoin结果：6.234s。
再增加一个SQL2017中无索引时hashjoin结果：5.207s。
总结
以上所述是小编给大家介绍的MySQL8.0新特性之哈希连接（HashJoin）,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

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

热门推荐

随机推荐