Un SELECT lent qui s’exécutait en 20 millisecondes se met soudain à bloquer pendant 12 secondes. La base de données est passée de 50 000 lignes à 5 millions, et chaque requête devient une loterie. Cela vous rappelle quelque chose?

Tout le monde a entendu parler des index, mais peu de gens les configurent de manière réfléchie. Vous en avez ajouté deux ou trois, les choses semblaient plus rapides, et vous êtes passé à autre chose. Puis, six mois plus tard, les INSERT sont plus lents que les SELECT sans index, parce que chaque insertion reconstruit cinq arbres B inutiles.

Voyons comment les index fonctionnent réellement, quels types existent et, surtout, quelles règles suivre pour les construire sans provoquer de catastrophe en production.

💡 Aperçu rapide:

• Comprendre le mécanisme des index B-tree, hash et composites, car choisir le bon type est impossible sans cette connaissance
• Maîtriser 7 pratiques clés: de l’indexation des clés étrangères à la suppression des index inutilisés
• Apprendre à lire EXPLAIN et à distinguer un index utile d’un index inutile

Qu’est-ce qu’un index de base de données

Un index de base de données est une structure séparée qui stocke les valeurs triées d’une ou plusieurs colonnes d’une table, accompagnées de pointeurs vers les lignes. Lorsque vous exécutez SELECT ... WHERE category_id = 42, le serveur sans index parcourt la table ligne par ligne (full table scan). Avec un index, il trouve les enregistrements nécessaires en O(log n), comme on cherche un nom dans un annuaire.

Un index se crée avec CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Mais le prix de lectures plus rapides, ce sont des écritures plus lentes. Chaque INSERT, UPDATE et DELETE doit mettre à jour non seulement la table, mais aussi tous les index associés. Trois index sur une table d’un million de lignes, et les insertions en masse ralentissent d’un ordre de grandeur. L’équilibre entre vitesse de lecture et vitesse d’écriture est la question centrale de la conception d’index.

Une analyse détaillée du sujet par Hussein Nasser: 497 000 abonnés, une approche d’ingénieur sans superflu. En prenant PostgreSQL comme exemple, il montre le fonctionnement interne des index, pourquoi un CREATE INDEX accélère une requête de 100x alors qu’un autre ne fait rien.

Types d’index: lequel utiliser et quand

Le choix du type d’index détermine l’efficacité avec laquelle la base de données traite votre requête. Les différents SGBD les implémentent différemment, mais les principes sont universels.

B-tree (arbre équilibré)

Le type par défaut dans la plupart des SGBD relationnels. PostgreSQL, MySQL, Oracle et SQL Server utilisent tous le B-tree comme index par défaut. Il stocke les clés dans l’ordre trié, prend en charge les opérations de comparaison, les plages (BETWEEN), la recherche par préfixe (LIKE 'prefix%') et le tri. C’est le meilleur choix dans la grande majorité des cas.

Index de hachage

Fonctionne uniquement pour les comparaisons d’égalité exacte =. Extrêmement rapide sur les requêtes ponctuelles, mais inutile pour les plages et le tri. Dans PostgreSQL, les index de hachage sont prêts pour la production depuis la version 10; dans MySQL, ils ne sont pas disponibles avec InnoDB (uniquement avec MEMORY).

Index clusterisé

Définit l’ordre physique des lignes sur le disque. Dans MySQL InnoDB, la clé primaire est toujours clusterisée: les lignes sont stockées dans l’ordre de la PRIMARY KEY. Dans SQL Server, il y a un index clusterisé par table. Choisir la bonne clé clusterisée (monotoniquement croissante: BIGSERIAL, AUTO_INCREMENT ou UNIQUEIDENTIFIER avec NEWSEQUENTIALID()) apporte des gains sur les requêtes par plage et évite la fragmentation des pages.

Index composite

Un index sur deux colonnes ou plus. Indispensable pour les requêtes qui filtrent et trient sur plusieurs colonnes:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

