MySQL 8.0 の新機能: ハッシュ結合

MySQL 8.0 の新機能: ハッシュ結合

MySQL 開発チームは、2019 年 10 月 14 日に MySQL 8.0.18 GA バージョンを正式にリリースし、いくつかの新機能と機能強化をもたらしました。最も注目すべき機能は、マルチテーブル結合クエリがハッシュ結合モードをサポートしていることです。まずは公式の説明を見てみましょう。

MySQL は、内部結合クエリにハッシュ結合メソッドを実装します。たとえば、MySQL 8.0.18 以降では、次のクエリで結合クエリにハッシュ結合を使用できます。

選択* 
  t1から 
  t2に参加 
    t1.c1=t2.c1 の場合;

ハッシュ結合にはインデックスのサポートは必要ありません。ほとんどの場合、ハッシュ結合は、インデックスなしの同等の結合に対して、以前のブロック ネスト ループ アルゴリズムよりも効率的です。次のステートメントを使用して、3 つのテスト テーブルを作成します。

テーブル t1 (c1 INT、c2 INT) を作成します。
テーブル t2 (c1 INT、c2 INT) を作成します。
テーブル t3 (c1 INT、c2 INT) を作成します。

実行プラン内のハッシュ結合を確認するには、EXPLAIN FORMAT=TREE コマンドを使用します。次に例を示します。

mysql> EXPLAIN FORMAT=TREE
  -> 選択* 
  -> t1から 
  -> t2 に参加 
  -> オン t1.c1=t2.c1\G
************************** 1. 行 ****************************
EXPLAIN: -> 内部ハッシュ結合 (t2.c1 = t1.c1) (コスト = 0.70 行 = 1)
  -> t2 のテーブルスキャン (コスト = 0.35 行 = 1)
  -> ハッシュ
    -> t1 のテーブルスキャン (コスト = 0.35 行 = 1)

ノード内のハッシュ結合を確認するには、EXPLAIN コマンドの FORMAT=TREE オプションを使用する必要があります。さらに、EXPLAIN ANALYZE コマンドでは、ハッシュ結合の使用情報も表示できます。これもこのバージョンで追加された新機能です。

複数のテーブル間の等価結合を使用するクエリも、この方法で最適化されます。たとえば、次のクエリ:

選択* 
  t1から
  t2に参加 
    オン (t1.c1 = t2.c1 かつ t1.c2 < t2.c2)
  t3に参加 
    オン（t2.c1 = t3.c1）;

上記の例では、結合操作後に、その他の非等価結合条件がフィルターとして使用されます。これは EXPLAIN FORMAT=TREE コマンドの出力で確認できます。

mysql> EXPLAIN FORMAT=TREE
  -> 選択* 
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1 かつ t1.c2 < t2.c2)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1)\G
************************** 1. 行 ****************************
EXPLAIN: -> 内部ハッシュ結合 (t3.c1 = t1.c1) (コスト = 1.05 行 = 1)
  -> t3 のテーブルスキャン (コスト = 0.35 行 = 1)
  -> ハッシュ
    -> フィルター: (t1.c2 < t2.c2) (コスト=0.70 行=1)
      -> 内部ハッシュ結合 (t2.c1 = t1.c1) (コスト = 0.70 行 = 1)
        -> t2 のテーブルスキャン (コスト = 0.35 行 = 1)
        -> ハッシュ
          -> t1 のテーブルスキャン (コスト = 0.35 行 = 1)

上記の出力から、複数の等価結合条件を含むクエリでは、複数のハッシュ結合接続も使用できることがわかります。

ただし、いずれかの接続ステートメント (ON) が同等の接続条件を使用しない場合、ハッシュ結合接続方法は使用されません。例えば：

mysql> EXPLAIN FORMAT=TREE
  -> 選択* 
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 < t3.c1)\G
************************** 1. 行 ****************************
EXPLAIN: <イテレータ実行プログラムでは実行できません>

このとき、より遅いblock nested loop接続アルゴリズムが使用されます。これは、インデックスのない MySQL 8.0.18 以前と同じです。

