全部產品
Search

搜尋本產品

    MaxCompute：EXPLAIN

    文件中心

    MaxCompute：EXPLAIN

    更新時間：Dec 10, 2024

    在實際開發過程中，通常需要分析查詢語句或表結構來分析效能瓶頸，MaxCompute SQL為您提供explain語句實現此功能。本文為您介紹explain的功能、命令格式及使用樣本。

    功能介紹

    EXPLAIN語句可以顯示MaxCompute SQL對應的DML語句執行計畫（執行SQL語義的程式）的結構，協助您瞭解SQL語句的處理過程，為最佳化SQL語句提供協助。一個查詢語句作業會對應多個Job，一個Job對應多個Task。

    說明

    如果查詢語句足夠複雜，EXPLAIN的結果較多，超過4 MB則會觸發API的限制，無法得到完整的EXPLAIN結果。此時您可以拆分查詢語句，對各部分分別執行EXPLAIN語句，以瞭解Job的結構。

    命令格式

    EXPLAIN <dml query>;

    dml query：必填。SELECT語句，更多資訊請參見SELECT文法。

    返回說明

    EXPLAIN的執行結果包含如下資訊：

    • Job間的依賴關係

      例如job0 is root job。如果查詢只需要一個Job（job0），只會顯示一行資訊。

    • Task間的依賴關係 

      In Job job0:
root Tasks: M1, M2
J3_1_2_Stg1 depends on: M1, M2

      job0包含三個Task，M1、M2和J3_1_2_Stg1。系統會先執行M1和M2兩個Task，執行完成後，再執行J3_1_2_Stg1。

      Task的命名規則如下：

      • 在MaxCompute中，共有四種Task類型：MapTask、ReduceTask、JoinTask和LocalWork。Task名稱的第一個字母表示了當前Task的類型，例如M2Stg1就是一個MapTask。

      • 緊跟著第一個字母后的數字，代表了當前Task的ID。這個ID在當前查詢對應的所有Task中是唯一的。

      • 用底線（_）分隔的數字代表當前Task的直接依賴，例如J3_1_2_Stg1表示當前Task ID為3，依賴ID為1（M1）和ID為2（M2）的兩個Task。

    • Task中所有Operator的依賴結構。

      Operator串描述了一個Task的執行語義。結構樣本如下：

      In Task M2:
    Data source: mf_mc_bj.sale_detail_jt/sale_date=2013/region=china  # "Data source"描述了當前Task的輸入內容。
    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

      各Operator的含義如下：

      • TableScanOperator（TS）：描述查詢語句中的FROM語句塊的邏輯。EXPLAIN結果中會顯示輸入表的名稱（Alias）。

      • SelectOperator（SEL）：描述查詢語句中的SELECT語句塊的邏輯。EXPLAIN結果中會顯示向下一個Operator傳遞的列，多個列由逗號分隔。

        • 如果是列的引用，則顯示為<alias>.<column_name>。

        • 如果是運算式的結果，則顯示為函數形式，例如func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))。

        • 如果是常量，則直接顯示常量值。

      • FilterOperator（FIL）：描述查詢語句中的WHERE語句塊的邏輯。EXPLAIN結果中會顯示一個WHERE條件運算式，形式類似SelectOperator的顯示規則。

      • JoinOperator（JOIN）：描述查詢語句中的JOIN語句塊的邏輯。EXPLAIN結果中會顯示哪些表以哪種方式JOIN在一起。

      • GroupByOperator（例如AGGREGATE）：描述彙總操作的邏輯。如果查詢中使用了彙總函式，就會出現該結構，EXPLAIN結果中會顯示彙總函式的內容。

      • ReduceSinkOperator（RS）：描述Task間資料分發操作的邏輯。如果當前Task的結果會傳遞給另一個Task，則必然需要在當前Task的最後，使用ReduceSinkOperator執行資料分發操作。EXPLAIN的結果中會顯示輸出結果的排序方式、分發的Key、Value以及用來求Hash值的列。

      • FileSinkOperator（FS）：描述最終資料的儲存操作。如果查詢中有INSERT語句塊，EXPLAIN結果中會顯示目標表名稱。

      • LimitOperator（LIM）：描述查詢語句中的LIMIT語句塊的邏輯。EXPLAIN結果中會顯示LIMIT數。

      • MapjoinOperator（HASHJOIN）：類似JoinOperator，描述大表的JOIN操作。

    樣本資料

    為便於理解，本文為您提供來源資料，基於來源資料提供相關樣本。建立表sale_detail和sale_detail_jt，並添加資料，命令樣本如下：

    --建立分區表sale_detail和sale_detail_jt。
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);

--向源表增加分區。
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      |
+------------+-------------+-------------+------------+------------+

--建立做關聯的表。
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