Lire les plans d'exécution — EXPLAIN QUERY PLAN

Le jeu de données de cet article — perf_sales (50 000 lignes de ventes)

Pour savoir si une requête est rapide ou lente, la première chose à regarder est le plan d'exécution (le plan de la requête).

À partir d'une instruction SQL, la base de données décide en interne « quelles tables lire, dans quel ordre, et comment ».

L'outil qui imprime cette procédure sous forme de texte, c'est EXPLAIN QUERY PLAN.

Préfixe ce mot-clé à une requête et tu verras les étapes d'exécution au lieu des résultats réels.

Avant de plonger dans les exercices, vérifie les définitions de colonnes et un échantillon des données de la table perf_sales utilisée tout au long de cet article. La génération des données prend un instant, donc le premier passage peut mettre quelques secondes.

① Utilise PRAGMA table_info(perf_sales); pour voir les noms de colonnes, les types et la clé primaire.

② Utilise SELECT * FROM perf_sales LIMIT 5; pour prévisualiser les 5 premières lignes.

③ Utilise SELECT COUNT(*) FROM perf_sales; pour confirmer qu'il y a 50 000 lignes.

SCAN et SEARCH — parcours complets et recherches par index

Les deux premiers mots à apprendre dans un plan d'exécution sont SCAN et SEARCH.

SCAN perf_sales signifie lire chaque ligne du haut vers le bas de la table (un parcours complet).

Quand tu filtres sur une colonne qui n'a pas d'index, la base ne sait pas quelles lignes correspondent, donc elle vérifie les 50 000 lignes une par une.

À l'inverse, SEARCH perf_sales USING INDEX nom_index (colonne=?) signifie suivre l'index pour atterrir directement sur les lignes correspondantes (une recherche par index).

Crée un index avec CREATE INDEX et les filtres sur cette colonne arrêtent de balayer chaque ligne — le plan passe de SCAN à SEARCH ... USING INDEX.

-- Filtre sur region : pas d'index, donc lit chaque ligne
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- Crée un index sur region, puis regarde le même plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

