クエリのパフォーマンスを最適化するために、LEFT JOINをRIGHT JOINに変更できるシナリオ - AnalyticDB

LEFT JOINは、左側のテーブルからすべてのレコードを返し、右側のテーブルから一致するレコードを返す外部結合の一種です。ハッシュ結合アルゴリズムは、右テーブルを使用してハッシュテーブルを構築し、左joinは左テーブルと右テーブルを並べ替えません。このように、右側のテーブルに大量のデータがあると、実行速度が遅くなり、メモリが過剰に消費されるなどの問題が発生します。このトピックでは、例を使用して、LEFT JOINをRIGHT JOINに置き換えることができるシナリオについて説明します。

背景情報

デフォルトでは、AnalyticDB for MySQLはハッシュ結合を使用してテーブルを結合します。ハッシュ結合アルゴリズムは、正しいテーブルを使用してハッシュテーブルを構築します。これは、大量のリソースを消費する。内部結合とは異なり、LEFT JOINとRIGHT JOINを含む外部結合は、左右のテーブルの順序を交換できません。右側のテーブルに大量のデータが含まれているシナリオでは、実行が遅くなり、メモリが消費される可能性があります。極端なシナリオでは、クラスタのパフォーマンスが影響を受けるか、Out of Memory Pool size pre calエラーメッセージが返されます。このような場合、このトピックで提供されている最適化方法を使用して、リソース消費を削減できます。

シナリオ

LEFT JOINをRIGHT JOINに変更するには、SQLステートメントを変更するか、ステートメントの前にヒントを追加します。このようにして、元の左テーブルが右テーブルに変更され、ハッシュテーブルが構築されます。新しい右側のテーブルに大量のデータが含まれている場合、パフォーマンスに影響があります。したがって、左のテーブルが小さく、右のテーブルが大きい場合は、左のJOINを右のJOINに変更することをお勧めします。

テーブル間のサイズ比較は、結合された列とクラスターリソースに関連しています。 EXPLAIN ANALYZEステートメントを実行して実行計画のパラメーターを照会し、PeakMemoryやWallTimeなどのパラメーターの変更に基づいてRIGHT JOINを使用できるかどうかを判断できます。詳細については、「EXPLAINおよびEXPLAIN ANALYZEコマンドを使用した実行計画の分析」をご参照ください。

変更方法

次のいずれかの方法を使用して、LEFT JOINをRIGHT JOINに変更できます。

SQL文を変更します。たとえば、a.col1 = b.col2の左結合bをb右結合a.col1 = b.col2に変更します。
SQL文の前にヒントを追加して、オプティマイザを指定します。オプティマイザは、左右のテーブルの推定サイズに基づいて、LEFT JOINをRIGHT JOINに変更するかどうかを決定します。
- V3.1.8以降のAnalyticDB For MySQLクラスターの場合、テーブル変更機能はデフォルトで有効になっています。この機能が無効になっている場合は、SQL文の前に次のヒントを追加して機能を有効にします。/* + O_CBO_RULE_SWAP_OUTER_JOIN=true */
- V3.1.8より前のAnalyticDB For MySQLクラスターの場合、テーブル変更機能はデフォルトで無効になっています。機能を有効にするには、SQL文の前に次のヒントを追加します。/* + LEFT_TO_RIGHT_ENABLED=true */

説明

クラスターのマイナーバージョンをクエリする方法については、AnalyticDB for MySQLクラスターのバージョンを照会するにはどうすればよいですか? クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。

例

この例では、nationは25行の小さなテーブルで、customerは15,000,000行の大きなテーブルです。 EXPLAIN ANALYZEステートメントを実行して、LEFT JOINを含むSQLステートメントの実行プランを照会できます。

分析を説明


選択
  カウント (*)
から
  国t1
  左JOINカスタマーt2 ON t1.n_nationkey = t2.c_nationkey

次のクエリ結果は、結合が実行されるステージ2の実行プランを示しています。 LEFT JOIN演算子には、次の情報が含まれます。

PeakMemory: 515MB (93.68%) 、WallTime: 4.34s (43.05%): ピークメモリ使用量は93.68% です。これは、LEFT JOINがSQL文のパフォーマンスのボトルネックであることを示しています。
左 (プローブ) 入力avg.: 0.52行; 右 (ビルド) 入力avg.: 312500.00行: 右のテーブルは大きなテーブルで、左のテーブルは小さなテーブルです。

このシナリオでは、LEFT JOINをRIGHT JOINに変更して、SQL文を最適化できます。

