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

推薦する

Vue Notepadの例の詳細な説明

この記事の例では、メモ帳機能を実装するためのVueの具体的なコードを参考までに共有しています。具体的...

MySQL テーブル結合クエリでグループ化と重複排除を実装する例

目次ビジネスロジックデータテーブル構造クエリロジックSQL スクリプトスクリプトの説明ビジネスロジッ...

Docker に MySQL と MariaDB をインストールする方法

MySQLとMariaDBの関係MariaDB データベース管理システムは MySQL のブランチで...

MySQL 5.7を完全にアンインストールするための詳細な手順

この記事は主に、MySQLを再インストールする際のクリーンでないアンインストールのさまざまな問題をま...

MySQL クエリ キャッシュとバッファ プール

1. キャッシュ - クエリキャッシュ次の図は、MySQL 公式サイトから提供されています: MyS...

Confluence と jira-software を Docker にデプロイする方法

バージョン: セントロス==7.2 jdk==1.8 合流==6.15.4 jira-ソフトウェア=...

Vue は Tencent Map を統合して API を実装します (デモ付き)

目次執筆の背景プロジェクトの説明事前準備注記執筆の背景以前のプロジェクトではTencent Maps...

Linux システムでの gcc コマンドの使用法の詳細な説明

目次1. 前処理2. コンパイル3. コンパイル4. リンク1. gccのインストール（Ubuntu...

初心者でもウェブサイトアイコンを作成する手順をすぐに学べます

<br />オリジナルURL: http://www.lxdong.com/post/m...

JS WebSocket 切断理由とハートビートの仕組みの詳しい説明

1. 切断理由WebSocket が切断される理由は多数あります。WebSocket が切断されたと...

レスポンシブ Web デザイン手法を実装し、ウォーターフォール モデルに別れを告げる 5 つのステップ (グラフィック チュートリアル)

次の Web デザイン プロジェクトはレスポンシブにする必要があると上司をようやく納得させることがで...

初心者のためのMySQL外部キーの設定方法

目次外部キーの役割mysql 外部キー設定方法要約する外部キーの役割データの一貫性、整合性を維持し、...

DIVマスクを使用して、マウスでチェックボックスを直接チェックすることが無効である問題を解決します

フロントエンドの開発過程で、チェックボックスが必要な状況が発生しました。ユーザー操作の利便性を考慮し...

ラベルタグを使用してテキストをクリックしてラジオボタンを選択します

<label> タグは、入力要素のラベル (タグ) を定義します。ラベル要素はユーザーに...

シャドウソックスを使用してLAN透過ゲートウェイを構築する

目次dnsmasq をインストールして設定するChinaDNS をインストールして設定するshado...