MySQL 8.0之hash join

用户1278550

发布于 2020-08-21 15:04:15

2.9K0

发布于 2020-08-21 15:04:15

文章被收录于专栏：idbaidba

前言

首先对于熟悉Oracle 的DBA 来说，hash join并不陌生，尤其涉及到多个表join时执行计划出现 hash join ，一般来说hash join的执行效率是比 Nest Loop 要好。运维MySQL 之后DBA也对MySQL 提出支持hash join的诉求。MySQL 在8.0.18 版本终于支持hash join了。那么什么是hash join呢？

hash join 就是当两个或者多个表join 查询时，基于其中一个表(驱动表)在内存构建一个哈希表，然后一行一行读另一个表(被驱动表)，计算其哈希值到内存哈希表中进行查找。

需要强调一下,使用hash join 是有条件的：where条件中join 的字段不能含有索引。

Beginning with MySQL 8.0.18, MySQL employs a hash join for any query for which each join has an equi-join condition and uses no indexes。

虽然官方文档说必须等值join查询,但是 8.0.20 版本可以支持非等值条件的查询。后面会有例子

hash join 工作原理

以官方技术blog中的例子 https://mysqlserverteam.com/hash-join-in-mysql-8/

SELECT given_name, country_name 
FROM persons JOIN countries ON persons.country_id = countries.country_id;

hash join 包含两个部分:build 构建阶段和probe 探测阶段

build 阶段

遍历驱动表，以join条件为key，查询需要的列作为value创建hash表。如何选择驱动表呢？标准就是比较参与join的两个表的结果集的大小，选择结果集小的表作为驱动表。

案例中对 countries.country_id 进行 hash 计算：hash(countries.country_id) 然后将值放入内存中 hash table 的相应位置。countries 表中的所有 country_id 都放入内存的hash 表中。

probe 探测阶段

build阶段完成后，MySQL逐行遍历被驱动表，然后计算 join条件的hash值，并在hash表中查找，如果匹配，则输出给客户端，否则跳过。所有内表记录遍历完，则整个过程就结束了。

如图所示 ,MySQL 对 persons 表中每行中的 join 字段的值进行 hash 计算；hash(persons.country_id)，拿着计算结果到内存 hash table 中进行查找匹配，找到记录就发送给 client。

整体上对驱动表遍历一次，被驱动表遍历1次(被驱动表有N行记录)。

hash join 构建hash表的大小是由参数join_buffer_size 控制的，实际生产环境中，如果驱动表的数据记录在内存中存不下怎么办？当然只能利用磁盘文件了。此时MySQL 要构建临时文件做hash join。此时的过程如下： build阶段会首先利用hash算将外表进行分区，并产生临时分片写到磁盘上；

然后在probe阶段，对于内表使用同样的hash算法进行分区。

由于使用分片hash函数相同，那么key相同(join条件相同)必然在同一个分片编号中。接下来，再对外表和内表中相同分片编号的数据进行内存hash join的过程，所有分片的内存hash join做完，整个join过程就结束了。

这种算法的代价是，对外表和内表分别进行了两次读IO，一次写IO。另外需要注意的是需要调大参数 join_buffer_size 和 open_files_limit .

测试实践

构建两个表t1,t3

CREATE TABLE `t1` (
  `f1` int NOT NULL,
  `f2` int NOT NULL,
  `c1` int DEFAULT '0',
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB ;

CREATE TABLE `t3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `f1` int NOT NULL,
  `f2` int NOT NULL,
  `c1` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_f12` (`f1`,`f2`,`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=321;

针对c1字段加上索引之后 ,再次执行sql，发现执行计划已经提示nest loop ,using index.

mysql> alter table t1 add key idx_c(c1);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t3 add key idx_c(c1);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行计划如下：

注意事项

1 推荐使用explain format=tree 来查看执行计划。

2 MySQL 8.0.18 支持使用hint: HASH_JOIN 和 NO_HASH_JOIN 和在 optimizer_switch 中设置 hash_join=on|off 控制是否使用hash join。但是在 8.0.19 和之后的版本中，这些参数不再起作用。

3 MySQL 8.0.18 之前 where条件必须是等值的，比如t1.c=t2.c ,在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)

Inner non-equi-join

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)

Semi-join

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)

Anti-join

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)

Left outer join

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)

Right outer join

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)