Imagine le scénario : « compter les ventes pour un commercial précis est lent. » Tu vas comparer le plan d'exécution avant et après l'ajout d'un index. Exécute la création de l'index et la vérification du plan ensemble dans une seule exécution. (Exécute-le correctement pour révéler l'explication.)

① D'abord, préfixe EXPLAIN QUERY PLAN à une requête qui compte les lignes où emp_id vaut 7 et vérifie le plan. Sans index, tu verras SCAN.

② Ensuite, supprime tout index existant avec DROP INDEX IF EXISTS, construis un index sur emp_id avec CREATE INDEX, et relance le même EXPLAIN QUERY PLAN. Confirme qu'il passe à SEARCH ... USING INDEX.

USE TEMP B-TREE — une zone temporaire pour le tri

USE TEMP B-TREE FOR ORDER BY (un B-tree temporaire) signifie que la colonne du ORDER BY n'a pas d'index, donc la base construit une zone de travail temporaire pour trier les résultats.

Les 50 000 lignes vont dans cette zone temporaire pour être réordonnées, donc le coût croît avec le nombre de lignes.

S'il existe un index utilisable pour le tri, cette ligne disparaît du plan et l'ordre déjà présent dans l'index est utilisé tel quel.

-- Top 10 par amount ascendant : pas d'index, donc une zone temp est nécessaire pour le tri
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- Construis un index sur amount et le tri peut réutiliser son ordre directement
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE disparaît du plan

Regarde le plan d'exécution pour « top 10 des ventes par amount ascendant » alors qu'aucun index n'existe. Les trois exercices qui suivent s'exécutent dans l'ordre pour que tu puisses comparer les plans avant et après l'index.

① Dans cette première console, préfixe EXPLAIN QUERY PLAN à une requête qui ordonne perf_sales par amount ascendant et tire les 10 premières lignes (sale_id, amount).

Confirme que la sortie contient deux lignes : SCAN perf_sales et le USE TEMP B-TREE FOR ORDER BY lié au tri. Sans index sur la colonne de tri amount, la base doit charger les 50 000 lignes dans une zone temporaire et les réordonner.

② Dans cette deuxième console, exécute DROP INDEX IF EXISTS ix_amount; pour effacer le précédent, puis crée un index nommé ix_amount sur la colonne amount de perf_sales.

La création d'index est un coût ponctuel, donc on ne l'inclut pas dans la comparaison pure de plan. La troisième console relance la vérification du plan pour la même requête.

③ Dans cette troisième console, relance le même EXPLAIN QUERY PLAN qu'en ①.

Confirme que la ligne USE TEMP B-TREE FOR ORDER BY n'apparaît plus dans le plan, remplacée par quelque chose comme SEARCH perf_sales USING INDEX ix_amount. L'ordre de l'index est réutilisé directement, donc le tri par zone temporaire n'est plus nécessaire.

USING COVERING INDEX — un index qui ne touche pas la table

USING COVERING INDEX (un Index-Only Scan (index couvrant), aussi appelé covering index en anglais) signifie que toutes les colonnes dont la requête a besoin sont contenues dans l'index, donc la base peut construire le résultat à partir de l'index seul sans lire la table elle-même.

Un index est une petite copie des seules colonnes que tu tires de la table, donc sauter la lecture de la table est ce qui rend l'opération plus rapide.

C'est un cran au-dessus de SEARCH ... USING INDEX — quand le plan montre USING COVERING INDEX, tu obtiens la lecture la plus efficace.

-- Index sur emp_id uniquement -> trouve les lignes via l'index, puis lit amount depuis la table
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (la table est aussi lue)

-- Index composite sur (emp_id, amount) -> toutes les colonnes nécessaires sont dans l'index, pas de lecture de table
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

Prends une requête qui « filtre par emp_id et ne renvoie que amount » et change son plan en un plan qui se termine dans l'index sans toucher à la table. Exécute la création de l'index et la vérification du plan ensemble dans une seule exécution.

① Supprime tout index existant avec DROP INDEX IF EXISTS, puis utilise CREATE INDEX pour construire un index composite sur emp_id et amount, dans cet ordre.

② Préfixe EXPLAIN QUERY PLAN à une requête qui filtre par emp_id = 7 et sélectionne uniquement emp_id et amount, et vérifie le plan. Lis que le plan se termine dans l'index seul, puisque toutes les colonnes nécessaires vivent dans l'index.

Plans de sous-requête — CORRELATED et MATERIALIZE

Les requêtes avec sous-requêtes ont leur propre vocabulaire dédié dans le plan.

CORRELATED SCALAR SUBQUERY signifie que la valeur de la sous-requête dépend de la ligne externe courante, donc elle est réexécutée une fois par ligne externe.

Avec 50 000 lignes externes, la sous-requête peut s'exécuter 50 000 fois — quand ce mot apparaît dans le plan, c'est un candidat pour une réécriture.

À l'inverse, MATERIALIZE signifie que le résultat d'une sous-requête ou d'un CTE est calculé une fois, stocké dans une table temporaire et réutilisé.

C'est une optimisation qui paie le coût de calcul une fois pour un résultat intermédiaire que tu référencerais plusieurs fois.

Lequel de ces deux mots apparaît te dit si la sous-requête est « recalculée à chaque fois » ou « calculée une fois et réutilisée ».

-- Sous-requête scalaire corrélée : recalcule « max amount pour cette region » par ligne externe
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> une ligne CORRELATED SCALAR SUBQUERY apparaît

-- Une table dérivée (sous-requête dans le FROM) est calculée une fois
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> une ligne MATERIALIZE peut apparaître

Regarde le plan pour « pour chaque ligne, prendre seulement les ventes qui dépassent le amount moyen de ce commercial, top 10 » et lis la structure qui relance la sous-requête par ligne.

① Préfixe EXPLAIN QUERY PLAN à une requête qui, pour chaque ligne de perf_sales, utilise une sous-requête corrélée pour calculer « le amount moyen pour le même emp_id » et tire 10 lignes où amount dépasse cette valeur.

② Lis, dans la sortie, la ligne indiquant une sous-requête scalaire corrélée et le SCAN perf_sales pour la requête externe comme pour la sous-requête. Note que c'est une forme lourde sur de grosses tables parce que la sous-requête interne est évaluée une fois par ligne externe.

Comment cela se traduit pour les autres SGBDR — MySQL / PostgreSQL / Oracle

Le vocabulaire de plan dans cet article vient du EXPLAIN QUERY PLAN de SQLite, mais MySQL / PostgreSQL / Oracle exposent les mêmes concepts sous des noms différents.

Commence par vérifier comment chaque base produit un plan d'exécution, puis compare l'aide-mémoire de terminologie — tu pourras lire les plans d'une autre base sans beaucoup d'apprentissage supplémentaire.

Comment imprimer un plan d'exécution dans chaque base

• SQLite : Préfixe EXPLAIN QUERY PLAN à la requête (ex. EXPLAIN QUERY PLAN SELECT ...;). Elle n'exécute pas la requête, n'imprime que le plan. Un simple EXPLAIN montre le bytecode bas niveau, donc pour une lecture humaine tu veux EXPLAIN QUERY PLAN
• MySQL : Préfixe EXPLAIN à la requête (ex. EXPLAIN SELECT ...;). MySQL 8.0+ a aussi EXPLAIN ANALYZE SELECT ...; pour le temps d'exécution réel et le nombre de lignes réel. Pour une sortie JSON, utilise EXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL : Préfixe EXPLAIN à la requête (ex. EXPLAIN SELECT ...;). C'est uniquement le plan estimé. Pour le réel, utilise EXPLAIN ANALYZE SELECT ...;, et pour les infos détaillées de tampons E/S, utilise EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle : Un motif en deux étapes — EXPLAIN PLAN FOR <requête>; sauve le plan dans une table interne, et SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); l'imprime. Pour une vue rapide, l'option de session SET AUTOTRACE ON fonctionne aussi

Aide-mémoire de terminologie de plan

La formulation diffère, mais ce que la base fait en interne est essentiellement la même chose. L'objectif de cet article est de pouvoir lire « lit-elle chaque ligne, restreint-elle avec un index, a-t-elle besoin d'une zone de travail pour trier, exécute-t-elle une sous-requête en boucle » dans la sortie d'EXPLAIN de chaque base.