EXPLAIN - MaxCompute - Centre de documentation Alibaba Cloud

L'instruction EXPLAIN affiche le plan d'exécution d'une requête SELECT dans MaxCompute SQL. Elle vous aide à identifier les goulots d'étranglement liés aux performances des instructions de requête ou des structures de table.

Une requête correspond à un ou plusieurs jobs, chacun contenant une ou plusieurs tâches. L'instruction EXPLAIN révèle les relations entre les jobs, les tâches et les opérateurs, ce qui vous permet de comprendre et d'optimiser votre code SQL.

Syntaxe

EXPLAIN <query>;

query : Obligatoire. Une instruction SELECT. Pour plus d'informations, consultez la rubrique Syntaxe SELECT.

Structure de la sortie

La sortie de l'instruction EXPLAIN comprend trois sections :

Dépendances des jobs — Répertorie tous les jobs et leur ordre d'exécution. La mention job0 is root job indique que la requête nécessite un seul job racine.
Dépendances des tâches — Répertorie les tâches au sein de chaque job et leurs dépendances. La sortie suivante signifie que job0 contient trois tâches : M1, M2 et J3_1_2_Stg1. MaxCompute exécute J3_1_2_Stg1 uniquement après l'achèvement des tâches M1 et M2.
```
    In Job job0:
    root Tasks: M1, M2
    J3_1_2_Stg1 depends on: M1, M2
```

Détails des opérateurs — Décrit les opérateurs au sein de chaque tâche et leur sémantique d'exécution. La ligne Data source identifie l'entrée de la tâche. Chaque ligne suivante représente un opérateur et ses paramètres, avec une indentation indiquant le pipeline d'opérateurs.

    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

Conventions de nommage des tâches

Composant

Signification

Exemple

Première lettre

Type de tâche : M (MapTask), R (ReduceTask), J (JoinTask) ou L (LocalWork)

M2Stg1 est une tâche map

Chiffre suivant la première lettre

ID de la tâche, unique au sein de la requête

M2 a pour ID de tâche 2

Chiffres séparés par des traits de soulignement

Dépendances directes de la tâche

J3_1_2_Stg1 dépend de M1 et M2

Opérateurs

Opérateur

Abréviation

Clause SQL

Description

TableScanOperator

FROM

Analyse la table d'entrée. La sortie affiche l'alias de la table d'entrée.

SelectOperator

SEL

SELECT

Projette les colonnes vers l'opérateur suivant. Une colonne s'affiche sous la forme <alias>.<column_name>, une expression s'affiche sous forme de liste de fonctions (par exemple, func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))) et une constante affiche sa valeur littérale.

FilterOperator

FIL

WHERE

Filtre les lignes en fonction d'une expression WHERE, affichée dans un format similaire à celui de SelectOperator.

JoinOperator

JOIN

JOIN

Joint les tables. La sortie indique quelles tables sont jointes et la méthode de jointure utilisée.

GroupByOperator

AGGREGATE

Fonctions d'agrégation

Effectue une agrégation. Apparaît lorsque la requête contient des fonctions d'agrégation. La sortie affiche le contenu de la fonction d'agrégation.

ReduceSinkOperator

Distribue les données entre les tâches. Apparaît à la fin d'une tâche lorsque sa sortie alimente une autre tâche. La sortie affiche l'ordre de tri, les clés de distribution, les valeurs et les colonnes de hachage.

FileSinkOperator

INSERT

Écrit les résultats finaux dans le stockage. Pour les instructions INSERT, la sortie affiche le nom de la table cible.

LimitOperator

LIM

LIMIT

Limite le nombre de lignes renvoyées.

MapjoinOperator

HASHJOIN

JOIN

Effectue une jointure côté map sur de grandes tables. Similaire à JoinOperator.

Chaque ligne d'opérateur émet également une ligne Statistics indiquant le nombre estimé de lignes et la taille des données à ce stade du pipeline. Par exemple, Statistics: Num rows: 3.0, Data size: 324.0 vous indique le nombre de lignes que l'optimiseur s'attend à voir produire par l'opérateur. Utilisez ces estimations pour identifier les opérateurs qui traitent beaucoup plus de données que prévu, signe courant d'un filtre manquant ou d'une clé de jointure déséquilibrée.

