天天看點
傳回

MySQL 8.0 新特性：引人注目的哈希連接配接（Hash Join）

作者：不剪發的Tony老師

blog.csdn.net/horses/article/details/102690076

MySQL 開發組于 2019 年 10 月 14 日 正式釋出了 MySQL 8.0.18 GA 版本，帶來了一些新特性和增強功能。其中最引人注目的莫過于多表連接配接查詢支援 hash join 方式了。我們先來看看官方的描述：

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

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 将會在磁盤中執行操作。搜尋網際網路架構師公衆号，回複“2T“，送你一份Java架構視訊

需要注意的是，如果 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 秒。是以在我們這個場景中的測試結果如下：

MySQL 8.0 新特性：引人注目的哈希連接配接（Hash Join）

再增加一個 Oracle 12c 中無索引時 hash join 結果：1.282 s。

再增加一個 PostgreSQL 11.5 中無索引時 hash join 結果：6.234 s。

再增加一個 SQL 2017 中無索引時 hash join 結果：5.207 s。

上一篇: 決策樹（CART）
下一篇: 決策樹（三）：CART算法