L’ordre des colonnes est important: placez en premier la colonne avec la sélectivité la plus élevée utilisée dans le WHERE. Le principe du préfixe le plus à gauche: un index (A, B, C) fonctionne pour WHERE A et WHERE A AND B, mais pas pour WHERE B AND C.

Index couvrant

Contient toutes les colonnes dont la requête a besoin, à la fois pour le filtrage et pour la sortie. La base de données récupère tout depuis l’index sans toucher à la table. Dans PostgreSQL, cela se fait avec les colonnes INCLUDE; dans MySQL InnoDB, une couverture implicite se produit via l’index clusterisé.

7 Pratiques clés pour l’indexation

1. Indexer les colonnes utilisées dans WHERE

Première règle: toute colonne régulièrement utilisée dans WHERE, JOIN ... ON et HAVING doit être indexée. Ce sont les opérations qui bénéficient le plus des index.

Avant de créer un index, vérifiez la sélectivité de la colonne. Si la colonne status n’a que trois valeurs («nouveau», «en cours», «terminé») et qu’il y a 2 millions de lignes, un index sur status est presque inutile: le planificateur choisira un parcours complet de la table comme option la moins coûteuse. Un index a du sens lorsque le nombre de valeurs distinctes est suffisamment grand par rapport à la taille de la table.

2. Indexer les colonnes utilisées pour le tri

ORDER BY sans index signifie filesort (MySQL) ou tri explicite (PostgreSQL): le serveur collecte toutes les lignes et les trie en mémoire (ou sur disque si work_mem/sort_buffer_size est trop petit). Un index sur les mêmes colonnes que ORDER BY est gratuit, puisque les données sont déjà triées dans le B-tree.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indexer les colonnes utilisées dans GROUP BY et les agrégations

Un regroupement sans index nécessite un parcours complet et la construction d’une table de hachage. Un index sur les colonnes du GROUP BY transforme l’opération en agrégation en continu: les lignes sont déjà groupées dans l’ordre des clés.

4. Indexer toutes les clés étrangères

Une clé étrangère non indexée est une bombe à retardement. DELETE FROM users WHERE id = 5 avec une FOREIGN KEY (user_id) REFERENCES users(id) sur la table orders mais sans index sur user_id signifie un parcours complet de orders pour chaque suppression. Tous les SGBD courants exigent un index sur la clé étrangère ou en créent un implicitement (MySQL InnoDB le fait automatiquement, PostgreSQL non).

5. Indexer les colonnes uniques et les clés primaires

La clé primaire est indexée automatiquement (souvent comme index clusterisé). Un UNIQUE INDEX explicite protège contre les doublons et accélère aussi les recherches. Toute colonne avec une contrainte d’unicité métier (par exemple email, slug ou external_id) doit avoir un index unique, à la fois pour l’intégrité et pour la performance.

6. Utiliser l’index clusterisé de manière réfléchie

Pour les grandes tables (dizaines de millions de lignes), la bonne clé clusterisée est critique. Un bon choix est une valeur monotoniquement croissante: AUTO_INCREMENT, BIGSERIAL ou UUID v7. Un UUID aléatoire comme clé clusterisée provoque une fragmentation des pages: chaque insertion atterrit à un endroit aléatoire du B-tree, divisant les pages pleines en deux pages à moitié remplies.

7. Supprimer les index inutilisés

Un index qu’aucune requête n’utilise est une perte nette. Il ralentit les écritures, occupe de l’espace disque et de la mémoire du buffer pool, et induit le planificateur de requêtes en erreur. Dans PostgreSQL, la table système pg_stat_user_indexes fournit une liste des index inutilisés:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

Dans MySQL, une information similaire est disponible dans sys.schema_unused_indexes (à partir de la version 5.7). Planifiez un audit mensuel et supprimez les index qui n’ont pas été utilisés une seule fois pendant la période de référence.

Comment vérifier que vos index fonctionnent

Une fois l’index créé, vérifiez qu’il est effectivement utilisé. La commande EXPLAIN (ou EXPLAIN ANALYZE) montre le plan d’exécution de la requête et l’utilisation réelle des index:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

