Code前端首页关于Code前端联系我们
首页 数据库正文

MySQL 8.0.18 GA 发布，你知道 Hash Join 但不熟悉吗？

terry 2年前 (2023-09-26) 阅读数 47 #数据库

有人不熟悉Mysql数据库吗？不需要？不。

2019年底，MySQL发布了8.0.18 GA版本，带来了一些新功能和改进。引人注目的是，多表连接查询支持Hash Join功能。

还是一样。建议懂英文的同学直接看这里：https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

MySQL Hash Join 特性介绍：

  • 1.对于大量数据的表连接，HJ（哈希连接）明显快于 NL（嵌套循环）
  • 2。在内存中处理
  • 3。必要时使用磁盘空间
  • 4。用于内部接头，可扩展为外部接头、半接头和反向接头
  • 5。替换查询计划中的块嵌套循环
  • 6。可以强制SQL使用HJ或NL TIP

有的同学可能会感到困惑。什么是哈希连接？什么是NL？ HINT 到底是什么？

第一部分是一个简单的科普

首先，当我们做几个表的联合查询时，如果我们看它的执行计划，我们会发现几个表之间有一个join方法。连接多个表有三种方式：嵌套循环、哈希连接和排序合并连接。

有人不得不说，按照阿里巴巴的规范，并发情况下是不能使用多表查询的。你的并发度是多少？任何系统后端都会使用多表连接查询。

Hash Join用于Spark和Flink的SQL部分Join时。我们之前发表过一篇文章：

【Spark SQL Join的三种实现方法】。

哈希联接 哈希联接是CBO联接大型数据集的常用方式，一般适合联接大小表。一般来说，使用带有 JOIN KEY 的小表在内存中创建哈希表，将列数据存储在哈希列表中，然后扫描较大的表。 HASH还JOIN KEY然后识别哈希表以查找与哈希表匹配的行。

有同学又一头雾水了。什么是国会预算办公室？我们在这里不做详细介绍。简单来说，CBO是一种SQL优化方法。它根据实际数据情况评估实施方案，选择最具成本效益的实施方案。

什么是实施计划？去百度...[黑色问号]

什么是嵌套循环？简单来说，就是一个两层循环。第一个数组用作外循环，第二个数组用作内循环。将外循环中的每条记录与内循环中的记录进行比较，以找到匹配的数据。当然，嵌套循环有很多种情况。让我们举一个最简单的例子。伪代码如下： 


    for (r in R) {
        for (s in S) {
            if (r satisfy condition s) {
                output <r, s>;
            }
        }
    }
复制代码

提示什么？英文单词“Hint”的意思是“快点”。简单来说，Hint就像我们开发代码时的笔记。代码中的注释提醒开发人员或其他人代码的含义。那么这个hint在SQL中就起到了特殊的作用。是对数据库的一个提示，表示我想让数据库按照我的提示来执行。这里就不举例了。

回归正文，新版本MySQL中如何使用Hash Join功能？

使用直接来自官网的示例。

假设我们有如下三个表： 

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
复制代码

有一个简单的表连接查询： 

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

我们使用EXPLAIN FORMAT=TREE:命令。 Me 当你看到关键字Inner hash join时，说明这条SQL使用了Hash Join。

此外，在多个表之间使用等值连接的查询也将经历此优化。例如，下面的查询： 


SELECT * 
    FROM t1
    JOIN t2 
        ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 
        ON (t2.c1 = t3.c1);
复制代码

查看 EXPLAIN FORMAT=TREE 命令的结果，我们还可以注意到非等值连接的条件在最后变成了过滤器。 


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

从上面的日志中还可以看到，如果SQL包含多个等值连接，MySQL会使用多个Hash Join。

但是注意！如果您的 SQL 条件不是等值连接，则不会使用哈希连接。

例如：


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: <not executable by iterator executor>

复制代码

解释，让我们看看：