mysql>説明
  -> 選択* 
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 < t3.c1)\G       
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t1
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 1
   フィルター: 100.00
    追加: NULL
************************** 2. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t2
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 1
   フィルター: 100.00
    追加: where の使用; join buffer の使用 (ブロックネストループ)
************************** 3. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t3
  パーティション: NULL
     タイプ: すべて
可能なキー: NULL
     キー: NULL
   キー長さ: NULL
     参照: NULL
     行数: 1
   フィルター: 100.00
    追加: where の使用; join buffer の使用 (ブロックネストループ)

ハッシュ結合は、クエリ条件が指定されていない場合、カルテシアン積にも適用されます。次に例を示します。

mysql> EXPLAIN FORMAT=TREE
  -> 選択*
  -> t1から
  -> t2 に参加
  -> t1.c2 > 50\G の場合
************************** 1. 行 ****************************
EXPLAIN: -> 内部ハッシュ結合 (コスト=0.70 行=1)
  -> t2 のテーブルスキャン (コスト = 0.35 行 = 1)
  -> ハッシュ
    -> フィルター: (t1.c2 > 50) (コスト=0.35 行=1)
      -> t1 のテーブルスキャン (コスト = 0.35 行 = 1)

デフォルトでは、MySQL は可能な場合は常にハッシュ結合を使用します。同時に、ハッシュ結合を使用するかどうかを制御する 2 つのメソッドが提供されます。

グローバルまたはセッション レベルで、サーバー システム変数optimizer_switchをhash_join=onまたはhash_join=offに設定します。デフォルトはhash_join=onです。

ステートメント レベルで特定の結合に対してオプティマイザー ヒント HASH_JOIN または NO_HASH_JOIN を指定します。

ハッシュ結合に許可されるメモリの量は、システム変数join_buffer_sizeによって制御できます。ハッシュ結合では、この変数で設定された量を超えるメモリは使用されません。ハッシュ結合に必要なメモリがこのしきい値を超えると、MySQL はディスク上で操作を実行します。ハッシュ結合がメモリ内で完了できず、開いているファイルの数がシステム変数open_files_limitの値を超えると、結合操作が失敗する可能性があることに注意してください。この問題を解決するには、次のいずれかの方法を使用します。

hash joinメモリ内で完了できるように、 join_buffer_sizeの値を増やします。

n_files_limitの値を増やします。

次に、 hash joinとblock nested loopのパフォーマンスを比較します。まず、t1、t2、t3 に対してそれぞれ 1,000,000 件のレコードが生成されます。

join_buffer_size=2097152000 を設定します。
@@cte_max_recursion_depth = 99999999 に設定します。
t1に挿入
-- t2 に挿入
-- t3 に挿入
再帰t AS（
 1 を c1 として、1 を c2 として選択します。
 ユニオンオール
 t.c1 + 1、t.c1 * 2 を選択
  から
  t.c1 < 1000000の場合
）
選択*
 t から;

インデックスなしのハッシュ結合:

mysql> EXPLAIN ANALYZE
  -> 選択カウント(*)
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1)\G
************************** 1. 行 ****************************
EXPLAIN: -> 集計: count(0) (実際の時間=22993.098..22993.099 行=1 ループ=1)
  -> 内部ハッシュ結合 (t3.c1 = t1.c1) (コスト = 9952535443663536.00 行 = 9952435908880402) (実際の時間 = 14489.176..21737.032 行 = 1000000 ループ = 1)
    -> t3 のテーブルスキャン (コスト = 0.00 行 = 998412) (実際の時間 = 0.103..3973.892 行 = 1000000 ループ = 1)
    -> ハッシュ
      -> 内部ハッシュ結合 (t2.c1 = t1.c1) (コスト = 99682753413.67 行 = 99682653660) (実際の時間 = 5663.592..12236.984 行 = 1000000 ループ = 1)
        -> t2 のテーブルスキャン (コスト = 0.01 行 = 998412) (実際の時間 = 0.067..3364.105 行 = 1000000 ループ = 1)
        -> ハッシュ
          -> t1 のテーブルスキャン (コスト = 100539.40 行 = 998412) (実際の時間 = 0.133..3395.799 行 = 1000000 ループ = 1)

