Les index SQL : accélérer ses requêtes

Quand une table contient quelques dizaines de lignes, la base de données peut la parcourir entièrement sans que vous vous en rendiez compte. Mais dès que le volume grimpe à des centaines de milliers de lignes, ce parcours complet (le fameux full table scan) devient le premier goulot d’étranglement de vos requêtes. L’index SQL est l’outil conçu exactement pour ce problème : il permet à la base de retrouver une ligne sans lire toute la table.

Ce guide passe en revue chaque type d’index, la manière de les créer, les moments où ils apportent (ou non) un gain, et les pièges qui font qu’un index reste inutilisé.

Pour illustrer, nous partirons d’une table utilisateurs volumineuse.

CREATE TABLE utilisateurs (
    id       INTEGER PRIMARY KEY,
    email    TEXT,
    nom      TEXT,
    ville    TEXT,
    actif    BOOLEAN,
    cree_le  DATE
);

À quoi sert un index

Un index est une structure de données annexe, maintenue à part de la table, qui associe les valeurs d’une ou plusieurs colonnes à l’emplacement physique des lignes correspondantes. L’analogie classique est l’index d’un livre : plutôt que de feuilleter les 800 pages pour trouver le mot « transaction », vous consultez l’index alphabétique en fin d’ouvrage, qui vous renvoie directement à la bonne page.

Sans index, cette requête oblige la base à examiner chaque ligne :

SELECT * FROM utilisateurs WHERE email = '[email protected]';

Avec un index sur email, la base localise l’entrée en quelques sauts logarithmiques au lieu de parcourir la table entière. Sur une grande table, le gain se mesure en ordres de grandeur.

CREATE INDEX : la syntaxe de base

La création d’un index tient en une instruction. On lui donne un nom, on précise la table et la ou les colonnes concernées.

CREATE INDEX idx_utilisateurs_email ON utilisateurs (email);

Une convention répandue consiste à préfixer le nom par idx_ puis le nom de la table et de la colonne, ce qui rend le schéma lisible. Pour supprimer un index devenu inutile :

DROP INDEX idx_utilisateurs_email;      -- PostgreSQL, SQLite
DROP INDEX idx_utilisateurs_email ON utilisateurs;  -- MySQL

L’index B-tree : le type par défaut

Quand vous créez un index sans préciser sa nature, vous obtenez un index B-tree (arbre équilibré). C’est le type par défaut sur MySQL, PostgreSQL et SQLite, et celui qui couvre la grande majorité des besoins.

Le B-tree maintient les valeurs triées, ce qui le rend efficace pour trois familles d’opérations :

-- Égalité
SELECT * FROM utilisateurs WHERE email = '[email protected]';

-- Intervalle
SELECT * FROM utilisateurs WHERE cree_le >= '2025-01-01';

-- Tri
SELECT * FROM utilisateurs ORDER BY nom;

Parce que les données sont ordonnées, un index B-tree accélère aussi un ORDER BY : la base lit l’index déjà trié plutôt que de trier les résultats à la volée. En revanche, il n’aide pas une recherche par motif commençant par un joker, comme LIKE '%dupont', car le début de la valeur est inconnu.

L’index unique : garantir l’unicité

Un index unique joue un double rôle : il accélère les recherches et interdit deux lignes avec la même valeur. C’est la façon standard d’imposer qu’un e-mail ne soit jamais dupliqué.

CREATE UNIQUE INDEX idx_utilisateurs_email_unique
ON utilisateurs (email);

Toute tentative d’insérer un e-mail déjà présent échoue avec une erreur de contrainte. Notez qu’une clé primaire crée automatiquement un index unique sous-jacent : vous n’avez jamais besoin d’indexer manuellement la colonne id déclarée PRIMARY KEY.

L’index composite : plusieurs colonnes

Un index composite (ou multicolonne) porte sur plusieurs colonnes à la fois. Il est précieux quand vos requêtes filtrent régulièrement sur une combinaison de champs.

CREATE INDEX idx_utilisateurs_ville_actif
ON utilisateurs (ville, actif);

L’ordre des colonnes est déterminant. Cet index sert les requêtes filtrant sur ville seule, ou sur ville et actif, mais pas sur actif seul. C’est la règle du préfixe gauche : un index composite (A, B, C) est utilisable pour A, A, B et A, B, C, mais pas pour B ni C isolés.

-- Utilise l'index (préfixe gauche respecté)
SELECT * FROM utilisateurs WHERE ville = 'Lyon' AND actif = true;

-- Utilise l'index (première colonne seule)
SELECT * FROM utilisateurs WHERE ville = 'Lyon';

-- N'utilise PAS l'index (saute la première colonne)
SELECT * FROM utilisateurs WHERE actif = true;

Placez donc en tête la colonne la plus souvent filtrée, ou la plus sélective (celle qui découpe la table en groupes les plus petits).

L’index partiel : indexer un sous-ensemble

Un index partiel ne couvre qu’une portion des lignes, définie par une condition WHERE. Il est idéal quand vos requêtes ne s’intéressent qu’à un sous-ensemble, par exemple les utilisateurs actifs.

