MySQL 開發(fā)組于 2019 年 10 月 14 日 正式發(fā)布了 MySQL 8.0.18 GA 版本，帶來了一些新特性和增強(qiáng)功能。其中最引人注目的莫過于多表連接查詢支持 hash join 方式了。我們先來看看官方的描述：
MySQL 實(shí)現(xiàn)了用于內(nèi)連接查詢的 hash join 方式。例如，從 MySQL 8.0.18 開始以下查詢可以使用 hash join 進(jìn)行連接查詢：
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
Hash join 不需要索引的支持。大多數(shù)情況下，hash join 比之前的 Block Nested-Loop 算法在沒有索引時(shí)的等值連接更加高效。使用以下語句創(chuàng)建三張測(cè)試表：
CREATE TABLE t1 (c1 INT, c2 INT);CREATE TABLE t2 (c1 INT, c2 INT);CREATE TABLE t3 (c1 INT, c2 INT);
使用EXPLAIN FORMAT=TREE命令可以看到執(zhí)行計(jì)劃中的 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 選項(xiàng)才能看到節(jié)點(diǎn)中的 hash join。另外，EXPLAIN ANALYZE命令也可以顯示 hash join 的使用信息。這也是該版本新增的一個(gè)功能。
多個(gè)表之間使用等值連接的的查詢也會(huì)進(jìn)行這種優(yōu)化。例如以下查詢：
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);
在以上示例中，任何其他非等值連接的條件將會(huì)在連接操作之后作為過濾器使用。可以通過EXPLAIN FORMAT=TREE命令的輸出進(jìn)行查看：
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)
從以上輸出同樣可以看出，包含多個(gè)等值連接條件的查詢也可以（會(huì)）使用多個(gè) hash join 連接。
但是，如果任何連接語句（ON）中沒有使用等值連接條件，將不會(huì)采用 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>
此時(shí)，將會(huì)采用性能更慢的 block nested loop 連接算法。這與 MySQL 8.0.18 之前版本中沒有索引時(shí)的情況一樣：
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: ALLpossible_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: ALLpossible_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: ALLpossible_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 連接同樣適用于不指定查詢條件時(shí)的笛卡爾積（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)
默認(rèn)配置時(shí)，MySQL 所有可能的情況下都會(huì)使用 hash join。同時(shí)提供了兩種控制是否使用 hash join 的方法：
在全局或者會(huì)話級(jí)別設(shè)置服務(wù)器系統(tǒng)變量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 選項(xiàng)。默認(rèn)為 hash_join=on。
在語句級(jí)別為特定的連接指定優(yōu)化器提示 HASH_JOIN 或者 NO_HASH_JOIN。
可以通過系統(tǒng)變量 join_buffer_size 控制 hash join 允許使用的內(nèi)存數(shù)量；hash join 不會(huì)使用超過該變量設(shè)置的內(nèi)存數(shù)量。如果 hash join 所需的內(nèi)存超過該閾值，MySQL 將會(huì)在磁盤中執(zhí)行操作。需要注意的是，如果 hash join 無法在內(nèi)存中完成，并且打開的文件數(shù)量超過系統(tǒng)變量 open_files_limit 的值，連接操作可能會(huì)失敗。為了解決這個(gè)問題，可以使用以下方法之一：
增加 join_buffer_size 的值，確保 hash join 可以在內(nèi)存中完成。
增加 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 t3WITH 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)
實(shí)際運(yùn)行花費(fèi)了 12.98 秒。這個(gè)時(shí)候如果使用 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。查詢跑了幾十分鐘也沒有出結(jié)果，其中一個(gè) CPU 使用率到了 100%；因?yàn)橐恢痹趫?zhí)行嵌套循環(huán)（1000000 的 3 次方）。
再看有索引時(shí)的 block nested loop 方法，增加索引：
mysql> CREATE index idx1 ON t1(c1);Query OK, 0 rows affected (7.39 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE index idx2 ON t2(c1);Query OK, 0 rows affected (6.77 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> CREATE index idx3 ON t3(c1);Query OK, 0 rows affected (7.23 sec)Records: 0 Duplicates: 0 Warnings: 0
查看執(zhí)行計(jì)劃并運(yùn)行相同的查詢語句：
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)
實(shí)際運(yùn)行花費(fèi)了 19.56 秒。所以在我們這個(gè)場(chǎng)景中的測(cè)試結(jié)果如下：
| Hash Join（無索引） | Block Nested Loop（無索引） | Block Nested Loop（有索引） |
|---|---|---|
| 12.98 s | 未返回 | 19.56 s |
再增加一個(gè) Oracle 12c 中無索引時(shí) hash join 結(jié)果：1.282 s。
再增加一個(gè) PostgreSQL 11.5 中無索引時(shí) hash join 結(jié)果：6.234 s。
再增加一個(gè) SQL 2017 中無索引時(shí) hash join 結(jié)果：5.207 s。
總結(jié)
以上所述是小編給大家介紹的MySQL 8.0 新特性之哈希連接（Hash Join）,希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)武林網(wǎng)網(wǎng)站的支持！如果你覺得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
新聞熱點(diǎn)
疑難解答
圖片精選
