Gunakan EXPLAIN untuk menganalisis rencana eksekusi kueri SQL - MaxCompute

Pernyataan EXPLAIN menampilkan rencana eksekusi kueri SELECT dalam MaxCompute SQL, membantu Anda mengidentifikasi bottleneck performa pada pernyataan kueri atau struktur tabel.

Satu kueri dipetakan ke satu atau beberapa job, dan setiap job terdiri atas satu atau beberapa task. Pernyataan EXPLAIN mengungkapkan hubungan antara job, task, dan operator sehingga Anda dapat mengoptimalkan SQL Anda.

Sintaksis

EXPLAIN <query>;

query: Wajib diisi. Merupakan pernyataan SELECT. Untuk informasi lebih lanjut, lihat sintaksis SELECT.

Struktur output

Output EXPLAIN terdiri dari tiga bagian:

Ketergantungan job — Menampilkan semua job beserta urutan eksekusinya. Contohnya, job0 is root job menunjukkan bahwa kueri hanya memerlukan satu job utama.
Ketergantungan task — Menampilkan task dalam setiap job beserta ketergantungannya. Contohnya: job0 berisi tiga task: M1, M2, dan J3_1_2_Stg1. MaxCompute menjalankan J3_1_2_Stg1 hanya setelah M1 dan M2 selesai.
```
    In Job job0:
    root Tasks: M1, M2
    J3_1_2_Stg1 depends on: M1, M2
```

Detail operator — Menjelaskan operator dalam setiap task beserta semantik eksekusinya. Contohnya: Baris Data source mengidentifikasi input task tersebut. Setiap baris berikutnya merepresentasikan satu operator beserta parameternya, dengan indentasi yang menunjukkan alur pipeline operator.

    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

Konvensi penamaan task

Komponen	Makna	Contoh
Huruf pertama	Jenis task: `M` (MapTask), `R` (ReduceTask), `J` (JoinTask), atau `L` (LocalWork)	`M2Stg1` adalah map task
Angka setelah huruf pertama	ID task, unik dalam satu kueri	`M2` memiliki ID task 2
Angka yang dipisahkan garis bawah	Ketergantungan Langsung Tugas	`J3_1_2_Stg1` bergantung pada M1 dan M2

Operator

Operator	Singkatan	Klausa SQL	Deskripsi
TableScanOperator	TS	`FROM`	Memindai tabel input. Output menampilkan alias dari tabel input tersebut.
SelectOperator	SEL	`SELECT`	Memproyeksikan kolom ke operator berikutnya. Kolom ditampilkan sebagai `<alias>.<column_name>`, ekspresi ditampilkan sebagai daftar fungsi (misalnya, `func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))`), dan konstanta ditampilkan sebagai nilai literalnya.
FilterOperator	FIL	`WHERE`	Menyaring baris berdasarkan ekspresi `WHERE`, ditampilkan dalam format yang mirip dengan SelectOperator.
JoinOperator	JOIN	`JOIN`	Melakukan join antar tabel. Output menunjukkan tabel mana saja yang di-join dan metode join yang digunakan.
GroupByOperator	AGGREGATE	Fungsi agregat	Melakukan agregasi. Muncul ketika kueri berisi fungsi agregat. Output menampilkan isi fungsi agregat tersebut.
ReduceSinkOperator	RS	--	Mendistribusikan data antar task. Muncul di akhir task ketika outputnya menjadi input task lain. Output menampilkan urutan pengurutan, kunci distribusi, nilai, dan kolom hash.
FileSinkOperator	FS	`INSERT`	Menulis hasil akhir ke penyimpanan. Untuk pernyataan `INSERT`, output menampilkan nama tabel tujuan.
LimitOperator	LIM	`LIMIT`	Membatasi jumlah baris yang dikembalikan.
MapjoinOperator	HASHJOIN	`JOIN`	Melakukan map-side join pada tabel besar. Mirip dengan JoinOperator.

Batasan

Jika pernyataan kueri kompleks dan ukuran baris dalam hasil output pernyataan EXPLAIN melebihi 4 MB, ambang batas yang ditentukan oleh API aplikasi lapisan atas akan tercapai. Akibatnya, hasil output tidak dapat ditampilkan secara lengkap. Untuk mengatasi masalah ini, Anda dapat membagi pernyataan kueri menjadi beberapa subkueri dan menjalankan pernyataan EXPLAIN pada masing-masing subkueri untuk mendapatkan struktur job tersebut.

Contoh

Siapkan data sampel

Buat dua tabel partisi, sale_detail dan sale_detail_jt, lalu masukkan data sampel.

-- Buat dua tabel partisi bernama sale_detail dan 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);

-- Tambahkan partisi ke kedua tabel.
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');

-- Masukkan data ke dalam tabel.
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);

Verifikasi data:

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      |
+------------+-------------+-------------+------------+------------+

Buat tabel non-partisi untuk operasi JOIN:

SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

Contoh 1: JOIN standar dengan agregasi

Contoh ini menjalankan EXPLAIN pada kueri yang melakukan INNER JOIN antara sale_detail_jt dan sale_detail, mengelompokkan hasilnya, lalu menerapkan pengurutan dengan batasan jumlah baris.

Kueri:

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;

Jalankan 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;

Output:

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

Rencana eksekusi menampilkan empat tugas:

M1 — Memindai sale_detail, menyaring nilai customer_id yang null, lalu menyiarkan data tersebut (distribusi BROADCAST).
M2_1 — Memindai sale_detail_jt, menyaring nilai customer_id yang null, lalu melakukan map-side hash join (HASHJOIN) dengan data siaran dari M1. Hasilnya didistribusikan berdasarkan HASH pada customer_id.
R3_2 — Melakukan agregasi dengan GROUP BY customer_id, menghitung SUM(total_price) dan COUNT(total_price). Kedua agregasi dijalankan dalam mode Complete.
R4_3 — Memilih kolom akhir, memberi alias (ashop, ap, bp), lalu menulis output ke layar.

Contoh 2: Map-side join (petunjuk MAPJOIN)

Contoh ini menggunakan petunjuk /*+ mapjoin(a) */ untuk memaksa map-side join dengan kondisi join non-equi (a.total_price < b.total_price).

Kueri:

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;

Jalankan 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;

Output:

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

Perbedaan utama dibandingkan Contoh 1:

M1 menyiarkan sale_detail_jt (tabel yang ditentukan oleh petunjuk mapjoin) tanpa menerapkan filter, karena kondisi join non-equi dievaluasi setelah proses join.
M2_1 melakukan HASHJOIN, lalu menerapkan filter LT(total_price, total_price) (merepresentasikan a.total_price < b.total_price), dan menjalankan agregasi parsial (Partial_1 phase).
R3_2 menyelesaikan agregasi dalam fase Final, menggabungkan hasil parsial dari M2_1.
R4_3 memilih dan memberi alias kolom output akhir, sama seperti pada Contoh 1.