-- PostgreSQL et SQLite
CREATE INDEX idx_utilisateurs_actifs
ON utilisateurs (ville)
WHERE actif = true;

L’index est plus petit (il n’indexe pas les comptes inactifs), donc plus rapide à parcourir et moins coûteux à maintenir. C’est une optimisation ciblée très efficace. À noter : MySQL ne prend pas en charge les index partiels de cette manière ; on y contourne parfois le besoin avec un index sur une colonne calculée.

L’index fonctionnel : indexer une expression

Quand vous filtrez sur le résultat d’une fonction plutôt que sur la colonne brute, un index classique reste inutilisé. Un index fonctionnel indexe directement l’expression.

-- Recherche insensible à la casse
CREATE INDEX idx_utilisateurs_email_lower
ON utilisateurs (LOWER(email));

-- La requête doit reprendre exactement la même expression
SELECT * FROM utilisateurs WHERE LOWER(email) = '[email protected]';

La condition indispensable est que la requête utilise exactement l’expression indexée. Un simple email = ... ne bénéficierait pas de cet index.

Quand faut-il indexer ?

Un index n’est pas gratuit, et tout indexer serait une erreur. Voici les colonnes qui méritent réellement un index :

Les colonnes utilisées dans les clauses WHERE fréquentes ;
Les colonnes de jointure (les clés étrangères notamment) ;
Les colonnes servant à trier (ORDER BY) ou grouper (GROUP BY) ;
Les colonnes soumises à une contrainte d’unicité.

À l’inverse, indexer une colonne peu sélective (un booléen à deux valeurs, un statut à trois états) apporte souvent peu : la base estime qu’un parcours complet reste plus rapide et ignore l’index. Les colonnes des clés étrangères, elles, gagnent presque toujours à être indexées ; si vous mettez en place des relations entre tables, pensez à indexer la colonne pointant vers la table parente, un sujet développé dans notre guide des clés étrangères.

Le coût en écriture

Le revers de la médaille : chaque INSERT, UPDATE ou DELETE doit mettre à jour tous les index de la table en plus des données. Plus une table possède d’index, plus ses écritures ralentissent et plus elle occupe d’espace disque.

Le compromis est donc un arbitrage entre lectures et écritures. Sur une table lue en permanence mais rarement modifiée (un catalogue produit), on peut multiplier les index. Sur une table d’écriture intensive (un journal d’événements), on reste économe. Un index jamais utilisé par aucune requête est pur gaspillage : il ralentit les écritures sans jamais accélérer une lecture.

Vérifier avec EXPLAIN

Comment savoir si une requête utilise réellement un index ? La commande EXPLAIN affiche le plan d’exécution choisi par la base.

EXPLAIN SELECT * FROM utilisateurs WHERE email = '[email protected]';

Sur PostgreSQL, on préfère EXPLAIN ANALYZE, qui exécute vraiment la requête et mesure le temps :

EXPLAIN ANALYZE
SELECT * FROM utilisateurs WHERE ville = 'Lyon';

Dans la sortie, cherchez le mode d’accès. Un Index Scan (ou Index Seek) confirme que l’index est employé. Un Seq Scan (sequential scan, parcours séquentiel) signale au contraire que la base lit toute la table — le signe qu’un index manque ou qu’il n’est pas exploitable pour cette requête.

Les pièges à connaître

La fonction sur la colonne annule l’index

Appliquer une fonction à une colonne indexée dans le WHERE empêche l’utilisation de l’index B-tree standard, car la base ne peut plus se repérer dans les valeurs triées.

-- N'utilise PAS l'index sur cree_le
SELECT * FROM utilisateurs WHERE YEAR(cree_le) = 2025;

-- Utilise l'index : on compare la colonne brute
SELECT * FROM utilisateurs
WHERE cree_le >= '2025-01-01' AND cree_le < '2026-01-01';

La bonne pratique est de réécrire la condition pour laisser la colonne intacte, ou de créer un index fonctionnel dédié.

Le joker en début de motif

Une recherche LIKE '%terme' ne peut pas s’appuyer sur un index B-tree, puisque le début de la chaîne est inconnu. Seul un motif ancré à gauche, comme LIKE 'terme%', exploite l’index. Pour de la recherche plein texte, tournez-vous vers un index dédié (full-text) plutôt que vers LIKE.

Trop d’index tue la performance

Empiler des index redondants (par exemple un index sur (ville) alors qu’il existe déjà (ville, actif)) alourdit les écritures sans rien apporter aux lectures. Auditez régulièrement les index réellement utilisés et supprimez les inutiles.

En résumé

Un index bien placé transforme une requête lente en réponse instantanée, mais chaque index a un coût en écriture et en espace. Indexez les colonnes filtrées, jointes et triées ; choisissez le bon type (B-tree par défaut, unique pour l’intégrité, composite en respectant le préfixe gauche, partiel pour cibler un sous-ensemble) ; et vérifiez toujours vos choix avec EXPLAIN. Méfiez-vous des fonctions qui neutralisent l’index et des jokers en début de motif. Avec cette discipline, vos bases restent rapides même à grande échelle.