Enseigner les index de base de données à un développeur backend junior

Comprendre les index de base de données : un guide pratique

Ce qu’est un index

Un index de base de données est une structure de données séparée que la base maintient en parallèle d’une table. Il stocke les valeurs d’une ou plusieurs colonnes sous une forme triée et interrogeable, avec des pointeurs vers les lignes réelles. La table elle‑même stocke vos données ; l’index est une structure supplémentaire dont le seul rôle est d’aider la base à trouver les lignes plus rapidement.

Voici une analogie, et c’est la seule dont vous avez besoin : un index ressemble à l’index à la fin d’un manuel. Si vous voulez toutes les pages qui mentionnent « transactions », vous ne lisez pas tout le livre page par page ; vous recherchez « transactions » dans l’index trié par ordre alphabétique, obtenez une courte liste de numéros de page et allez directement à celles‑ci. Sans cet index, votre seule option est de parcourir chaque page. Une base de données est face exactement au même choix : utiliser un index pour sauter aux lignes correspondantes, ou scanner la table entière.

Maintenant le comportement réel, sans l’analogie. Lorsque vous exécutez une requête comme SELECT * FROM orders WHERE customer_id = 42, la base a deux stratégies de base. Un parcours complet de la table lit chaque ligne et vérifie la condition, ce qui coûte un temps proportionnel à la taille de la table. Une recherche via l’index parcourt plutôt la structure d’index triée pour customer_id = 42, trouve rapidement les entrées correspondantes et suit les pointeurs enregistrés pour récupérer seulement ces lignes. Pour une table volumineuse où seules quelques lignes correspondent, la voie de l’index peut être des milliers de fois moins coûteuse.

Comment fonctionne un index B‑tree, de façon générale

Le type d’index le plus courant est un B‑tree. C’est une structure d’arbre équilibré où les clés sont conservées en ordre trié. Le nœud supérieur divise l’espace des clés en plages, chaque nœud enfant subdivise davantage, et le niveau inférieur (les feuilles) contient les valeurs indexées réelles avec des pointeurs vers les lignes de la table. Parce que l’arbre est équilibré et que chaque nœud contient de nombreuses clés, même une table de centaines de millions de lignes nécessite généralement seulement trois à cinq lectures de nœuds pour trouver une valeur spécifique.

Parce qu’un B‑tree conserve les valeurs en ordre trié, il prend en charge plus que des correspondances exactes. Il gère efficacement les conditions de plage (WHERE created_at >= '2024-01-01'), les correspondances de préfixe sur les chaînes (WHERE email LIKE 'anna%'), et peut renvoyer des lignes déjà triées, ce qui permet à la base d’éviter une étape de tri séparée pour les clauses ORDER BY correspondantes.

Pourquoi les index ont un coût

Les index ne sont pas gratuits, et c’est le compromis que vous devez intégrer.

Les écritures ralentissent. Chaque INSERT doit ajouter une entrée à chaque index sur la table. Chaque DELETE doit supprimer des entrées. Chaque UPDATE qui modifie une colonne indexée doit mettre à jour les entrées d’index correspondantes. Une table avec six index effectue effectivement jusqu’à sept écritures pour chaque insertion logique de ligne. Sur des tables à fort volume d’écritures, un indexage négligent nuit de manière mesurable au débit.

Le stockage augmente. Chaque index est une copie complète des valeurs de colonne indexées plus des pointeurs et la structure de l’arbre. Les index sur de grandes tables peuvent rivaliser avec la taille de la table elle‑même ou la dépasser, ce qui affecte aussi les sauvegardes et la mise en cache en mémoire.

Donc le principe directeur est : les index échangent un coût d’écriture et d’espace de stockage contre la vitesse de lecture. Vous les ajoutez là où les lectures en bénéficient clairement, pas partout.

Sélectivité : le concept clé pour décider de la valeur

