EXPLAIN - MaxCompute: Central de Documentação do Alibaba Cloud

A instrução EXPLAIN exibe o plano de execução de uma consulta SELECT no MaxCompute SQL. Esse recurso ajuda a identificar gargalos de desempenho nas instruções de consulta ou nas estruturas das tabelas.

Uma consulta é mapeada para um ou mais jobs, cada um contendo uma ou mais tarefas. O comando EXPLAIN revela como jobs, tarefas e operadores se relacionam, permitindo que você compreenda e otimize seu SQL.

Sintaxe

EXPLAIN <query>;

query: Obrigatório. Uma instrução SELECT. Para obter mais informações, consulte SELECT syntax.

Estrutura da saída

A saída do EXPLAIN possui três seções:

Dependências de jobs — Lista todos os jobs e sua ordem de execução. A mensagem job0 is root job indica que a consulta requer um único job raiz.
Dependências de tarefas — Apresenta as tarefas dentro de cada job e suas dependências. A saída abaixo significa que job0 contém três tarefas — M1, M2 e J3_1_2_Stg1 — e que o MaxCompute executa J3_1_2_Stg1 somente após a conclusão de M1 e M2.
```
    In Job job0:
    root Tasks: M1, M2
    J3_1_2_Stg1 depends on: M1, M2
```

Detalhes dos operadores — Descreve os operadores dentro de cada tarefa e suas semânticas de execução. A linha Data source identifica a entrada da tarefa. Cada linha subsequente representa um operador e seus parâmetros, com recuo para mostrar o pipeline de operadores.

    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

Convenções de nomenclatura de tarefas

Componente	Significado	Exemplo
Primeira letra	Tipo de tarefa: `M` (MapTask), `R` (ReduceTask), `J` (JoinTask) ou `L` (LocalWork)	`M2Stg1` é uma tarefa de map
Dígito após a primeira letra	ID da tarefa, exclusivo dentro da consulta	`M2` tem o ID de tarefa 2
Dígitos separados por sublinhados	Dependências diretas da tarefa	`J3_1_2_Stg1` depende de M1 e M2

Operadores

Operador	Abreviação	Cláusula SQL	Descrição
TableScanOperator	TS	`FROM`	Varre a tabela de entrada. A saída mostra o alias da tabela de entrada.
SelectOperator	SEL	`SELECT`	Projeta colunas para o próximo operador. Uma coluna é exibida como `<alias>.<column_name>`, uma expressão aparece como uma lista de funções (por exemplo, `func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))`) e uma constante exibe seu valor literal.
FilterOperator	FIL	`WHERE`	Filtra linhas com base em uma expressão `WHERE`, exibida em formato semelhante ao do SelectOperator.
JoinOperator	JOIN	`JOIN`	Une tabelas. A saída indica quais tabelas foram unidas e o método de junção utilizado.
GroupByOperator	AGGREGATE	Funções de agregação	Executa agregações. Aparece quando a consulta contém funções de agregação. A saída mostra o conteúdo da função de agregação.
ReduceSinkOperator	RS	--	Distribui dados entre tarefas. Surge no final de uma tarefa quando sua saída alimenta outra tarefa. A saída exibe a ordem de classificação, chaves de distribuição, valores e colunas de hash.
FileSinkOperator	FS	`INSERT`	Grava os resultados finais no armazenamento. Para instruções `INSERT`, a saída mostra o nome da tabela de destino.
LimitOperator	LIM	`LIMIT`	Limita o número de linhas retornadas.
MapjoinOperator	HASHJOIN	`JOIN`	Executa uma junção no lado do map para tabelas grandes. Funciona de maneira similar ao JoinOperator.

Cada linha de operador também emite uma linha Statistics que mostra a contagem estimada de linhas e o tamanho dos dados naquele ponto do pipeline. Por exemplo, Statistics: Num rows: 3.0, Data size: 324.0 informa quantas linhas o otimizador espera que o operador produza. Utilize essas estimativas para identificar operadores que processam muito mais dados do que o esperado — um sinal comum de filtro ausente ou chave de junção desbalanceada.

