EXPLAIN - MaxCompute - Centro de documentación de Alibaba Cloud

La instrucción EXPLAIN muestra el plan de ejecución de una consulta SELECT en MaxCompute SQL. Esto le ayuda a identificar cuellos de botella de rendimiento en las consultas o en las estructuras de las tablas.

Una consulta se asigna a uno o más jobs, y cada uno contiene una o más tareas. EXPLAIN revela cómo se relacionan los jobs, las tareas y los operadores para que usted comprenda y optimice su código SQL.

Sintaxis

EXPLAIN <query>;

query: obligatorio. Una instrucción SELECT. Para obtener más información, consulte SELECT syntax.

Estructura de la salida

La salida de EXPLAIN consta de tres secciones:

Dependencias de jobs: enumera todos los jobs y su orden de ejecución. job0 is root job significa que la consulta requiere un único job raíz.
Dependencias de tareas: enumera las tareas dentro de cada job y sus dependencias. A continuación se muestra una salida donde job0 contiene tres tareas: M1, M2 y J3_1_2_Stg1. MaxCompute ejecuta J3_1_2_Stg1 solo después de que finalicen M1 y M2.
```
    In Job job0:
    root Tasks: M1, M2
    J3_1_2_Stg1 depends on: M1, M2
```

Detalles de los operadores: describe los operadores dentro de cada tarea y su semántica de ejecución. La línea Data source identifica la entrada de la tarea. Cada línea posterior representa un operador y sus parámetros, con sangría para mostrar la canalización del operador.

    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

Convenciones de nomenclatura de tareas

Componente	Significado	Ejemplo
Primera letra	Tipo de tarea: `M` (MapTask), `R` (ReduceTask), `J` (JoinTask) o `L` (LocalWork).	`M2Stg1` es una tarea map.
Dígito después de la primera letra	Identificador de la tarea, único dentro de la consulta.	`M2` tiene el identificador de tarea 2.
Dígitos separados por guiones bajos	Dependencias directas de la tarea.	`J3_1_2_Stg1` depende de M1 y M2.

Operadores

Operador	Abreviatura	Cláusula SQL	Descripción
TableScanOperator	TS	`FROM`	Escanea la tabla de entrada. La salida muestra el alias de dicha tabla.
SelectOperator	SEL	`SELECT`	Proyecta columnas hacia el siguiente operador. Una columna se muestra como `<alias>.<column_name>`, una expresión aparece como una lista de funciones (por ejemplo, `func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))`) y una constante muestra su valor literal.
FilterOperator	FIL	`WHERE`	Filtra filas según una expresión `WHERE`, mostrada en un formato similar al de SelectOperator.
JoinOperator	JOIN	`JOIN`	Combina tablas. La salida indica qué tablas se combinan y el método utilizado.
GroupByOperator	AGGREGATE	Funciones de agregación	Ejecuta la agregación. Aparece cuando la consulta contiene funciones de agregación. La salida muestra el contenido de dichas funciones.
ReduceSinkOperator	RS	--	Distribuye datos entre tareas. Aparece al final de una tarea cuando su salida alimenta a otra. La salida muestra el orden de clasificación, las claves de distribución, los valores y las columnas hash.
FileSinkOperator	FS	`INSERT`	Escribe los resultados finales en el almacenamiento. En las instrucciones `INSERT`, la salida muestra el nombre de la tabla de destino.
LimitOperator	LIM	`LIMIT`	Limita la cantidad de filas devueltas.
MapjoinOperator	HASHJOIN	`JOIN`	Ejecuta una combinación del lado map en tablas grandes. Es similar a JoinOperator.

Cada línea de operador también genera una línea Statistics que muestra la cantidad estimada de filas y el tamaño de los datos en ese punto de la canalización. Por ejemplo, Statistics: Num rows: 3.0, Data size: 324.0 indica cuántas filas espera generar el optimizador para ese operador. Utilice estas estimaciones para identificar operadores que procesan muchos más datos de lo esperado, ya que suele ser señal de un filtro faltante o de una clave de combinación sesgada.

