こんにちは。サイオステクノロジー OSS サポート担当 Y です。
今回は MySQL 8.0.18 で実装された Hash Join について検証してみました。(※以下の内容は MySQL 8.0.17/MySQL 8.0.18 にて検証しています。)
■はじめに
MySQL 8.0 では、様々な新機能が追加されています。
普段の業務では PostgreSQL を扱うことが多いのですが、最近 MySQL に Hash Join が実装されたという話を聞いたので、今回はこの Hash Join について検証してみました。
■MySQL 8.0.18 (Hash Join 実装) にて検証
それではさっそく検証してみます。
まずは、以下の様にテスト用のテーブル t1 と t2 を作成します。
mysql> SELECT version(); +-----------+ | version() | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE t1 (a VARCHAR(20)); Query OK, 0 rows affected (0.10 sec) mysql> mysql> CREATE TABLE t2 (a VARCHAR(20)); Query OK, 0 rows affected (0.02 sec) mysql>
作成したテーブル t1 にテスト用のデータ (ランダムに生成された 20文字の文字列) を 16384 レコードほど格納します。
mysql> INSERT INTO t1 VALUES (); mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; ～(中略)～ mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; Query OK, 8192 rows affected (0.11 sec) Records: 8192 Duplicates: 0 Warnings: 0 mysql> mysql> UPDATE t1 SET a = SUBSTRING(MD5(RAND()), 1, 20); Query OK, 16384 rows affected (0.81 sec) Rows matched: 16384 Changed: 16384 Warnings: 0 mysql>
t2 についても同様に、テスト用のデータ (ランダムに生成された 20文字の文字列) を 16384 レコードほど格納します。
mysql> INSERT INTO t2 SELECT * FROM t1; Query OK, 16384 rows affected (0.58 sec) Records: 16384 Duplicates: 0 Warnings: 0 mysql> mysql> UPDATE t2 SET a = SUBSTRING(MD5(RAND()), 1, 20); Query OK, 16384 rows affected (0.30 sec) Rows matched: 16384 Changed: 16384 Warnings: 0 mysql>
上記 2つのテーブル t1 と t2 について、以下の様なクエリ (EXPLAIN ANALYZE) を実行してみます。
mysql> EXPLAIN ANALYZE SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
+-------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t1.a = t2.a) (cost=26368857.25 rows=26367169) (actual time=42.485..52.619 rows=5 loops=1)
-> Table scan on t1 (cost=0.01 rows=16269) (actual time=0.016..10.830 rows=16384 loops=1)
-> Hash
-> Table scan on t2 (cost=1644.95 rows=16207) (actual time=0.051..10.163 rows=16384 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql>
すると、実行計画に “Inner hash join” と出力されており、Hash Join が実行されていることが確認できます。
また、(後述する MySQL 8.0.17 との比較のために) SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a を何度か実行してみました。
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 58687fa1fe4e3add4e77 | 58687fa1fe4e3add4e77 | | e59a678e56d5ca79e36f | e59a678e56d5ca79e36f | | d90b944a598c038d508f | d90b944a598c038d508f | | a7f6aaeaab20bc2b18ed | a7f6aaeaab20bc2b18ed | | ee2f5ba2a6ac7c73ad6f | ee2f5ba2a6ac7c73ad6f | +----------------------+----------------------+ 5 rows in set (0.05 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 58687fa1fe4e3add4e77 | 58687fa1fe4e3add4e77 | | e59a678e56d5ca79e36f | e59a678e56d5ca79e36f | | d90b944a598c038d508f | d90b944a598c038d508f | | a7f6aaeaab20bc2b18ed | a7f6aaeaab20bc2b18ed | | ee2f5ba2a6ac7c73ad6f | ee2f5ba2a6ac7c73ad6f | +----------------------+----------------------+ 5 rows in set (0.10 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 58687fa1fe4e3add4e77 | 58687fa1fe4e3add4e77 | | e59a678e56d5ca79e36f | e59a678e56d5ca79e36f | | d90b944a598c038d508f | d90b944a598c038d508f | | a7f6aaeaab20bc2b18ed | a7f6aaeaab20bc2b18ed | | ee2f5ba2a6ac7c73ad6f | ee2f5ba2a6ac7c73ad6f | +----------------------+----------------------+ 5 rows in set (0.05 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 58687fa1fe4e3add4e77 | 58687fa1fe4e3add4e77 | | e59a678e56d5ca79e36f | e59a678e56d5ca79e36f | | d90b944a598c038d508f | d90b944a598c038d508f | | a7f6aaeaab20bc2b18ed | a7f6aaeaab20bc2b18ed | | ee2f5ba2a6ac7c73ad6f | ee2f5ba2a6ac7c73ad6f | +----------------------+----------------------+ 5 rows in set (0.05 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 58687fa1fe4e3add4e77 | 58687fa1fe4e3add4e77 | | e59a678e56d5ca79e36f | e59a678e56d5ca79e36f | | d90b944a598c038d508f | d90b944a598c038d508f | | a7f6aaeaab20bc2b18ed | a7f6aaeaab20bc2b18ed | | ee2f5ba2a6ac7c73ad6f | ee2f5ba2a6ac7c73ad6f | +----------------------+----------------------+ 5 rows in set (0.05 sec) mysql>
上記の様に、実行時間はおよそ 0.05 sec ～ 0.10 sec という結果になりました。
■MySQL 8.0.17 (Hash Join 未実装のバージョン) にて検証
バージョンや作成するテストデータ (ランダムな文字列) の内容が異なっているため一概に比較することはできませんが、試しに MySQL 8.0.17 で同じ様なクエリを実行した際の実行時間について検証してみました。
先程と同じ様に、テスト用のテーブル t1 と t2 を作成します。
mysql> SELECT version(); +-----------+ | version() | +-----------+ | 8.0.17 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> CREATE TABLE t1 (a VARCHAR(20)); Query OK, 0 rows affected (0.09 sec) mysql> mysql> CREATE TABLE t2 (a VARCHAR(20)); Query OK, 0 rows affected (0.02 sec) mysql>
こちらも先程と同様に、各テーブルにテスト用のデータ (ランダムに生成された 20文字の文字列) を 16384 レコードほど格納します。
mysql> INSERT INTO t1 VALUES (); mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; ～(中略)～ mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; mysql> INSERT INTO t1 SELECT * FROM t1; Query OK, 8192 rows affected (0.11 sec) Records: 8192 Duplicates: 0 Warnings: 0 mysql> mysql> UPDATE t1 SET a = SUBSTRING(MD5(RAND()), 1, 20); Query OK, 16384 rows affected (0.78 sec) Rows matched: 16384 Changed: 16384 Warnings: 0 mysql> mysql> INSERT INTO t2 SELECT * FROM t1; Query OK, 16384 rows affected (0.49 sec) Records: 16384 Duplicates: 0 Warnings: 0 mysql> mysql> UPDATE t2 SET a = SUBSTRING(MD5(RAND()), 1, 20); Query OK, 16384 rows affected (0.32 sec) Rows matched: 16384 Changed: 16384 Warnings: 0 mysql>
この 2つのテーブル t1 と t2 に対して、SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a を何度か実行してみました。(MySQL 8.0.17 以前のバージョンでは Nested Loop Join が実行されます)
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 9c586d9f86508016474d | 9c586d9f86508016474d | | 060fabd3a56605b3a70d | 060fabd3a56605b3a70d | | 79b1d083ac1c59d28e2d | 79b1d083ac1c59d28e2d | | ce37e5158ee4f771df1b | ce37e5158ee4f771df1b | | 5b5179b3e10244ce8575 | 5b5179b3e10244ce8575 | | 3efce6a85b380fcc4b42 | 3efce6a85b380fcc4b42 | | e93708c8619d2724473c | e93708c8619d2724473c | | 842fffe86350c453936c | 842fffe86350c453936c | | bbefad95859615f548df | bbefad95859615f548df | | 476fb188116bc92e7599 | 476fb188116bc92e7599 | | 89d00cf0315f53c16337 | 89d00cf0315f53c16337 | +----------------------+----------------------+ 11 rows in set (39.67 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 9c586d9f86508016474d | 9c586d9f86508016474d | | 060fabd3a56605b3a70d | 060fabd3a56605b3a70d | | 79b1d083ac1c59d28e2d | 79b1d083ac1c59d28e2d | | ce37e5158ee4f771df1b | ce37e5158ee4f771df1b | | 5b5179b3e10244ce8575 | 5b5179b3e10244ce8575 | | 3efce6a85b380fcc4b42 | 3efce6a85b380fcc4b42 | | e93708c8619d2724473c | e93708c8619d2724473c | | 842fffe86350c453936c | 842fffe86350c453936c | | bbefad95859615f548df | bbefad95859615f548df | | 476fb188116bc92e7599 | 476fb188116bc92e7599 | | 89d00cf0315f53c16337 | 89d00cf0315f53c16337 | +----------------------+----------------------+ 11 rows in set (39.81 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 9c586d9f86508016474d | 9c586d9f86508016474d | | 060fabd3a56605b3a70d | 060fabd3a56605b3a70d | | 79b1d083ac1c59d28e2d | 79b1d083ac1c59d28e2d | | ce37e5158ee4f771df1b | ce37e5158ee4f771df1b | | 5b5179b3e10244ce8575 | 5b5179b3e10244ce8575 | | 3efce6a85b380fcc4b42 | 3efce6a85b380fcc4b42 | | e93708c8619d2724473c | e93708c8619d2724473c | | 842fffe86350c453936c | 842fffe86350c453936c | | bbefad95859615f548df | bbefad95859615f548df | | 476fb188116bc92e7599 | 476fb188116bc92e7599 | | 89d00cf0315f53c16337 | 89d00cf0315f53c16337 | +----------------------+----------------------+ 11 rows in set (40.05 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 9c586d9f86508016474d | 9c586d9f86508016474d | | 060fabd3a56605b3a70d | 060fabd3a56605b3a70d | | 79b1d083ac1c59d28e2d | 79b1d083ac1c59d28e2d | | ce37e5158ee4f771df1b | ce37e5158ee4f771df1b | | 5b5179b3e10244ce8575 | 5b5179b3e10244ce8575 | | 3efce6a85b380fcc4b42 | 3efce6a85b380fcc4b42 | | e93708c8619d2724473c | e93708c8619d2724473c | | 842fffe86350c453936c | 842fffe86350c453936c | | bbefad95859615f548df | bbefad95859615f548df | | 476fb188116bc92e7599 | 476fb188116bc92e7599 | | 89d00cf0315f53c16337 | 89d00cf0315f53c16337 | +----------------------+----------------------+ 11 rows in set (39.05 sec) mysql> mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; +----------------------+----------------------+ | a | a | +----------------------+----------------------+ | 9c586d9f86508016474d | 9c586d9f86508016474d | | 060fabd3a56605b3a70d | 060fabd3a56605b3a70d | | 79b1d083ac1c59d28e2d | 79b1d083ac1c59d28e2d | | ce37e5158ee4f771df1b | ce37e5158ee4f771df1b | | 5b5179b3e10244ce8575 | 5b5179b3e10244ce8575 | | 3efce6a85b380fcc4b42 | 3efce6a85b380fcc4b42 | | e93708c8619d2724473c | e93708c8619d2724473c | | 842fffe86350c453936c | 842fffe86350c453936c | | bbefad95859615f548df | bbefad95859615f548df | | 476fb188116bc92e7599 | 476fb188116bc92e7599 | | 89d00cf0315f53c16337 | 89d00cf0315f53c16337 | +----------------------+----------------------+ 11 rows in set (39.46 sec) mysql>
すると、実行時間はおよそ 40 sec 前後という結果になりました。
バージョンやテストデータ (ランダムな文字列) の内容に差異があるため一概に比較することはできませんが、Hash Join がより適切なクエリに関しては、MySQL 8.0.17 (Nested Loop Join) に比べて MySQL 8.0.18 (Hash Join) の方が性能が上がっている様です。
■最後に
今回は MySQL 8.0.18 で実装された Hash Join について検証してみました。
今回検証した Hash Join 以外にも、前回検証した CHECK 制約や CTE, GIS 等、MySQL 8.0 では様々な機能が追加/強化されており、MySQL を適用できる幅が更に広がっているのではないでしょうか。今後も MySQL のリリースに注目していきたいと思います。
