2021-07-15发表2023-03-06更新MySQL系列 / MySQL优化12 分钟读完 (大约1767个字)

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 查询执行计划可以看到正在使用哈希连接，如下所示：

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 的输出中看到：

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 及更高版本中，在这种情况下使用散列连接，如下所示：

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)

（本节后面提供了其他示例。）

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

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: 非等条件内连接

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：半连接

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：反连接

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：左外连接

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 将所有右外连接重写为左外连接

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=on 或 hash_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 及更高版本中，哈希连接也用于外连接（包括反连接和半连接），因此这不再是问题。

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

http://blog.gxitsky.com/2021/07/15/MySQL-Optimization-07-hash-join/

作者

光星

发布于

2021-07-15

更新于

2023-03-06

许可协议

#索引优化 SELECT Hash Join

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

作者

发布于

更新于

许可协议

喜欢这篇文章？打赏一下作者吧

评论