Les leçons apprises avec les index de BDDLes leçons apprises avec les index de BDD

Les index de base de données sont ces petits héros invisibles qui rendent vos requêtes acceptables ou les transforment en cauchemars. Après des années à maintenir des applications en production, à optimiser des requêtes qui mettaient 47 secondes à s'exécuter et à déboguer des deadlocks mystérieux, j'ai compris une vérité simple : un bon index change tout, un mauvais index ne change rien, et trop d'index changent absolument tout... en mal.

Cet article est le fruit de retours d'expérience concrets, loin des abstractions théoriques. Vous y trouverez ce qui fonctionne vraiment en production, les pièges récurrents et surtout, comment éviter de reproduire les mêmes erreurs que j'ai commises.

Le mythe de l'index magique

Commençons par démolir une croyance populaire : ajouter un index sur toutes les colonnes d'une clause WHERE ne résout pas les problèmes de performance. J'ai hérité d'une base de données avec 287 index sur une table de 15 colonnes. Les requêtes restaient lentes. Le problème ? Les index s'entretuaient.

Voici ce qui s'est réellement passé : les opérations d'insertion et de mise à jour maintenaient ces 287 index, consommant plus de ressources que les requêtes SELECT ne gagnaient. De plus, l'optimiseur de requête, noyé dans les choix, prenait parfois les pires décisions.

La leçon ? Un bon index est un index utilisé. Avant de créer un index, posez-vous ces questions : cette colonne apparaît-elle dans une clause WHERE fréquente ? Est-elle utilisée en jointure ? Quel est le gain réel ? Je recommande systematiquement de vérifier avec des outils comme EXPLAIN PLAN ou l'équivalent dans votre SGBD.

L'ordre des colonnes : votre arme secrète

Voici une situation que j'ai rencontrée trop souvent. Un développeur crée un index sur (user_id, status, created_at) mais exécute systématiquement des requêtes filtrant sur (status, user_id). Résultat : l'index est pratiquement inutile.

L'ordre des colonnes dans un index composite est critique. Les index fonctionnent comme un annuaire téléphonique : si vous cherchez quelqu'un par prénom sans connaître le nom de famille, vous êtes perdu.

Règle d'or que j'applique maintenant : construisez votre index en mettant d'abord les colonnes de l'égalité (WHERE col = value), puis les colonnes de comparaison (WHERE col > value), puis les colonnes de tri. Exemple :

Ce simple réarrangement a réduit le temps d'exécution de 2.3 secondes à 45 millisecondes sur une table de 50 millions de lignes. Pas de magic, juste de la logique appliquée correctement.

Les pièges du covering index et de la sélectivité

Un covering index (index qui contient toutes les colonnes nécessaires pour répondre à une requête sans accéder à la table) semble séduisant. Pourquoi ne pas y ajouter les colonnes du SELECT ? Parce que cela agrandit considérablement l'index et ralentit les écritures.

J'ai appris cela à mes dépens. Une table de logs avec un covering index sur 12 colonnes ralentissait les insertions de 60%. Quand on injecte 500 000 enregistrements par jour, c'est un vrai problème. Solution : garder l'index mince, laisser la table faire son travail pour les colonnes supplémentaires.

La sélectivité est tout aussi importante. Un index sur une colonne booléenne (true/false) est généralement inutile car l'optimiseur préférera un full table scan. Je vérifie systématiquement avec une simple requête :

En règle générale, une sélectivité inférieure à 5% signifie que l'index sera rarement utilisé. Au-dessus de 20%, l'index devient intéressant.

Monitoring et maintenance : ce qui fait vraiment la différence

Le plus grand changement dans ma pratique a été de mettre en place un monitoring systématique des index. Un index créé il y a trois ans peut devenir inutile suite à des modifications de code. Un autre peut être fragmenté et perdre en efficacité.

Je mets en place automatiquement :

1. Détection des index inutilisés : avec des vues système, identifier les index qui n'accélèrent aucune requête. Les supprimer libère de la mémoire et accélère les écritures.

2. Défragmentation régulière : sur MySQL/MariaDB, une simple réorganisation peut regagner 30-40% de performance après quelques mois d'utilisation.

3. Mesure du vrai impact : avant/après chaque création d'index, je mesure le temps de réponse des requêtes pertinentes avec un outil comme Percona Monitoring and Management ou les vues natives de PostgreSQL.

Cette requête seule m'a permis de supprimer 340 Go d'index inutiles sur un cluster de production.

Conclusion : l'index philosophique

Après avoir optimisé des centaines de requêtes, je suis convaincu d'une chose : les index ne sont pas une solution, c'est un outil. La vraie performance vient d'une architecture réfléchie, de requêtes bien écrites et d'une compréhension profonde de vos données.

Commencez par les requêtes lentes, mesurez précisément, créez des index ciblés, puis validez le gain. Supprimer les index inutilisés régulièrement. Et surtout, testez vos changements en staging avant production. Ces habitudes simples transforment radicalement la stabilité de vos applications.