Indexation de base de données expliquée
Votre application web semble rapide pendant le développement, mais à mesure que les données augmentent, les requêtes qui prenaient quelques millisecondes prennent maintenant des secondes. Les utilisateurs se plaignent et le CPU de votre base de données s'envole. Le coupable est souvent l'absence ou la mauvaise utilisation d'index. L'indexation est l'une des compétences les plus rentables pour les développeurs backend, mais elle est souvent mal comprise. Ce guide explique comment fonctionnent les index de base de données, quand les utiliser et comment éviter les pièges courants.
Qu'est-ce qu'un index de base de données ?
Pensez à un index comme à l'index à la fin d'un manuel. Au lieu de parcourir chaque page pour trouver un sujet, vous le recherchez dans l'index, qui vous indique les bonnes pages. Un index de base de données fonctionne de manière similaire : c'est une structure de données qui permet au moteur de base de données de trouver rapidement des lignes sans parcourir toute la table.
Sans index, une requête comme SELECT * FROM users WHERE email = 'alice@example.com' force un balayage complet de la table — la base de données lit chaque ligne jusqu'à trouver une correspondance. Avec un index sur email, la base de données peut accéder directement à la ligne correspondante.
Comment fonctionnent les index sous le capot
La plupart des bases de données relationnelles utilisent par défaut des index B-tree (arbre équilibré). Un B-tree maintient les données triées et permet les recherches, l'accès séquentiel, les insertions et les suppressions en temps logarithmique. C'est pourquoi les recherches indexées sont rapides même pour des millions de lignes.
D'autres types d'index incluent :
- Index de hachage : Bons pour les recherches exactes, pas pour les requêtes de plage.
- Index bitmap : Efficaces pour les colonnes à faible cardinalité (par exemple, les indicateurs de statut), courants dans les entrepôts de données.
- Index de texte intégral : Spécialisés pour la recherche de contenu textuel.
- Index GiST/GIN : Utilisés dans PostgreSQL pour les données géométriques et JSON.
Pour la plupart des applications web, les index B-tree sont la base.
Quand créer un index
Les index ne sont pas gratuits — ils consomment du stockage et ralentissent les écritures. Créez-les stratégiquement :
- Colonnes dans les clauses WHERE : Si vous filtrez fréquemment par une colonne, indexez-la.
- Colonnes dans les conditions JOIN : Indexez les clés étrangères pour accélérer les jointures.
- Colonnes dans ORDER BY : Un index peut éliminer le besoin d'une opération de tri.
- Colonnes dans GROUP BY : Les index peuvent aider les requêtes d'agrégation.
Cependant, évitez d'indexer les colonnes rarement interrogées ou à très faible cardinalité (par exemple, un indicateur booléen) sauf si elles sont utilisées en combinaison avec d'autres colonnes.
Types d'index et leurs cas d'utilisation
| Type d'index | Idéal pour | Exemple |
|---|---|---|
| Mono-colonne | Filtres simples | CREATE INDEX idx_email ON users(email); |
| Composite | Requêtes filtrant sur plusieurs colonnes | CREATE INDEX idx_name_age ON users(last_name, first_name); |
| Unique | Application de l'unicité | CREATE UNIQUE INDEX idx_username ON users(username); |
| Partiel | Indexation d'un sous-ensemble de lignes | CREATE INDEX idx_active ON users(email) WHERE active = true; |
| Couvert | Requêtes qui n'ont besoin que des colonnes indexées | CREATE INDEX idx_covering ON users(email, name); |
Comment créer et vérifier les index
Créer un index est simple. Par exemple, dans PostgreSQL :
CREATE INDEX idx_users_email ON users(email);Après avoir créé un index, vérifiez qu'il est utilisé. Utilisez EXPLAIN (ou EXPLAIN ANALYZE) pour voir le plan de requête :
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';Recherchez "Index Scan" ou "Index Only Scan" au lieu de "Seq Scan". Si vous voyez un balayage séquentiel, l'index pourrait ne pas être utilisé en raison de incompatibilités de type, de fonctions sur la colonne ou de statistiques obsolètes.
Erreurs d'indexation courantes
- Tout indexer : Trop d'index ralentissent les écritures et gaspillent de l'espace.
- Ignorer l'ordre des index composites : Pour un index composite sur
(a, b), les requêtes qui filtrent uniquement surbne peuvent pas utiliser l'index efficacement. - Utiliser des fonctions sur les colonnes indexées :
WHERE YEAR(created_at) = 2025empêche l'utilisation de l'index. Utilisez plutôt des conditions de plage :WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'. - Ne pas mettre à jour les statistiques : Les bases de données s'appuient sur les statistiques pour choisir les index. Exécutez
ANALYZErégulièrement. - Négliger la surcharge d'écriture : Chaque INSERT, UPDATE et DELETE doit mettre à jour les index. Pour les tables à forte écriture, soyez sélectif.
Techniques avancées
Index couvrants
Un index couvrant inclut toutes les colonnes nécessaires à une requête, de sorte que la base de données peut récupérer les données directement depuis l'index sans toucher la table. Cela peut considérablement accélérer les requêtes à forte lecture.
Index partiels
Si vous interrogez fréquemment un sous-ensemble de lignes (par exemple, les utilisateurs actifs), un index partiel est plus petit et plus rapide qu'un index complet.
Balayages index-only
Certaines bases de données prennent en charge les balayages index-only, où toutes les données requises sont dans l'index. C'est le type d'accès par index le plus rapide.
Surveillance et maintenance des index
Les index peuvent devenir gonflés au fil du temps en raison des mises à jour et des suppressions. Dans PostgreSQL, VACUUM et REINDEX aident à maintenir les performances. Dans MySQL, OPTIMIZE TABLE peut reconstruire les index. Examinez régulièrement les journaux de requêtes lentes pour identifier les index manquants.
FAQ
Comment savoir si ma requête utilise un index ?
Utilisez la commande EXPLAIN (ou EXPLAIN ANALYZE) avant votre requête. La sortie indique si la base de données utilise un balayage d'index ou un balayage séquentiel.
Puis-je avoir trop d'index ?
Oui. Chaque index ajoute une surcharge aux opérations d'écriture et consomme du stockage. Visez un équilibre basé sur votre ratio lecture/écriture.
Quelle est la différence entre un index clusterisé et non clusterisé ?
Un index clusterisé détermine l'ordre physique des lignes dans la table (comme une clé primaire dans InnoDB). Un index non clusterisé est une structure distincte qui pointe vers les lignes. Une table ne peut avoir qu'un seul index clusterisé mais plusieurs index non clusterisés.
Prêt à optimiser votre base de données ? Commencez par analyser vos requêtes lentes et ajouter des index là où c'est nécessaire. Pour un formatage et une validation JSON rapides, essayez notre JSON Formatter — c'est gratuit et fonctionne entièrement dans votre navigateur.