La sélectivité décrit dans quelle mesure une condition réduit le nombre de lignes. Une colonne hautement sélective a beaucoup de valeurs distinctes par rapport au nombre de lignes. Un email ou un identifiant de commande est très sélectif : filtrer dessus renvoie une ou quelques lignes sur des millions, et un index est très utile. Une colonne comme status avec trois valeurs ('pending', 'shipped', 'cancelled') ou un drapeau booléen is_active a une faible sélectivité : le filtrage peut toujours correspondre à 40 % de la table.

Pourquoi cela importe‑t‑il ? Si une condition correspond à une grande fraction de la table, aller et venir entre l’index et la table pour des millions de lignes est souvent plus lent que de simplement scanner la table séquentiellement. Les planificateurs de requêtes le savent et ignoreront un index lorsque la fraction estimée de correspondance est trop élevée. À titre d’intuition approximative, si une requête typique utilisant l’index renverrait plus que quelques pourcents des lignes, l’index peut ne pas être utilisé du tout, et il constitue alors un surcoût pur.

Index composites et règle du préfixe le plus à gauche

Un index peut couvrir plusieurs colonnes, dans un ordre spécifique. Par exemple :

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Considérez cela comme triant les entrées d’abord par customer_id, puis par created_at au sein de chaque client, comme un annuaire téléphonique trié par nom de famille, puis par prénom.

L’idée du préfixe le plus à gauche découle directement de cet ordre de tri. Cet index peut servir efficacement :

• WHERE customer_id = 42
• WHERE customer_id = 42 AND created_at >= '2024-01-01'

Mais il ne peut pas servir efficacement WHERE created_at >= '2024-01-01' seul, parce que les entrées pour une plage de dates donnée sont réparties entre tous les clients ; vous ne pouvez pas utiliser un annuaire trié par nom de famille pour trouver toutes les personnes prénommées « Anna ». L’index est utilisable seulement lorsque vos conditions contraignent un préfixe de sa liste de colonnes, en commençant par la colonne la plus à gauche. Cela signifie que (customer_id, created_at) et (created_at, customer_id) sont des index différents servant des requêtes différentes, et l’ordre des colonnes doit suivre vos modèles de requêtes les plus importants. Une règle empirique commune : mettez d’abord les colonnes filtrées par égalité, puis la colonne de plage ou de tri.

Quand un index n’aide pas

• Faible sélectivité : filtrer WHERE is_active = true sur une table où 90 % des lignes sont actives. Le planificateur fera un scan à la place.
• Fonctions ou expressions sur la colonne : WHERE LOWER(email) = 'x@y.com' ne peut pas utiliser un index simple sur email, parce que l’index stocke les valeurs brutes, pas les valeurs transformées. (Certaines bases de données prennent en charge des index d’expression, mais l’index simple ne sera pas utilisé.)
• Wildcards en tête : WHERE name LIKE '%son' ne peut pas utiliser un B‑tree, car l’ordre trié n’aide que lorsque le préfixe est connu.
• Sauter la colonne la plus à gauche d’un index composite, comme décrit ci‑dessus.
• Tables minuscules : pour quelques centaines de lignes, un scan est déjà rapide ; l’index ajoute un coût d’écriture sans bénéfice.
• Incompatibilités de type ou conversions implicites sur la colonne indexée peuvent aussi empêcher l’utilisation de l’index.

Deux petits exemples

Un index utile. Supposons que votre application exécute constamment :

SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Alors cet index convient parfaitement :

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

La base saute aux entrées du client 42, qui sont déjà triées par created_at, lit les 20 plus récentes, et s’arrête. C’est rapide sur une table de n’importe quelle taille, et il sert aussi les recherches simples par customer_id grâce au préfixe le plus à gauche.

Un index problématique. Supposons que vous créiez plutôt :

CREATE INDEX idx_orders_status ON orders (status);

où status a trois valeurs possibles et la plupart des lignes sont 'shipped'. Une requête comme SELECT * FROM orders WHERE status = 'shipped' correspond à la majeure partie de la table, donc le planificateur fera de toute façon un scan de la table. L’index est rarement ou jamais utilisé, pourtant chaque insert et chaque mise à jour du status paie pour le maintenir. C’est une perte nette. (Une exception à connaître : indexer une colonne de faible cardinalité peut être rentable quand une valeur est rare et fortement interrogée, comme le petit ensemble de commandes 'pending', mais la version générique ci‑dessus est une erreur classique.)