セット1列目（23.22秒）

mysql> SELECT COUNT(*)
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1);
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット1列目（12.98秒）

実際の走行には12.98秒かかりました。このとき、ブロックネストループを使用すると、

mysql> EXPLAIN FORMAT=TREE
  -> SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1)\G
************************** 1. 行 ****************************
EXPLAIN: <イテレータ実行プログラムでは実行できません>

セット内の 1 行 (0.00 秒)

SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
 t1から
 t2に参加 
  オン（t1.c1 = t2.c1）
 t3に参加 
  オン（t2.c1 = t3.c1）;

EXPLAIN はハッシュ結合が使用できないことを示します。クエリは結果が生成されずに数十分間実行され、ネストされたループ (1,000,000 の 3 乗) を継続的に実行していたため、CPU の 1 つが最大 100% 使用されました。

インデックス付きのブロックネストされたループメソッドを見て、インデックスを追加してみましょう。

mysql> t1(c1) にインデックス idx1 を作成します。
クエリは正常、影響を受けた行は 0 行 (7.39 秒)
レコード: 0 重複: 0 警告: 0
mysql> t2(c1) に idx2 インデックスを作成します。
クエリは正常、影響を受けた行は 0 行 (6.77 秒)
レコード: 0 重複: 0 警告: 0
mysql> t3(c1) に idx3 インデックスを作成します。
クエリは正常、影響を受けた行は 0 行 (7.23 秒)
レコード: 0 重複: 0 警告: 0

実行プランを表示し、同じクエリを実行します。

mysql> EXPLAIN ANALYZE
  -> 選択カウント(*)
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1)\G
************************** 1. 行 ****************************
EXPLAIN: -> 集計: count(0) (実際の時間=47684.034..47684.035 行=1 ループ=1)
  -> ネストされたループの内部結合 (コスト = 2295573.22 行 = 998412) (実際の時間 = 0.116..46363.599 行 = 1000000 ループ = 1)
    -> ネストされたループの内部結合 (コスト = 1198056.31 行 = 998412) (実際の時間 = 0.087..25788.696 行 = 1000000 ループ = 1)
      -> フィルター: (t1.c1 は null ではありません) (コスト = 100539.40 行 = 998412) (実際の時間 = 0.050..5557.847 行 = 1000000 ループ = 1)
        -> idx1 を使用して t1 のインデックス スキャン (コスト = 100539.40 行 = 998412) (実際の時間 = 0.043..3253.769 行 = 1000000 ループ = 1)
      -> idx2 を使用して t2 でインデックス検索 (c1=t1.c1) (コスト = 1.00 行 = 1) (実際の時間 = 0.012..0.015 行 = 1 ループ = 1000000)
    -> idx3 を使用して t3 のインデックス検索 (c1=t1.c1) (コスト = 1.00 行 = 1) (実際の時間 = 0.012..0.015 行 = 1 ループ = 1000000)

セット1列目（47.68秒）

mysql> SELECT COUNT(*)
  -> t1から
  -> t2 に参加 
  -> オン (t1.c1 = t2.c1)
  -> t3 に参加 
  -> オン (t2.c1 = t3.c1);
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット1列目（19.56秒）

実際の走行には19.56秒かかりました。したがって、このシナリオでのテスト結果は次のようになります。

ハッシュ結合（インデックスなし）ブロックネストループ（インデックスなし）ブロックネストループ（インデックス付き）
12.98秒返却されません19.56秒

Oracle 12c でインデックスなしで別のハッシュ結合結果を追加します: 1.282 秒。

以下は、PostgreSQL 11.5 でのインデックスなしの別のハッシュ結合結果です: 6.234 秒。

SQL 2017 でインデックスなしで別のハッシュ結合結果を追加します: 5.207 秒。

要約する

以上が、MySQL 8.0 の新機能であるハッシュ結合についてご紹介しました。お役に立てれば幸いです。ご質問がございましたら、メッセージを残していただければ、すぐに返信させていただきます。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