La salida de ReduceSinkOperator (RS) incluye los siguientes campos:

Campo	Descripción
`order`	Dirección de ordenamiento para cada clave: `+` (ascendente) o `-` (descendente).
`nullDirection`	Indica cómo se ordenan los valores nulos respecto a los no nulos: `*` (orden predeterminado), `a` (nulos primero) o `z` (nulos al final).
`optimizeOrderBy`	Indica si el optimizador puede omitir la ordenación completa porque los datos ya están ordenados. `True` significa que se omite la ordenación.
`valueDestLimit`	Cantidad máxima de filas enviadas a la tarea descendente. `0` significa que no hay límite. Se asigna a la cláusula `LIMIT` cuando está presente.
`dist`	Método de distribución: `HASH` particiona las filas mediante el hash de las columnas clave; `BROADCAST` copia todas las filas a cada tarea descendente.
`keys`	Columnas utilizadas para particionar y ordenar los datos entre tareas.
`values`	Columnas pasadas a la tarea descendente.
`partitions`	Columnas utilizadas para determinar qué tarea recibe cada fila.

Límites

Si una consulta es compleja y la salida de EXPLAIN supera los 4 MB, se alcanza el límite de la API de la aplicación de capa superior y la salida se trunca. Para evitarlo, divida la consulta en subconsultas más pequeñas y ejecute EXPLAIN en cada una por separado.

Ejemplos

Preparar datos de muestra

Cree dos tablas particionadas, sale_detail y sale_detail_jt, e inserte datos de muestra.

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

Compruebe los datos:

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

Cree una tabla no particionada para la operación JOIN:

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

Ejemplo 1: JOIN estándar con agregación

En este ejemplo se ejecuta EXPLAIN sobre una consulta que realiza un INNER JOIN entre sale_detail_jt y sale_detail, agrupa los resultados y aplica un ordenamiento con límite.

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;

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

Salida:

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

El plan de ejecución muestra cuatro tareas:

M1: escanea sale_detail, filtra los valores nulos de customer_id y transmite los datos a todas las tareas descendentes (dist: BROADCAST). Los campos vacíos keys y partitions confirman que los datos no se particionan, por lo que cada fila se envía a todas partes.
M2_1: escanea sale_detail_jt, filtra los valores nulos de customer_id y ejecuta una combinación hash del lado map (HASHJOIN) con los datos transmitidos desde M1. Los resultados se particionan por hash según customer_id para la tarea reduce descendente.
R3_2: agrega los datos con GROUP BY customer_id y calcula SUM(total_price) y COUNT(total_price). Ambas operaciones se ejecutan en modo Complete, lo que significa que la agregación completa ocurre en una sola fase sin preagregación parcial.
R4_3: selecciona las columnas finales, les asigna alias (ashop, ap, bp) y escribe la salida en pantalla.

Ejemplo 2: Combinación del lado map (sugerencia MAPJOIN)

Este ejemplo utiliza la sugerencia /*+ mapjoin(a) */ para forzar una combinación del lado map con una condición de combinación no 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;

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

Salida:

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

Diferencias principales respecto al ejemplo 1:

M1 transmite sale_detail_jt (la tabla especificada por la sugerencia mapjoin) sin aplicar filtros. Esto ocurre porque la condición de combinación no equitativa se evalúa después de la combinación y no antes.
M2_1 ejecuta el HASHJOIN, luego aplica el filtro LT(total_price, total_price) (que representa a.total_price < b.total_price) y ejecuta una agregación parcial (fase Partial_1). La fase Partial_1 preagrega los datos del lado map, lo que reduce el volumen enviado a la tarea reduce.
R3_2 completa la agregación en la fase Final y fusiona los resultados parciales de M2_1. Este enfoque de dos fases (de Partial_1 a Final) resulta más eficiente que el modo Complete de una sola fase del ejemplo 1 cuando el volumen de datos del lado map es grande.
R4_3 selecciona las columnas de salida finales y les asigna alias, igual que en el ejemplo 1.