EXPLAIN および EXPLAIN ANALYZE コマンドを使用して実行計画を分析する方法 - AnalyticDB

クエリの実行が遅い場合や、過剰なメモリを消費する場合、実行計画は時間とリソースがどこで消費されているかを正確に示します。EXPLAIN は、実行前にオプティマイザが計画したクエリの実行パスを表示します。EXPLAIN ANALYZE はクエリを実行し、実際の分散実行コスト (オペレーターレベルの期間、ピークメモリ使用量、入出力行数) を取得します。これにより、推定値と実際の結果を比較してボトルネックを特定できます。

前提条件

開始する前に、次のことを確認してください：

お使いの AnalyticDB for MySQL クラスターが V 3.1.3 以降のバージョンであること。バージョンを確認するには、「AnalyticDB for MySQL クラスターのバージョンを確認する方法」をご参照ください。アップグレードするには、チケットを送信してください。

EXPLAIN

EXPLAIN は、SQL クエリの実行計画を評価します。その出力はオプティマイザによる推定値であり、実際の実行結果と一致しない場合があります。

構文

EXPLAIN (format text) <SELECT ステートメント>;

説明

出力のツリー階層の可読性を向上させるため、複雑でないクエリに (format text) を追加します。

例

EXPLAIN (format text)
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA';

出力は、各ノードが演算子であるツリーです。各ノードの Outputs フィールドは、その演算子が生成する列を示し、Estimates は、オプティマイザが結合順序とデータシャッフル戦略を決定するために使用する行数とデータ量の予測を示します。

Output[count(*)]
│   Outputs: [count:bigint]
│   Estimates: {rows: 1 (8B)}
│   count(*) := count
└─ Aggregate(FINAL)
   │   Outputs: [count:bigint]
   │   Estimates: {rows: 1 (8B)}
   │   count := count(`count_1`)
   └─ LocalExchange[SINGLE] ()
      │   Outputs: [count_0_1:bigint]
      │   Estimates: {rows: 1 (8B)}
      └─ RemoteExchange[GATHER]
         │   Outputs: [count_0_2:bigint]
         │   Estimates: {rows: 1 (8B)}
         └─ Aggregate(PARTIAL)
            │   Outputs: [count_0_4:bigint]
            │   Estimates: {rows: 1 (8B)}
            │   count_4 := count(*)
            └─ InnerJoin[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_6]
               │   Outputs: []
               │   Estimates: {rows: 302035 (4.61MB)}
               │   Distribution: REPLICATED
               ├─ Project[]
               │  │   Outputs: [c_nationkey:integer, $hashvalue:bigint]
               │  │   Estimates: {rows: 1500000 (5.72MB)}
               │  │   $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
               │  └─ RuntimeFilter
               │     │   Outputs: [c_nationkey:integer]
               │     │   Estimates: {rows: 1500000 (5.72MB)}
               │     ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
               │     │      Outputs: [c_nationkey:integer]
               │     │      Estimates: {rows: 1500000 (5.72MB)}
               │     │      c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
               │     └─ RuntimeCollect
               │        │   Outputs: [n_nationkey:integer]
               │        │   Estimates: {rows: 5 (60B)}
               │        └─ LocalExchange[ROUND_ROBIN] ()
               │           │   Outputs: [n_nationkey:integer]
               │           │   Estimates: {rows: 5 (60B)}
               │           └─ RuntimeScan
               │                  Outputs: [n_nationkey:integer]
               │                  Estimates: {rows: 5 (60B)}
               └─ LocalExchange[HASH][$hashvalue_0_6] ("n_nationkey")
                  │   Outputs: [n_nationkey:integer, $hashvalue_0_6:bigint]
                  │   Estimates: {rows: 5 (60B)}
                  └─ Project[]
                     │   Outputs: [n_nationkey:integer, $hashvalue_0_10:bigint]
                     │   Estimates: {rows: 5 (60B)}
                     │   $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
                     └─ RemoteExchange[REPLICATE]
                        │   Outputs: [n_nationkey:integer]
                        │   Estimates: {rows: 5 (60B)}
                        └─ InnerJoin[(`n_regionkey` = `r_regionkey`)][$hashvalue_0_7, $hashvalue_0_8]
                           │   Outputs: [n_nationkey:integer]
                           │   Estimates: {rows: 5 (60B)}
                           │   Distribution: REPLICATED
                           ├─ Project[]
                           │  │   Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue_0_7:bigint]
                           │  │   Estimates: {rows: 25 (200B)}
                           │  │   $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
                           │  └─ RuntimeFilter
                           │     │   Outputs: [n_nationkey:integer, n_regionkey:integer]
                           │     │   Estimates: {rows: 25 (200B)}
                           │     ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
                           │     │      Outputs: [n_nationkey:integer, n_regionkey:integer]
                           │     │      Estimates: {rows: 25 (200B)}
                           │     │      n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
                           │     │      n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
                           │     └─ RuntimeCollect
                           │        │   Outputs: [r_regionkey:integer]
                           │        │   Estimates: {rows: 1 (4B)}
                           │        └─ LocalExchange[ROUND_ROBIN] ()
                           │           │   Outputs: [r_regionkey:integer]
                           │           │   Estimates: {rows: 1 (4B)}
                           │           └─ RuntimeScan
                           │                  Outputs: [r_regionkey:integer]
                           │                  Estimates: {rows: 1 (4B)}
                           └─ LocalExchange[HASH][$hashvalue_0_8] ("r_regionkey")
                              │   Outputs: [r_regionkey:integer, $hashvalue_0_8:bigint]
                              │   Estimates: {rows: 1 (4B)}
                              └─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
                                     Outputs: [r_regionkey:integer, $hashvalue_0_9:bigint]
                                     Estimates: {rows: 1 (4B)}/{rows: 1 (B)}
                                     $hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
                                     r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}

