Utilisation des commandes EXPLAIN et EXPLAIN ANALYZE pour analyser les plans d'exécution - AnalyticDB

Lorsqu'une requête s'exécute lentement ou consomme une quantité excessive de mémoire, le plan d'exécution vous indique précisément où le temps et les ressources sont dépensés. La commande EXPLAIN affiche le chemin prévu par l'optimiseur pour une requête avant son exécution. La commande EXPLAIN ANALYZE exécute la requête et capture les coûts réels de l'exécution distribuée : durées au niveau des opérateurs, utilisation maximale de la mémoire et nombre de lignes en entrée/sortie. Vous pouvez ainsi comparer les estimations à la réalité et identifier les goulots d'étranglement.

Prérequis

Avant de commencer, assurez-vous que :

Votre cluster AnalyticDB for MySQL est en version V3.1.3 ou ultérieure. Pour vérifier la version, consultez la rubrique Comment afficher la version d'un cluster AnalyticDB for MySQL ?. Pour mettre à niveau, soumettez un ticket.

EXPLAIN

La commande EXPLAIN évalue le chemin d'exécution prévu pour une requête SQL. Le résultat correspond à une estimation de l'optimiseur et peut ne pas refléter les résultats réels de l'exécution.

Syntaxe

EXPLAIN (format text) <SELECT statement>;

Remarque

Ajoutez l'option (format text) pour les requêtes non complexes afin d'améliorer la lisibilité de la hiérarchie arborescente dans le résultat.

Exemple

EXPLAIN (format text)
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA';

Le résultat se présente sous forme d'arborescence où chaque nœud représente un opérateur. Le champ Outputs de chaque nœud indique les colonnes produites par l'opérateur, tandis que le champ Estimates affiche les prévisions de l'optimiseur concernant le nombre de lignes et le volume de données. L'optimiseur utilise ces estimations pour déterminer l'ordre des jointures et la stratégie de redistribution des données.

Output[count(*)]
│   Outputs: [count:bigint]
│   Estimates: {rows: 1 (8B)}
│   count(*) := count
└─ Aggregate(FINAL)
   │   Outputs: [count:bigint]
   │   Estimates: {rows: 1 (8B)}
   │   count := count(`count_1`)
   └─ LocalExchange[SINGLE] ()
      │   Outputs: [count_0_1:bigint]
      │   Estimates: {rows: 1 (8B)}
      └─ RemoteExchange[GATHER]
         │   Outputs: [count_0_2:bigint]
         │   Estimates: {rows: 1 (8B)}
         └─ Aggregate(PARTIAL)
            │   Outputs: [count_0_4:bigint]
            │   Estimates: {rows: 1 (8B)}
            │   count_4 := count(*)
            └─ InnerJoin[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_6]
               │   Outputs: []
               │   Estimates: {rows: 302035 (4.61MB)}
               │   Distribution: REPLICATED
               ├─ Project[]
               │  │   Outputs: [c_nationkey:integer, $hashvalue:bigint]
               │  │   Estimates: {rows: 1500000 (5.72MB)}
               │  │   $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
               │  └─ RuntimeFilter
               │     │   Outputs: [c_nationkey:integer]
               │     │   Estimates: {rows: 1500000 (5.72MB)}
               │     ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
               │     │      Outputs: [c_nationkey:integer]
               │     │      Estimates: {rows: 1500000 (5.72MB)}
               │     │      c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
               │     └─ RuntimeCollect
               │        │   Outputs: [n_nationkey:integer]
               │        │   Estimates: {rows: 5 (60B)}
               │        └─ LocalExchange[ROUND_ROBIN] ()
               │           │   Outputs: [n_nationkey:integer]
               │           │   Estimates: {rows: 5 (60B)}
               │           └─ RuntimeScan
               │                  Outputs: [n_nationkey:integer]
               │                  Estimates: {rows: 5 (60B)}
               └─ LocalExchange[HASH][$hashvalue_0_6] ("n_nationkey")
                  │   Outputs: [n_nationkey:integer, $hashvalue_0_6:bigint]
                  │   Estimates: {rows: 5 (60B)}
                  └─ Project[]
                     │   Outputs: [n_nationkey:integer, $hashvalue_0_10:bigint]
                     │   Estimates: {rows: 5 (60B)}
                     │   $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
                     └─ RemoteExchange[REPLICATE]
                        │   Outputs: [n_nationkey:integer]
                        │   Estimates: {rows: 5 (60B)}
                        └─ InnerJoin[(`n_regionkey` = `r_regionkey`)][$hashvalue_0_7, $hashvalue_0_8]
                           │   Outputs: [n_nationkey:integer]
                           │   Estimates: {rows: 5 (60B)}
                           │   Distribution: REPLICATED
                           ├─ Project[]
                           │  │   Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue_0_7:bigint]
                           │  │   Estimates: {rows: 25 (200B)}
                           │  │   $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
                           │  └─ RuntimeFilter
                           │     │   Outputs: [n_nationkey:integer, n_regionkey:integer]
                           │     │   Estimates: {rows: 25 (200B)}
                           │     ├─ TableScan[adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
                           │     │      Outputs: [n_nationkey:integer, n_regionkey:integer]
                           │     │      Estimates: {rows: 25 (200B)}
                           │     │      n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
                           │     │      n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
                           │     └─ RuntimeCollect
                           │        │   Outputs: [r_regionkey:integer]
                           │        │   Estimates: {rows: 1 (4B)}
                           │        └─ LocalExchange[ROUND_ROBIN] ()
                           │           │   Outputs: [r_regionkey:integer]
                           │           │   Estimates: {rows: 1 (4B)}
                           │           └─ RuntimeScan
                           │                  Outputs: [r_regionkey:integer]
                           │                  Estimates: {rows: 1 (4B)}
                           └─ LocalExchange[HASH][$hashvalue_0_8] ("r_regionkey")
                              │   Outputs: [r_regionkey:integer, $hashvalue_0_8:bigint]
                              │   Estimates: {rows: 1 (4B)}
                              └─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
                                     Outputs: [r_regionkey:integer, $hashvalue_0_9:bigint]
                                     Estimates: {rows: 1 (4B)}/{rows: 1 (B)}
                                     $hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
                                     r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}

