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

推薦する

CSS3はキングをマッチングさせるときにパーティクルアニメーション効果を実現します

コーディングをしていると、多くのことが同じ結末を迎えることに気づくでしょう。問題を解決する方法は何千...

MySQLクエリ文の実行プロセスを理解するための記事

序文要件を満たす特定のデータをデータベースから取得する必要があります。Select ABC FROM...

HTML CSS を使用して div またはテーブルを指定した位置に固定する方法

CSSコードコンテンツをクリップボードにコピー.bottomTable{背景色: rgb (249,...

vue.js を使用してドラッグ アンド ドロップ機能を実装する方法

序文ドラッグ アンド ドロップ機能を追加すると、プログラムがより自然でユーザーフレンドリーになります...

CSSアダプティブレイアウトは、サブ要素項目の全体的な中央揃えと内部項目の左揃えを実現します。

日常業務では、次のようなレイアウトに遭遇することがあります。親要素のフレーム (ブラウザのサイズに応...

JavaScriptのプロトタイプオブジェクトを徹底的に理解しましょう

目次1. プロトタイプとは何ですか? 1.1 関数プロトタイプオブジェクト1.2 コンストラクタを使...

CSSコンテンツ属性の具体的な使用法

コンテンツ属性は通常、::before および ::after 疑似要素で使用され、疑似要素のコンテ...

Podmanはコンテナを自動的に起動し、Dockerと比較します

目次1. podmanの紹介2. Dockerと比較した利点3. 互換性4. バックグラウンド サー...

CSS の高さの崩壊問題の解決

1. 崩壊度が高いドキュメント フローでは、親要素の高さはデフォルトで子要素によって拡張されます。つ...

レスポンシブなアコーディオン効果を実現するための CSS3 の詳細な説明

最近、外国人が CSS3 を使用してアコーディオン効果を実現しているビデオを見たので、自分で学習した...

ベースリンクタグの使用の紹介ベース

<br />リンクをクリックすると、ポップアップ表示される Web ページ アドレスは ...

iframeリフレッシュ方式の方が便利

iframeを更新する方法1. 更新するには、JavaScriptのdocument.fr.loca...

Nginx がリクエストを処理する際のマッチングルールの詳細な分析

nginx はリクエストを受信すると、まず server_name でサーバーを照合し、次にサーバー...

MySQL トランザクションの詳細

目次導入取引の4つの特徴トランザクション分離レベル確認するMVCC現在の読書スナップショット読み取り...

Windows に MySQL 8.0.16 をインストールする手順とエラーの解決方法

1. はじめに: mysql8以降は、これまでよく使われていたバージョンと比べてかなり変更点が大きい...