Aller au contenu principal
Valrose.tech

Bases de données & SQL · L2 · Section 7/11

Plans d’exécution (EXPLAIN)

Progression

Points d’expérience : —XPSérie de jours consécutifs : —· —Progression du module : — / —compris

#Plans d'exécution : lire ce que fait le moteur

Un plan d'exécution décrit la recette que le moteur a choisie pour évaluer votre requête : quels parcours de tables, quels index, quels algorithmes de jointure, dans quel ordre. Savoir le lire transforme l'optimisation d'un art divinatoire en démarche : observer, identifier l'opération dominante, la supprimer ou la réduire.

#Prérequis et objectifs

  • Prérequis : SELECT et JOIN (ordre logique), Index (B-Tree, SARGability, index composites).
  • Lire un plan SQLite avec EXPLAIN QUERY PLAN et reconnaître les opérations coûteuses.
  • Relier chaque nœud du plan à une décision d'index ou de réécriture.
  • Conduire une session d'optimisation complète : avant, modification, après.

#Le pipeline du moteur

Parser
Analyse syntaxique, construction AST
Rewriter
Simplifications, descente des prédicats
Planner
Choix des index, type de jointure, ordre
Executor
Parcours, jointures, agrégats en flux
Résultat
Retour des lignes au client

Votre requête SQL est déclarative : elle décrit le résultat, pas la manière. Entre les deux, le planner énumère les plans candidats et estime leur coût à partir de statistiques (nombre de lignes par table, distribution des valeurs). Le plan affiché est son choix, jamais une fatalité : vous influencez la décision par le schéma, les index et la forme de la requête.

#Plan interactif

Explorez le plan ci-dessous : chaque nœud consomme le résultat de ses enfants. L'arbre se lit de bas en haut et de l'intérieur vers l'extérieur.

Filter
Join
Scan A
Scan B
Plan heuristique simplifié: Scan/Join → Filter → Aggregate

Exemple de plan (retranscrit, grammaire PostgreSQL) :

texttext

1Aggregate (sum)2  -> Hash Join (users.id = orders.user_id)3       -> Seq Scan users4       -> Index Scan orders(user_id)

Lecture : le scan d'orders via son index (enfant droit) et le balayage complet d'users (enfant gauche) alimentent une jointure par table de hachage, dont l'agrégat final consomme les lignes. Le nœud le plus bas n'est pas le moins important : c'est celui qui produit le flux que tout le reste transforme.

#Vocabulaire des nœuds

Nœud (SQLite)SignificationQuand l'inquiéter
SCAN tableBalayage complet de la tableGrande table + prédicat sélectif non indexé
SEARCH ... USING INDEXDescente de B-TreePresque jamais
SEARCH ... USING COVERING INDEXIndex seul, table non lueJamais, c'est l'idéal
SCAN ... AS v (sous-requête)Matérialisation d'une CTE/vueGros volume intermédiaire
USE TEMP B-TREE FOR ORDER BYTri expliciteTri non couvert par un index

Sous PostgreSQL, la grammaire s'enrichit (Seq Scan, Index Scan, Hash Join, Nested Loop, Merge Join, Sort, Aggregate) mais la démarche de lecture est identique : identifier le nœud qui travaille le plus, souvent celui dont l'estimation de lignes est la plus élevée.

#La statistique qui décide : l'estimation de cardinalité

Le planner choisit ses algorithmes d'après le nombre de lignes qu'il estime à chaque étape. Ces estimations viennent de statistiques collectées par ANALYZE (PostgreSQL, MySQL) ou issues des métadonnées de la table (SQLite). Une estimation fausse produit un plan absurde : par exemple un Nested Loop exécuté un million de fois là où un Hash Join s'imposait. D'où le réflexe : après de grosses insertions, rafraîchir les statistiques (ANALYZE) avant d'incriminer le moteur.

#Session guidée : avant, index, après

Jeu de données : users(id, name), orders(id, user_id, amount) avec quelques milliers de lignes. Requête cible : « total des commandes des utilisateurs dont le nom commence par A ».

sqlsql

1select sum(o.amount)2from orders o3join users u on u.id = o.user_id4where u.name like 'A%';

Étape 1, observer :

sqlsql

1explain query plan2select sum(o.amount)3from orders o4join users u on u.id = o.user_id5where u.name like 'A%';6-- Attendu sans index : SCAN orders et SCAN users

Étape 2, agir : le prédicat sélectif porte sur users.name, la jointure sur orders.user_id.

sqlsql

1create index idx_users_name on users(name);2create index idx_orders_user on orders(user_id);

Étape 3, revérifier : SEARCH users USING INDEX idx_users_name apparaît (LIKE 'A%' est un préfixe, donc SARGable), et la jointure devient SEARCH orders USING INDEX idx_orders_user. La conclusion ne se décrète pas : elle se constate plan après plan, requête après requête.

#Atelier

Sur le jeu orders(user_id, created_at, status, amount) avec les index existants (user_id) et (created_at), analysez :

sqlsql

1select sum(amount) from orders2where user_id = 42 and created_at >= date('now', '-7 days');
  1. Observez le plan choisi : quel index le moteur privilégie ?
  2. Créez l'index composite (user_id, created_at), ré-exécutez le plan.
  3. Expliquez la différence.

#Mini-exercice : prédire le plan

Même base, aucun index composite. Pour select sum(amount) from orders where user_id = 42 and created_at >= date('now','-7 days'), quel plan attendez-vous sur SQLite, et pourquoi le composite (user_id, created_at) l'améliore-t-il ?

Réponse : SEARCH sur l'index (user_id) pour l'égalité, puis rejet ligne à ligne des entrées hors fenêtre de sept jours ; le composé aligne les deux prédicats sur la même descente, éliminant le filtre résiduel, conformément au principe d'égalité puis plage défini dans la section Index.