Un index de base de données, c'est la différence entre une requête qui répond en 50 ms et une requête qui fait ramer ton serveur. Je ne compte plus les applications qui se traînent à cause d'un index manquant sur une colonne utilisée dans un WHERE. Le problème inverse existe aussi : un index inutile ralentit les écritures et bouffe de la RAM.
Dans ce tutoriel, je te montre comment choisir les bons index pour PostgreSQL et MySQL. On commence par les types de base : B-tree, index cluster, non-cluster, couvrant. Ensuite, on passe à la pratique : créer des index sur les colonnes des clauses WHERE et JOIN, et utiliser les index composites pour les requêtes multi-colonnes. Je te donne aussi les pièges à éviter, comme les index trop larges ou les colonnes rarement utilisées.
Prenons un exemple concret : une requête SELECT * FROM users WHERE email = 'a@b.com' sans index sur email, c'est un scan complet de la table. Avec un index B-tree, PostgreSQL trouve la ligne en quelques millisecondes. Mais si tu ajoutes un index sur une colonne que tu ne filtres jamais, tu paies le coût de maintenance sans aucun bénéfice.
L'optimisation SQL n'est pas une science exacte. C'est un compromis permanent entre vitesse de lecture et coût d'écriture. Je te montrerai comment vérifier l'utilisation réelle d'un index avec EXPLAIN, et comment repérer les index qui ne servent à rien. Si tu es développeur back-end ou admin BDD, ce guide est pour toi.
Comprendre les différents types d'index (B-tree, cluster, non-cluster, couvrant)
Le B-tree, c'est le couteau suisse des index. PostgreSQL et MySQL l'utilisent par défaut pour toutes les recherches. Il garde les données triées et permet une recherche en log n. Pour 95% des requêtes, ça suffit. Mais il y a des pièges où un autre type d'index te sort de la galère.
L'index cluster, c'est une autre bête. Dans MySQL avec InnoDB, la clé primaire est un index cluster : les lignes sont physiquement stockées dans l'ordre de cette clé. Dans PostgreSQL, tu peux faire pareil avec la commande CLUSTER, mais c'est une opération ponctuelle, pas un mode permanent. L'index non-cluster, lui, est un index séparé qui référence les lignes. C'est le cas de tous les index secondaires dans InnoDB, et de tous les index dans PostgreSQL par défaut.
La différence se voit sur les requêtes qui scannent une plage de valeurs. Avec un index cluster, les lignes sont adjacentes sur le disque, donc la lecture est séquentielle et rapide. Avec un index non-cluster, chaque ligne peut être à un endroit différent, donc le moteur fait des allers-retours aléatoires. Si tu as une table de millions de lignes et une requête SELECT * FROM commandes WHERE date BETWEEN '2024-01-01' AND '2024-01-31', un index cluster sur la date va faire des merveilles. Mais attention : tu ne peux avoir qu'un seul index cluster par table, car les lignes ne peuvent être rangées que d'une seule façon.
Ensuite, il y a l'index couvrant. C'est mon préféré. Il contient toutes les colonnes dont ta requête a besoin, donc le moteur n'a pas besoin de retourner à la table. Exemple :
Avec cet index, la requête SELECT id FROM users WHERE email = 'a@b.com' est servie entièrement par l'index. Pas de lecture de la table, pas de saut aléatoire. C'est le genre de gain qui passe de 5 ms à 0.2 ms. Mais chaque colonne ajoutée à l'index le gonfle et ralentit les écritures. Je réserve ça aux requêtes que je vois en boucle dans les logs.
Alors, comment choisir ? Mon avis : commence par le B-tree, c'est le standard. Si ton moteur utilise un index cluster pour la clé primaire (InnoDB), laisse ça. Pour les requêtes critiques, regarde si un index couvrant peut éliminer le retour à la table. Et surtout, ne crée jamais un index non-cluster sur une colonne que tu filtres une fois par mois. Vérifie avec EXPLAIN avant de créer quoi que ce soit. C'est le seul moyen de savoir si ton index sert vraiment.
Créer des index efficaces pour les clauses WHERE et JOIN
Un seul index manquant sur une colonne de WHERE, et ta requête passe de 50 ms à 5 secondes. Je l'ai vu trop souvent sur PostgreSQL et MySQL. Sans index, le moteur scanne toute la table. Sur 10 millions de lignes, ça veut dire 10 millions de comparaisons. Avec un index B-tree, tu tombes à quelques millisecondes. C'est le premier truc que je vérifie quand une requête rame.
Pour créer un index sur une colonne utilisée dans un WHERE, c'est direct : CREATE INDEX idx_users_email ON users(email); Ensuite, ta requête SELECT * FROM users WHERE email = 'a@b.com' va utiliser l'index. Mais attention : si tu filtres sur plusieurs colonnes, un index sur chaque colonne ne suffit pas. Le moteur ne peut utiliser qu'un index par requête dans la plupart des cas. Il faut un index composite.
Prenons un exemple concret. Une table commandes avec client_id et date_creation. Ta requête : SELECT * FROM commandes WHERE client_id = 42 AND date_creation > '2024-01-01'. Un index composite CREATE INDEX idx_commandes_client_date ON commandes(client_id, date_creation); va faire des merveilles. L'ordre des colonnes compte : mets d'abord la colonne d'égalité, puis celle de la plage. Si tu inverses, l'index n'est pas efficace. Et si tu as une requête avec seulement date_creation, l'index ne sera pas utilisé : la première colonne doit être dans le WHERE.
Pour les JOINs, c'est le même principe. La colonne utilisée dans la jointure doit être indexée. Par exemple, SELECT * FROM commandes JOIN clients ON commandes.client_id = clients.id : l'index sur commandes.client_id est indispensable. Dans PostgreSQL, l'index sur une clé étrangère n'est pas automatique. Dans MySQL avec InnoDB, il l'est. Vérifie avec \di ou SHOW INDEX FROM commandes; pour être sûr.
Un détail que je vois souvent oublié : un index sur une colonne de JOIN sert aussi pour les WHERE sur cette même colonne. Par exemple, si tu as WHERE client_id = 1, l'index idx_commandes_client_id va accélérer ce filtre. C'est un deux-en-un. Mais ne te laisse pas tenter par indexer toutes les colonnes de toutes les tables. C'est le meilleur moyen de ralentir tes INSERT et de bouffer de la RAM.
Mon conseil : ne crée pas d'index à l'aveugle. Utilise EXPLAIN ANALYZE pour voir si ta requête fait un scan complet. Si oui, et que la table est grosse, ajoute un index. Mais si la table est petite ou la requête rare, l'index n'est qu'un coût. C'est un compromis permanent. Et si tu veux aller plus loin, regarde les index couvrants : ils incluent les colonnes du SELECT pour éviter la lecture de la table. C'est la prochaine étape.
Utiliser les index composites pour les requêtes multi-colonnes
Un index composite qui est la réponse quand ta requête filtre sur plusieurs colonnes et que le moteur n'utilise qu'un seul index. Je l'ai vu des centaines de fois : une table orders avec un index sur user_id, et une requête WHERE user_id = 1 AND status = 'pending'. Le moteur récupère toutes les commandes de l'utilisateur, puis filtre le statut en mémoire. Si l'utilisateur a 10 000 commandes, tu viens de charger 10 000 lignes pour en garder 50. C'est exactement le genre de requête qui se traîne en production.
Un index composite sur (user_id, status) règle le problème. Le moteur descend dans l'arbre B-tree avec les deux valeurs d'un coup. La requête devient une recherche de plage précise, pas un filtre après coup. Voici la commande :
Après ça, EXPLAIN ANALYZE te montre un Index Scan au lieu d'un Seq Scan. Le gain est souvent spectaculaire : je suis passé de 120 ms à 20 ms sur une table de 2 millions de lignes. C'est valable dans PostgreSQL et MySQL.
Mais l'ordre des colonnes est critique. Un index composite suit le principe du préfixe le plus à gauche : les colonnes de gauche sont utilisées en premier pour la recherche. Pour une requête avec une égalité sur user_id et une plage sur created_at, l'index (user_id, created_at) est parfait. Le moteur trouve l'utilisateur, puis parcourt la plage de dates. Si tu inverses en (created_at, user_id), le moteur ne peut pas utiliser l'index pour cette requête, car il doit d'abord connaître la date pour trouver l'utilisateur. La règle simple : les colonnes d'égalité d'abord, les colonnes de plage en dernier.
Un index composite peut aussi servir d'index couvrant. Si ta requête ne sélectionne que les colonnes présentes dans l'index, le moteur n'a pas besoin de revenir à la table. Par exemple, pour SELECT user_id, status FROM orders WHERE user_id = 1, l'index (user_id, status) suffit. C'est un gain énorme sur les requêtes de comptage. Mais attention : ne mets pas toutes les colonnes de la table dans l'index. Tu obtiendrais un index énorme, lent à maintenir, et tu perdrais tout le bénéfice.
Mon conseil : commence par un index composite à deux colonnes sur les filtres les plus fréquents. Vérifie avec EXPLAIN ANALYZE que le moteur l'utilise vraiment. Et ne crée pas un index pour chaque combinaison possible. Chaque index ralentit les INSERT, UPDATE et DELETE. Sur une table en écriture intensive, un index inutile coûte plus cher qu'il ne rapporte. Je garde les index composites pour les requêtes qui reviennent souvent, et je supprime ceux qui n'apparaissent pas dans les logs de requêtes lentes.
Bonnes pratiques : éviter les index inutiles et vérifier leur utilisation
Un index que personne n'utilise, c'est un passif clandestin. Il ralentit chaque INSERT, UPDATE et DELETE et il bouffe de la RAM pour rien. Je l'ai vu trop souvent : des devs ajoutent des index sur toutes les colonnes qui passent, et la base devient lente à écrire. Le premier réflexe, c'est de se demander : est-ce que cette colonne est vraiment utilisée dans un WHERE ou un JOIN ?
Les colonnes trop larges sont un piège classique. Un index sur un TEXT ou un VARCHAR(255), ça peut peser plus lourd que la table elle-même. J'ai vu un index sur une colonne JSONB dans PostgreSQL : il prenait 10 fois la place de la donnée, et il n'était jamais utilisé. Résultat : chaque écriture était plus lente, et le cache était pollué. Même chose pour les colonnes rarement utilisées. Si une colonne a une cardinalité faible, comme un boolean ou un statut à 3 valeurs, l'index ne sert à rien : le moteur va lire 30% de la table de toute façon. Un index sur une colonne qui n'apparaît que dans 0,1% des requêtes, c'est du gaspillage pur.
Alors, comment vérifier l'utilisation réelle ? Dans PostgreSQL, tu as la vue pg_stat_user_indexes. La colonne idx_scan te dit combien de fois l'index a été utilisé. Voici la requête que je lance régulièrement :
Si idx_scan est à zéro, l'index n'a jamais servi. Mais attention : un index peut être utilisé pour une contrainte d'unicité ou une clé étrangère sans jamais apparaître dans un scan. Vérifie avec \d table avant de le supprimer.
Côté MySQL, c'est plus simple avec le sys schema : SELECT * FROM sys.schema_unused_indexes; Ça liste tous les index qui n'ont jamais été utilisés. Si tu n'as pas le sys schema, tu peux interroger performance_schema.table_io_waits_per_index_by_io, mais c'est plus verbeux. Et ne te fie pas à un simple EXPLAIN sur une requête : ça te dit seulement si l'index est utilisé pour cette requête précise, pas s'il est utile globalement. Pour ça, il faut regarder les statistiques sur plusieurs jours.
Mon conseil : passe la liste de tes index chaque mois. Supprime ceux qui ont idx_scan à zéro depuis 30 jours, sauf s'ils protègent une contrainte. Commence par le minimum : clé primaire et index sur les colonnes de jointure. Ajoute le reste quand tu vois des requêtes lentes dans les logs. C'est la seule méthode qui tient la route.
Conclusion
Un index bien placé, c'est le genre de changement qui fait passer une requête de 2 secondes à 10 millisecondes. J'ai vu ça des dizaines de fois sur des tables de plusieurs millions de lignes. Et le plus souvent, c'est un simple index B-tree sur une colonne utilisée dans un WHERE qui fait le travail.
Dans ce tutoriel, tu as parcouru les types d'index fondamentaux : B-tree, index cluster, non-cluster, couvrant. Tu as appris à créer des index simples pour les WHERE et les JOIN, et à utiliser des index composites pour les requêtes multi-colonnes. Par exemple, un index sur (client_id, statut_commande) peut transformer une requête qui scannait toute la table en une recherche quasi instantanée.
Mais n'oublie pas que chaque index a un coût. Chaque INSERT, UPDATE ou DELETE doit maintenir l'index. Trop d'index, c'est la facture sur les écritures. C'est pour ça que je te conseille de vérifier l'usage réel de tes index avec EXPLAIN avant de les créer, et de surveiller les index jamais utilisés avec pg_stat_user_indexes sous PostgreSQL ou performance_schema sous MySQL. Un index qui ne sert jamais, c'est de la RAM et du temps de disque gaspillés.
Mon avis final : commence simple. Un index sur les colonnes de filtrage et de jointure, mesure l'impact, et n'ajoute des index couvrants ou partiels que si tu en as vraiment besoin. L'optimisation SQL, c'est un jeu de compromis entre lecture et écriture. Mais avec ces bases, tu peux déjà optimiser en confiance les performances de tes bases de données.