Tu as déjà vu une requête passer de 2 secondes à 10 millisecondes grâce à un index ? Moi oui, et c'est exactement ce genre de victoire qui m'a poussé à creuser le sujet. Dans cet article, je partage les leçons que j'ai apprises en optimisant des bases de données en production. On va voir comment repérer les requêtes lentes, valider que ton index est vraiment utilisé avec EXPLAIN ANALYZE, concevoir des index composites efficaces, et comprendre pourquoi les index de couverture et la cardinalité font toute la différence.
J'ai commencé par tâtonner, en ajoutant des index au hasard sur les colonnes qui semblaient logiques. Résultat : des gains parfois spectaculaires, mais aussi des pièges sournois. Un index mal conçu peut ralentir tes écritures et ne même pas être utilisé par le planificateur. C'est là que j'ai compris qu'il faut une méthode, pas de l'intuition.
Dans ce retour d'expérience, je te montre concrètement comment j'identifie les requêtes lentes à partir des logs, comment je vérifie avec EXPLAIN ANALYZE que mon index est bien sollicité, et comment je conçois des index composites qui tiennent la route. On parlera aussi des index de couverture, ces alliés méconnus qui peuvent éliminer des accès table entiers, et de la cardinalité, ce critère qui change tout dans le choix des colonnes à indexer.
Identifier les requêtes lentes et valider l'utilisation des index
Le plus dur n'est pas de créer un index, c'est de savoir s'il est réellement utilisé. J'ai vu trop de collègues ajouter des index au hasard sans vérifier l'impact. La première étape, c'est de repérer les requêtes qui traînent. Sur PostgreSQL, j'active log_min_duration_statement à 100ms et je consulte pg_stat_statements pour avoir le top des requêtes les plus lentes. Sur MySQL, le slow query log fait le même travail. Tu obtiens une liste de requêtes avec leur temps d'exécution, et tu peux déjà identifier les coupables.
Mais attention : une requête lente ne veut pas dire qu'un index manque. Parfois, l'index existe mais le planificateur ne l'utilise pas. C'est là que EXPLAIN ANALYZE devient ton meilleur ami. Regarde ce plan typique :
Si tu vois un Seq Scan alors que tu as un index sur email, c'est que quelque chose cloche. Peut-être que la cardinalité est trop faible, ou que ton index est mal conçu. En ajoutant un index et en relançant EXPLAIN ANALYZE, tu devrais voir un Index Scan et un temps d'exécution bien plus bas. C'est la seule façon de valider que ton index sert vraiment.
Concevoir des index composites efficaces
L'erreur classique que j'ai faite pendant des années : créer un index composite en mettant les colonnes dans l'ordre où elles apparaissent dans la requête. Résultat : des index énormes, rarement utilisés, et des performances pires qu'avant. L'ordre des colonnes n'est pas anodin. Il détermine quelles conditions peuvent être filtrées efficacement et lesquelles ne le peuvent pas.
Prenons un exemple concret. Tu as une table orders avec customer_id, status, et created_at. Tu veux souvent récupérer les commandes d'un client avec un statut donné et triées par date. Si tu crées l'index (customer_id, status, created_at), le planificateur peut utiliser l'index pour filtrer sur customer_id et status, puis trier sur created_at sans tri supplémentaire. Mais si tu inverses l'ordre, disons (status, customer_id, created_at), l'index ne sera efficace que si tu filtres d'abord sur status, ce qui est rarement le cas. La règle d'or : place les colonnes avec la plus haute cardinalité en premier. Ici, customer_id a une cardinalité bien plus élevée que status.
Vérifie toujours avec EXPLAIN ANALYZE que l'index est bien utilisé. Si tu vois un Bitmap Index Scan ou un Index Scan avec les bonnes conditions, c'est bon. Sinon, ajuste l'ordre. Un index composite mal ordonné, c'est pire que pas d'index du tout : il prend de la place et ralentit les écritures. J'ai réduit une requête de 800ms à 15ms simplement en réordonnant les colonnes d'un index existant. Ça vaut le coup de tester plusieurs ordres avec des données réelles.
Index de couverture et cardinalité : des concepts clés
Le jour où j'ai découvert les index de couverture, j'ai eu l'impression de tricher. Un index de couverture, c'est un index qui contient toutes les colonnes nécessaires à ta requête. Résultat : le moteur n'a même pas besoin de toucher à la table. Il lit l'index, il te donne la réponse. Point final.
Prenons un exemple concret. Tu as une table commandes avec des millions de lignes. Ta requête veut le total par client : SELECT client_id, SUM(montant) FROM commandes GROUP BY client_id. Si tu crées un index sur (client_id, montant), le moteur peut tout calculer depuis l'index. Plus d'accès à la table. C'est un index de couverture. J'ai vu des requêtes passer de 200ms à 2ms comme ça.
La cardinalité, c'est le nombre de valeurs distinctes dans une colonne. Plus elle est élevée, plus l'index est sélectif. Un index sur une colonne avec 2 valeurs (comme un booléen) ne sert presque à rien. Sur une colonne avec des millions de valeurs uniques, il devient redoutable. J'ai vu des gens créer des index sur des colonnes à faible cardinalité en pensant que ça allait accélérer. Non. Le planificateur les ignore, et tu as juste ralenti tes INSERT. Mon conseil : vérifie toujours la cardinalité avant de créer un index. Si elle est trop faible, cherche autre chose.
Conclusion
Un index bien pensé peut faire passer une requête de 2 secondes à 10 millisecondes. Mais un index mal conçu, c'est pire que pas d'index du tout : il ralentit tes écritures et ne sert même pas le planificateur. J'ai appris ça à mes dépens avec un index composite sur (status, created_at) alors que toutes mes requêtes filtraient sur created_at en premier. Résultat : des seq scans systématiques et des INSERT plus lents.
La leçon, c'est que l'intuition ne suffit pas. Tu dois systématiquement valider avec EXPLAIN ANALYZE que ton index est utilisé. Et pour concevoir un index composite, réfléchis à l'ordre des colonnes : commence par celles qui ont la meilleure cardinalité, celles qui éliminent le plus de lignes. Un index de couverture peut aussi te sauver la vie en évitant des accès table, mais attention à ne pas en abuser.
En résumé : identifie les requêtes lentes avec les logs, valide chaque index avec EXPLAIN ANALYZE, et pense cardinalité avant de créer quoi que ce soit. C'est la seule méthode qui tient la route en production.