EXPLAIN - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxCompute SQL は、クエリ文またはテーブル構造のパフォーマンスボトルネックを分析するための EXPLAIN 文を提供します。このトピックでは、explain の関数、コマンド形式、および使用例について説明します。

説明

EXPLAIN 文は、MaxCompute SQL で DQL 文の実行計画構造を表示できます。この文は、SQL 文の実行方法を理解するのに役立ち、SQL 文を最適化するためのガイダンスを提供します。1 つのクエリ文は複数のジョブに対応し、1 つのジョブは複数のタスクに対応します。

説明

クエリ文が複雑で、EXPLAIN 文の出力結果の行のサイズが 4 MB（上位層アプリケーションの API で指定されたしきい値）を超える場合、出力結果は完全には表示されません。この問題に対処するには、クエリ文を複数のサブクエリに分割し、各サブクエリに対して EXPLAIN 文を実行して、ジョブの構造を取得できます。

構文

EXPLAIN <dml query>;

dml query: 必須。SELECT 文。詳細については、「SELECT 構文」をご参照ください。

戻り値

EXPLAIN 文の出力結果には、次の情報が含まれます。

ジョブ間の依存関係
たとえば、job0 is a root job です。クエリで job0 のみが必要な場合は、1 行のデータのみが表示されます。
タスク間の依存関係
```
In Job job0:
root Tasks: M1, M2
J3_1_2_Stg1 depends on: M1, M2
```
job0 には、M1、M2、および J3_1_2_Stg1 のタスクが含まれています。MaxCompute は、M1 タスクと M2 タスクが実行された後に、J3_1_2_Stg1 タスクを実行します。
タスクの命名規則：
- タスクタイプ: MapTask（M）、ReduceTask（R）、JoinTask（J）、LocalWork（L）。
  説明
  タスク名の最初の文字は、タスクのタイプを示します。たとえば、M2Stg1 はマップタスクです。
- 最初の文字の後に続く数字は、タスク ID を示します。この ID は、特定のクエリに対応するすべてのタスクの中で一意です。
- アンダースコア（_）で区切られた数字は、タスクの直接の依存関係を表します。たとえば、J3_1_2_Stg1 は、ID が 3 のタスクが M1 タスクと M2 タスクに依存していることを示します。
タスク内のすべての演算子間の依存関係
演算子文字列は、タスクの実行セマンティクスを記述します。演算子文字列の構造：
```
In Task M2:
    Data source: mf_mc_bj.sale_detail_jt/sale_date=2013/region=china # データソースはタスクの入力を記述します。
    TS: mf_mc_bj.sale_detail_jt/sale_date=2013/region=china           # TableScanOperator
        FIL: ISNOTNULL(customer_id)                                   # FilterOperator
            RS: order: +                                              # ReduceSinkOperator
                nullDirection: *
                optimizeOrderBy: False
                valueDestLimit: 0
                dist: HASH
                keys:
                      customer_id
                values:
                      customer_id (string)
                      total_price (double)
                partitions:
                      customer_id


In Task J3_1_2:
    JOIN:                                                           # JoinOperator
         StreamLineRead1 INNERJOIN StreamLineRead2
         keys:
             0:customer_id
             1:customer_id

        AGGREGATE: group by:customer_id                            # GroupByOperator
         UDAF: SUM(total_price) (__agg_0_sum)[Complete],SUM(total_price) (__agg_1_sum)[Complete]
            RS: order: +
                nullDirection: *
                optimizeOrderBy: True
                valueDestLimit: 10
                dist: HASH
                keys:
                      customer_id
                values:
                      customer_id (string)
                      __agg_0 (double)
                      __agg_1 (double)
                partitions:


In Task R4_3:
    SEL: customer_id,__agg_0,__agg_1                               # SelectOperator
        LIM:limit 10                                               # LimitOperator
            FS: output: Screen                                     # FileSinkOperator
                schema:
                  customer_id (string) AS ashop
                  __agg_0 (double) AS ap
                  __agg_1 (double) AS bp
```
演算子の説明：
- TableScanOperator（TS）: クエリ文の FROM 文ブロックのロジックを記述します。EXPLAIN 文の出力結果には、入力テーブルのエイリアスが表示されます。
- SelectOperator（SEL）: クエリ文の SELECT 文ブロックのロジックを記述します。EXPLAIN 文の実行結果には、次の演算子に渡される列が表示されます。複数の列はコンマ（,）で区切られます。
  - 列が指定されている場合、値は <alias>.<column_name> 形式で表示されます。
  - 式が指定されている場合、値は func1(arg1_1, arg1_2, func2(arg2_1, arg2_2)) などの関数のリストとして表示されます。
  - 定数が指定されている場合、定数値が表示されます。
- FilterOperator（FIL）: クエリ文の WHERE 文ブロックのロジックを記述します。EXPLAIN 文の出力結果には、SelectOperator と同様の形式の WHERE 式が含まれます。
- JoinOperator（JOIN）: クエリ文の JOIN 文ブロックのロジックを記述します。EXPLAIN 文の出力結果には、どのテーブルがどの方法で結合されるかが示されます。
- GroupByOperator（AGGREGATE）: 集約操作のロジックを記述します。この演算子は、クエリ文で集約関数が使用されている場合に表示されます。EXPLAIN 文の実行結果には、集約関数の内容が表示されます。
- ReduceSinkOperator（RS）: タスク間のデータ配布のロジックを記述します。タスクの結果が別のタスクに転送される場合、タスクの最終段階で ReduceSinkOperator を使用してデータを配布する必要があります。EXPLAIN 文の出力結果には、結果のソート方法、配布キー、配布値、およびハッシュ値の計算に使用される列が表示されます。
- FileSinkOperator（FS）: 最終データレコードのストレージ操作のロジックを記述します。クエリ文に INSERT 文ブロックが含まれている場合、EXPLAIN 文の出力結果には、データを挿入するテーブルの名前が表示されます。
- LimitOperator（LIM）: クエリ文の LIMIT 文ブロックのロジックを記述します。EXPLAIN 文の実行結果には、LIMIT 文ブロックで指定された戻り行数が表示されます。
- MapjoinOperator（HASHJOIN）: 大きなテーブルに対する JOIN 操作を記述します。この演算子は JoinOperator と似ています。

サンプルデータ

このトピックの例を理解しやすいように、サンプルのソースデータが提供されています。次の文は、sale_detail テーブルと sale_detail_jt テーブルを作成し、テーブルにデータを挿入する方法を示しています。

-- sale_detail と sale_detail_jt という名前の 2 つのパーティションテーブルを作成します。
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE if NOT EXISTS sale_detail_jt
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- 2 つのテーブルにパーティションを追加します。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');

-- テーブルにデータを挿入します。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

-- sale_detail テーブルと sale_detail_jt テーブルからデータをクエリします。サンプル文：
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
-- 次の結果が返されます。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;
-- 次の結果が返されます。
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

-- JOIN 操作用のテーブルを作成します。
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

例

次の例は、「サンプルデータ」に基づいています。

例 1

クエリ文:

SELECT a.customer_id AS ashop, SUM(a.total_price) AS ap,COUNT(b.total_price) AS bp 
FROM (SELECT * FROM sale_detail_jt WHERE sale_date='2013' AND region='china') a 
INNER JOIN (SELECT * FROM sale_detail WHERE sale_date='2013' AND region='china') b 
ON a.customer_id=b.customer_id 
GROUP BY a.customer_id 
ORDER BY a.customer_id 
LIMIT 10;

クエリ文の実行計画を取得します。

EXPLAIN 
SELECT a.customer_id AS ashop, SUM(a.total_price) AS ap,COUNT(b.total_price) AS bp 
FROM (SELECT * FROM sale_detail_jt WHERE sale_date='2013' AND region='china') a 
INNER JOIN (SELECT * FROM sale_detail WHERE sale_date='2013' AND region='china') b 
ON a.customer_id=b.customer_id 
GROUP BY a.customer_id 
ORDER BY a.customer_id 
LIMIT 10;

次の結果が返されます。

job0 is root job

In Job job0:
root Tasks: M1
M2_1 depends on: M1
R3_2 depends on: M2_1
R4_3 depends on: R3_2

In Task M1:
    Data source: doc_****.default.sale_detail/sale_date=2013/region=china
    TS: doc_****.default.sale_detail/sale_date=2013/region=china
        Statistics: Num rows: 3.0, Data size: 324.0
        FIL: ISNOTNULL(customer_id)
            Statistics: Num rows: 2.7, Data size: 291.6
            RS: valueDestLimit: 0
                dist: BROADCAST
                keys:
                values:
                      customer_id (string)
                      total_price (double)
                partitions:

                Statistics: Num rows: 2.7, Data size: 291.6

In Task M2_1:
    Data source: doc_****.default.sale_detail_jt/sale_date=2013/region=china
    TS: doc_****.default.sale_detail_jt/sale_date=2013/region=china
        Statistics: Num rows: 3.0, Data size: 324.0
        FIL: ISNOTNULL(customer_id)
            Statistics: Num rows: 2.7, Data size: 291.6
            HASHJOIN:
                     Filter1 INNERJOIN StreamLineRead1
                     keys:
                         0:customer_id
                         1:customer_id
                     non-equals:
                         0:
                         1:
                     bigTable: Filter1

                Statistics: Num rows: 3.6450000000000005, Data size: 787.32
                RS: order: +
                    nullDirection: *
                    optimizeOrderBy: False
                    valueDestLimit: 0
                    dist: HASH
                    keys:
                          customer_id
                    values:
                          customer_id (string)
                          total_price (double)
                          total_price (double)
                    partitions:
                          customer_id

                    Statistics: Num rows: 3.6450000000000005, Data size: 422.82000000000005

In Task R3_2:
    AGGREGATE: group by:customer_id
     UDAF: SUM(total_price) (__agg_0_sum)[Complete],COUNT(total_price) (__agg_1_count)[Complete]
        Statistics: Num rows: 1.0, Data size: 116.0
        RS: order: +
            nullDirection: *
            optimizeOrderBy: True
            valueDestLimit: 10
            dist: HASH
            keys:
                  customer_id
            values:
                  customer_id (string)
                  __agg_0 (double)
                  __agg_1 (bigint)
            partitions:

            Statistics: Num rows: 1.0, Data size: 116.0

In Task R4_3:
    SEL: customer_id,__agg_0,__agg_1
        Statistics: Num rows: 1.0, Data size: 116.0
        SEL: customer_id ashop, __agg_0 ap, __agg_1 bp, customer_id
            Statistics: Num rows: 1.0, Data size: 216.0
            FS: output: Screen
                schema:
                  ashop (string)
                  ap (double)
                  bp (bigint)

                Statistics: Num rows: 1.0, Data size: 116.0

OK

例 2

クエリ文:

SELECT /*+ mapjoin(a) */
       a.customer_id AS ashop, SUM(a.total_price) AS ap,COUNT(b.total_price) AS bp 
FROM (SELECT * FROM sale_detail_jt 
WHERE sale_date='2013' AND region='china') a 
INNER JOIN (SELECT * FROM sale_detail WHERE sale_date='2013' AND region='china') b 
ON a.total_price<b.total_price 
GROUP BY a.customer_id 
ORDER BY a.customer_id 
LIMIT 10;

クエリ文の実行計画を取得します。

EXPLAIN 
SELECT /*+ mapjoin(a) */
       a.customer_id AS ashop, SUM(a.total_price) AS ap,COUNT(b.total_price) AS bp 
FROM (SELECT * FROM sale_detail_jt 
WHERE sale_date='2013' AND region='china') a 
INNER JOIN (SELECT * FROM sale_detail WHERE sale_date='2013' AND region='china') b 
ON a.total_price<b.total_price 
GROUP BY a.customer_id 
ORDER BY a.customer_id 
LIMIT 10;

次の結果が返されます。

job0 is root job

In Job job0:
root Tasks: M1
M2_1 depends on: M1
R3_2 depends on: M2_1
R4_3 depends on: R3_2

In Task M1:
    Data source: doc_****.sale_detail_jt/sale_date=2013/region=china
    TS: doc_****.sale_detail_jt/sale_date=2013/region=china
        Statistics: Num rows: 3.0, Data size: 324.0
        RS: valueDestLimit: 0
            dist: BROADCAST
            keys:
            values:
                  customer_id (string)
                  total_price (double)
            partitions:

            Statistics: Num rows: 3.0, Data size: 324.0

In Task M2_1:
    Data source: doc_****.sale_detail/sale_date=2013/region=china
    TS: doc_****.sale_detail/sale_date=2013/region=china
        Statistics: Num rows: 3.0, Data size: 24.0
        HASHJOIN:
                 StreamLineRead1 INNERJOIN TableScan2
                 keys:
                     0:
                     1:
                 non-equals:
                     0:
                     1:
                 bigTable: TableScan2

            Statistics: Num rows: 9.0, Data size: 1044.0
            FIL: LT(total_price,total_price)
                Statistics: Num rows: 6.75, Data size: 783.0
                AGGREGATE: group by:customer_id
                 UDAF: SUM(total_price) (__agg_0_sum)[Partial_1],COUNT(total_price) (__agg_1_count)[Partial_1]
                    Statistics: Num rows: 2.3116438356164384, Data size: 268.1506849315069
                    RS: order: +
                        nullDirection: *
                        optimizeOrderBy: False
                        valueDestLimit: 0
                        dist: HASH
                        keys:
                              customer_id
                        values:
                              customer_id (string)
                              __agg_0_sum (double)
                              __agg_1_count (bigint)
                        partitions:
                              customer_id

                        Statistics: Num rows: 2.3116438356164384, Data size: 268.1506849315069

In Task R3_2:
    AGGREGATE: group by:customer_id
     UDAF: SUM(__agg_0_sum)[Final] __agg_0,COUNT(__agg_1_count)[Final] __agg_1
        Statistics: Num rows: 1.6875, Data size: 195.75
        RS: order: +
            nullDirection: *
            optimizeOrderBy: True
            valueDestLimit: 10
            dist: HASH
            keys:
                  customer_id
            values:
                  customer_id (string)
                  __agg_0 (double)
                  __agg_1 (bigint)
            partitions:

            Statistics: Num rows: 1.6875, Data size: 195.75

In Task R4_3:
    SEL: customer_id,__agg_0,__agg_1
        Statistics: Num rows: 1.6875, Data size: 195.75
        SEL: customer_id ashop, __agg_0 ap, __agg_1 bp, customer_id
            Statistics: Num rows: 1.6875, Data size: 364.5
            FS: output: Screen
                schema:
                  ashop (string)
                  ap (double)
                  bp (bigint)

                Statistics: Num rows: 1.6875, Data size: 195.75

OK