A saída do ReduceSinkOperator (RS) inclui os seguintes campos:

Campo	Descrição
`order`	Direção de ordenação para cada chave: `+` (ascendente) ou `-` (descendente).
`nullDirection`	Define como valores nulos são ordenados em relação aos não nulos: `*` (ordem padrão), `a` (nulos primeiro) ou `z` (nulos por último).
`optimizeOrderBy`	Indica se o otimizador pode ignorar uma ordenação completa porque os dados já estão ordenados. O valor `True` significa que a ordenação foi ignorada.
`valueDestLimit`	Número máximo de linhas enviadas para a tarefa subsequente. O valor `0` indica ausência de limite. Corresponde à cláusula `LIMIT` quando presente.
`dist`	Método de distribuição: `HASH` particiona linhas pelo hash das colunas-chave; `BROADCAST` copia todas as linhas para cada tarefa subsequente.
`keys`	Colunas usadas para particionar e ordenar dados entre tarefas.
`values`	Colunas passadas para a tarefa subsequente.
`partitions`	Colunas usadas para determinar qual tarefa recebe cada linha.

Limitações

Se uma consulta for complexa e a saída do EXPLAIN ultrapassar 4 MB, o limite da API da aplicação de camada superior será atingido e a saída será truncada. Para contornar isso, divida a consulta em subconsultas menores e execute EXPLAIN em cada uma separadamente.

Exemplos

Preparar dados de amostra

Crie duas tabelas particionadas, sale_detail e sale_detail_jt, e insira dados de amostra.

-- Create two partitioned tables named sale_detail and 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);

-- Add partitions to the two tables.
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 data into the tables.
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);

Verifique os dados:

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

Crie uma tabela não particionada para a operação de JOIN:

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

Exemplo 1: JOIN padrão com agregação

Este exemplo executa EXPLAIN em uma consulta que realiza um INNER JOIN entre sale_detail_jt e sale_detail, agrupa os resultados e aplica ordenação com limite.

Consulta:

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;

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

Saída:

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

O plano de execução mostra quatro tarefas:

M1 — Varre sale_detail, filtra valores nulos de customer_id e transmite os dados para todas as tarefas subsequentes (dist: BROADCAST). Os campos keys e partitions vazios confirmam que os dados não são particionados — cada linha vai para todos os lugares.
M2_1 — Lê sale_detail_jt, filtra valores nulos de customer_id e executa uma junção hash no lado do map (HASHJOIN) com os dados transmitidos de M1. Os resultados são particionados por hash usando customer_id para a tarefa de reduce subsequente.
R3_2 — Agrega com GROUP BY customer_id, calculando SUM(total_price) e COUNT(total_price). Ambas as operações rodam no modo Complete, o que significa que a agregação completa ocorre em uma única fase, sem pré-agregação parcial.
R4_3 — Seleciona as colunas finais, aplica aliases (ashop, ap, bp) e grava a saída na tela.

Exemplo 2: Junção no lado do map (hint MAPJOIN)

Neste exemplo, utiliza-se o hint /*+ mapjoin(a) */ para forçar uma junção no lado do map com uma condição de junção não equitativa (a.total_price < b.total_price).

Consulta:

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;

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

Saída:

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

Principais diferenças em relação ao Exemplo 1:

M1 transmite sale_detail_jt (a tabela especificada pelo hint mapjoin) sem filtro, pois a condição de junção não equitativa é avaliada após a junção, e não antes.
M2_1 executa o HASHJOIN, aplica o filtro LT(total_price, total_price) (representando a.total_price < b.total_price) e roda uma agregação parcial (fase Partial_1). A fase Partial_1 pré-agrega os dados no lado do map, reduzindo o volume enviado para a tarefa de reduce.
R3_2 conclui a agregação na fase Final, mesclando os resultados parciais de M2_1. A abordagem em duas fases (de Partial_1 para Final) é mais eficiente do que o modo Complete de fase única do Exemplo 1 quando o volume de dados no lado do map é grande.
R4_3 seleciona e aplica aliases às colunas de saída finais, da mesma forma que no Exemplo 1.