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

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

前提条件

開始する前に、以下を確認してください。

ご利用の AnalyticDB for MySQL クラスターが V3.1.3 以降であること。バージョンを確認するには、「AnalyticDB for MySQL クラスターのバージョンを確認する方法」をご参照ください。アップグレードするには、チケットを起票してください。

EXPLAIN

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

構文

EXPLAIN (format text) <SELECT statement>;

説明

複雑でないクエリの場合、(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 statement>;

例

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 を実行し、Fragment 2 を比較します。

SQL 1 の出力 (フィルターがプッシュダウンされた場合): フラグメントは TableScan オペレーターを使用し、Input avg. は 0.00 rows です。これは、ストレージレイヤーがデータを返す前にすべての行をフィルター処理したことを意味します。

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 の出力（フィルターがプッシュダウンされていない）: このフラグメントでは、ScanFilterProject が TableScan の代わりに使用され、Input には 9999 行 と表示されます。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 を使用して、消費を駆動している特定のオペレーターを突き止めます。ビジネス要件に基づいてフィルタリング条件を追加してデータ量を制限するか、クエリを再構築して中間結果のサイズを削減してください。