Checklist pratique avant d’ajouter un index

1. Identifiez d’abord la requête réellement lente ; n’indexez pas de manière spéculative. Regardez les vrais modèles de requêtes et utilisez EXPLAIN pour voir le plan actuel.
2. Vérifiez la sélectivité : les requêtes typiques utilisant cet index renverront‑elles une petite fraction de la table ? Sinon, reconsidérez.
3. Pour les filtres et tris sur plusieurs colonnes, concevez un index composite unique avec le bon ordre de colonnes (colonnes d’égalité en premier, puis colonnes de plage/tri) plutôt que plusieurs index mono‑colonne.
4. Confirmez la règle du préfixe le plus à gauche : votre requête la plus courante contraint‑elle la première colonne de l’index ?
5. Assurez‑vous que la requête ne neutralise pas l’index avec des fonctions, des wildcards en tête, ou des conversions de type sur la colonne indexée.
6. Pesez le trafic d’écriture : sur des tables fortement écrites, chaque index supplémentaire a un coût réel. Supprimez les index qui se chevauchent ou qui sont des préfixes d’autres.
7. Vérifiez si un index existant couvre déjà la requête avant d’en créer un nouveau.
8. Après avoir créé l’index, vérifiez avec EXPLAIN que le planificateur l’utilise réellement, et mesurez le temps de la requête avant et après.
9. Passez en revue périodiquement et supprimez les index inutilisés ; ils coûtent en écritures et en stockage pour toujours.

Le modèle mental essentiel à conserver : un index est une structure de recherche triée que vous payez à chaque écriture pour que des lectures spécifiques et sélectives deviennent bon marché. Ajoutez‑en un lorsque vous pouvez nommer la requête qu’il sert et démontrer qu’il aide.

Parlons des index de bases de données. Vous avez écrit des requêtes SQL qui fonctionnent probablement très bien sur votre machine de développement avec quelques centaines de lignes. Mais lorsqu'une table atteint des millions de lignes, certaines de ces requêtes peuvent devenir incroyablement lentes. Un index est l'outil le plus courant que nous utilisons pour résoudre ce problème.

Qu'est-ce qu'un index de base de données ? Une analogie

Pensez à un index comme à l'index à la fin d'un manuel. Si vous voulez trouver toutes les pages qui mentionnent "Arbres B", vous ne lisez pas le livre entier de couverture en couverture. Au lieu de cela, vous allez à l'index, trouvez l'entrée pour "Arbres B", et voyez une liste triée de numéros de page (par exemple, 112, 254, 256). C'est beaucoup plus rapide.

Un index de base de données fonctionne de la même manière. C'est une structure de données distincte que la base de données maintient. Elle contient une copie des données d'une ou plusieurs colonnes dans un ordre trié, ainsi qu'un pointeur vers la ligne d'origine dans la table. Lorsque vous interrogez des données dans cette colonne, la base de données peut utiliser cet index trié pour trouver rapidement les pointeurs vers les lignes dont vous avez besoin, au lieu de parcourir toute la table ligne par ligne (un "scan complet de table").

Comment ça marche : L'arbre B

La plupart des bases de données relationnelles utilisent un arbre B pour leurs index standard. Vous n'avez pas besoin de connaître les détails informatiques approfondis, mais à un niveau élevé, un arbre B est une structure d'arbre auto-équilibrée. Imaginez une hiérarchie ramifiée. Pour trouver une valeur, la base de données commence au nœud racine et suit un chemin descendant à travers les branches. Chaque étape réduit considérablement la recherche. Cela permet à la base de données de trouver n'importe quelle valeur dans un ensemble de données massif en très peu d'étapes, rendant les recherches incroyablement efficaces (complexité temporelle logarithmique, ou O(log n)) par rapport à un scan complet de table (complexité temporelle linéaire, ou O(n)).

Les compromis : Vitesse d'écriture et stockage

Les index ne sont pas gratuits. Ils entraînent deux coûts principaux :