La sortie ReduceSinkOperator (RS) inclut les champs suivants :

Champ

Description

order

Direction de tri pour chaque clé : + (croissant) ou - (décroissant).

nullDirection

Mode de tri des valeurs nulles par rapport aux valeurs non nulles : * (ordre par défaut), a (valeurs nulles en premier) ou z (valeurs nulles en dernier).

optimizeOrderBy

Indique si l'optimiseur peut ignorer un tri complet car les données sont déjà ordonnées. La valeur True signifie que le tri est ignoré.

valueDestLimit

Nombre maximal de lignes envoyées à la tâche en aval. La valeur 0 signifie qu'il n'y a aucune limite. Correspond à la clause LIMIT lorsqu'elle est présente.

dist

Méthode de distribution : HASH partitionne les lignes par hachage des colonnes clés ; BROADCAST copie toutes les lignes vers chaque tâche en aval.

keys

Colonnes utilisées pour partitionner et trier les données entre les tâches.

values

Colonnes transmises à la tâche en aval.

partitions

Colonnes utilisées pour déterminer quelle tâche reçoit chaque ligne.

Limites

Si une requête est complexe et que la sortie de l'instruction EXPLAIN dépasse 4 Mo, la limite supérieure de l'API de l'application est atteinte et la sortie est tronquée. Pour contourner ce problème, divisez la requête en sous-requêtes plus petites et exécutez l'instruction EXPLAIN sur chacune d'elles séparément.

Exemples

Préparer les exemples de données

Créez deux tables partitionnées, sale_detail et sale_detail_jt, et insérez des exemples de données.

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

Vérifiez les données :

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

Créez une table non partitionnée pour l'opération JOIN :

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

Exemple 1 : Jointure standard avec agrégation

Cet exemple exécute l'instruction EXPLAIN sur une requête qui effectue une jointure INNER JOIN entre sale_detail_jt et sale_detail, regroupe les résultats et applique un ordre avec une limite.

Requête :

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;

Exécuter 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;

Sortie :

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

Le plan d'exécution affiche quatre tâches :

M1 — Analyse sale_detail, filtre les valeurs customer_id nulles et diffuse les données vers toutes les tâches en aval (dist: BROADCAST). Les champs keys et partitions vides confirment que les données ne sont pas partitionnées : chaque ligne est envoyée partout.
M2_1 — Analyse sale_detail_jt, filtre les valeurs customer_id nulles et effectue une jointure de hachage côté map (HASHJOIN) avec les données diffusées depuis M1. Les résultats sont partitionnés par hachage selon customer_id pour la tâche reduce en aval.
R3_2 — Agrège avec GROUP BY customer_id, en calculant SUM(total_price) et COUNT(total_price). Les deux s'exécutent en mode Complete, ce qui signifie que l'agrégation complète se fait en une seule phase sans pré-agrégation partielle.
R4_3 — Sélectionne les colonnes finales, leur attribue des alias (ashop, ap, bp) et écrit la sortie à l'écran.

Exemple 2 : Jointure côté map (indication MAPJOIN)

Cet exemple utilise une indication /*+ mapjoin(a) */ pour forcer une jointure côté map avec une condition de jointure non équivalente (a.total_price < b.total_price).

Requête :

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;

Exécuter 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;

Sortie :

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

Principales différences par rapport à l'exemple 1 :

M1 diffuse sale_detail_jt (la table spécifiée par l'indication mapjoin) sans filtre, car la condition de jointure non équivalente est évaluée après la jointure plutôt qu'avant.
M2_1 effectue la jointure HASHJOIN, puis applique le filtre LT(total_price, total_price) (représentant a.total_price < b.total_price), et exécute une agrégation partielle (phase Partial_1). La phase Partial_1 pré-agrège les données côté map, réduisant le volume envoyé à la tâche reduce.
R3_2 termine l'agrégation dans la phase Final, en fusionnant les résultats partiels de M2_1. L'approche en deux phases (de Partial_1 à Final) est plus efficace que le mode Complete en une seule phase de l'exemple 1 lorsque le volume de données côté map est important.
R4_3 sélectionne et attribue des alias aux colonnes de sortie finales, comme dans l'exemple 1.