Mysql 8.0.18 ハッシュ結合テスト (推奨)

ハッシュ結合

ハッシュ結合は実行にインデックスを必要とせず、ほとんどの場合、現在のブロックネストループアルゴリズムよりも効率的です。

次のサンプルコードは、MySQL 8.0.18 ハッシュ結合テストを紹介しています。具体的な内容は次のとおりです。

information_schema.`COLUMNS` から select * として COLUMNS_hj テーブルを作成します。
INSERT INTO COLUMNS SELECT * FROM COLUMNS; -- 最後に 250,000 行を挿入します CREATE TABLE COLUMNS_hj2 as select * from information_schema.`COLUMNS`;

format=tree の説明
選択 
 COUNT(c1.権限)、
 SUM(c1.序数位置)
から
 列_hj c1、
 列_hj2 c2
どこ
 c1.テーブル名 = c2.テーブル名
かつ c1.列名 = c2.列名
グループ化
 c1.テーブル名、
 c1.列名
注文する
 c1.テーブル名、
 c1.列名;

ハッシュ結合の実行プランを表示するには、format=tree (8.0.16 の新機能) を使用する必要があります。

-> 並べ替え: <一時>.TABLE_NAME、<一時>.COLUMN_NAME
 -> <temporary> のテーブルスキャン
  -> 一時テーブルを使用して集計する
   -> 内部ハッシュ結合 (c1.`COLUMN_NAME` = c2.`COLUMN_NAME`)、(c1.`TABLE_NAME` = c2.`TABLE_NAME`) (コスト = 134217298.97 行 = 13421218)
    -> c1 のテーブルスキャン (コスト = 1.60 行 = 414619)
    -> ハッシュ
     -> c2 のテーブルスキャン (コスト = 347.95 行 = 3237)

join_buffer_size=1048576000 に設定します。

選択 
 COUNT(c1.権限)、
 SUM(c1.序数位置)
から
 列_hj c1、
 列_hj2 c2
どこ
 c1.テーブル名 = c2.テーブル名
かつ c1.列名 = c2.列名
グループ化
 c1.テーブル名、
 c1.列名
注文する
 c1.テーブル名、
 c1.列名;

約1.5秒です。

BNL をもう一度見てみましょう。まずインデックスを作成します (個別に最適化してから結果を比較するのが妥当です)。

テーブル columns_hj を変更し、インデックス idx_columns_hj を削除します。
テーブル columns_hj2 を変更し、インデックス idx_columns_hj2 を削除します。
columns_hj(table_name,column_name) にインデックス idx_columns_hj を作成します。
columns_hj2(table_name,column_name) にインデックス idx_columns_hj2 を作成します。

-> 並べ替え: <一時>.TABLE_NAME、<一時>.COLUMN_NAME
 -> <temporary> のテーブルスキャン
  -> 一時テーブルを使用して集計する
   -> ネストされたループ内部結合 (コスト = 454325.17 行 = 412707)
    -> フィルター: ((c2.`TABLE_NAME` は null ではありません) かつ (c2.`COLUMN_NAME` は null ではありません)) (コスト = 347.95 行 = 3237)
     -> c2 のテーブルスキャン (コスト = 347.95 行 = 3237)
    -> idx_COLUMNS_hj を使用して c1 のインデックス検索 (TABLE_NAME=c2.`TABLE_NAME`、COLUMN_NAME=c2.`COLUMN_NAME`) (コスト = 127.50 行 = 127)

約4.5秒です。ハッシュ結合の効果は依然として非常に良好であることがわかります。

MySQL のオプティマイザープロンプトについて不満があります。HASH_JOIN/NO_HASH_JOIN は効果がないようです。

hash_join に加えて、MySQL 8.0.3 で導入された SET_VAR オプティマイザヒントも非常に便利です。次のように、ステートメントレベルのパラメータを設定するために使用できます (Oracle はこれをサポートしており、MariaDB もこれをサポートしていたと記憶しています)。

mysql> select /*+ set_var(optimizer_switch='index_merge=off') set_var(join_buffer_size=4M) */ c_id from customer limit 1;

SET_VAR でサポートされる変数のリスト:

自動増分
自動増分オフセット
大きなテーブル
一括挿入バッファサイズ
デフォルトのtmpストレージエンジン
div_precision_increment
json の終了マーカー
等価範囲インデックスダイブ制限
外部キーチェック
グループ連結最大長
挿入ID
内部tmpメモリストレージエンジン
結合バッファサイズ
ロック待ちタイムアウト
最大エラー数
最大実行時間
最大ヒープテーブルサイズ
最大結合サイズ
ソートデータの最大長
ジオメトリ内の最大ポイント数
キーの最大シーク数
最大ソート長
最適化レベル
optimizer_search_depth 変数
オプティマイザースイッチ
範囲割り当てブロックサイズ
範囲オプティマイザの最大メモリサイズ
読み取りバッファサイズ
読み取りバッファサイズ
ソートバッファサイズ
sql_auto_is_null
sql_big_selects
SQLバッファ結果
SQLモード
sql_safe_updates
sql_select_limit
タイムスタンプ
tmp_テーブルサイズ
制限付きで更新可能なビュー
ユニークチェック
ウィンドウ使用高精度

要約する

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

以下もご興味があるかもしれません: