EXPLAIN を使用した SQL クエリの実行計画分析 - MaxCompute

EXPLAIN 文は、MaxCompute SQL の SELECT クエリの実行計画を表示します。これにより、クエリ文やテーブル構造におけるパフォーマンスボトルネックを特定できます。

1 つのクエリは 1 つ以上のジョブにマッピングされ、各ジョブには 1 つ以上のタスクが含まれます。EXPLAIN 文は、これらのジョブ、タスク、オペレーターがどのように関連しているかを明らかにし、SQL の最適化に役立ちます。

構文

EXPLAIN <query>;

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

出力構造

EXPLAIN の出力には、次の 3 つのセクションが含まれます：

ジョブの依存関係 -- すべてのジョブとその実行順序をリストします。たとえば、job0 is root job は、クエリが単一のルートジョブを必要とすることを示します。
タスクの依存関係 -- 各ジョブ内のタスクとその依存関係をリストします。たとえば、これは job0 に M1、M2、J3_1_2_Stg1 の 3 つのタスクが含まれていることを意味します。MaxCompute は、J3_1_2_Stg1 を M1 と M2 の両方が完了した後にのみ実行します。
```
    In Job job0:
    root Tasks: M1, M2
    J3_1_2_Stg1 depends on: M1, M2
```

オペレーターの詳細 -- 各タスク内のオペレーターと実行セマンティクスを記述します。たとえば、Data source 行はタスクの入力を示します。後続の各行は、オペレーターパイプラインを示すためにインデントされた、オペレーターとそのパラメーターを表します。

    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
            FIL: ISNOTNULL(customer_id)
                RS: order: +
                    nullDirection: *
                    optimizeOrderBy: False
                    valueDestLimit: 0
                    dist: HASH
                    keys:
                          customer_id
                    values:
                          customer_id (string)
                          total_price (double)
                    partitions:
                          customer_id

タスクの命名規則

コンポーネント	意味	例
最初の文字	タスクタイプ：`M` (MapTask)、`R` (ReduceTask)、`J` (JoinTask)、または `L` (LocalWork)	`M2Stg1` はマップタスクです
最初の文字の後の数字	タスク ID、クエリ内で一意	`M2` のタスク ID は 2 です
アンダースコアで区切られた数字	タスクの直接の依存関係	`J3_1_2_Stg1` は M1 と M2 に依存します

オペレーター

オペレーター	略語	SQL 句	説明
TableScanOperator	TS	`FROM`	入力テーブルをスキャンします。出力には入力テーブルのエイリアスが表示されます。
SelectOperator	SEL	`SELECT`	次のオペレーターに列を射影します。列は `<エイリアス>.<列名>` として表示され、式は関数リスト (例：`func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))`) として表示され、定数はそのリテラル値を表示します。
FilterOperator	FIL	`WHERE`	SelectOperator と同様のフォーマットで表示される `WHERE` 式に基づいて行をフィルターします。
JoinOperator	JOIN	`JOIN`	テーブルを結合します。出力には、どのテーブルが結合され、どの結合メソッドが使用されたかが表示されます。
GroupByOperator	AGGREGATE	集計関数	集約を実行します。クエリに集計関数が含まれている場合に表示されます。出力には集計関数の内容が表示されます。
ReduceSinkOperator	RS	--	タスク間でデータを分散します。タスクの出力が別のタスクへの入力となる場合、タスクの末尾に表示されます。出力には、ソート順、分散キー、値、ハッシュ列が表示されます。
FileSinkOperator	FS	`INSERT`	最終結果をストレージに書き込みます。`INSERT` 文の場合、出力にはターゲットテーブル名が表示されます。
LimitOperator	LIM	`LIMIT`	返される行数を制限します。
MapjoinOperator	HASHJOIN	`JOIN`	大きなテーブルに対してマップサイド結合を実行します。JoinOperator に似ています。

制限事項

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

例

サンプルデータの準備

2 つのパーティションテーブル 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);

データを確認します：

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：集約を伴う標準 JOIN

この例では、sale_detail_jt と sale_detail の間で INNER JOIN を実行し、結果をグループ化し、制限付きで順序付けを適用するクエリに対して 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;

EXPLAIN の実行：

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

実行計画には 4 つのタスクが表示されます：

M1 -- sale_detail をスキャンし、customer_id の null 値をフィルターし、データをブロードキャストします (`BROADCAST` 分散)。
M2_1 -- sale_detail_jt をスキャンし、customer_id の null 値をフィルターし、M1 からのブロードキャストデータとマップサイドハッシュ結合 (HASHJOIN) を実行します。結果は customer_id の `HASH` によって分散されます。
R3_2 -- GROUP BY customer_id で集約し、SUM(total_price) と COUNT(total_price) を計算します。両方の集約は `Complete` モードで実行されます。
R4_3 -- 最終的な列を選択し、エイリアスを付け (ashop、ap、bp)、出力を画面に書き込みます。

例 2：マップサイド結合 (MAPJOIN ヒント)

この例では、/*+ mapjoin(a) */ ヒントを使用して、非等価結合条件 (a.total_price < b.total_price) でマップサイド結合を強制します。

クエリ：

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 の実行：

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

例 1 との主な違い：

M1 は、非等価結合条件が結合後に評価されるため、フィルターを適用せずに sale_detail_jt (mapjoin ヒントで指定されたテーブル) をブロードキャストします。
M2_1 は HASHJOIN を実行し、次にフィルター LT(total_price, total_price) (a.total_price < b.total_price を表す) を適用し、部分集約 (Partial_1 フェーズ) を実行します。
R3_2 は Final フェーズで集約を完了し、M2_1 からの部分的な結果をマージします。
R4_3 は、例 1 と同様に、最終的な出力列を選択してエイリアスを付けます。