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

Une requête qui met plusieurs secondes à s’exécuter sur une grosse table est presque toujours un problème d’index. Les index sont le levier le plus efficace pour accélérer une base de données relationnelle : bien posés, ils font passer une recherche de plusieurs secondes à quelques millisecondes. Mal posés — ou absents — ils laissent le moteur parcourir des tables entières ligne par ligne. Ce guide explique ce qu’est un index, quels types existent, quand en créer, et à quel prix.

À quoi sert un index ?

Sans index, pour retrouver une ligne selon un critère, le SGBD réalise un balayage complet de la table (full table scan) : il lit chaque ligne et vérifie la condition. Sur un million de lignes, cela signifie un million de lectures.

Un index est une structure de données annexe, triée, qui pointe vers les lignes de la table. L’analogie classique est l’index d’un livre : plutôt que de feuilleter les 500 pages pour trouver un mot, vous consultez l’index alphabétique en fin d’ouvrage, qui vous renvoie directement à la bonne page. Le moteur fait de même : il consulte l’index trié, localise la valeur en quelques sauts, et accède directement aux lignes concernées.

Prenons une table de clients :

CREATE TABLE clients (
    id    INTEGER PRIMARY KEY,
    nom   TEXT,
    email TEXT,
    ville TEXT
);

La requête suivante, sur une grande table sans index sur email, oblige à tout balayer :

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

Avec un index sur email, la recherche devient quasi instantanée.

Créer un index : CREATE INDEX

La syntaxe de base est simple et commune à la plupart des SGBD :

CREATE INDEX idx_clients_email ON clients(email);

Le nom de l’index (idx_clients_email) est libre, mais une convention claire — préfixe idx_, nom de table, colonne — facilite la maintenance. Pour supprimer un index devenu inutile :

DROP INDEX idx_clients_email;         -- PostgreSQL, SQLite
-- ou, selon le SGBD :
-- DROP INDEX idx_clients_email ON clients;   -- MySQL

Notez que la syntaxe de suppression varie légèrement entre MySQL et les autres.

Les types d’index

L’index B-tree (par défaut)

La grande majorité des index sont des B-tree (arbres équilibrés). C’est le type par défaut quand vous écrivez CREATE INDEX sans précision. Le B-tree maintient les valeurs triées et permet des recherches efficaces sur l’égalité (=), mais aussi sur les plages (<, >, BETWEEN) et les tris (ORDER BY).

CREATE INDEX idx_clients_ville ON clients(ville);

-- Ces requêtes profitent de l'index :
SELECT * FROM clients WHERE ville = 'Lyon';
SELECT * FROM clients WHERE ville BETWEEN 'A' AND 'M' ORDER BY ville;

L’index unique

Un index unique garantit qu’aucune valeur ne se répète dans la colonne, tout en accélérant les recherches. C’est à la fois une contrainte d’intégrité et une optimisation.

CREATE UNIQUE INDEX idx_clients_email_unique ON clients(email);

Toute tentative d’insérer un email déjà présent échouera. Les clés primaires bénéficient automatiquement d’un index unique ; inutile d’en créer un manuellement dessus.

L’index composite (multi-colonnes)

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

CREATE INDEX idx_clients_ville_nom ON clients(ville, nom);

L’ordre des colonnes est déterminant. Cet index sert les requêtes filtrant sur ville seul, ou sur ville et nom, mais pas sur nom seul. C’est la règle du « préfixe gauche » : l’index est utilisable tant qu’on part de la première colonne.

-- Profite de l'index composite
SELECT * FROM clients WHERE ville = 'Nice' AND nom = 'Martin';
SELECT * FROM clients WHERE ville = 'Nice';

-- N'en profite PAS (nom n'est pas en tête)
SELECT * FROM clients WHERE nom = 'Martin';

Placez donc en tête la colonne la plus souvent filtrée seule.

Quand faut-il indexer ?

Un index se justifie sur les colonnes réellement sollicitées. Bons candidats :