パラメーター	説明
`Outputs: [symbol:type]`	各オペレーターの出力列とデータ型。
`Estimates: {rows: %s (%sB)}`	各オペレーターに対するオプティマイザの推定行数とデータ量。オプティマイзаはこれらの推定値を使用して、結合順序とデータシャッフル戦略を決定します。

EXPLAIN ANALYZE

EXPLAIN ANALYZE はクエリを実行し、各オペレーターとフラグメントの実行時間、ピークメモリ使用量、および入出力行数といった実際の分散実行コストを返します。

構文

EXPLAIN ANALYZE <SELECT ステートメント>;

例

EXPLAIN ANALYZE
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA';

出力はフラグメントごとに整理されており、各フラグメントは分散実行計画のステージに対応します。各オペレーターについて、実際の行数、メモリ使用量、実時間をオプティマイザーの推定値と並べて確認できます。Estimates と Output を比較してオプティマイザーのモデルが実際の実行と乖離している箇所を特定したり、PeakMemory と WallTime を使用して最もコストのかかるオペレーターを見つけたりすることができます。

Fragment 1 [SINGLE]
    Output: 1 row (9B), PeakMemory: 178KB, WallTime: 1.00ns, Input: 32 rows (288B); per task: avg.: 32.00 std.dev.: 0.00
    Output layout: [count]
    Output partitioning: SINGLE []
    Aggregate(FINAL)
    │   Outputs: [count:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 2 rows (18B), PeakMemory: 24B (0.00%), WallTime: 70.39us (0.03%)
    │   count := count(`count_1`)
    └─ LocalExchange[SINGLE] ()
       │   Outputs: [count1:bigint]
       │   Estimates: {rows: ? (?)}
       │   Output: 64 rows (576B), PeakMemory: 8KB (0.07%), WallTime: 238.69us (0.10%)
       └─ RemoteSource[2]
              Outputs: [count2:bigint]
              Estimates:
              Output: 32 rows (288B), PeakMemory: 32KB (0.27%), WallTime: 182.82us (0.08%)
              Input avg.: 4.00 rows, Input std.dev.: 264.58%

Fragment 2 [adb:AdbPartitioningHandle{schema=tpch, tableName=customer, dimTable=false, shards=32, tableEngineType=Cstore, partitionColumns=c_custkey, prunedBuckets= empty}]
    Output: 32 rows (288B), PeakMemory: 6MB, WallTime: 164.00ns, Input: 1500015 rows (20.03MB); per task: avg.: 500005.00 std.dev.: 21941.36
    Output layout: [count4]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count4:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 64 rows (576B), PeakMemory: 336B (0.00%), WallTime: 1.01ms (0.42%)
    │   count_4 := count(*)
    └─ INNER Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue6]
       │   Outputs: []
       │   Estimates: {rows: 302035 (4.61MB)}
       │   Output: 300285 rows (210B), PeakMemory: 641KB (5.29%), WallTime: 99.08ms (41.45%)
       │   Left (probe) Input avg.: 46875.00 rows, Input std.dev.: 311.24%
       │   Right (build) Input avg.: 0.63 rows, Input std.dev.: 264.58%
       │   Distribution: REPLICATED
       ├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: {rows: 1500000 (5.72MB)}/{rows: 1500000 (5.72MB)}
       │      Output: 1500000 rows (20.03MB), PeakMemory: 5MB (44.38%), WallTime: 68.29ms (28.57%)
       │      $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
       │      c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
       │      Input: 1500000 rows (7.15MB), Filtered: 0.00%
       └─ LocalExchange[HASH][$hashvalue6] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue6:bigint]
          │   Estimates: {rows: 5 (60B)}
          │   Output: 30 rows (420B), PeakMemory: 394KB (3.26%), WallTime: 455.03us (0.19%)
          └─ Project[]
             │   Outputs: [n_nationkey:integer, $hashvalue10:bigint]
             │   Estimates: {rows: 5 (60B)}
             │   Output: 15 rows (210B), PeakMemory: 24KB (0.20%), WallTime: 83.61us (0.03%)
             │   Input avg.: 0.63 rows, Input std.dev.: 264.58%
             │   $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
             └─ RemoteSource[3]
                    Outputs: [n_nationkey:integer]
                    Estimates:
                    Output: 15 rows (75B), PeakMemory: 24KB (0.20%), WallTime: 45.97us (0.02%)
                    Input avg.: 0.63 rows, Input std.dev.: 264.58%