Paramètre	Description
`Outputs: [symbol:type]`	Colonne de sortie et type de données de chaque opérateur.
`Estimates: {rows: %s (%sB)}`	Nombre de lignes et volume de données estimés par l'optimiseur pour chaque opérateur. L'optimiseur utilise ces estimations pour déterminer l'ordre des jointures et la stratégie de redistribution des données.

EXPLAIN ANALYZE

La commande EXPLAIN ANALYZE exécute la requête et renvoie les coûts réels de l'exécution distribuée : durées d'exécution, utilisation maximale de la mémoire, ainsi que le nombre de lignes en entrée et en sortie pour chaque opérateur et fragment.

Syntaxe

EXPLAIN ANALYZE <SELECT statement>;

Exemple

EXPLAIN ANALYZE
SELECT count(*)
FROM nation, region, customer
WHERE c_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA';

Le résultat est organisé par fragment ; chaque fragment correspond à une étape du plan d'exécution distribué. Pour chaque opérateur, vous pouvez consulter les nombres de lignes réels, l'utilisation de la mémoire et le temps écoulé, en regard des estimations de l'optimiseur. Comparez les champs Estimates et Output pour identifier les écarts entre le modèle de l'optimiseur et l'exécution réelle. Utilisez les champs PeakMemory et WallTime pour repérer les opérateurs les plus coûteux.

