SIOS Tech. LabSIOS Tech. Lab | エンジニアのタメになるOSS・クラウド・認証の技術トピックス
新着記事

MySQL 8.0.18 の Hash Join について検証してみた

2019-11-19 OSS 0
◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
【5/21開催】Azure OpenAI ServiceによるRAG実装ガイドを公開しました
生成AIを活用したユースケースで最も一番熱いと言われているRAGの実装ガイドを公開しました。そのガイドの紹介をおこなうイベントです!!
https://tech-lab.connpass.com/event/315703/

こんにちは。サイオステクノロジー 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 のリリースに注目していきたいと思います。

アバター画像
About サイオステクノロジーの中の人 41 Articles
サイオステクノロジーで働く中の人です。
Twitter YouTube
ご覧いただきありがとうございます！ この投稿はお役に立ちましたか？

役に立った 役に立たなかった

0人がこの投稿は役に立ったと言っています。


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー＆Facebookページにいいねをお願い致します！


SIOS Tech Lab

＞＞ 雑誌等の執筆依頼を受付しております。
　　 ご希望の方はお気軽にお問い合わせください！

Related Articles

Be the first to comment

Leave a Reply

Your email address will not be published.


*


Copyright © 2024 | MH Magazine WordPress Theme by MH Themes

質問はこちら 閉じる