MySQL 8.0 官方文档第八章优化（五）—— 哈希连接优化

发表于： 2023-08-09 08:28:48
分类：网络资源
浏览：浏览(46)

转载：https://blog.csdn.net/qq_39785418/article/details/116428222?spm=1001.2014.3001.5501

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.4 哈希连接优化

从MySQL 8.0.18开始，MySQL对任何连接查询都使用哈希连接，每个连接都有一个相等的连接条件，并且也可以应用于没有任何连接条件的索引，例如：

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

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

哈希连接通常比旧版MySQL采用的块嵌套循环算法（请参阅块嵌套循环连接算法）更快，并打算在需要块嵌套循环算法时候使用。从MySQL8.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);
1
2
3

通过使用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) # 使用where；使用了连接缓冲(哈希连接)

（在MySQL8.0.20之前，EXPLAIN只有在包含 FORMAT=TREE（格式=树）选项时，才能查看给定连接是否使用了哈希联接。）

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

【以下是译者进行的测试和解释：

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1;

# 输出结果：
EXPLAIN: -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1) (actual time=0.040..0.040 rows=0 loops=1)
    -> Table scan on t2  (cost=0.35 rows=1) (never executed)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1) (actual time=0.032..0.032 rows=0 loops=1)
        
# 对上面树状输出解释如下，执行顺序是从下向上：
# Inner hash join (t2.c1 = t1.c1)：哈希内连接，连接条件是 t2.c1 = t1.c1
# Table scan on t2：在t2表上进行表扫描
# Hash: 使用哈希连接
# cost=0.70：评估执行的时间成本。（有些迭代器没有集成成本模型，因此不包括在估算中。）
# rows=1：评估返回行的数量
# actual time=0.040：返回第一行的实际时间
# ..0.040：返回所有行或实际行的时间，单位毫秒
# （如果有多层循环，这个数值表示每个循环的平均时间）
# rows=0：迭代器返回的行数
# loops=1：循环的层次

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

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)                   # 在t1上进行表扫描
        # 使用<自动不同键>(c2=t1.c1)在<子查询2>上进行单行索引查找
    -> 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)    # 过滤，条件是t2.c2 is not null
                -> Table scan on t2  (cost=0.85 rows=6)           # 在t2上进行表扫描

反连接：

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），以便哈希连接不会溢出到磁盘上。
增加打开文件限制的值（Increase open_files_limit）。

从MySQL8.0.18开始，哈希连接的连接缓冲区是增量分配的；因此，您可以将join_buffer_size设置得更高一些，就不出现小查询分配了大量的RAM，但是外部连接会分配整个缓冲区。在MySQL 8.0.20及更高版本中，哈希连接也用于外部连接（包括反连接和半连接），因此这不再是一个问题。

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论