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

推薦する

CSS 透明ボーダー背景クリップマジック

この記事では、CSSの透明な境界線の背景クリップの素晴らしい使い方を主に紹介し、みんなと共有し、自分...

CSS 評価効果の星の例

何？何のスターコートですか？さて、もっとわかりやすくするために写真を見てみましょう。 よく見ると、パ...

Linux でファイル権限を変更する chmod コマンドの詳細な分析

Linux chmodコマンドを使用して、ターゲット ファイルにアクセス、読み取り、書き込み、または...

Mysql で group_concat の長さ制限を変更する方法

MySQL には、「group_concat」という関数があります。通常の使用では問題がないかもしれ...

Mysql クラシック高レベル/コマンドライン操作 (クイック) (推奨)

サーバーとデータベースの構築方法を学ぶ必要があるため、最近は SQL 言語を独学で学び始めました。デ...

MySQL のスロークエリの方法と例

1. はじめにスロークエリログを有効にすると、MySQL は指定された時間を超えるクエリステートメン...

MySQL 構成 SSL マスタースレーブ レプリケーション

MySQL5.6 SSLファイルの作成方法公式ドキュメント: https://dev.mysql.c...

MySQL にテーブルが存在するかどうかを確認し、それを一括で削除する方法

1. インターネットで長時間検索しましたが、判定表が存在するかどうかがわからなかったので、漠然と削除...

Reactはいくつかの方法でパラメータを渡します

目次親コンポーネントと子コンポーネント間でパラメータを渡すルーティングパラメータステータスの改善コン...

JavaScript を使用したコマンドライン アプリケーションの構築

目次1. ノードをインストールする2. Commander.jsをインストールする3. JavaSc...

ネイティブ JavaScript でショッピングカートを実装する

この記事では、ショッピングカートを実装するためのJavaScriptの具体的なコードを参考までに紹介...

MySQL パーティション テーブルに関するパフォーマンス バグ

目次2. pt-pmapを使用したスタック分析3. このコラムのボトルネックポイントの分析4. パー...

JavaScript を使用してページ要素のオフセットを取得/計算する方法

質問コントロールをクリックすると、コントロールの下にフローティング レイヤーが表示されます。通常の方...

VMware15.5 インストール Ubuntu20.04 グラフィック チュートリアル

1. インストール前の準備1. 公式ウェブサイトからUbuntu 20.04のイメージファイルを直接...

.html、.htm、.shtml、.shtm の違いと関連性について簡単に説明します。

ご存知のとおり、私たちが毎日閲覧する Web ページ、Web サイト、または Web ページには独自...