les colonnes utilisées dans les clauses WHERE fréquentes ;
les colonnes servant de condition de jointure (les clés étrangères, notamment) ;
les colonnes utilisées dans ORDER BY ou GROUP BY ;
les colonnes à forte sélectivité, c’est-à-dire avec beaucoup de valeurs distinctes (un email, un identifiant) plutôt que peu (un booléen, un sexe).

Une colonne de faible sélectivité, comme un champ « actif » ne valant que 0 ou 1, ne gagne presque rien à être indexée : l’index renverrait encore la moitié de la table. Les index sur les clés étrangères sont particulièrement rentables, car ils accélèrent les jointures. Ce lien avec la modélisation est développé dans notre guide sur la clé primaire et la clé étrangère.

Le coût des index

Un index n’est jamais gratuit. Il faut en peser le prix.

Coût en écriture. Chaque INSERT, UPDATE ou DELETE doit mettre à jour non seulement la table, mais aussi tous les index qui la couvrent. Une table sur-indexée voit ses écritures ralentir. Sur une table à très forte charge d’insertion, multiplier les index peut devenir contre-productif.

Coût en espace. Un index occupe de l’espace disque, parfois considérable sur de grandes tables. Dix index sur une table peuvent peser plus lourd que les données elles-mêmes.

La règle est donc l’équilibre : indexez ce qui accélère vos requêtes réelles, pas « au cas où ». Un index qui n’est jamais utilisé par aucune requête ne fait que coûter, sans rien rapporter.

Vérifier l’usage d’un index : EXPLAIN

Comment savoir si le moteur utilise réellement un index ? Grâce à EXPLAIN, qui affiche le plan d’exécution d’une requête sans la lancer (ou EXPLAIN ANALYZE pour l’exécuter et mesurer).

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

Ce que vous cherchez dans la sortie :

une mention de type index scan ou l’usage d’une clé (key en MySQL) : bon signe, l’index est exploité ;
une mention de type seq scan (PostgreSQL) ou type: ALL (MySQL) : mauvais signe, c’est un balayage complet.

Si un index existe mais n’est pas utilisé, c’est souvent à cause d’un des pièges qui suivent.

Les pièges à connaître

Une fonction sur la colonne annule l’index

Appliquer une fonction à la colonne indexée dans le WHERE empêche généralement l’usage de l’index, car le moteur ne peut plus s’appuyer sur les valeurs triées telles quelles.

-- L'index sur nom N'est PAS utilisé
SELECT * FROM clients WHERE UPPER(nom) = 'DUPONT';

-- Réécriture qui préserve l'index (selon collation)
SELECT * FROM clients WHERE nom = 'Dupont';

Certaines bases permettent des index sur expression ou fonctionnels pour contourner cela, mais le réflexe est d’éviter de transformer la colonne indexée.

Le LIKE avec joker initial

Un LIKE 'Dup%' peut utiliser un index B-tree (le préfixe est connu). Un LIKE '%pont' ou '%pon%' ne le peut pas, car il n’y a pas de préfixe fixe pour naviguer dans l’arbre trié.

SELECT * FROM clients WHERE nom LIKE 'Dup%';   -- index possible
SELECT * FROM clients WHERE nom LIKE '%pont';  -- balayage complet

Pour la recherche « contient », on se tourne vers des index spécialisés (recherche plein texte, par exemple).

Trop d’index

Répétons-le, car c’est l’erreur la plus fréquente une fois qu’on a compris leur utilité : ajouter des index à tout va dégrade les écritures et gonfle la base. Chaque index doit servir une requête identifiée.

En résumé

Les index transforment un balayage complet en recherche ciblée : c’est le principal outil d’optimisation d’une base relationnelle. Retenez les types essentiels — B-tree par défaut, unique pour l’intégrité, composite en respectant l’ordre des colonnes. Indexez les colonnes des WHERE, des jointures et des tris à forte sélectivité, vérifiez leur usage avec EXPLAIN, et méfiez-vous des fonctions, des jokers initiaux et de la sur-indexation. Un index posé à bon escient est souvent la différence entre une application rapide et une application inutilisable.