Fragment 1 [SINGLE]
    Output: 1 row (9B), PeakMemory: 178KB, WallTime: 1.00ns, Input: 32 rows (288B); per task: avg.: 32.00 std.dev.: 0.00
    Output layout: [count]
    Output partitioning: SINGLE []
    Aggregate(FINAL)
    │   Outputs: [count:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 2 rows (18B), PeakMemory: 24B (0.00%), WallTime: 70.39us (0.03%)
    │   count := count(`count_1`)
    └─ LocalExchange[SINGLE] ()
       │   Outputs: [count1:bigint]
       │   Estimates: {rows: ? (?)}
       │   Output: 64 rows (576B), PeakMemory: 8KB (0.07%), WallTime: 238.69us (0.10%)
       └─ RemoteSource[2]
              Outputs: [count2:bigint]
              Estimates:
              Output: 32 rows (288B), PeakMemory: 32KB (0.27%), WallTime: 182.82us (0.08%)
              Input avg.: 4.00 rows, Input std.dev.: 264.58%

Fragment 2 [adb:AdbPartitioningHandle{schema=tpch, tableName=customer, dimTable=false, shards=32, tableEngineType=Cstore, partitionColumns=c_custkey, prunedBuckets= empty}]
    Output: 32 rows (288B), PeakMemory: 6MB, WallTime: 164.00ns, Input: 1500015 rows (20.03MB); per task: avg.: 500005.00 std.dev.: 21941.36
    Output layout: [count4]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count4:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 64 rows (576B), PeakMemory: 336B (0.00%), WallTime: 1.01ms (0.42%)
    │   count_4 := count(*)
    └─ INNER Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue6]
       │   Outputs: []
       │   Estimates: {rows: 302035 (4.61MB)}
       │   Output: 300285 rows (210B), PeakMemory: 641KB (5.29%), WallTime: 99.08ms (41.45%)
       │   Left (probe) Input avg.: 46875.00 rows, Input std.dev.: 311.24%
       │   Right (build) Input avg.: 0.63 rows, Input std.dev.: 264.58%
       │   Distribution: REPLICATED
       ├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=customer, partitionColumnHandles=[c_custkey]}]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: {rows: 1500000 (5.72MB)}/{rows: 1500000 (5.72MB)}
       │      Output: 1500000 rows (20.03MB), PeakMemory: 5MB (44.38%), WallTime: 68.29ms (28.57%)
       │      $hashvalue := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`c_nationkey`), 0))
       │      c_nationkey := AdbColumnHandle{columnName=c_nationkey, type=4, isIndexed=true}
       │      Input: 1500000 rows (7.15MB), Filtered: 0.00%
       └─ LocalExchange[HASH][$hashvalue6] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue6:bigint]
          │   Estimates: {rows: 5 (60B)}
          │   Output: 30 rows (420B), PeakMemory: 394KB (3.26%), WallTime: 455.03us (0.19%)
          └─ Project[]
             │   Outputs: [n_nationkey:integer, $hashvalue10:bigint]
             │   Estimates: {rows: 5 (60B)}
             │   Output: 15 rows (210B), PeakMemory: 24KB (0.20%), WallTime: 83.61us (0.03%)
             │   Input avg.: 0.63 rows, Input std.dev.: 264.58%
             │   $hashvalue_10 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_nationkey`), 0))
             └─ RemoteSource[3]
                    Outputs: [n_nationkey:integer]
                    Estimates:
                    Output: 15 rows (75B), PeakMemory: 24KB (0.20%), WallTime: 45.97us (0.02%)
                    Input avg.: 0.63 rows, Input std.dev.: 264.58%

Fragment 3 [adb:AdbPartitioningHandle{schema=tpch, tableName=nation, dimTable=true, shards=32, tableEngineType=Cstore, partitionColumns=, prunedBuckets= empty}]
    Output: 5 rows (25B), PeakMemory: 185KB, WallTime: 1.00ns, Input: 26 rows (489B); per task: avg.: 26.00 std.dev.: 0.00
    Output layout: [n_nationkey]
    Output partitioning: BROADCAST []
    INNER Join[(`n_regionkey` = `r_regionkey`)][$hashvalue7, $hashvalue8]
    │   Outputs: [n_nationkey:integer]
    │   Estimates: {rows: 5 (60B)}
    │   Output: 11 rows (64B), PeakMemory: 152KB (1.26%), WallTime: 255.86us (0.11%)
    │   Left (probe) Input avg.: 25.00 rows, Input std.dev.: 0.00%
    │   Right (build) Input avg.: 0.13 rows, Input std.dev.: 264.58%
    │   Distribution: REPLICATED
    ├─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=nation, partitionColumnHandles=[]}]
    │      Outputs: [n_nationkey:integer, n_regionkey:integer, $hashvalue7:bigint]
    │      Estimates: {rows: 25 (200B)}/{rows: 25 (200B)}
    │      Output: 25 rows (475B), PeakMemory: 16KB (0.13%), WallTime: 178.81us (0.07%)
    │      $hashvalue_7 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`n_regionkey`), 0))
    │      n_nationkey := AdbColumnHandle{columnName=n_nationkey, type=4, isIndexed=true}
    │      n_regionkey := AdbColumnHandle{columnName=n_regionkey, type=4, isIndexed=true}
    │      Input: 25 rows (250B), Filtered: 0.00%
    └─ LocalExchange[HASH][$hashvalue8] ("r_regionkey")
       │   Outputs: [r_regionkey:integer, $hashvalue8:bigint]
       │   Estimates: {rows: 1 (4B)}
       │   Output: 2 rows (28B), PeakMemory: 34KB (0.29%), WallTime: 57.41us (0.02%)
       └─ ScanProject[table = adb:AdbTableHandle{schema=tpch, tableName=region, partitionColumnHandles=[]}]
              Outputs: [r_regionkey:integer, $hashvalue9:bigint]
              Estimates: {rows: 1 (4B)}/{rows: 1 (4B)}
              Output: 1 row (14B), PeakMemory: 8KB (0.07%), WallTime: 308.99us (0.13%)
              $hashvalue_9 := `combine_hash`(BIGINT '0', COALESCE(`$operator$hash_code`(`r_regionkey`), 0))
              r_regionkey := AdbColumnHandle{columnName=r_regionkey, type=4, isIndexed=true}
              Input: 1 row (5B), Filtered: 0.00%

Paramètre	Signification
`Outputs: [symbol:type]`	Colonne de sortie et type de données de chaque opérateur.
`Estimates: {rows: %s (%sB)}`	Nombre de lignes et volume de données estimés par l'optimiseur. Comparez cette valeur avec le champ `Output` pour repérer les écarts entre les estimations et l'exécution réelle.
`PeakMemory: %s`	Utilisation maximale de la mémoire par l'opérateur ou le fragment. Utilisez cette métrique pour identifier le principal consommateur de mémoire lors du diagnostic des goulots d'étranglement liés à la mémoire.
`WallTime: %s`	Durée totale d'exécution des opérateurs. En raison du calcul parallèle, cette durée ne correspond pas exactement au temps d'exécution réel. Utilisez-la pour analyser les causes des goulots d'étranglement survenant lors des opérations de calcul.
`Input: %s rows (%sB)`	Nombre de lignes et octets lus en entrée.
`per task: avg.: %s std.dev.: %s`	Nombre moyen de lignes d'entrée par tâche et écart-type entre les tâches. Un écart-type élevé indique un déséquilibre des données : certaines tâches traitent beaucoup plus de données que d'autres, ce qui limite l'efficacité du parallélisme.
`Output: %s row (%sB)`	Nombre de lignes et octets produits en sortie.

Diagnostic des problèmes courants

Filtre non propagé (push down)

Lorsqu'un filtre ne peut pas être propagé vers la couche de stockage, la requête analyse l'intégralité de la table puis applique le filtre en mémoire, ce qui augmente considérablement les E/S et l'utilisation de la mémoire.

Les deux requêtes suivantes illustrent cette différence :

SQL 1 — filtre sur une valeur de colonne brute (peut être propagé) :
```
SELECT count(*) FROM test WHERE string_test = 'a';
```
SQL 2 — filtre encapsulé dans une fonction (ne peut pas être propagé) :
```
SELECT count(*) FROM test WHERE length(string_test) = 1;
```

Exécutez la commande EXPLAIN ANALYZE sur les deux requêtes et comparez le Fragment 2.

Résultat du SQL 1 (filtre propagé) : Le fragment utilise l'opérateur TableScan et la valeur Input avg. est de 0.00 rows, ce qui signifie que la couche de stockage a filtré toutes les lignes avant de renvoyer les données.

Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
    Output: 4 rows (36B), PeakMemory: 0B, WallTime: 6.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
    Output layout: [count_0_1]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_1:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 212.92us (3.99%)
    │   count_0_1 := count(*)
    └─ TableScan[adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}]
           Outputs: []
           Estimates: {rows: 4 (0B)}
           Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 4.76ms (89.12%)
           Input avg.: 0.00 rows, Input std.dev.: ?%

Résultat du SQL 2 (filtre non propagé) : Le fragment utilise l'opérateur ScanFilterProject au lieu de TableScan, et le champ Input affiche 9999 rows. Le champ filterPredicate confirme que le filtre est appliqué après l'analyse, et non au niveau de la couche de stockage.

Fragment 2 [adb:AdbPartitioningHandle{schema=test4dmp, tableName=test, dimTable=false, shards=4, tableEngineType=Cstore, partitionColumns=id, prunedBuckets= empty}]
    Output: 4 rows (36B), PeakMemory: 0B, WallTime: 102.00ns, Input: 0 rows (0B); per task: avg.: 0.00 std.dev.: 0.00
    Output layout: [count_0_1]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_1:bigint]
    │   Estimates: {rows: 1 (8B)}
    │   Output: 8 rows (72B), PeakMemory: 0B (0.00%), WallTime: 252.23us (0.12%)
    │   count_0_1 := count(*)
    └─ ScanFilterProject[table = adb:AdbTableHandle{schema=test4dmp, tableName=test, partitionColumnHandles=[id]}, filterPredicate = (`test4dmp`.`length`(`string_test`) = BIGINT '1')]
           Outputs: []
           Estimates: {rows: 9999 (312.47kB)}/{rows: 9999 (312.47kB)}/{rows: ? (?)}
           Output: 0 rows (0B), PeakMemory: 0B (0.00%), WallTime: 101.31ms (49.84%)
           string_test := AdbColumnHandle{columnName=string_test, type=13, isIndexed=true}
           Input: 9999 rows (110.32kB), Filtered: 100.00%

Pour diagnostiquer ce schéma, recherchez l'opérateur ScanFilterProject dans le résultat. Si le champ Input affiche un nombre de lignes élevé et que la valeur Filtered est proche de 100 %, la requête analyse beaucoup plus de données que nécessaire. Réécrivez le filtre sous une forme pouvant être propagée ou ajoutez des conditions permettant de réduire l'étendue de l'analyse.

Utilisation élevée de la mémoire

Utilisez la métrique PeakMemory au niveau du fragment pour identifier l'étape consommant le plus de mémoire. Les causes courantes d'une valeur PeakMemory élevée incluent :

Des jointures par diffusion (broadcast) involontaires entraînant une forte consommation de mémoire.
De grands résultats intermédiaires de jointure dus à une augmentation du volume de données.
Une table de sonde (gauche) volumineuse dans une jointure.
Un opérateur TableScan lisant un grand volume de données.

Après avoir identifié le fragment concerné, utilisez la métrique PeakMemory par opérateur pour cibler l'opérateur spécifique responsable de la consommation. Ajoutez des conditions de filtrage adaptées aux besoins métier pour limiter le volume de données, ou restructurez la requête afin de réduire la taille des résultats intermédiaires.

Requêtes lentes sur des colonnes autres que la clé de distribution

Lorsqu'une requête filtre sur des colonnes qui ne constituent pas la clé de distribution, l'optimiseur ne peut pas éliminer de partitions et doit analyser tous les shards, ce qui entraîne une analyse complète de la table. Dans le résultat de la commande EXPLAIN ANALYZE , cela se traduit par un opérateur TableScan affichant un nombre de lignes Input élevé et un pourcentage de WallTime important.

Pour optimiser ce type de requête :

Définissez les colonnes faisant l'objet de requêtes d'égalité fréquentes comme clé de regroupement (CLUSTERED KEY) afin de contrôler la disposition physique des données et d'accélérer les analyses par plage.
Évitez d'utiliser SELECT * ; interrogez uniquement les colonnes nécessaires pour réduire les E/S et la surcharge mémoire.
Exécutez la commande ANALYZE TABLE <table_name> pour actualiser les statistiques de la table et aider l'optimiseur à générer un plan d'exécution plus efficace.