哈希连接优化

来自泡泡学习笔记
跳到导航 跳到搜索

默认情况下，MySQL（8.0.18及更高版本）在可能的情况下使用哈希连接。可以使用 BNL 和 NO_BNL 优化提示之一，或者将 block_nested_loop=on 或 block_nested_loop=off 作为 optimizer_switch 服务器系统变量的设置的一部分来控制是否使用哈希连接。 

注意
MySQL 8.0.18 可以通过在 optimizer_switch 中设置 hash_join 标志，以及使用优化提示 HASH_JOIN 和 NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些设置都不再起作用。


从 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: -> 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)


  • 反连接。
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


  • 左外连接。
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 将所有的右外连接重写为左外连接）。
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 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 设置得更高，而不会导致小型查询分配大量的内存，但是外连接会分配整个缓冲区。在 MySQL 8.0.20 及更高版本中，哈希连接也用于外连接（包括反连接和半连接），因此这不再是问题。
取自“http://bubblestudy.info/wiki/index.php?title=哈希连接优化&oldid=1435”