1. Stockage : Un index est une structure de données physique stockée sur disque. Il duplique les données de votre table, il consomme donc de l'espace de stockage supplémentaire.
2. Écritures plus lentes : C'est le compromis le plus important. Lorsque vous INSERT, UPDATE ou DELETE une ligne, la base de données ne se contente pas de modifier la table. Elle doit également mettre à jour chaque index contenant les colonnes affectées pour le maintenir synchronisé et trié. Si vous avez une table avec cinq index, une instruction INSERT entraîne en fait six écritures : une pour la table et une pour chaque index. Cela peut considérablement ralentir les applications dont les écritures sont intensives.

Concepts clés pour l'indexation pratique

1. Sélectivité
La sélectivité fait référence au nombre de valeurs uniques dans une colonne par rapport au nombre total de lignes.

• Haute sélectivité : Une colonne où la plupart des valeurs sont uniques, comme user_id ou email_address. Les index sur ces colonnes sont très efficaces car ils peuvent réduire une recherche à une seule ou à quelques lignes très rapidement.
• Basse sélectivité : Une colonne avec très peu de valeurs uniques, comme un indicateur booléen is_active (vrai/faux) ou une colonne status ('en attente', 'actif', 'inactif'). Un index sur une colonne à faible sélectivité est souvent inutile. Si vous recherchez tous les utilisateurs actifs, cela peut représenter 80 % de votre table. Il est plus rapide pour la base de données de lire toute la table que de faire des allers-retours entre un index et les données de la table.

2. Index composites et règle du préfixe le plus à gauche
Un index composite est un index sur deux colonnes ou plus, comme CREATE INDEX idx_name ON users (last_name, first_name);.

L'ordre des colonnes est critique. C'est la règle du préfixe le plus à gauche. Un index sur (A, B, C) peut être utilisé par la base de données pour accélérer les requêtes qui filtrent sur :

• A
• A et B
• A, B et C

Cependant, il ne peut pas être utilisé pour les requêtes qui filtrent uniquement sur B, C ou B et C. La base de données a besoin des premières colonnes de la définition de l'index pour commencer sa recherche.

Exemples SQL

Supposons une table products avec des millions de lignes.

Bon exemple d'index :
De nombreux utilisateurs recherchent des produits par un code produit unique.

-- La requête
SELECT name, price FROM products WHERE product_code = 'ABC-12345';

-- La solution : Un index sur une colonne à haute sélectivité
CREATE INDEX idx_products_product_code ON products (product_code);

Cet index est très efficace car product_code est unique (haute sélectivité), et l'index permet à la base de données de trouver la ligne de produit exacte presque instantanément.

Exemple d'index moins utile :
Nous voulons trouver tous les produits actuellement disponibles à la vente.

-- La requête
SELECT * FROM products WHERE is_available = TRUE;

-- L'index problématique
CREATE INDEX idx_products_is_available ON products (is_available);

Cet index n'est pas très utile. La colonne is_available a une faible sélectivité (seulement deux valeurs : vrai et faux). Si 90 % des produits sont disponibles, la base de données ignorera probablement cet index et effectuera un scan complet de la table, car c'est plus efficace.

Liste de contrôle : Quand ajouter un index

Avant d'ajouter un index, posez-vous ces questions :

1. La table est-elle grande ? (Les index sur de petites tables ont peu ou pas d'avantages).
2. Une requête spécifique est-elle lente ? (N'ajoutez pas d'index aveuglément ; ajoutez-les pour résoudre un problème de performance connu).
3. Filtre-je fréquemment sur cette colonne dans une clause WHERE ou l'utilise-je dans un JOIN ? (Ce sont les principaux candidats à l'indexation).
4. La colonne a-t-elle une haute sélectivité ? (L'index réduira-t-il significativement le nombre de lignes à vérifier ?).
5. Pour un index composite, ma requête utilise-t-elle la ou les premières colonnes de l'index ?
6. S'agit-il d'une table à forte lecture ? (Si la table a des écritures très fréquentes mais peu de lectures, le coût de maintenance de l'index pourrait l'emporter sur les avantages en lecture).