Un index, c'est le sommaire d'un livre : il évite de tout relire pour trouver une page.
⏱️ Durée : environ 85 minutes · 🎓 Niveau : intermédiaire · 🏅 Badge : Accélérateur de requêtes
🎬 L'histoire
La page « mes inscriptions » de HashBox met 8 secondes pour certains membres. La table inscriptions contient 5 millions de lignes. La requête est correcte, mais la base lit toute la table à chaque fois.
« Demande à la base comment elle exécute ta requête, dit Sam. Elle te le dira. Et ensuite, donne-lui un index. »
Tu apprends à lire un plan d'exécution et à choisir les bons index.
🎯 Ta mission
À la fin, je serai capable de :
- expliquer ce qu'est un index (B-tree) et ce qu'il accélère
- lire un plan d'exécution (SCAN ou SEARCH)
- créer un index simple, composite et couvrant
- expliquer pourquoi un index ralentit les écritures
- mesurer le gain d'un index
🧩 Défi d'échauffement (5 XP)
Tu cherches le mot « abeille » dans un dictionnaire de 1 000 pages. Option A : tu lis page par page depuis le début. Option B : tu ouvres vers le début (lettre A), tu ajustes. Option C : tu utilises les onglets alphabétiques. Combien de pages lis-tu environ pour chaque option ? Quel prix à payer si le dictionnaire est mis à jour chaque jour (ajouter des mots, refaire les onglets) ?
💡 Le concept
Sans index : le balayage complet
Pour répondre à WHERE ville = 'Lyon', la base lit chaque ligne de la table : c'est un balayage complet (full table scan). Pour n lignes : O(n).
Avec un index : la recherche
Un index est une structure supplémentaire, triée, qui permet de retrouver rapidement les lignes qui ont une valeur donnée. La plupart des index sont des arbres B (B-tree) : des arbres équilibrés larges, optimisés pour le disque. Une recherche coûte O(log n) : quelques lectures, même pour des millions de lignes.
Index sur ville :
Lille → lignes 7, 12
Lyon → lignes 1, 3, 9, 15
Nice → ligne 4
(trié par ville ; on cherche 'Lyon' par recherche dichotomique)
Créer un index
CREATE INDEX idx_membres_ville ON membres(ville);
CREATE INDEX idx_insc_membre_cours ON inscriptions(membre_id, cours_code); -- composite
CREATE UNIQUE INDEX idx_membres_email ON membres(email); -- + unicité
Une clé primaire et une contrainte UNIQUE créent automatiquement un index.
Lire un plan
EXPLAIN QUERY PLAN SELECT * FROM membres WHERE ville = 'Lyon';
| Plan | Signification |
|---|---|
SCAN membres | balayage complet |
SEARCH membres USING INDEX idx_membres_ville (ville=?) | recherche par l'index |
SEARCH ... USING COVERING INDEX | l'index suffit, la table n'est pas lue |
(PostgreSQL : EXPLAIN / EXPLAIN ANALYZE, avec Seq Scan, Index Scan, Index Only Scan.)
Index composite : l'ordre compte
Un index sur (a, b) sert pour : WHERE a = ? et WHERE a = ? AND b = ? (préfixe gauche). Il ne sert pas pour WHERE b = ? seul.
Index couvrant
Si l'index contient toutes les colonnes demandées, la base n'a pas besoin de lire la table.
CREATE INDEX idx_couvrant ON inscriptions(membre_id, cours_code);
SELECT cours_code FROM inscriptions WHERE membre_id = 42; -- répondu par l'index seul
Sélectivité
Un index est utile quand il élimine beaucoup de lignes. Un index sur un booléen (50 % / 50 %) apporte peu. Sur un identifiant unique, il est excellent.
Le coût des index
| Avantage | Coût |
|---|---|
| lectures plus rapides | écritures plus lentes (chaque INSERT/UPDATE/DELETE met à jour les index) |
| espace disque |
Ne pas indexer partout : indexer ce que les requêtes réelles utilisent.
Quand un index n'est pas utilisé
- Fonction sur la colonne :
WHERE LOWER(nom) = 'ada'(sauf index sur l'expression). LIKE '%ada'(joker au début).- Types incompatibles, très peu sélectif, ou table minuscule (balayer est plus rapide).
Mesurer
import sqlite3, time
bd = sqlite3.connect(":memory:")
bd.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, ville TEXT)")
bd.executemany("INSERT INTO t (ville) VALUES (?)", [(f"ville{i % 1000}",) for i in range(200_000)])
t = time.perf_counter(); bd.execute("SELECT COUNT(*) FROM t WHERE ville = 'ville7'").fetchone(); print(round(time.perf_counter() - t, 4))
bd.execute("CREATE INDEX idx ON t(ville)")
t = time.perf_counter(); bd.execute("SELECT COUNT(*) FROM t WHERE ville = 'ville7'").fetchone(); print(round(time.perf_counter() - t, 4))
🔍 Exemple pas à pas
Problème : accélérer la page « mes inscriptions » avec des index, en lisant le plan avant et après.
import random, sqlite3, time
bd = sqlite3.connect(":memory:")
bd.executescript('''
CREATE TABLE inscriptions (id INTEGER PRIMARY KEY, membre_id INTEGER, cours_code TEXT, date TEXT);
''')
rng = random.Random(1)
bd.executemany("INSERT INTO inscriptions (membre_id, cours_code, date) VALUES (?, ?, ?)",
[(rng.randint(1, 50_000), f"HEF-{rng.randint(0, 160):03d}", "2025-01-01") for _ in range(300_000)])
def plan(sql):
return [ligne[3] for ligne in bd.execute("EXPLAIN QUERY PLAN " + sql)]
def mesurer(sql, n=200):
t = time.perf_counter()
for i in range(n):
bd.execute(sql, (1 + i * 7,)).fetchall()
return (time.perf_counter() - t) / n * 1000
requete = "SELECT cours_code FROM inscriptions WHERE membre_id = ?"
print("sans index :", plan(requete.replace("?", "42")), round(mesurer(requete, 20), 3), "ms")
bd.execute("CREATE INDEX idx_membre ON inscriptions(membre_id)")
(, plan(requete.replace(, )), (mesurer(requete), ), )
bd.execute()
(, plan(requete.replace(, )), (mesurer(requete), ), )
Résultats typiques :
| Étape | Plan | Temps par requête |
|---|---|---|
| Sans index | SCAN inscriptions | une dizaine de ms |
| Index simple | SEARCH ... USING INDEX idx_membre | moins d'une ms |
| Index couvrant | SEARCH ... USING COVERING INDEX idx_couvrant | encore un peu moins |
Lecture : on passe d'un balayage de 300 000 lignes à quelques lectures. Avec l'index couvrant, la table n'est même plus consultée.
Le coût en écriture :
import sqlite3, time
def temps_insertion(avec_index):
bd = sqlite3.connect(":memory:")
bd.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER, c INTEGER)")
if avec_index:
bd.execute("CREATE INDEX i1 ON t(a)"); bd.execute("CREATE INDEX i2 ON t(b)"); bd.execute("CREATE INDEX i3 ON t(c)")
donnees = [(i % 1000, i % 777, i % 333) for i in range(150_000)]
t = time.perf_counter()
bd.executemany("INSERT INTO t (a, b, c) VALUES (?, ?, ?)", donnees)
return time.perf_counter() - t
print(round(temps_insertion(False), 3), "s sans index ;", round(temps_insertion(True), 3), "s avec 3 index")
Les insertions sont plus lentes avec trois index : chacun doit être maintenu.
✍️ À toi, avec aide (10 XP)
Table membres(id, nom, ville, score) de 100 000 lignes.
- Pour les requêtes suivantes, dis quel index créer (ou pourquoi aucun) :
WHERE ville = ?WHERE ville = ? AND score > ?WHERE score > ?WHERE LOWER(nom) = ?WHERE nom LIKE '%ada'
- Vérifie avec
EXPLAIN QUERY PLANavant/après.
💡 Indice 1
Pour ville = ? AND score > ?, un index composite (ville, score) est idéal : égalité d'abord, plage ensuite.
💡 Indice 2
Pour LOWER(nom) = ?, il faut un index sur l'expression.
💡 Indice 3
LIKE '%ada' ne peut pas utiliser un index B-tree classique.
✅ Réponse
import random, sqlite3
bd = sqlite3.connect(":memory:")
bd.execute("CREATE TABLE membres (id INTEGER PRIMARY KEY, nom TEXT, ville TEXT, score INTEGER)")
rng = random.Random(0)
bd.executemany("INSERT INTO membres (nom, ville, score) VALUES (?, ?, ?)",
[(f"Nom{i}", f"Ville{rng.randint(1, 300)}", rng.randint(0, 100)) for i in range(100_000)])
def plan(sql):
return [l[3] for l in bd.execute("EXPLAIN QUERY PLAN " + sql)]
cas = {
"ville": "SELECT * FROM membres WHERE ville = 'Ville5'",
"ville+score": "SELECT * FROM membres WHERE ville = 'Ville5' AND score > 50",
"score": "SELECT * FROM membres WHERE score > 98",
"lower": "SELECT * FROM membres WHERE LOWER(nom) = 'nom7'",
"like": "SELECT * FROM membres WHERE nom LIKE '%7'",
}
print("avant :", {k: plan(v)[0] for k, v in cas.items()})
bd.execute("CREATE INDEX idx_ville_score ON membres(ville, score)")
bd.execute()
bd.execute()
(, {k: plan(v)[] k, v cas.items()})
🚀 À toi, en solo (20 XP)
Audit d'index. Voici les 5 requêtes les plus fréquentes d'une application :
SELECT * FROM commandes WHERE client_id = ? ORDER BY date DESC LIMIT 20SELECT COUNT(*) FROM commandes WHERE statut = 'en_attente'SELECT * FROM commandes WHERE date BETWEEN ? AND ?SELECT produit_id, SUM(quantite) FROM lignes WHERE commande_id = ? GROUP BY produit_idUPDATE commandes SET statut = ? WHERE id = ?
La table commandes reçoit 200 insertions par seconde.
- Crée les tables avec 200 000 commandes et 600 000 lignes.
- Propose un jeu d'index minimal et justifie chacun.
- Mesure avant/après et le coût en insertion.
- Identifie un index que tu n'as pas créé et dis pourquoi.
✅ Pistes de correction
import random, sqlite3, time
rng = random.Random(3)
bd = sqlite3.connect(":memory:")
bd.executescript('''
CREATE TABLE commandes (id INTEGER PRIMARY KEY, client_id INTEGER, date TEXT, statut TEXT);
CREATE TABLE lignes (commande_id INTEGER, produit_id INTEGER, quantite INTEGER);
''')
statuts = ["livree"] * 95 + ["en_attente"] * 3 + ["annulee"] * 2
bd.executemany("INSERT INTO commandes (client_id, date, statut) VALUES (?, ?, ?)",
[(rng.randint(1, 20000), f"2025-{rng.randint(1,12):02d}-{rng.randint(1,28):02d}", rng.choice(statuts)) for _ in range(200_000)])
bd.executemany("INSERT INTO lignes VALUES (?, ?, ?)", [(rng.randint(1, 200000), rng.randint(1, 500), rng.randint(1, 5)) for _ in range(600_000)])
def temps(sql, params, n=30):
t = time.perf_counter()
for _ in range(n):
bd.execute(sql, params).fetchall()
(time.perf_counter() - t) / n *
R = {
: (, (,)),
: (, (, )),
: (, (,)),
}
avant = {k: (temps(*v), ) k, v R.items()}
bd.execute()
bd.execute()
bd.execute()
apres = {k: (temps(*v), ) k, v R.items()}
(, avant)
(, apres)
⚠️ Pièges classiques
| Piège | Pourquoi c'est un problème | Comment l'éviter |
|---|---|---|
| Indexer toutes les colonnes | Écritures lentes, espace gaspillé | Indexer ce que les requêtes réelles utilisent |
| Mauvais ordre dans un index composite | L'index ne sert pas | Égalités d'abord, plage ensuite |
| Fonction sur la colonne indexée | L'index est ignoré | Index sur l'expression ou réécrire la condition |
| Ne pas vérifier le plan | On croit qu'il est utilisé | EXPLAIN avant et après |
🐉 Mini-boss (40 XP)
Dossier d'optimisation d'une base. Construis une base de 500 000 lignes pour une plateforme de e-commerce (clients, produits, commandes, lignes). Prends cinq requêtes lentes de ton choix (recherche par client, par période, top produits, recherche par préfixe de nom, comptage par statut).
- Mesure chaque requête sans index et relève le plan.
- Propose des index et justifie-les (sélectivité, ordre des colonnes, couvrant ou non).
- Mesure à nouveau, calcule le gain.
- Mesure le coût sur 100 000 insertions avec tous les index.
- Écris une recommandation : quels index garder, lesquels abandonner, pourquoi.
✅ Pistes de correction
Un bon dossier : tableau avant/après (ms) pour chaque requête et son plan, avec le facteur d'accélération ; un index composite justifié par « égalité puis plage » ; un index couvrant pour le top produits ; une explication de ce que LIKE 'abc%' peut utiliser (en SQLite, avec COLLATE NOCASE ou PRAGMA case_sensitive_like, ou un index avec collation adaptée) ; une mesure honnête de l'insertion (elle peut être plusieurs fois plus lente avec quatre index) ; une décision finale qui abandonne l'index peu sélectif ou redondant (un index (a) est redondant si (a, b) existe).
⚡ Quiz éclair (15 XP si 4/5 ou plus)
1. Un index sert à :
- A. retrouver rapidement des lignes sans tout lire
- B. chiffrer les données
- C. dupliquer une table
2. SCAN dans un plan d'exécution signifie :
- A. recherche par index
- B. balayage complet de la table
- C. jointure
3. Un index composite (a, b) sert pour :
- A. WHERE b = ? seul
- B. aucune requête
- C. WHERE a = ? (préfixe gauche)
4. 🔁 Révision mission 06 : Un LEFT JOIN renvoie :
- A. toutes les lignes de gauche, avec NULL s'il n'y a pas de correspondance
- B. uniquement les lignes avec correspondance
- C. les lignes de droite seulement
5. 🔁 Révision mission 06 : HAVING filtre :
- A. les lignes avant le regroupement
- B. les groupes après le GROUP BY
- C. les colonnes
✅ Réponses
- A.
- B.
- C.
- A.
- B.
🪞 Je réfléchis (10 XP)
- Quels « index » utilises-tu dans la vie (sommaire, marque-pages, répertoire) et quel est leur coût d'entretien ?
- Pourquoi un index mal choisi peut-il ralentir une application ?
🎒 À retenir
- Index B-tree : recherche en O(log n).
- Plan d'exécution : SCAN vs SEARCH.
- Composite : préfixe gauche, égalité d'abord.
- Couvrant : la table n'est pas lue.
- Coût : écritures et espace ; mesurer avant et après.
flowchart LR
T(["Index et plans d'exécution"])
T --> R1["1. Index B-tree : recherche en Olog n."]
T --> R2["2. Plan d'exécution : SCAN vs SEARCH."]
T --> R3["3. Composite : préfixe gauche, égalité d'abord."]
T --> R4["4. Couvrant : la table n'est pas lue."]
T --> R5["5. Coût : écritures et espace ; mesurer avant et après."]
Description du schéma : la mission au centre, reliée aux cinq idées à retenir.
🏅 Récompenses
Total possible : 100 XP · 🏅 Badge Accélérateur de requêtes
← Mission 06 — Jointures et agrégations · Mission 08 — Transactions et ACID →
HEF-070 — Bases de données