Fragment 2 [ハッシュ]
    出力: 48行 (432B) 、PeakMemory: 516MB、WallTime: 6.52us、入力: 15000025行 (200.27MB); タスクごと: avg.: 2500004.17 std.de v.: 2410891.74
    出力レイアウト: [count_0_2]
    出力パーティション：SINGLE[]
    集約 (PARTIAL)
    │ 出力: [count_0_2:bigint]
    │ 見積もり: {rows: ? (?)}
    │ 出力: 96行 (864B) 、ピークメモリ: 96B (0.00%) 、WallTime: 88.21ms (0.88%)
    │ count_2 := count(*)
    └ ─ LEFT Join[('n_nationkey' = 'c_nationkey')][$hashvalue, $hashvalue_0_4]
       │ 出力: []
       │ 見積もり: {rows: 15000000 (0B)}
       │ 出力: 30000000行 (200.27MB) 、ピークメモリ: 515MB (93.68%) 、WallTime: 4.34秒 (43.05%)
       │ 左 (プローブ) 入力avg.: 0.52行, 入力std.de v.: 379.96%
       │ 右 (ビルド) 入力avg.: 312500.00行, 入力std.de v.: 380.00%
       │ 分布: PARTITIONED
       â ─ RemoteSource[3]
       │ 出力: [n_nationkey:integer, $hashvalue:bigint]
       │ 推定値: 
       │ 出力: 25行 (350B) 、ピークメモリ: 64KB (0.01%) 、WallTime: 63.63us (0.00%)
       │ 入力avg.: 0.52行, 入力std.de v.: 379.96%
       └ ─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
          │ 出力: [c_nationkey:integer, $hashvalue_0_4:bigint]
          │ 見積もり: {行: 15000000 (57.22MB)}
          │ 出力: 30000000行 (400.54MB) 、ピークメモリ: 10MB (1.84%) 、WallTime: 1.81秒 (17.93%)
          └ ─ RemoteSource[4]
                 出力: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 見積もり: 
                 出力: 15000000行 (200.27MB) 、PeakMemory: 3MB (0.67%) 、WallTime: 191.32ms (1.90%)
                 入力avg.: 312500.00行, 入力std.de v.: 380.00%

SQL文を変更してLEFT JOINをRIGHT JOINに変更

SELECT
  カウント (*)
から
  顧客t2
  右JOIN nationt1 ON t1.n_nationkey = t2.c_nationkey

SQL文の前にヒントを追加して、LEFT JOINをRIGHT JOINに変更します
- V3.1.8以降のAnalyticDB For MySQLクラスターの場合、次のステートメントを実行します。
```
/* + O_CBO_RULE_SWAP_OUTER_JOIN=true * /
選択
  カウント (*)
から
  国t1
  左JOINカスタマーt2 ON t1.n_nationkey = t2.c_nationkey 
```
- V3.1.8より前のAnalyticDB For MySQLクラスターの場合は、次のステートメントを実行します。
```
/* + LEFT_TO_RIGHT_ENABLED=true * /
選択
  カウント (*)
から
  国t1
  左JOINカスタマーt2 ON t1.n_nationkey = t2.c_nationkey 
```

上記のSQL文に対してEXPLAIN ANALYZE文を実行すると、実行計画でLEFT JOINがRIGHT JOINに変更されていることがわかります。この場合、ヒントが有効になります。 PeakMemoryパラメーターの値が515 MBから889 KBに減少しました。 RIGHT JOINは大量のリソースを消費しません。

Fragment 2 [ハッシュ]
    出力: 96行 (864B) 、PeakMemory: 12MB、WallTime: 4.27us、入力: 15000025行 (200.27MB); タスクごと: avg.: 2500004.17 std.de v.: 2410891.74
    出力レイアウト: [count_0_2]
    出力パーティション：SINGLE[]
    集約 (PARTIAL)
    │ 出力: [count_0_2:bigint]
    │ 見積もり: {rows: ? (?)}
    │ 出力: 192行 (1.69kB) 、PeakMemory: 456B (0.00%) 、WallTime: 5.31ms (0.08%)
    │ count_2 := count(*)
    └ ─ RIGHT Join[('c_nationkey' = 'n_nationkey')][$hashvalue, $hashvalue_0_4]
       │ 出力: []
       │ 見積もり: {rows: 15000000 (0B)}
       │ 出力: 15000025行 (350B) 、ピークメモリ: 889KB (3.31%) 、WallTime: 3.15秒 (48.66%)
       │ 左 (プローブ) 入力avg.: 312500.00行, 入力std.de v.: 380.00%
       │ 右 (ビルド) 入力avg.: 0.52行, 入力std.de v.: 379.96%
       │ 分布: PARTITIONED
       â ─ RemoteSource[3]
       │ 出力: [c_nationkey:integer, $hashvalue:bigint]
       │ 推定値: 
       │ 出力: 15000000行 (200.27MB) 、ピークメモリ: 3MB (15.07%) 、WallTime: 634.81ms (9.81%)
       │ 入力avg.: 312500.00行, 入力std.de v.: 380.00%
       └ ─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
          │ 出力: [n_nationkey:integer, $hashvalue_0_4:bigint]
          │ 見積もり: {rows: 25 (100B)}
          │ 出力: 50行 (700B) 、ピークメモリ: 461KB (1.71%) 、WallTime: 942.37us (0.01%)
          └ ─ RemoteSource[4]
                 出力: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 見積もり: 
                 出力: 25行 (350B) 、PeakMemory: 64KB (0.24%) 、WallTime: 76.34us (0.00%)
                 入力avg.: 0.52行, 入力std.de v.: 379.96%