Dans la plupart des applications, le goulot est la base de données. Index, plans d'exécution, requêtes en lot : quelques notions suffisent à transformer des secondes en millisecondes.
⏱️ Durée : environ 105 minutes · 🎓 Niveau : avancé · 🏅 Badge : Dresseur de requêtes
🎬 L'histoire
La page d'administration de HashBox affiche la liste des inscriptions du mois. Au lancement, 400 lignes : instantané. Un an plus tard, 2 millions de lignes : 11 secondes. Aucun code n'a changé. Pire : pour chaque ligne, l'affichage lance une requête supplémentaire pour récupérer le nom du membre.
« Deux problèmes classiques, dit Sam. Le premier : la base lit toute la table pour trouver quelques lignes, faute d'index. Le deuxième : le "N+1", une requête par ligne au lieu d'une seule jointure. La base te dit elle-même comment elle exécute une requête, il suffit de lui demander. »
Tu apprends à lire un plan d'exécution, à poser les bons index (y compris composites et couvrants), à repérer et supprimer le N+1, à paginer correctement et à grouper les écritures.
🎯 Ta mission
À la fin, je serai capable de :
- lire un plan d'exécution (
EXPLAIN QUERY PLAN) et distinguer parcours complet et recherche par index - concevoir un index (simple, composite, couvrant) selon la requête
- détecter et corriger le problème N+1
- paginer efficacement (clé de pagination plutôt que décalage)
- grouper les écritures en transactions et mesurer le gain
🧩 Défi d'échauffement (5 XP)
Une bibliothèque de 2 millions de livres. On te demande tous les livres de « Hugo ». Sans catalogue, tu parcours les rayons un par un. Avec un fichier par auteur, tu vas droit au bon endroit. Quel prix paie-t-on pour tenir ce fichier à jour à chaque nouveau livre ? Dans quels cas l'index ne servira pas (par exemple « tous les livres dont le titre contient mer ») ?
💡 Le concept
Comment la base exécute une requête
Une requête SQL décrit ce que tu veux. L'optimiseur choisit comment : quel index, quel ordre de jointure. Le plan d'exécution montre ce choix :
EXPLAIN QUERY PLAN SELECT * FROM inscriptions WHERE membre_id = 42;
→ SCAN inscriptions (parcours complet : O(n))
→ SEARCH inscriptions USING INDEX idx_membre (membre_id=?) (recherche : O(log n))
Premier réflexe devant une requête lente : lire son plan (EXPLAIN / EXPLAIN ANALYZE / EXPLAIN QUERY PLAN selon la base).
Les index
Un index est une structure triée (arbre B) qui permet de retrouver des lignes en O(log n). Il a un coût : espace disque, et chaque écriture doit aussi le mettre à jour.
| Type | Quand |
|---|---|
Simple (membre_id) | filtre ou jointure sur une colonne |
Composite (membre_id, date) | filtre sur plusieurs colonnes |
| Couvrant : contient toutes les colonnes de la requête | la table n'est même plus lue |
| Unique | contrainte et recherche |
| Partiel | seulement certaines lignes (WHERE actif = 1) |
Règle des colonnes d'un index composite : d'abord les colonnes testées par égalité, ensuite celle testée par intervalle ou tri. L'index (membre_id, date) sert WHERE membre_id = ? AND date > ? et WHERE membre_id = ?, mais pas WHERE date > ? seul (préfixe gauche).
Ce qui empêche d'utiliser un index
- fonction sur la colonne :
WHERE lower(nom) = 'ada'; - recherche par motif commençant par un joker :
LIKE '%mer'; - conversion implicite de type ;
- faible sélectivité (colonne booléenne : l'index n'aide guère) ;
- statistiques périmées.
Le problème N+1
1 requête : SELECT * FROM inscriptions LIMIT 100 → 100 lignes
puis 100 requêtes : SELECT nom FROM membres WHERE id = ? (une par ligne)
= 101 requêtes (1 + N) au lieu d'1 jointure. Chaque requête a un coût fixe (réseau, analyse). Solution : jointure (JOIN), ou requête IN (...) en lot, ou chargement groupé (mission 6).
Pagination
| Méthode | Principe | Problème |
|---|---|---|
Décalage LIMIT 50 OFFSET 100000 | saute 100 000 lignes | la base doit les parcourir : plus on va loin, plus c'est lent |
Clé de pagination (keyset) WHERE id > :dernier ORDER BY id LIMIT 50 | repart de la dernière valeur vue, grâce à l'index | rapide quelle que soit la page |
Écritures : les transactions
Chaque INSERT en autocommit déclenche un COMMIT (écriture durable, fsync). 10 000 insertions = 10 000 commits. En les groupant dans une transaction (ou par lots de 1 000), on divise le coût par des dizaines.
Autres leviers
| Levier | Effet |
|---|---|
| Requêtes préparées / paramétrées | analyse réutilisée, sécurité |
SELECT des colonnes utiles (pas *) | moins de données |
| Dénormalisation ciblée (compteur précalculé) | évite des agrégats coûteux |
| Vues matérialisées / tables de synthèse | résultats précalculés |
| Réplicas en lecture | répartir la lecture |
| Partitionnement | tables énormes |
| Pool de connexions | évite d'ouvrir une connexion par requête |
Méthode
- repérer les requêtes lentes (journal des requêtes lentes,
pg_stat_statements, etc.) ; - lire le plan ;
- corriger (index, réécriture, lot) ;
- re-mesurer avec des données réalistes en volume ;
- vérifier que les écritures ne sont pas trop ralenties.
🧠 Astuce : une requête rapide sur 1 000 lignes peut être catastrophique sur 10 millions. Teste toujours avec un volume proche de la production.
🔍 Exemple pas à pas
Problème : avec SQLite, lire des plans d'exécution avant/après index, compter les requêtes d'un N+1 et d'une jointure, comparer les paginations, et mesurer le gain des transactions.
import os, random, sqlite3, tempfile, time
def temps(f, n=3):
meilleur = float("inf")
for _ in range(n):
d = time.perf_counter(); f(); meilleur = min(meilleur, time.perf_counter() - d)
return meilleur
def plan(bd, requete, params=()):
return " | ".join(r[3] for r in bd.execute("EXPLAIN QUERY PLAN " + requete, params))
# ---------------- données ----------------
bd = sqlite3.connect(":memory:")
bd.executescript('''
CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT);
CREATE TABLE inscriptions (id INTEGER PRIMARY KEY, membre_id INTEGER, cours TEXT, jour INTEGER, montant INTEGER);
''')
rng = random.Random(1)
bd.executemany("INSERT INTO membres VALUES (?, ?)", ((i, f"membre-{i}") for i in range(1, 5001)))
bd.executemany("INSERT INTO inscriptions (membre_id, cours, jour, montant) VALUES (?,?,?,?)",
((rng.randint(1, 5000), f"HEF-{rng.randint(0, 89):03d}", rng.randint(1, 365), rng.randint(10, )) _ ()))
bd.commit()
Q =
p_avant = plan(bd, Q, (,))
t_avant = temps(: [bd.execute(Q, (m,)).fetchone() m (, )])
bd.execute()
p_apres = plan(bd, Q, (,))
t_apres = temps(: [bd.execute(Q, (m,)).fetchone() m (, )])
(, p_avant); (, p_apres)
()
p_avant p_apres p_apres
t_avant > * t_apres
Q2 =
bd.execute()
p = plan(bd, Q2, (, , ))
(, p)
p
p_seul = plan(bd, , (, ))
(, p_seul)
p_seul p_seul
bd.execute()
p_ok = plan(bd, )
p_ko = plan(bd, )
(, p_ok, , p_ko)
p_ok p_ko p_ko p_ok
requetes = []
bd.set_trace_callback( sql: requetes.append(sql))
lignes = bd.execute().fetchall()
noms = [bd.execute(, (m,)).fetchone()[] _, m lignes]
n_plus_1 = (requetes)
requetes.clear()
joint = bd.execute().fetchall()
une_jointure = (requetes)
bd.set_trace_callback()
()
n_plus_1 == une_jointure == [n _, n joint] == noms
(): bd.execute(, (decalage,)).fetchall()
(): bd.execute(, (dernier_id,)).fetchall()
page_offset() == page_cle()
t_off, t_cle = temps(: page_offset(), ), temps(: page_cle(), )
()
t_off > * t_cle
tempfile.TemporaryDirectory() d:
():
chemin = os.path.join(d, )
c = sqlite3.connect(chemin); c.execute()
c.commit()
debut = time.perf_counter()
autocommit_par_ligne:
i (n):
c.execute(, (i,)); c.commit()
:
c.executemany(, ((i,) i (n))); c.commit()
duree = time.perf_counter() - debut
compte = c.execute().fetchone()[]; c.close()
duree, compte
t1, c1 = ecrire(); t2, c2 = ecrire()
()
c1 == c2 == t1 > * t2
Lecture :
- Plan : avant l'index,
SCAN inscriptions(parcours des 300 000 lignes pour chaque requête) ; après,SEARCH ... USING INDEX. Le gain est de plusieurs ordres de grandeur sur 40 requêtes. - L'index composite couvrant
(membre_id, jour, montant)sert la requête sans toucher la table :COVERING INDEX. Mais un filtre surjourseul ne peut pas l'utiliser efficacement (préfixe gauche manquant). - Une fonction sur la colonne (
lower(cours)) désactive l'index ; l'égalité simple l'utilise. - Le N+1 coûte 101 requêtes pour 100 lignes ; la jointure en coûte 1, avec les mêmes résultats.
- La pagination par clé reste rapide à la page 5000, alors que le décalage doit parcourir 250 000 lignes.
- Grouper les insertions dans une transaction supprime les 400 commits durables.
✍️ À toi, avec aide (10 XP)
On a la table commandes(id, client_id, statut, date, total) avec 5 millions de lignes. Voici trois requêtes fréquentes :
SELECT * FROM commandes WHERE client_id = ? AND statut = 'payee' ORDER BY date DESC LIMIT 20SELECT COUNT(*) FROM commandes WHERE date >= ? AND date < ?SELECT client_id, SUM(total) FROM commandes WHERE statut = 'payee' GROUP BY client_id
Pour chacune : quel index proposer (colonnes dans l'ordre, et pourquoi) ? Vérifie les plans avec SQLite sur 100 000 lignes générées. Quel coût les index imposent-ils sur les écritures, et lequel garderais-tu si tu devais en limiter le nombre ?
💡 Indice 1
Requête 1 : égalité sur client_id et statut, tri sur date.
💡 Indice 2
Requête 2 : un intervalle sur date seul.
💡 Indice 3
Requête 3 : un index couvrant (statut, client_id, total) évite de lire la table.
✅ Réponse
import random, sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("CREATE TABLE commandes (id INTEGER PRIMARY KEY, client_id INT, statut TEXT, date INT, total INT)")
rng = random.Random(1)
bd.executemany("INSERT INTO commandes (client_id, statut, date, total) VALUES (?,?,?,?)",
((rng.randint(1, 3000), rng.choice(["payee", "annulee", "en_cours"]), rng.randint(1, 1000), rng.randint(5, 500)) for _ in range(100000)))
def plan(q, p=()): return " | ".join(r[3] for r in bd.execute("EXPLAIN QUERY PLAN " + q, p))
Q1 = "SELECT * FROM commandes WHERE client_id = ? AND statut = 'payee' ORDER BY date DESC LIMIT 20"
Q2 = "SELECT COUNT(*) FROM commandes WHERE date >= ? AND date < ?"
Q3 = "SELECT client_id, SUM(total) FROM commandes WHERE statut = 'payee' GROUP BY client_id"
bd.execute("CREATE INDEX i1 ON commandes (client_id, statut, date)")
bd.execute("CREATE INDEX i2 ON commandes (date)")
bd.execute("CREATE INDEX i3 ON commandes (statut, client_id, total)")
for nom, q, p in (("Q1", Q1, (42,)), ("Q2", Q2, (, )), (, Q3, ())):
(nom, , plan(q, p))
plan(Q1, (,)) plan(Q2, (, )) plan(Q3)
🚀 À toi, en solo (20 XP)
Détecteur de N+1. Écris un petit outil qui instrumente une connexion SQLite et détecte les motifs de N+1 :
- un enveloppeur qui enregistre chaque requête avec ses paramètres (via
set_trace_callback) ; - normalise les requêtes (remplace les valeurs littérales et paramètres par
?) et compte les occurrences de chaque forme ; - signale comme suspecte une forme exécutée plus de 20 fois dans une même « unité de travail » (un bloc
with) ; - propose une correction en texte (JOIN ou
IN (...)) ; - teste sur une fonction
afficher_inscriptions()N+1 et sur sa version corrigée ; - ajoute un test automatique qui échoue si une unité de travail dépasse un budget de requêtes (utile en CI).
✅ Pistes de correction
import re, sqlite3
from collections import Counter
class Observateur:
def __init__(self, bd, budget=None):
self.bd, self.budget, self.requetes = bd, budget, []
def __enter__(self):
self.requetes = []
self.bd.set_trace_callback(self.requetes.append); return self
def __exit__(self, type_, valeur, trace):
self.bd.set_trace_callback(None)
if type_ is None and self.budget is not None and len(self.requetes) > self.budget:
raise AssertionError(f"budget de requêtes dépassé : {len(self.requetes)} > {self.budget}")
@staticmethod
def ():
sql = re.sub(, , sql); sql = re.sub(, , sql)
re.sub(, , sql).strip()
():
{f: n f, n Counter(.forme(r) r .requetes).items() n > seuil}
bd = sqlite3.connect()
bd.executescript()
bd.executemany(, ((i, ) i (, )))
bd.executemany(, ((i % + , ) i ()))
():
[(i, bd.execute(, (m,)).fetchone()[], c) i, m, c bd.execute().fetchall()]
():
bd.execute().fetchall()
Observateur(bd) obs:
r1 = afficher_n_plus_1()
(, obs.suspectes())
(obs.suspectes().values()) == [] obs.suspectes()
Observateur(bd) obs2:
r2 = afficher_jointure()
obs2.suspectes() == {} (obs2.requetes) == (r1) == (r2)
:
Observateur(bd, budget=):
afficher_n_plus_1()
SystemExit()
AssertionError e:
(, e)
Observateur(bd, budget=):
afficher_jointure()
()
⚠️ Pièges classiques
| Piège | Pourquoi c'est un problème | Comment l'éviter |
|---|---|---|
| Aucun index sur les colonnes filtrées/jointes | Parcours complet de grandes tables | Lire le plan, indexer selon la requête |
| Tester sur 100 lignes | Tout est rapide, jusqu'à la production | Volumes réalistes |
| N+1 | Des centaines de requêtes inutiles | JOIN, IN (...), chargement groupé |
| Pagination par décalage profond | Plus on avance, plus c'est lent | Pagination par clé |
🐉 Mini-boss (40 XP)
Rapport d'administration lent. On te donne (ou tu génères) une base de 1 million d'inscriptions, 50 000 membres, 100 cours, et un rapport qui exécute 5 requêtes lentes : (a) inscriptions d'un membre dans un intervalle, (b) total par cours du mois, (c) liste paginée avec nom du membre (N+1 + décalage), (d) membres sans inscription depuis 6 mois (sous-requête corrélée), (e) import quotidien de 100 000 lignes en autocommit.
- mesure chaque requête et lis son plan ;
- corrige par index, réécriture, clés de pagination, jointures, lots ;
- vérifie à chaque étape l'équivalence des résultats ;
- tableau avant/après (temps, lignes lues, requêtes) ;
- mesure le coût sur les écritures des index ajoutés ;
- écris les garde-fous (budget de requêtes, test de plan) pour la CI.
✅ Pistes de correction
Un bon travail : tableau avant/après avec des gains de l'ordre de ×50 à ×1000 selon la requête; réécriture de la sous-requête corrélée en NOT EXISTS/LEFT JOIN ... IS NULL avec index (membre_id, jour); pagination par clé (jour, id); import en transaction par lots de 10 000; mesure du ralentissement des insertions avec 3 index (quelques dizaines de pourcents) et décision argumentée sur les index à conserver; garde-fous : test de plan (SEARCH attendu) et budget de requêtes.
⚡ Quiz éclair (15 XP si 4/5 ou plus)
1. Un SCAN dans un plan d'exécution signifie :
- A. un parcours complet de la table
- B. une recherche par index
- C. une erreur de syntaxe
2. Le problème N+1 correspond à :
- A. une table avec N+1 colonnes
- B. une requête pour la liste puis une requête par élément
- C. un index en trop
3. Dans un index composite, on place d'abord :
- A. la colonne la plus longue
- B. les colonnes au hasard
- C. les colonnes testées par égalité, puis celle d'intervalle ou de tri
4. 🔁 Révision mission 06 : Pour de petites requêtes lointaines, ce qui domine le temps est :
- A. la latence (nombre d'allers-retours)
- B. la bande passante
- C. la couleur de la page
5. 🔁 Révision mission 06 : Le keep-alive permet de :
- A. chiffrer les échanges
- B. réutiliser une connexion pour plusieurs requêtes
- C. compresser les images
✅ Réponses
- A.
- B.
- C.
- A.
- B.
🪞 Je réfléchis (10 XP)
- Quelle requête de ton application s'exécute le plus souvent, et connais-tu son plan ?
- Quels index existent chez toi sans que personne sache plus pourquoi ?
🎒 À retenir
- Lire le plan : SCAN contre SEARCH / COVERING INDEX.
- Index composite : égalités puis intervalle/tri ; préfixe gauche.
- Une fonction sur la colonne désactive l'index.
- N+1 : jointure ou chargement groupé ; compter les requêtes en test.
- Pagination par clé ; écritures groupées en transaction.
flowchart LR
T(["Bases de données et performance"])
T --> R1["1. Lire le plan : SCAN contre SEARCH / COVERING INDEX."]
T --> R2["2. Index composite : égalités puis intervalle/tri ; préfixe gauche."]
T --> R3["3. Une fonction sur la colonne désactive l'index."]
T --> R4["4. N+1 : jointure ou chargement groupé ; compter les requêtes en test."]
T --> R5["5. Pagination par clé ; écritures groupées en transaction."]
Description du schéma : la mission au centre, reliée aux cinq idées à retenir.
🏅 Récompenses
Total possible : 100 XP · 🏅 Badge Dresseur de requêtes
← Mission 06 — Réseau et latence · Mission 08 — Cache et invalidation →
HEF-130 — Performance

