Le moment où tout se met à ramer
Prends une table classique, celle des utilisateurs. Tu cherches une personne par son adresse mail, parce qu'elle se connecte :
SELECT * FROM utilisateurs
WHERE email = 'sarah@exemple.fr';Cette requête a l'air anodine. Pour la base, sans index, elle veut dire ceci : lis la première ligne, compare son email. Pas la bonne. Ligne suivante, compare. Pas la bonne. Et ainsi de suite jusqu'à trouver, ou jusqu'à la fin de la table.
Ce parcours porte un nom : le full table scan, le balayage complet de la table. Avec cent lignes, il est instantané. Avec deux cent mille, la base lit en moyenne cent mille lignes avant de trouver la bonne. Tu ne le vois pas en développement parce que ta base de test est minuscule. Tu le découvres en production, quand les données réelles s'accumulent.
Le piège est que le coût grossit en ligne droite avec tes données. Deux fois plus de lignes, deux fois plus de travail pour la même requête. Multiplie ce coût par le nombre d'utilisateurs qui chargent la page en même temps, et tu obtiens un serveur de base de données qui sature alors que ton code n'a pas changé d'une virgule. C'est le genre de problème qui n'arrive jamais le jour de la mise en ligne, mais trois mois plus tard, sans prévenir.
C'est le piège le plus courant chez les développeurs juniors et les personnes en reconversion. Le code est correct, la requête renvoie le bon résultat, les tests passent. Le souci n'apparaît que sous le poids des données. Une requête lente ignorée fait partie des signaux qu'un projet se dégrade en silence, un sujet abordé dans notre article sur les signaux d'alarme de la dette technique.
Ce qu'un index fait réellement
Prends un livre technique de huit cents pages. Tu veux retrouver toutes les pages où on parle de "transactions". Deux options.
La première : tu lis le livre page par page en notant chaque occurrence. C'est le full table scan. La seconde : tu vas à l'index alphabétique à la fin, tu cherches "transactions", et tu lis directement : pages 142, 387, 601. Tu sautes au bon endroit sans tout parcourir.
Un index de base de données, c'est exactement cet index alphabétique. C'est une structure annexe, triée, qui contient les valeurs d'une colonne et l'emplacement de la ligne correspondante. La base ne lit plus toute la table, elle consulte l'index, trouve la position, et va chercher la ligne directement.
Dans la quasi-totalité des bases relationnelles (MySQL, PostgreSQL, SQLite), cet index prend la forme d'un arbre équilibré, le B-tree. L'idée tient en une image : au lieu de chercher dans une liste à plat, tu descends dans un arbre où chaque embranchement élimine la moitié des possibilités restantes.
[ M ]
/ \
[ F ] [ S ]
/ \ / \
A..E G..L N..R T..ZLe résultat est spectaculaire. Sur deux cent mille lignes, un balayage complet lit deux cent mille entrées. Un arbre équilibré en lit environ dix-huit. C'est la différence entre un travail qui explose avec tes données et un travail qui reste quasi constant. Voilà pourquoi un index peut transformer huit secondes en quelques millisecondes.
Cette idée est sans doute la plus rentable que tu apprendras sur les bases de données. Elle ne demande pas de maîtriser une théorie complexe. Elle demande de comprendre un seul mécanisme : la base cherche, soit en parcourant tout, soit en suivant un index. Ton travail consiste à lui fournir le bon index au bon endroit, puis à vérifier qu'elle l'utilise. Le reste découle de là.
Créer et vérifier un index
La syntaxe est courte. Pour indexer la colonne email de notre table :
CREATE INDEX idx_utilisateurs_email
ON utilisateurs (email);Une ligne. Et la requête qui prenait huit secondes répond instantanément. Mais avant de poser un index au hasard, apprends à mesurer. C'est le réflexe qui sépare le bricolage de la méthode.
EXPLAIN, ton meilleur outil de diagnostic
Toutes les bases relationnelles savent te dire comment elles comptent exécuter une requête, avant de l'exécuter. Le mot-clé est EXPLAIN. Tu le places devant ta requête :
EXPLAIN SELECT * FROM utilisateurs
WHERE email = 'sarah@exemple.fr';Sans index, la sortie affiche un type d'accès ALL sous MySQL, ce qui signifie balayage complet, avec un nombre de lignes examinées proche du total de la table. C'est le drapeau rouge. Après avoir créé l'index, relance la même commande : le type passe à ref ou const, et le nombre de lignes examinées chute à une poignée. Tu as la preuve chiffrée que ton index sert.
Ne saute jamais cette étape. Poser un index sans vérifier avec EXPLAIN, c'est deviner. Mesurer avant et après, c'est savoir. La même logique de vérification s'applique d'ailleurs à ton code applicatif, comme on le voit dans notre guide sur les tests et le TDD.
L'index composite et l'ordre des colonnes
Souvent tu ne filtres pas sur une seule colonne. Imagine une table de commandes où tu cherches les commandes d'un client, triées par date :
SELECT * FROM commandes
WHERE client_id = 42
ORDER BY date_creation DESC;Ici un index sur deux colonnes, dit composite, est le bon choix :
CREATE INDEX idx_commandes_client_date
ON commandes (client_id, date_creation);L'ordre des colonnes compte énormément. Un index composite se lit de gauche à droite, comme un annuaire trié d'abord par nom de famille, puis par prénom. Avec un annuaire trié ainsi, tu retrouves vite tous les "Martin", puis le bon prénom parmi eux. Mais si tu ne connais que le prénom, l'annuaire ne te sert à rien.
La règle pratique : mets en première position la colonne sur laquelle tu fais une égalité (client_id = 42), et en seconde celle qui sert au tri ou à une plage. Un index (client_id, date_creation) sert la requête ci-dessus. Un index (date_creation, client_id) la sert beaucoup moins bien.
Index simple ou index unique ?
Il existe une variante que tu vas croiser souvent : l'index unique. En plus d'accélérer la lecture, il interdit deux valeurs identiques dans la colonne. Sur un email, c'est précisément ce que tu veux : la base refusera la création de deux comptes avec la même adresse.
CREATE UNIQUE INDEX idx_utilisateurs_email
ON utilisateurs (email);Tu gagnes sur deux tableaux. La recherche par email reste rapide, et la règle d'unicité est garantie au niveau de la base, pas seulement dans ton code. C'est une protection que tu ne peux pas oublier d'écrire un jour de fatigue, car la base la fait respecter pour toi à chaque insertion. Beaucoup de doublons en production viennent justement d'une contrainte posée dans le code applicatif mais absente de la base.
Si tout ce vocabulaire est encore flou, c'est le signe qu'il te manque les fondations SQL. Notre formation SQL et bases de données relationnelles avec MySQL couvre ces bases, de l'interrogation à la conception, sur quatre jours.
Les pièges qui annulent tes index
Un index n'est pas magique. Plusieurs erreurs courantes le rendent inutile, ou pire, font de lui un poids mort. Les voici, par ordre de fréquence.
Indexer tout, partout
Un index accélère les lectures mais ralentit les écritures. À chaque INSERT, UPDATE ou DELETE, la base doit aussi mettre à jour chaque index concerné. Sur une table qui reçoit beaucoup d'écritures, dix index inutiles pèsent lourd. Indexe les colonnes que tu interroges souvent dans un WHERE, un JOIN ou un ORDER BY, pas toutes les colonnes par précaution.
Indexer une colonne à faible cardinalité
La cardinalité, c'est le nombre de valeurs distinctes dans une colonne. Une colonne est_actif qui ne vaut que vrai ou faux a une cardinalité de deux. Un index dessus ne sert presque à rien : si la moitié de la table est active, la base lit quand même la moitié des lignes. L'index brille quand il sépare beaucoup de valeurs uniques, comme un email ou un numéro de commande.
Appliquer une fonction sur la colonne indexée
-- L'index sur email est ignoré
WHERE LOWER(email) = 'sarah@exemple.fr'Dès que tu enveloppes la colonne dans une fonction (LOWER, DATE, une concaténation), la base ne peut plus utiliser l'index, car l'index contient les valeurs brutes, pas leur version transformée. Elle repart en balayage complet. Garde la colonne nue à gauche du signe égal et applique la transformation côté application, ou crée un index adapté à la fonction si ta base le permet.
Le LIKE qui commence par un joker
Une recherche WHERE nom LIKE 'Mar%' peut utiliser l'index, parce qu'elle fixe le début. Mais WHERE nom LIKE '%mar%', avec un joker en tête, oblige la base à tout parcourir. C'est logique : un annuaire trié ne t'aide pas à trouver tous les noms qui contiennent "mar" au milieu. Pour ce besoin, il faut une autre approche, comme la recherche plein texte.
Tu remarqueras que beaucoup de ces requêtes sont générées pour toi par un ORM, dans un projet Symfony ou une API FastAPI. L'ORM écrit le SQL, mais c'est à toi de décider quels index poser. Comprendre ce qui se passe sous le capot reste ta responsabilité de développeur backend.
Oublier de retirer les index morts
Les besoins évoluent. Une requête disparaît, son index reste. Il continue de ralentir tes écritures et d'occuper de l'espace sans rien apporter. Fais le ménage de temps en temps : la plupart des bases savent te dire quels index ne sont jamais utilisés.
Retiens une chose simple. L'index n'est pas une option à activer pour aller plus vite partout. C'est un outil ciblé : tu observes une requête lente, tu confirmes le balayage complet avec EXPLAIN, tu poses l'index qui correspond au filtre, tu vérifies le gain. Trois index bien choisis valent mieux que vingt posés à l'aveugle. Et le jour où une requête redevient lente, tu sauras exactement quoi regarder en premier.
