L'indexation des bases de données expliquée

Presque tous les problèmes de « base de données lente » que nous analysons se résument à un index manquant ou mal utilisé. L'indexation est la compétence de performance la plus rentable qu'un développeur puisse acquérir, et pourtant elle est souvent perçue comme obscure. Elle ne l'est pas. Une fois que vous comprenez ce qu'est réellement un index et comment lire un plan d'exécution, vous pouvez transformer une requête de dix secondes en une requête de dix millisecondes — délibérément, et non par chance.

Ce qu'est réellement un index

Imaginez un index comme celui qu'on trouve en fin de livre. Sans lui, trouver chaque occurrence d'un sujet implique de lire chaque page — c'est un parcours séquentiel. Avec lui, vous allez directement aux bonnes pages. Dans une base de données, un index est une structure de données séparée et triée qui permet au moteur de retrouver des lignes sans parcourir la table entière.

La plupart des index sont des B-trees, qui maintiennent les valeurs triées afin que la base de données puisse effectuer une recherche binaire. C'est pourquoi un index B-tree accélère les égalités (=), les plages (<, >, BETWEEN), les tris et les correspondances par préfixe — mais n'apporte rien pour un LIKE '%foo' avec un joker en début de chaîne.

Indexez ce que vous filtrez, joignez et triez

La règle de base : toute colonne qui apparaît régulièrement dans une clause WHERE, une condition de JOIN ou un ORDER BY est candidate à un index. Les clés étrangères valent presque toujours la peine d'être indexées, car vous les utilisez en permanence pour vos jointures.

Pour les requêtes qui filtrent sur plusieurs colonnes, un index composite portant sur plusieurs colonnes ensemble surpasse des index distincts colonne par colonne. Mais l'ordre des colonnes est déterminant : un index sur (tenant_id, created_at) aide les requêtes filtrant sur tenant_id seul ou sur les deux colonnes, mais pas celles filtrant sur created_at seul. Placez en premier la colonne la plus sélective et la plus fréquemment filtrée.

Apprenez à lire EXPLAIN

Vous n'avez jamais à deviner si un index est utilisé. Dans PostgreSQL, exécutez EXPLAIN ANALYZE sur votre requête et lisez le plan :

• Un Seq Scan sur une grande table dans une requête fréquente est un signal d'alarme — la base de données lit tout.
• Un Index Scan ou un Index Only Scan indique que votre index fait son travail.
• L'écart entre le nombre de lignes estimé et le nombre réel vous révèle si les statistiques du planificateur sont à jour.

Faites de EXPLAIN ANALYZE un réflexe avant et après l'ajout d'un index. Cela transforme le travail de performance : finis les tâtonnements, place à la mesure.

Les index ne sont pas gratuits

La tentation d'indexer tout est réelle, mais chaque index a un coût. Les index occupent de l'espace disque et, surtout, chaque INSERT, UPDATE et DELETE doit mettre à jour tous les index concernés. Sur-indexer une table à forte charge en écriture ralentit précisément les opérations qui vous importent.

Indexez donc de façon raisonnée :

1. Ajoutez des index pour soutenir des requêtes lentes réelles et mesurées, pas des cas hypothétiques.
2. Supprimez périodiquement les index inutilisés (Postgres suit les statistiques d'utilisation des index).
3. Repérez les index redondants, notamment quand un index composite couvre déjà une colonne seule.

Cultivez le réflexe

Toute la discipline tient en une boucle : écrivez la requête, exécutez EXPLAIN ANALYZE, ajoutez l'index que le plan réclame, puis mesurez à nouveau. Appliquez cela systématiquement et les crises de « base de données lente » disparaissent presque entièrement. Les équipes qui peinent ne manquent pas d'un secret mystérieux — elles n'ont simplement jamais regardé un plan d'exécution.

Si votre application rame et que vous suspectez la base de données, parlez-nous-en.