其他
MySQL8.0发布,你熟悉又陌生的Hash Join?
对于大数据量的表关联,HJ(Hash Join)速度将明显比NL(Nested Loop)快很多
在内存中处理
必要情况下,会使用磁盘空间
用于内连接,可扩展到外连接、半连接和反连接
替换查询计划中的Block Nested Loop
可以通过HINT强制SQL走HJ或者NL
for (r in R) {
for (s in S) {
if (r satisfy condition s) {
output <r, s>;
}
}
}
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;
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)
SELECT *
FROM t1
JOIN t2
ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3
ON (t2.c1 = t3.c1);
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> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: <notexecutablebyiteratorexecutor>
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> 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的龟速Join,然后项目经理让优化时候再打开HJ查询岂不是美滋滋?你说HJ跟你有没有关系
两种方式任选其一:
全局设置服务器系统变量hash_join=on
在SQL中使用Hint指定 HASH_JOIN 或者 NO_HASH_JOIN
更为牛逼的是,HJ自身不受索引的影响,意思就是即使没有进行索引优化,HJ依然速度很快。
下面是我找了一个网上其他人的测试,展示一下HJ的强大。
首先分别为 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;
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: <notexecutablebyiteratorexecutor>
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);
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 isnotnull) (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 秒。所以在我们这个场景中的测试结果如下:
-- MORE | 更多精彩文章 --
文章不错?点个【在看】吧! 👇