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

LEFT JOIN は、一般的に使用されるテーブル結合方法です。ハッシュ結合が実行される場合、右テーブルを使用してハッシュテーブルがビルドされますが、LEFT JOIN の左テーブルと右テーブルの順序は変更できません。右テーブルが大きい場合、クエリの実行が遅くなり、大量のメモリが消費されます。このトピックでは、具体的な例を使用して、LEFT JOIN を RIGHT JOIN に変更できるシナリオについて説明します。

背景情報

AnalyticDB for MySQL は、デフォルトでハッシュ結合を使用してテーブルを結合します。ハッシュ結合では、右テーブルを使用してハッシュテーブルをビルドするため、大量のリソースが消費されます。内部結合とは異なり、外部結合 (LEFT JOIN および RIGHT JOIN を含む) は、クエリのセマンティクスを変更せずに左テーブルと右テーブルの順序を交換することはできません。したがって、右テーブルが大きい場合、クエリの実行が遅くなり、大量のメモリが消費されます。右テーブルが非常に大きい極端なケースでは、クラスターのパフォーマンスが影響を受けたり、クエリの実行中に Out of Memory Pool size pre cal エラーが報告されたりします。この場合、このトピックで紹介する最適化方法を使用して、リソース消費を削減できます。

シナリオ

SQL ステートメントを変更するか、ヒントを追加することで、LEFT JOIN を RIGHT JOIN に変更できます。LEFT JOIN を RIGHT JOIN に変更すると、元の左テーブルが右テーブルとなり、ハッシュテーブルがビルドされます。新しい右テーブルが大きすぎる場合、パフォーマンスは依然として影響を受けます。したがって、LEFT JOIN の左テーブルが小さく、右テーブルが大きい場合にこの最適化を実行することを推奨します。

テーブルが小さいか大きいかは相対的なものであり、結合列やクラスターリソースなどの要因によって異なります。実際に、EXPLAIN ANALYZE を使用して実行計画のパラメーターを表示できます。RIGHT JOIN を使用するかどうかを判断するには、PeakMemory や WallTime などのパラメーターの変更を確認します。

使用方法

以下の 2 つの方法のいずれかで、LEFT JOIN を RIGHT JOIN に変更できます：

SQL ステートメントを直接変更します。たとえば、a left join b on a.col1 = b.col2 を b right join a on a.col1 = b.col2 に変更します。
ヒントを追加して、リソース消費に基づいて LEFT JOIN を RIGHT JOIN に変更するようにオプティマイザーに指示します。この方法では、オプティマイザーは、左テーブルと右テーブルの推定サイズに基づいて、LEFT JOIN を RIGHT JOIN に変更するかどうかを決定します。以下のいずれかの方法を使用します：
- V3.1.8 以降のクラスターでは、この機能はデフォルトで有効になっています。機能が無効になっている場合は、SQL ステートメントの先頭に次のヒントを追加して手動で有効にします：/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
- V3.1.8 より前のバージョンのクラスターでは、この機能はデフォルトで無効になっています。SQL ステートメントの先頭に次のヒントを追加して有効にします：/*+LEFT_TO_RIGHT_ENABLED=true*/

説明

Data Lakehouse Edition クラスターのマイナーバージョンを確認するには、SELECT adb_version(); を実行します。マイナーバージョンをアップグレードするには、クラスターのマイナーバージョンの更新をご参照ください。

例

次の例では、nation は 25 行の小さなテーブルで、customer は 15,000,000 行の大きなテーブルです。EXPLAIN ANALYZE を使用して、LEFT JOIN を含むクエリの実行計画を表示します：

explain analyze
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

以下の出力は、結合計算を実行するステージ 2 を示しています。LEFT Join 演算子には、次の情報が含まれています：

PeakMemory: 515 MB (93.68%), WallTime: 4.34 s (43.05%)：PeakMemory の値が全体の 93.68% を占めており、LEFT JOIN がクエリのパフォーマンスボトルネックであることを示します。
Left (probe) Input avg.: 0.52 rows; Right (build) Input avg.: 312500.00 rows：右テーブルが大きなテーブルで、左テーブルが小さなテーブルです。

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

Fragment 2 [HASH]
    Output: 48 rows (432 B), PeakMemory: 516 MB, WallTime: 6.52us, Input: 15000025 rows (200.27 MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 96 rows (864 B), PeakMemory: 96 B (0.00%), WallTime: 88.21ms (0.88%)
    │   count_2 := count(*)
    └─ LEFT Join[(`n_nationkey` = `c_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 30000000 rows (200.27 MB), PeakMemory: 515 MB (93.68%), WallTime: 4.34s (43.05%)
       │   Left (probe) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Right (build) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [n_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 25 rows (350B), PeakMemory: 64 KB (0.01%), WallTime: 63.63us (0.00%)
       │      Input avg.: 0.52 rows, Input std.dev.: 379.96%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
          │   Outputs: [c_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 15000000 (57.22 MB)}
          │   Output: 30000000 rows (400.54 MB), PeakMemory: 10 MB (1.84%), WallTime: 1.81s (17.93%)
          └─ RemoteSource[4]
                 Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 15000000 rows (200.27 MB), PeakMemory: 3 MB (0.67%), WallTime: 191.32ms (1.90%)
                 Input avg.: 312500.00 rows, Input std.dev.: 380.00%

SQL ステートメントを変更して LEFT JOIN を RIGHT JOIN に変更する：

SELECT
  COUNT(*)
FROM
  customer t2
  right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey

ヒントを追加して LEFT JOIN を RIGHT JOIN に変更する：
- V3.1.8 以降のクラスターでは、次のステートメントを実行してこの機能を有効にします：
```
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
```
- V3.1.8 より前のバージョンのクラスターでは、次のステートメントを実行してこの機能を有効にします：
```
/*+LEFT_TO_RIGHT_ENABLED=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
```

上記のいずれかのクエリで EXPLAIN ANALYZE を実行すると、実行計画には LEFT Join の代わりに RIGHT Join が表示されます。これは、この最適化が有効になったことを示します。調整後、PeakMemory の値は 889 KB (3.31%) になり、515 MB から 889 KB に減少し、もはやパフォーマンスのボトルネックではなくなります。

Fragment 2 [HASH]
    Output: 96 rows (864 B), PeakMemory: 12 MB, WallTime: 4.27us, Input: 15000025 rows (200.27 MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 192 rows (1.69 kB), PeakMemory: 456 B (0.00%), WallTime: 5.31ms (0.08%)
    │   count_2 := count(*)
    └─ RIGHT Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 15000025 rows (350 B), PeakMemory: 889 KB (3.31%), WallTime: 3.15s (48.66%)
       │   Left (probe) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Right (build) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 15000000 rows (200.27 MB), PeakMemory: 3 MB (15.07%), WallTime: 634.81ms (9.81%)
       │      Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 25 (100 B)}
          │   Output: 50 rows (700 B), PeakMemory: 461 KB (1.71%), WallTime: 942.37us (0.01%)
          └─ RemoteSource[4]
                 Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 25 rows (350 B), PeakMemory: 64 KB (0.24%), WallTime: 76.34us (0.00%)
                 Input avg.: 0.52 rows, Input std.dev.: 379.96%