MySQL :: MySQL 8.4 参考手册 :: 10.2.1.4 哈希连接优化

版本 8.4

9.0
8.0

8.0 日语

MySQL 8.4 参考手册 / ... / 哈希连接优化

10.2.1.4 哈希连接优化

默认情况下，MySQL 会尽可能使用哈希连接。可以使用 BNL 和 NO_BNL 优化器提示之一来控制是否使用哈希连接，或者通过在 optimizer_switch 服务器系统变量的设置中设置 block_nested_loop=on 或 block_nested_loop=off 来控制。

MySQL 对每个连接都具有等值连接条件且连接条件中没有可应用的索引的任何查询使用哈希连接，例如下面的查询：

Press CTRL+C to copy
 
SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

当存在一个或多个可用于单表谓词的索引时，也可以使用哈希连接。

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

Press CTRL+C to copy
 
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

您可以使用 EXPLAIN 查看正在使用的哈希连接，如下所示：

Press CTRL+C to copy
 
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)

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

哈希连接也用于涉及多个连接的查询，只要每对表至少有一个连接条件是等值连接，例如下面显示的查询：

Press CTRL+C to copy
 
SELECT * FROM t1
    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 ON (t2.c1 = t3.c1);

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

Press CTRL+C to copy
 
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)

正如刚刚显示的输出所示，多个哈希连接可以（并且是）用于具有多个等值连接条件的连接。

即使任何一对连接的表至少没有一个等值连接条件，也会使用哈希连接，如下所示：

Press CTRL+C to copy
 
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)

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

即使未指定连接条件（即笛卡尔积），也会应用哈希连接，如下所示：

Press CTRL+C to copy
 
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)

连接不必包含至少一个等值连接条件才能使用哈希连接。这意味着可以使用哈希连接优化的查询类型包括以下列表中的查询类型（以及示例）：

内部非等值连接:

Press CTRL+C to copy
 
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)

半连接:

Press CTRL+C to copy
 
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
    ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

反连接:

Press CTRL+C to copy
 
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
    ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash antijoin (t1.c1 = t2.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 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1

左外连接:

Press CTRL+C to copy
 
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=0.70 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

右外连接（请注意，MySQL 会将所有右外连接重写为左外连接）

Press CTRL+C to copy
 
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=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

默认情况下，MySQL 会尽可能使用哈希连接。可以使用 BNL 和 NO_BNL 优化器提示来控制是否使用哈希连接。

可以使用 join_buffer_size 系统变量控制哈希连接的内存使用量；哈希连接使用的内存不能超过此数量。当哈希连接所需的内存超过可用内存量时，MySQL 将通过使用磁盘上的文件来处理这种情况。如果发生这种情况，您应该注意，如果哈希连接无法放入内存中并且创建的文件数量超过为 open_files_limit 设置的文件数量，则连接可能会失败。为避免此类问题，请进行以下任一更改：

增加 join_buffer_size，以便哈希连接不会溢出到磁盘。
增加 open_files_limit。

哈希连接的连接缓冲区是增量分配的；因此，您可以将 join_buffer_size 设置得更高，而不会使小型查询分配非常大量的 RAM，但外连接会分配整个缓冲区。哈希连接也用于外连接（包括反连接和半连接），因此这不再是一个问题。