MySQL 8.0 新特性之哈希连接(Hash Join)

MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持 hash join 方式了。我们先来看看官方的描述:

MySQL 实现了用于内连接查询的 hash join 方式。例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询:

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

Hash join 不需要索引的支持。大多数情况下,hash join 比之前的 Block Nested-Loop 算法在没有索引时的等值连接更加高效。使用以下语句创建三张测试表:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

使用EXPLAIN FORMAT=TREE命令可以看到执行计划中的 hash join,例如:

mysql> EXPLAIN FORMAT=TREE
  -> SELECT *
  ->   FROM t1
  ->   JOIN t2
  ->     ON t1.c1=t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> 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)

必须使用 EXPLAIN 命令的 FORMAT=TREE 选项才能看到节点中的 hash join。另外,EXPLAIN ANALYZE命令也可以显示 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)

从以上输出同样可以看出,包含多个等值连接条件的查询也可以(会)使用多个 hash join 连接。

但是,如果任何连接语句(ON)中没有使用等值连接条件,将不会采用 hash join 连接方式。例如:

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>

此时,将会采用性能更慢的 block nested loop 连接算法。这与 MySQL 8.0.18 之前版本中没有索引时的情况一样:

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)

Hash join 连接同样适用于不指定查询条件时的笛卡尔积(Cartesian product),例如:

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 的方法:

在全局或者会话级别设置服务器系统变量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 选项。默认为 hash_join=on

在语句级别为特定的连接指定优化器提示 HASH_JOIN 或者 NO_HASH_JOIN。

可以通过系统变量 join_buffer_size 控制 hash join 允许使用的内存数量;hash join 不会使用超过该变量设置的内存数量。如果 hash join 所需的内存超过该阈值,MySQL 将会在磁盘中执行操作。需要注意的是,如果 hash join 无法在内存中完成,并且打开的文件数量超过系统变量 open_files_limit 的值,连接操作可能会失败。为了解决这个问题,可以使用以下方法之一:

增加 join_buffer_size 的值,确保 hash join 可以在内存中完成。

增加 open_files_limit 的值。

接下来他们比较一下 hash join block nested loop 的性能,首先分别为 t1、t2 和 t3 生成 1000000 条记录:

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;

没有索引情况下的 hash join:

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 秒。这个时候如果使用 block nested loop:

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 显示无法使用 hash join。查询跑了几十分钟也没有出结果,其中一个 CPU 使用率到了 100%;因为一直在执行嵌套循环(1000000 的 3 次方)。

再看有索引时的 block nested loop 方法,增加索引:

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 秒。所以在我们这个场景中的测试结果如下:

Hash Join(无索引) Block Nested Loop(无索引) Block Nested Loop(有索引)
12.98 s 未返回 19.56 s

再增加一个 Oracle 12c 中无索引时 hash join 结果:1.282 s。

再增加一个 PostgreSQL 11.5 中无索引时 hash join 结果:6.234 s。

再增加一个 SQL 2017 中无索引时 hash join 结果:5.207 s。

总结

以上所述是小编给大家介绍的MySQL 8.0 新特性之哈希连接(Hash Join),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

(0)