以下もご興味があるかもしれません:
  • MySQL 8.0 の新機能 - チェック制約の紹介
  • MySQL 8.0の新機能、隠しフィールドの詳細な説明
  • MySQL 8.0 でのチェック制約の実装
  • MySQL 8.0 の新機能の分析 - トランザクション データ ディクショナリとアトミック DDL
  • MySQL 8.0 の新機能の落とし穴と解決策についての簡単な説明 (要約)
  • MySQL 8.0 の新機能: アトミック DDL ステートメントのサポート
  • MySQL 8.0 の新しいリレーショナル データベース機能の詳細な説明
  • IDEA が MySQL ポート番号占有に接続できない問題の解決方法
  • MySQL を使用してポート 3306 を開いたり変更したり、Ubuntu/Linux 環境でアクセス許可を開く
  • phpstudy をインストールした後に MySQL を起動できない問題に対する完璧なソリューション (元のデータベースを削除する必要はなく、設定を変更する必要もなく、ポートを変更する必要もありません) 直接共存
  • LinuxでMySQLのリモートアクセス権を有効にし、ファイアウォールでポート3306を開きます。
  • MySQL 8.0 の新機能 - 管理ポートの使用の概要

<<:  Dockerでコンテナを作成するときのディレクトリ権限

>>:  カレンダー効果を実現するための Bootstrap+JQuery

推薦する

Linux での MySQL 8.0.11 のインストールに関するチュートリアル

1. 公式サイトにアクセスしてインストールパッケージをダウンロードしますダウンロードリンク: クリッ...

Dockerを使用してコンテナリソースを制限する方法

覗き見の問題サーバーでは、IIS サービスが複数のサイトを展開していると仮定すると、サイトの 1 つ...

Webpack5-react スキャフォールディングをゼロから構築するための実装手順 (ソースコード付き)

目次ウェブパック5公式スタート建築ガイド構築を開始する依存する準備が完了したら、プロジェクトの構築を...

CSS3アニメーションジャミングソリューションについての簡単な説明

なぜ詰まっているのでしょうか?言及しなければならない前提があります。フロントエンド開発者は皆、ブラウ...

nginx設定ファイルの場所を見つける方法の詳細な説明

よく知らないサーバーの場合や、かなり前にインストールした場所を忘れてしまった場合、構成ファイルの場所...

MySQL 8.0.12 winx64 詳細なインストールチュートリアル

この記事では、MySQL 8.0.12のインストールチュートリアルを参考までに紹介します。具体的な内...

mysql57サービスが突然消えた問題をすぐに解決する

1つ、 G:\MySQL\MySQL Server 5.7\bin> mysqld --ini...

MySQLデータベースのスケジュールバックアップを実装する方法

1. シェルスクリプトを作成する vim バックアップdb.sh 次のようにスクリプトを作成します。...

React ページ ターナーの実装 (フロント エンドとバックエンドを含む)

目次フロントエンド上記のアイデアに従って、ページめくり機能を設計して記述します。バックエンド（Jav...

HTML テーブルタグチュートリアル (24): 行の水平方向の配置属性 ALIGN

水平方向では、行の配置を左、中央、右に設定できます。基本的な構文<TR ALIGN="...

Vue3 の参照と参照の詳細

エディターは、Vue3のデータの関連する問題も共有します。次のような例を見てみましょう。 Vue.c...

Webスキル: 複数のIEバージョンの共存ソリューション IETester

私のおすすめ複数のIEバージョンの共存のためのソリューション以前に IE6、IE7、IE8 の共存に...

Java は Apache.POI を使用して HSSFWorkbook を Excel にエクスポートします

Apache.POI の HSSFWorkbook を使用して Excel にエクスポートします。具...

MySQL の異常なエラー ERROR: 2002 を解決する方法

最近、MySQL の起動中にエラーが発生しました。エラー メッセージは次のとおりです。 エラー 20...

mysql5.7 リモート アクセス設定

mysql5.7 でリモート アクセスを設定することは、ユーザーを作成して権限を付与するだけでアクセ...