Index BDD

Le problème #

Sans index, retrouver une ligne précise dans une table oblige le moteur à faire un scan complet : lire ligne par ligne, du début à la fin, en comparant chaque valeur au critère recherché. Sur une table de 100 lignes, ce n’est pas un problème. Sur une table de 50 millions de lignes, une requête qui devrait répondre en quelques millisecondes peut prendre plusieurs secondes, voire minutes.

Le problème grossit avec les données : plus la table grandit, plus le coût d’un scan complet augmente linéairement. Une application qui semblait rapide en développement (peu de données) peut devenir inutilisable en production (des millions de lignes) si personne n’a pensé aux index.

L’idée générale #

Un index est une structure de données auxiliaire, maintenue à part de la table, qui permet de retrouver rapidement les lignes correspondant à une valeur sans les parcourir toutes. La structure la plus répandue est le B-Tree (arbre équilibré) : les valeurs indexées y sont organisées de façon triée et hiérarchique, ce qui permet de localiser une valeur en un petit nombre de comparaisons, plutôt qu’en parcourant chaque ligne.

Ce gain a un coût. À chaque INSERT, UPDATE ou DELETE, le moteur doit non seulement modifier la table, mais aussi mettre à jour chaque index concerné pour qu’il reste cohérent. Plus une table a d’index, plus les écritures sont lentes — c’est un compromis lecture/écriture, pas un gain gratuit.

Analogie du quotidien #

Un index, c’est comme l’index alphabétique à la fin d’un livre technique. Sans lui, pour trouver toutes les pages qui parlent de “transactions”, il faudrait feuilleter le livre entier page par page. Avec l’index, on va directement à la lettre T, on trouve “transactions, p. 42, 108, 203” et on saute directement aux bonnes pages.

Mais cet index n’existe pas par magie : quelqu’un doit le maintenir à jour. Si l’auteur ajoute un chapitre au milieu du livre, il doit aussi mettre à jour l’index pour que les numéros de page restent corrects. C’est exactement le coût qu’un moteur de base de données paie à chaque écriture sur une table indexée.

Diagramme #

Exemple de code #

-- Sans index : recherche par email = scan complet sur des millions de lignes
SELECT * FROM utilisateurs WHERE email = 'kevin@example.com';

-- Créer un index sur la colonne fréquemment filtrée
CREATE INDEX idx_utilisateurs_email ON utilisateurs (email);

-- La même requête peut désormais utiliser l'index au lieu du scan complet

-- Vérifier si le moteur utilise bien l'index
EXPLAIN ANALYZE
SELECT * FROM utilisateurs WHERE email = 'kevin@example.com';

-- Index composite : utile si les requêtes filtrent sur plusieurs colonnes ensemble
CREATE INDEX idx_commandes_client_date ON commandes (client_id, date_commande);

// L'index est transparent côté code applicatif : la requête ne change pas,
// seul le temps de réponse change radicalement selon qu'il existe ou non.
$stmt = $pdo->prepare('SELECT * FROM utilisateurs WHERE email = :email');
$stmt->execute(['email' => $email]);
$utilisateur = $stmt->fetch(PDO::FETCH_ASSOC);

Quand utiliser un index ? #

• Sur les colonnes fréquemment utilisées dans un WHERE, un JOIN, ou un ORDER BY — surtout sur de grandes tables.
• Sur les clés étrangères : la plupart des moteurs n’indexent pas automatiquement les colonnes FOREIGN KEY, ce qui peut rendre les jointures lentes si on l’oublie.
• Sur les colonnes sélectives (beaucoup de valeurs distinctes, comme un email) : un index sur une colonne peu sélective (comme un booléen actif) apporte souvent peu de gain.
• À éviter sur les tables à très forte fréquence d’écriture et peu lues, où le coût de maintenance de l’index dépasse le bénéfice en lecture.

Points importants #

• Un index accélère la lecture mais ralentit l’écriture — ce n’est jamais un gain sans contrepartie, c’est un arbitrage à faire consciemment.
• EXPLAIN (ou EXPLAIN ANALYZE) permet de vérifier si le moteur utilise réellement un index plutôt que de le supposer.
• Un index composite (sur plusieurs colonnes) est efficace surtout si les colonnes sont utilisées dans le même ordre dans les requêtes — l’ordre des colonnes dans l’index compte.
• La clé primaire crée presque toujours un index automatiquement (souvent unique) : pas besoin de le recréer manuellement dans la plupart des moteurs.