相关推荐

  • Mysql 8.0.18 hash join测试(推荐)

    Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效. 下面通过实例代码给大家介绍Mysql 8.0.18 hash join测试,具体内容如下所示: CREATE TABLE COLUMNS_hj as select * from information_schema.`COLUMNS`; INSERT INTO COLUMNS SELECT * FROM COLUMNS; -- 最后一次插入25万行 CREATE TABLE COLUM

  • MySQL 8.0.18 稳定版发布! Hash Join如期而至

    MySQL 8.0.18 稳定版(GA)已于昨日正式发布,Hash Join 也如期而至. 快速浏览一下这个版本的亮点! 1.Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效. 2.EXPLAIN ANALYZE EXPLAIN ANALYZE 将运行查询,然后生成 EXPLAIN 输出,以及有关优化程序估计如何与实际执行相匹配的其他信息. 3.创建用户时可以随机生成密码 为 CREATE USER, ALTER USER和 SET P

  • MySQL 8.0 新特性之哈希连接(Hash Join)

    MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能.其中最引人注目的莫过于多表连接查询支持 hash join 方式了.我们先来看看官方的描述: MySQL 实现了用于内连接查询的 hash join 方式.例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询: SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; Hash join 不需要索引的支

  • MySQL 8.0 新特性之检查约束的实现

    大家好,我是只谈技术不剪发的 Tony 老师.这次我们来介绍一个 MySQL 8.0 增加的新功能:检查约束(CHECK ). SQL 中的检查约束属于完整性约束的一种,可以用于约束表中的某个字段或者一些字段必须满足某个条件.例如用户名必须大写.余额不能小于零等. 我们常见的数据库都实现了检查约束,例如 Oracle.SQL Server.PostgreSQL 以及 SQLite:然而 MySQL 一直以来没有真正实现该功能,直到最新的 MySQL 8.0.16. MySQL 8.0.15 之前

  • MySQL 8.0新特性之隐藏字段的深入讲解

    前言 MySQL 8.0.23 版本增加了一个新的功能:隐藏字段(Invisible Column),也称为不可见字段.本文给大家介绍一下 MySQL 隐藏字段的相关概念和具体实现. 基本概念 隐藏字段需要在查询中进行显式引用,否则对查询而言是不可见的.MySQL 8.0.23 开始支持隐藏字段,在此之前所有的字段都是可见字段. 考虑以下应用场景,假如一个应用程序使用SELECT *语句访问某个表,并且必需持续不断地进行查询,即使我们为该表增加了一个该应用不需要的新字段时也要求能够正常工作.对于

  • MySQL 8.0新特性 — 管理端口的使用简介

    前言 下面这个报错,相信大多数童鞋都遇见过:那么碰到这个问题,我们应该怎么办呢?在MySQL 5.7及之前版本,出现"too many connection"报错,超级用户root也无法登录上去,除了重启实例,没有其他更好的解决办法:不过在MySQL 8.0版本中,是对连接管理做了一些优化,下面我们就来看一下. ERROR 1040 (HY000): Too many connections 连接管理 在MySQL 8.0版本中,对连接管理这一块,是先后做了两个比较大的改变:一个是允许

  • MySQL 8.0新特性 — 检查性约束的使用简介

    前言 在MySQL 8.0版本中,引入了一个非常有用的新特性 - 检查性约束,它可以提高对非法或不合理数据写入的控制能力:接下来我们就来详细了解一下. 检查性约束 创建.删除与查看 (1)可以在建表时,创建检查性约束 mysql> CREATE TABLE t1 -> ( -> CHECK (c1 <> c2), -> c1 INT CHECK (c1 > 10), -> c2 INT CONSTRAINT c2_positive CHECK (c2 >

  • 红帽RedHat 8.0新特性(网络、yum源、Web界面管理等)

    1.Red Hat8 配置静态IP 注意:Red Hat8网络管理默认使用NetworkManager,而不是之前版本的network. 按照之前版本我们一般通过配置文件设置静态IP地址信息,如下: [root@localhost ~]# cd /etc/sysconfig/network-scripts [root@localhost network-scripts]# cp ifcfg-ens160 ifcfg-ens160.bak // 备份一下,要养成习惯 手动添加静态IP地址: [ro

  • 解析MySQL8.0新特性——事务性数据字典与原子DDL

    前言 事务性数据字典与原子DDL,是MySQL 8.0推出的两个非常重要的新特性,之所以将这两个新特性放在一起,是因为两者密切相关,事务性数据字典是前提,原子DDL是一个重要应用场景. MySQL 8.0之前的数据字典 MySQL 8.0之前的数据字典,主要由以下三部分组成: (1)操作系统文件 db.opt:数据库元数据信息 frm:表元数据信息 par:表分区元数据信息 TRN/TRG:触发器元数据信息 ddl_log.log:DDL过程中产生的元数据信息 (2)mysql库下的非InnoD

  • Mysql 5.7 新特性之 json 类型的增删改查操作和用法

    目录 插入数据 :insert操作 更新记录:update 追加内容: 更新记录:追加内容到数组: 更新记录:update 修改json属性: 删除记录: 查询记录: 扩展: 一.插入一条数据: 1.查找 JSON 中的某个字段: 2.根据JSON中的某个字段查询表中记录: 二.修改 JSON 1.修改 JSON 中的某个字段: 2.往 JSON 中插入一个新的字段: 近几年来,nosql大行其道,json更是火的一塌糊涂,作为数据库的元老,mysql在5.7版本中添加了对json数据的支持.这

  • C# 8.0新特性介绍

    C# 语言是在2000发布的,至今已正式发布了7个版本,每个版本都包含了许多令人兴奋的新特性和功能更新.同时,C# 每个版本的发布都与同时期的 Visual Studio 以及 .NET 运行时版本高度耦合,这也有助于开发者更好的学习掌握 C#,并将其与 Visual Studio 以及 .NET 的使用结合起来. 加快 C# 版本的发布速度 在被称为"新微软"的推动下,微软创新的步伐也加快了.为了做到加快步伐,微软开发部门将一些过去集成在一起的技术现在都分离了出来. Visual S

  • AngularJS 2.0新特性有哪些

    AngularJS已然成为Web应用开发世界里最受欢迎的开源JavaScript框架.自成立以来,见证其成功的是惊人的经济增长以及团体的支持与采用--包括个人开发者.企业.社区. Angular已经变成一个构建复杂单页面应用的客户端MVW框架(Model-View-Whatever).它在应用测试和应用编写方面都扮演重要角色,同时简化了开发过程. Angular目前的版本为1.3,该版本稳定,并被谷歌(框架维护者)用于支持众多应用(据估计,在谷歌有超过1600个应用运行于Angular1.2或1

随机推荐