Vos INSERT ont-ils ralenti sans qu’aucune ligne de code n’ait bougé ? Neuf fois sur dix, quelqu’un a ajouté un index « pour être sûr », et la table en porte désormais une pile que personne n’audite. L’index est l’outil de performance le plus puissant côté base, et le plus mal dosé : trop rare là où il sauverait une requête, partout ailleurs par réflexe de prudence.
Voici la procédure que je suis, dans l’ordre, quand on me confie une base dont les requêtes traînent.
Un index transforme un balayage de table en accès direct : en lecture, c’est souvent l’optimisation la plus rentable, mais chaque index ralentit les écritures et mange du disque — tout le métier tient dans le choix des bonnes colonnes.
1. Je pars des requêtes lentes, pas des colonnes. Le réflexe naturel consiste à regarder le schéma et à se demander « qu’est-ce que je pourrais indexer ». Mauvaise entrée. J’ouvre d’abord le journal des requêtes lentes et je liste ce qui pèse vraiment sur la production. Sur une table de quelques milliers de lignes, un balayage séquentiel ne se voit pas ; à dix millions, lire la table d’un bout à l’autre se sent. C’est là que se trouve le travail.
2. Je passe la requête coupable au EXPLAIN. Cette commande montre comment la base compte exécuter la requête : balayage complet, ou accès direct via un index. Quand je vois un Seq Scan sur une grosse table filtrée par une clause WHERE, j’ai trouvé ma cible. Un index est une structure ordonnée qui pointe sur les lignes concernées, à la manière de l’index alphabétique au dos d’un livre. Sur une colonne filtrée souvent, on passe d’une seconde à quelques millisecondes.
3. Je choisis la colonne en fonction de ce que la requête fait réellement. Trois familles de colonnes méritent un index : celles des clauses WHERE récurrentes, les clés étrangères jointes dans les JOIN, et les colonnes d’ORDER BY qui trient de gros résultats. Si une requête filtre sur plusieurs colonnes à la fois, un index composite sur la bonne combinaison bat plusieurs index isolés. L’ordre des colonnes décide de tout : un composite (client, date) sert les requêtes qui partent du client, tandis qu’une requête ne connaissant que la date n’en tirera rien.
4. Je mesure le revers, côté écriture. C’est l’étape qu’on saute, et celle qui coûte cher. Chaque index présent doit être remis à jour à chaque insertion, modification ou suppression. J’ai repris la base d’un client industriel où la consigne maison était d’indexer chaque colonne par prudence : une table de logs en portait onze. Les lectures volaient, mais le batch de nuit qui chargeait les relevés débordait sur la matinée. On en a retiré huit, qu’aucune requête n’empruntait, et le traitement nocturne est rentré dans ses horaires. Reste le disque, que personne ne surveille tant qu’il en reste : un index pèse parfois autant que les données qu’il décrit.
5. Je vérifie après coup qui sert vraiment. Un index posé n’est pas un index utile. Quelques semaines plus tard, je relis les compteurs d’usage pour traquer ceux qu’aucune requête n’a touchés. Du coût sans contrepartie : je les supprime.
Cette boucle ne se fige pas. Un nouvel écran, un filtre inédit, un volume qui gonfle, et la liste des bons index bouge. Je la rejoue au ralentissement suivant plutôt que de m’appuyer sur l’état de la base d’il y a deux ans. La dernière fois, le coupable n’était même pas une requête : c’était un rapport mensuel exporté en Excel le 1er du mois, qui balayait une table de douze millions de lignes faute d’un seul index sur la colonne de date.