Fragment 3 [adb:AdbPartitioningHandle{schema=tpch, tableName=nation, dimTable=true, shards=32, tableEngineType=Cstore, partitionColumns=, prunedBuckets= empty}]
    Output: 5 rows (25B), PeakMemory: 185KB, WallTime: 1.00ns, Input: 26 rows (489B); per task: avg.: 26.00 std.dev.: 0.00
    Output layout: [n_nationkey]
    Output partitioning: BROADCAST []
    INNER Join[(`n_regionkey` = `r_regionkey`)][$hashvalue7, $hashvalue8]
    │   Outputs: [n_nationkey:integer]
    │   Estimates: {rows: 5 (60B)}
    │   Output: 11 rows (64B), PeakMemory: 152KB (1.26%), WallTime: 255.86us (0.11%)
    │   Left (probe) Input avg.: 25.00 rows, Input std.dev.: 0.00%
    │   Right (build) Input avg.: 0.13 rows, Input std.dev.: 264.58%
    │   Distribution: REPLICATED
    ├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
    │      Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue7:bigint]
    │      Estimates: {rows: 25 (200B)}/{rows: 25 (200B)}
    │      Output: 25 rows (475B), PeakMemory: 16KB (0.13%), WallTime: 178.81us (0.07%)
    │      $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
    │      n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
    │      n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
    │      Input: 25 rows (250B), Filtered: 0.00%
    └─ LocalExchange[HASH][$hashvalue8] ("r_regionkey")
       │   Outputs: [r_regionkey:integer, $hashvalue8:bigint]
       │   Estimates: {rows: 1 (4B)}
       │   Output: 2 rows (28B), PeakMemory: 34KB (0.29%), WallTime: 57.41us (0.02%)
       └─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
              Outputs: [r_regionkey:integer, $hashvalue9:bigint]
              Estimates: {rows: 1 (4B)}/{rows: 1 (4B)}
              Output: 1 row (14B), PeakMemory: 8KB (0.07%), WallTime: 308.99us (0.13%)
              $hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
              r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}
              Input: 1 row (5B), Filtered: 0.00%

パラメーター	説明
`Outputs: [symbol:type]`	各オペレーターの出力列とデータ型。
`Estimates: {rows: %s (%sB)}`	`Output`オプティマイザによる推定行数とデータ量。と比較して、推定値が実際の実行とどこで乖離しているかを確認できます。
`PeakMemory: %s`	オペレーターまたはフラグメントが使用したピークメモリ。メモリのボトルネックを診断する際に、主要なメモリ消費者を特定するために使用します。
`WallTime: %s`	オペレーターの総実行期間。この期間は、並列計算のため、実際の実行期間ではありません。計算処理中に発生するボトルネックの原因を分析するために使用します。
`Input: %s rows (%sB)`	入力として読み込まれた行数とバイト数。
`per task: avg.: %s std.dev.: %s`	タスクごとの平均入力行数と、タスク間の標準偏差。標準偏差が高い場合はデータスキューを示しており、一部のタスクが他のタスクよりはるかに多くのデータを処理していることを意味します。これは並列処理の効率を制限します。
`Output: %s row (%sB)`	出力として生成された行数とバイト数。