mysql> EXPLAIN
    ->     SELECT * 
    ->         FROM t1
    ->         JOIN t2 
    ->             ON (t1.c1 = t2.c1)
    ->         JOIN t3 
    ->             ON (t2.c1 < t3.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 (Block Nested Loop)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   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 (Block Nested Loop)
复制代码

看，MySQL 目前选择了嵌套循环。

笛卡尔积查询也可以用HJ：


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 只要有可能就使用 Hash Join。同时提供了两种方式来控制Hash Join的使用。比如我就是不喜欢HJ，但是我喜欢NL的slow Join。如果项目经理在优化期间让我开一份 HJ 调查不是很好吗？

选择以下两种方法之一：

  • 全局设置服务器系统变量 hash_join=on
  • 使用 SQL 中的提示指定 HASHJOIN 或 NO，我们HASH_。 HJ本身不受索引影响，这意味着在没有索引优化的情况下，HJ仍然很快。

    以下是我从网上其他人那里找到的一个测试，可以看出HJ的强大。

    首先为 t1、t2 和 t3 创建 1,000,000 条记录： 

    
set join_buffer_size=2097152000;

SET @@cte_max_recursion_depth = 99999999;

INSERT INTO t1
-- INSERT INTO t2
-- INSERT INTO t3
WITH RECURSIVE t AS (
  SELECT 1 AS c1, 1 AS c2
  UNION ALL
  SELECT t.c1 + 1, t.c1 * 2
    FROM t
   WHERE t.c1 < 1000000
)
SELECT *
  FROM t;

复制代码

    无索引的哈希联接：

    
mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(*)
    ->   FROM t1
    ->   JOIN t2 
    ->     ON (t1.c1 = t2.c1)
    ->   JOIN t3 
    ->     ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (actual time=22993.098..22993.099 rows=1 loops=1)
    -> Inner hash join (t3.c1 = t1.c1)  (cost=9952535443663536.00 rows=9952435908880402) (actual time=14489.176..21737.032 rows=1000000 loops=1)
        -> Table scan on t3  (cost=0.00 rows=998412) (actual time=0.103..3973.892 rows=1000000 loops=1)
        -> Hash
            -> Inner hash join (t2.c1 = t1.c1)  (cost=99682753413.67 rows=99682653660) (actual time=5663.592..12236.984 rows=1000000 loops=1)
                -> Table scan on t2  (cost=0.01 rows=998412) (actual time=0.067..3364.105 rows=1000000 loops=1)
                -> Hash
                    -> Table scan on t1  (cost=100539.40 rows=998412) (actual time=0.133..3395.799 rows=1000000 loops=1)

1 row in set (23.22 sec)

mysql> SELECT COUNT(*)
    ->   FROM t1
    ->   JOIN t2 
    ->     ON (t1.c1 = t2.c1)
    ->   JOIN t3 
    ->     ON (t2.c1 = t3.c1);
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (12.98 sec)

复制代码

    实际运行耗时 12.98 秒。如果您当前正在使用块嵌套循环： 

    
mysql> EXPLAIN FORMAT=TREE
    -> SELECT /*+  NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
    ->   FROM t1
    ->   JOIN t2 
    ->     ON (t1.c1 = t2.c1)
    ->   JOIN t3 
    ->     ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>

1 row in set (0.00 sec)

SELECT /*+  NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
  FROM t1
  JOIN t2 
    ON (t1.c1 = t2.c1)
  JOIN t3 
    ON (t2.c1 = t3.c1);

复制代码

    EXPLAIN 表示无法使用散列连接。查询运行了几十分钟没有返回结果，其中一次CPU利用率达到100%；这是因为执行了嵌套循环（1000000 的三次方）。

    查看索引存在时块的嵌套循环方法，添加索引： 

    
mysql> CREATE index idx1 ON t1(c1);
Query OK, 0 rows affected (7.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE index idx2 ON t2(c1);
Query OK, 0 rows affected (6.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE index idx3 ON t3(c1);
Query OK, 0 rows affected (7.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

复制代码

    查看执行计划，执行相同的查询语句：

    
mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(*)
    ->   FROM t1
    ->   JOIN t2 
    ->     ON (t1.c1 = t2.c1)
    ->   JOIN t3 
    ->     ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (actual time=47684.034..47684.035 rows=1 loops=1)
    -> Nested loop inner join  (cost=2295573.22 rows=998412) (actual time=0.116..46363.599 rows=1000000 loops=1)
        -> Nested loop inner join  (cost=1198056.31 rows=998412) (actual time=0.087..25788.696 rows=1000000 loops=1)
            -> Filter: (t1.c1 is not null)  (cost=100539.40 rows=998412) (actual time=0.050..5557.847 rows=1000000 loops=1)
                -> Index scan on t1 using idx1  (cost=100539.40 rows=998412) (actual time=0.043..3253.769 rows=1000000 loops=1)
            -> Index lookup on t2 using idx2 (c1=t1.c1)  (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000)
        -> Index lookup on t3 using idx3 (c1=t1.c1)  (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000)

1 row in set (47.68 sec)

mysql> SELECT COUNT(*)
    ->   FROM t1
    ->   JOIN t2 
    ->     ON (t1.c1 = t2.c1)
    ->   JOIN t3 
    ->     ON (t2.c1 = t3.c1);
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (19.56 sec)

复制代码

    实际执行耗时19.56秒。所以在我们的场景中，测试结果如下：

    MySQL 8.0.18 GA版本发布，你熟悉又陌生的Hash Join？

    在 Oracle 12c 中添加另一个不带索引的 Hash Join 结果：1.282s 在 PostgreSQL 11.5 中添加另一个不带索引的 Hash Join 结果：6.234s 添加另一个不带索引的 Hash Join 结果 SQL On Server 2017：5,207 页

    您见过 Hash Join 的威力吗？你学会了吗？

    作者：王志武
    链接：https://juejin.im/post/5e1f2622e51d45026d6edfed
    来源：掘金版权所有如需商业转载请联系作者授权。非商业转载请注明出处。

版权声明

本文仅代表作者观点，不代表Code前端网立场。
本文系作者Code前端网发表，如需转载，请注明页面地址。

上一篇：当InnoDB和row_id自增键用完后会发生什么？ 下一篇：MySQL 中带 IN 子句的语句是如何执行的
相关文章

发表评论:

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

terry

terry

作者文章
浏览更多
热门
最新文章
标签列表
Copyright by code前端网 All Rights Reserved.粤ICP备12067512号-4

Powered By Z-BlogPHP Theme By 编程老白