【ACDU 翻译】8.2.1.4哈希联接优化

从MySQL 8.0.18开始，MySQL对所有联接具有等联接条件且没有索引可应用于任何联接条件的查询都使用哈希联接，例如：

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;
复制

当有一个或多个索引可用于单表谓词时，也可以使用哈希联接。

散列连接通常比在这种情况下要快，并且打算在这种情况下代替在MySQL早期版本中使用的块嵌套循环算法（请参阅 块嵌套循环联接算法）。从MySQL 8.0.20开始，删除了对块嵌套循环的支持，并且服务器在以前曾使用过块嵌套循环的任何地方都采用了哈希联接。

在刚刚所示的例子，在本节剩下的例子，我们假设三个表 t1，t2以及 t3使用下面的语句已创建：

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之前，有必要包括该 FORMAT=TREE选项以查看是否对给定的联接使用了哈希联接。）

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

哈希联接也用于涉及多个联接的查询，只要每对表的至少一个联接条件为等联接，如此处所示的查询：

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及更高版本中，为了使用哈希联接，联接不再需要包含至少一个等联接条件。这意味着可以使用哈希联接优化的查询类型包括以下列表中的查询类型（带有示例）：

• 内部非等额联接：

  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)
  复制

• 半连接：

  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)
  复制

• 反连接：

  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)
  复制

• 左外连接：

  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)
  复制

• 右外连接（观察到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和更高版本中，哈希联接也用于外部联接（包括反联接和半联接），因此这不再是问题。