一般的な問題の診断

プッシュダウンされないフィルター

フィルターをストレージレイヤーにプッシュダウンできない場合、クエリはフルテーブルスキャンを実行し、その後メモリ内でフィルターを適用します。これにより、I/O とメモリの使用量が大幅に増加します。

次の 2 つのクエリで、その違いを説明します：

SQL 1 — 未加工の列値に対するフィルター (プッシュダウン可能)：
```
SELECT count(*) FROM test WHERE string_test = 'a';
```
SQL 2 — 関数でラップされたフィルター (プッシュダウン不可)：
```
SELECT count(*) FROM test WHERE length(string_test) = 1;
```

両方のクエリで EXPLAIN ANALYZE を実行し、フラグメント 2 を比較します。

SQL 1 出力 (フィルタープッシュダウン): フラグメントでは TableScan 演算子が使用され、Input avg. が 0.00 行 となっています。これは、ストレージレイヤーがデータを返す前にすべての行をフィルターで除外したことを意味します。

Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
    Output: 4 rows (36B), PeakMemory: 0B, WallTime: 6.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
    Output layout: [count_0_1]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_1:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 212.92us (3.99%)
    │   count_0_1 := count(*)
    └─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}]
           Outputs: []
           Estimates: {rows: 4 (0B)}
           Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 4.76ms (89.12%)
           Input avg.: 0.00 rows, Input std.dev.: ?%

SQL 2 の出力 (フィルターがプッシュダウンされていない場合): フラグメントは TableScan の代わりに ScanFilterProject を使用し、Input は 9999 rows を示します。filterPredicate フィールドは、フィルターがストレージレイヤーではなく、スキャンの後に適用されることを示しています。

Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
    Output: 4 rows (36B), PeakMemory: 0B, WallTime: 102.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
    Output layout: [count_0_1]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_1:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 252.23us (0.12%)
    │   count_0_1 := count(*)
    └─ ScanFilterProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}, filterPredicate = (`test4dmp`.`length`(`string_test`) = BIGINT '1')]
           Outputs: []
           Estimates: {rows: 9999 (312.47kB)}/{rows: 9999 (312.47kB)}/{rows: ? (?)}
           Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 101.31ms (49.84%)
           string_test := AdbColumnHandle{columnName=string_test, type=13, isIndexed=true}
           Input: 9999 rows (110.32kB), Filtered: 100.00%

このパターンを診断するには、出力で ScanFilterProject を探してください。Input に表示される行数が多く、Filtered が 100% に近い場合、クエリは必要以上に多くのデータをスキャンしています。フィルターをプッシュダウンできる形式に書き換えるか、スキャンを絞り込む条件を追加してください。

高いメモリ使用量

フラグメントレベルで PeakMemory を使用して、どのステージが最も多くのメモリを消費するかを特定します。PeakMemory が高くなる一般的な原因には、次のようなものがあります。

意図しないブロードキャスト結合による高いメモリ消費
データ肥大化による大きな中間結合結果
結合における大きなプローブ側テーブル (左)
TableScan が大量のデータを読み取っている

フラグメントを特定した後、オペレーターごとの PeakMemory を使用して、消費を押し上げている特定のオペレーターを突き止めます。ビジネス要件に基づいてフィルター条件を追加してデータ量を制限するか、クエリを再構成して中間結果のサイズを削減します。

分散キー以外の列での低速なクエリ

クエリが分布キーではない列でフィルター処理する場合、オプティマイザーはパーティションをプルーニングできず、すべてのシャードをスキャンする必要があるため、フルテーブルスキャンが発生します。 EXPLAIN ANALYZE の出力では、これは Input の行数が多く、WallTime の割合が大きい TableScan オペレーターとして表示されます。

このタイプのクエリを最適化するには、次の手順を実行してください：

等価クエリが頻繁に使用される列をクラスター化キー (CLUSTERED KEY) として設定すると、物理的なデータレイアウトを制御して範囲スキャンを高速化できます。
SELECT * の使用は避け、必要なカラムのみをクエリして I/O とメモリのオーバーヘッドを削減します。
ANALYZE TABLE <table_name> を実行すると、テーブル統計が更新され、オプティマイザがより効率的な実行計画を生成するのに役立ちます。