MySQL Hash Join前世今生

singerhowe

发布于 2023-1-6 11:38

9641浏览

0收藏

1. WL#2241: Hash join (变更版本：8.0.18)

主要内容：

新增执行类 HashJoinIterator ，实现 hash join 算法原型 (支持on-disk hash)
HASH JOIN 仅支持 INNER JOIN ，并对使用 hash join 做了限制：关联表连接条件必须至少包含一条等值条件(equi-join condition, 如t1.a = t2.a)，且join条件中的列不包含索引

注：这里我认为官网的

Release Notes[2] 描述是不太准确的，以如下例子为例，虽然该查询包含了非等值连接条件(non-equi-join condition, 如 t1.a <> t2.a ,t1.a = 1, t2.a > 1

-- 版本：8.0.18
-- 在创建iterator时，t1.a > 1 会被当成表的过滤条件，而非inner join的join条件
-- 此查询仍使用了hash join（join 条件为空）
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.i > 1;
-> Inner hash join  (cost=1.17 rows=3)
    -> Table scan on t2  (cost=0.34 rows=2)
    -> Hash
        -> Filter: (t1.i > 1)  (cost=0.65 rows=1)
            -> Table scan on t1  (cost=0.65 rows=4)1.
2.
3.
4.
5.
6.
7.
8.
9.

(尽量)使用HASH JOIN算法替换BNL(Blocked Nested-Loop)连接算法

2. WL#13377: Add support for hash outer, anti and semi join( 变更版本：8.0.20)

主要内容:

HASH INNER JOIN改进
INNER JOIN中的non-equi-join conditions, 会被附为hash join的过滤条件:

-- 版本：8.0.20
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.i <> t2.i;
-> Filter: (t1.i <> t2.i)  (cost=1.10 rows=2)
    -> Inner hash join (no condition)  (cost=1.10 rows=2)
        -> Table scan on t2  (cost=0.18 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)1.
2.
3.
4.
5.
6.
7.

HAH JOIN支持outer join/anti join/semi join

-- 版本：8.0.20
-- Left outer join
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.i=t2.i;
-> Left hash join (t2.i = t1.i)  (cost=0.88 rows=4)                                                                                                                                                                          
    -> Table scan on t1  (cost=0.45 rows=2)                                                                                                                                                                                    
    -> Hash                                                                                                                                                                                                                    
        -> Table scan on t2  (cost=0.23 rows=2)

-- Right outer join（注：MySQL在parser阶段会将所有的right join改写为left join
--                      所以我们这里看到的explain为Left hash join
EXPLAIN FORMAT=tree SELECT * FROM t1 RIGHT JOIN t2 ON t1.i=t2.i;
-> Left hash join (t1.i = t2.i)  (cost=0.88 rows=4)                                                                                                                                                                          
    -> Table scan on t2  (cost=0.45 rows=2)                                                                                                                                                                                    
    -> Hash                                                                                                                                                                                                                    
        -> Table scan on t1  (cost=0.23 rows=2)

-- Semijoin
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
-> Hash semijoin (t2.i = t1.i)  (cost=0.83 rows=2)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Table scan on t2  (cost=0.18 rows=2)

-- Antijoin
EXPLAIN FORMAT=tree 
SELECT * FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.i = t2.i);
-> Hash antijoin (t1.i = t2.i)  (cost=1.10 rows=4)                                                                                                                                                                           
    -> Table scan on t2  (cost=0.45 rows=2)                                                                                                                                                                                    
    -> Hash                                                                                                                                                                                                                    
        -> Table scan on t1  (cost=0.45 rows=2)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

所有使用BNL的连接，都将被转为使用HASH JOIN
non-equi-join conditions 处理
Hash join iterator引入"extra" condition的概念，部分non-equi-join conditions会被当成Hash join iterator的extra condition, 在建hash table时，join key的计算不依赖这些条件，但会在hash查找到匹配项后，作为附加的过滤条件：

-- 版本: 8.0.20
-- 注： t1.i > 1 会被放到hash join的 extra conditions中
EXPLAIN FORMAT=tree SELECT * FROM  t1 LEFT JOIN t2 ON t1.i=t2.i AND t1.i > 1;
-> Left hash join (t2.i = t1.i), extra conditions: (t1.i > 1)  (cost=0.88 rows=4)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Table scan on t2  (cost=0.23 rows=2)1.
2.
3.
4.
5.
6.
7.

3. WL#13459: Optimize hash table in hash join (变更版本：8.0.23）

主要内容：

优化hash join table的创建方法
这里MySQL所说的“优化”，实际上会更激进一点，这个版本中，MySQL直接使用了一个基于
robin hood hashing[3] 实现的
开源hash table[4]
，更换了原先的hash join table实现( frommem_root_unordered_multimaptorobin_hood::unordered_flat_map)
优化内存管理和使用，降低了 on-disk hash 的频率，提高了内存有效使用率等（其他的改进内容及提升的效果可以参考MySQL 8.0.23的
release notes[5] 以及
worklog #13459[6]
的Low Level Design页面）