Dans la sortie, cherchez Index Scan ou Index Only Scan (PostgreSQL) / Using index (MySQL). Si vous voyez Seq Scan (PostgreSQL) ou Using where; Using filesort (MySQL), l’index n’est pas utilisé. Raisons possibles: faible sélectivité, mauvais type d’index, ordre des colonnes inadapté dans un index composite, ou statistiques obsolètes (ANALYZE table_name;).

Surveillez régulièrement les métriques: pg_stat_user_indexes.idx_scan dans PostgreSQL, sys.schema_index_statistics dans MySQL. Un index avec zéro scan sur un mois est candidat à la suppression.

⁉️🤔 Questions fréquentes

Combien d’index une table doit-elle avoir?

Idéalement, 2 à 6 index par table activement utilisée. Moins de deux signifie presque certainement que certaines requêtes sont sous-optimales. Plus de six, et vous devriez vérifier soigneusement si tous sont vraiment nécessaires: chaque index supplémentaire ralentit les écritures. Pour les tables de référence (écritures rares, lectures fréquentes), davantage d’index se justifient. Pour les tables opérationnelles à fort trafic (INSERT/UPDATE intensifs), limitez le nombre au minimum.

En quoi un index composite est-il meilleur que plusieurs index sur une seule colonne?

Un index composite (A, B) est UNE seule structure. Le serveur la parcourt une fois. Trois index séparés (A), (B) et (C) pour WHERE A=1 AND B=2 obligent le serveur soit à choisir un seul index (et filtrer le reste), soit à effectuer un bitmap index scan (fusion de bitmaps). Un index composite est presque toujours plus efficace, à condition que l’ordre des colonnes corresponde à vos requêtes.

Quand un index nuit-il au lieu d’aider?

Trois scénarios typiques. Premier: la table est petite (jusqu’à quelques milliers de lignes), et un parcours complet est plus rapide que lire l’index puis récupérer les lignes. Deuxième: un index sur une colonne à faible sélectivité (is_deleted, status avec trois valeurs). Troisième: les insertions en masse pendant un ETL/import, où les index sont reconstruits à chaque lot. Supprimez-les avant le chargement et recréez-les après.

Faut-il indexer les colonnes utilisées dans les JOIN?

Absolument. Chaque JOIN sans index sur la colonne de jointure de la table externe est une boucle imbriquée avec un parcours complet. Pour LEFT JOIN orders ON users.id = orders.user_id, un index sur orders.user_id transforme la boucle imbriquée en recherche par index. Indexez toujours les colonnes sur lesquelles vous faites la jointure.

B-tree ou hash: lequel choisir pour les recherches exactes?

Pour =, le hash est plus rapide: une recherche par hachage prend un temps constant, tandis que le B-tree parcourt l’arbre en un nombre logarithmique d’étapes. Mais le hash ne prend pas en charge les plages, le tri ni UNIQUE. En pratique, le B-tree couvre la grande majorité des scénarios; le hash est un outil de niche pour les recherches ponctuelles par clé dans les systèmes à forte charge (sessions, caches). Dans PostgreSQL, les index de hachage sont prêts pour la production depuis la version 10 et occupent moins d’espace que le B-tree.

Faut-il indexer «au cas où»?

Non. Chaque index est un compromis. Il accélère les lectures au prix d’écritures plus lentes et d’espace disque supplémentaire. N’indexez pas «au cas où»; indexez pour des requêtes spécifiques qui s’exécutent réellement dans votre application. Profilez les requêtes lentes (pg_stat_statements, slow_query_log), ajoutez des index de manière chirurgicale et vérifiez EXPLAIN avant et après.

Le principal enseignement est simple: les index sont un outil, pas un but en soi. Un index composite bien conçu peut remplacer trois index sur une seule colonne et économiser des gigaoctets d’espace disque. Et un seul index inutilisé sur une table avec beaucoup d’écritures peut ralentir toute l’application.

Si vous voulez approfondir, commencez par le guide officiel de conception d’index SQL Server et la documentation PostgreSQL sur les types d’index. Et si vous rencontrez une requête que les index ne peuvent pas résoudre, le problème vient peut-être du modèle de données lui-même.