MySQL优化(7):SELECT 之 Hash Join优化

从 MySQL 8.0.18 开始,MySQL 对每个连接(join)具有等连接条件的任何查询使用哈希连接(Hash Join)。哈希连接通常比以前版本的 MySQL 中使用的块嵌套循环算法更快。

从 MySQL 8.0.18 开始,MySQL 对每个连接(join)具有等连接条件的任何查询使用哈希连接(Hash Join),并且其中没有可应用于任何连接条件的索引,例如:

1
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;

当有一个或多个索引可用于单表谓词(断言)时,也可以使用哈希连接。

哈希连接通常比以前版本的 MySQL 中使用的块嵌套循环算法更快,并且旨在用于这种情况。 从 MySQL 8.0.20 开始,删除了对块嵌套循环的支持,并且服务器在以前使用块嵌套循环的任何地方使用哈希连接。

在刚刚显示的示例和本节的其余示例中,我们假设已使用以下语句创建了三个表 t1、t2 和 t3:

1
2
3
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

在上面查询示例中,使用 EXPLAIN 查询执行计划可以看到正在使用哈希连接,如下所示:

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
mysql> EXPLAIN
-> SELECT * FROM t1
-> JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using join buffer (hash join)

(在 MySQL 8.0.20 之前,EXPLAIN 必须包含 FORMAT=TREE 选项才能看到给定的连接是否使用了哈希连接)

EXPLAIN ANALYZE 还显示有关使用的哈希连接的信息。

哈希连接也用于涉及多个连接的查询,只要每对表的至少一个连接条件是等连接,就像这里显示的查询:

1
2
3
SELECT * FROM t1
JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3 ON (t2.c1 = t3.c1);

在刚刚显示的使用内部联接的情况下,任何不是等联接的额外条件都将在联接执行后作为过滤器应用。 (对于外连接,例如左连接、半连接和反连接,它们作为连接的一部分打印。)这可以在 EXPLAIN 的输出中看到:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
-> JOIN t3
-> ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1)
-> Table scan on t3 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 < t2.c2) (cost=0.70 rows=1)
-> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)

从刚刚显示的输出中也可以看出,多个散列连接可以(并且已经)用于具有多个等连接条件的连接。

在 MySQL 8.0.20 之前,如果任何一对连接表没有至少一个等连接条件,则无法使用散列连接,并且使用较慢的块嵌套循环算法。 在 MySQL 8.0.20 及更高版本中,在这种情况下使用散列连接,如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> EXPLAIN FORMAT=TREE
-> SELECT * FROM t1
-> JOIN t2 ON (t1.c1 = t2.c1)
-> JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1) (cost=1.05 rows=1)
-> Inner hash join (no condition) (cost=1.05 rows=1)
-> Table scan on t3 (cost=0.35 rows=1)
-> Hash
-> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)

(本节后面提供了其他示例。)

哈希连接也适用于笛卡尔积——也就是说,当没有指定连接条件时,如下所示:

1
2
3
4
5
6
7
8
9
10
11
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 > 50) (cost=0.35 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)

在 MySQL 8.0.20 及更高版本中,连接不再需要包含至少一个等连接条件才能使用散列连接。 这意味着可以使用散列连接优化的查询类型包括以下列表(带有示例):

  • Inner non-equi-join: 非等条件内连接

    1
    2
    3
    4
    5
    6
    7
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
    *************************** 1. row ***************************
    EXPLAIN: -> Filter: (t1.c1 < t2.c1) (cost=4.70 rows=12)
    -> Inner hash join (no condition) (cost=4.70 rows=12)
    -> Table scan on t2 (cost=0.08 rows=6)
    -> Hash
    -> Table scan on t1 (cost=0.85 rows=6)
  • Semijoin:半连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
    -> WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
    *************************** 1. row ***************************
    EXPLAIN: -> Nested loop inner join
    -> Filter: (t1.c1 is not null) (cost=0.85 rows=6)
    -> Table scan on t1 (cost=0.85 rows=6)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c2=t1.c1)
    -> Materialize with deduplication
    -> Filter: (t2.c2 is not null) (cost=0.85 rows=6)
    -> Table scan on t2 (cost=0.85 rows=6)
  • Antijoin:反连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
    -> WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.col1 = t2.col1)\G
    *************************** 1. row ***************************
    EXPLAIN: -> Nested loop antijoin
    -> Table scan on t2 (cost=0.85 rows=6)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c1=t2.c1)
    -> Materialize with deduplication
    -> Filter: (t1.c1 is not null) (cost=0.85 rows=6)
    -> Table scan on t1 (cost=0.85 rows=6)
  • Left outer join:左外连接

    1
    2
    3
    4
    5
    6
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
    *************************** 1. row ***************************
    EXPLAIN: -> Left hash join (t2.c1 = t1.c1) (cost=3.99 rows=36)
    -> Table scan on t1 (cost=0.85 rows=6)
    -> Hash
    -> Table scan on t2 (cost=0.14 rows=6)
  • Right outer join:右外连接

    观察 MySQL 将所有右外连接重写为左外连接

    1
    2
    3
    4
    5
    6
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
    *************************** 1. row ***************************
    EXPLAIN: -> Left hash join (t1.c1 = t2.c1) (cost=3.99 rows=36)
    -> Table scan on t2 (cost=0.85 rows=6)
    -> Hash
    -> Table scan on t1 (cost=0.14 rows=6)

默认情况下,MySQL 8.0.18 及更高版本尽可能使用哈希连接。 可以使用 BNL 和 NO_BNL 优化器提示之一来控制是否使用哈希连接。

(MySQL 8.0.18 支持 hash_join=onhash_join=off 作为 optimizer_switch 服务器系统变量以及优化器提示 HASH_JOIN 或 NO_HASH_JOIN 设置的一部分。在 MySQL 8.0.19 及更高版本中,这些不再有任何影响。)

可以使用 join_buffer_size 系统变量控制哈希连接的内存使用情况; 哈希连接不能使用比这个数量更多的内存。

当哈希连接所需的内存超过可用量时,MySQL 会使用磁盘上的文件来处理此问题。 如果发生这种情况,您应该意识到如果哈希连接无法放入内存并且它创建的文件多于为 open_files_limit 设置的文件,则连接可能不会成功。 为避免此类问题,请进行以下任一更改:

  • 增大 join_buffer_size 以便哈希连接不会溢出到磁盘。
  • 增大 open_files_limit 创建文件数量。

从 MySQL 8.0.18 开始,哈希连接的连接缓冲区是增量分配的; 因此,您可以将 join_buffer_size 设置得更高,而无需分配大量 RAM 的小查询,但外连接分配整个缓冲区。在 MySQL 8.0.20 及更高版本中,哈希连接也用于外连接(包括反连接和半连接),因此这不再是问题。

作者

光星

发布于

2021-07-15

更新于

2023-03-06

许可协议

评论