data / sql

Index : types et stratégie

Leçon 101 exercice

Explication

Ce que vous allez apprendre

Comprendre pourquoi un index accélère une recherche sans changer les données
Créer un index simple et un index composite, et connaître l'importance de l'ordre des colonnes
Identifier le coût d'un index sur les écritures (INSERT/UPDATE/DELETE)
Créer un index UNIQUE combinant intégrité et performance
Savoir qu'un index créé n'est pas forcément un index utilisé, et pourquoi vérifier avec EXPLAIN

Dans quel contexte ?

Une application affiche la fiche d'un utilisateur en le recherchant par email dans une table utilisateurs de 2 millions de lignes. Sans index sur la colonne email, chaque connexion déclenche un parcours complet de la table, ce qui ralentit sévèrement toutes les connexions dès que le nombre d'utilisateurs grandit. C'est exactement le problème que résout cette leçon.

D'abord, imaginons la situation sans index

Cherche la définition du mot "table" dans un dictionnaire qui ne serait PAS trié par ordre alphabétique. Il faudrait lire chaque page une par une jusqu'à tomber dessus. C'est exactement ce que fait une base de données sans index : un parcours complet, ligne par ligne.

La solution : une structure séparée et triée

Un index est une structure à part, triée, qui permet de sauter directement à la bonne zone, comme l'ordre alphabétique d'un vrai dictionnaire. Créer un index sur une colonne souvent filtrée accélère donc énormément les recherches sur cette colonne.

Mais rien n'est gratuit : le revers de la médaille

Chaque fois qu'on insère, modifie ou supprime une ligne, la base doit aussi mettre à jour chaque index concerné. Indexer une colonne rarement filtrée coûte donc en écriture sans apporter grand bénéfice en lecture.

Piège fréquent

Ajouter un index sur chaque colonne "au cas où" ralentit chaque INSERT/UPDATE sans bénéfice réel si ces colonnes ne sont jamais filtrées dans un WHERE. Un excès d'index peut même ralentir une table davantage qu'il ne l'accélère.

Une subtilité une fois qu'on indexe plusieurs colonnes

Un index sur plusieurs colonnes, par exemple (categorie, prix), fonctionne comme un annuaire trié d'abord par ville, puis par nom à l'intérieur de chaque ville. Il est efficace pour chercher par ville seule, ou par ville puis nom.

Type d'index	Colonnes	Sert pour
Index simple	`categorie`	`WHERE categorie = 'x'`
Index composite	`(categorie, prix)`	`WHERE categorie = 'x'`, ou `categorie = 'x' AND prix > 10`
Index `UNIQUE`	`email`	intégrité + recherche rapide par email
Index composite mal utilisé	`(categorie, prix)`	inefficace pour `WHERE prix > 10` seul

Mais l'ordre des colonnes est déterminant

Ce même index composite est totalement inutile pour chercher uniquement par nom sans préciser la ville. L'ordre des colonnes dans la définition de l'index doit donc suivre l'ordre naturel de filtrage des requêtes réelles.

Le piège final : croire qu'un index créé est un index utilisé

Un index qui existe ne garantit pas qu'il est réellement utilisé : la base peut préférer un parcours complet si la table est petite, ou si le filtre ne réduit pas assez le nombre de lignes.

Bonne pratique

Ordonne toujours les colonnes d'un index composite de la colonne la plus souvent filtrée seule vers la moins souvent filtrée seule, et vérifie ensuite son utilisation réelle avec EXPLAIN ANALYZE plutôt que de faire confiance à ton intuition.

La seule façon de vérifier, et la suite

EXPLAIN ANALYZE, détaillé dans une leçon dédiée plus loin, est le seul moyen fiable de confirmer qu'un index sert réellement. Ne jamais deviner, toujours mesurer.

Commandes & code

Index

-- Sans index, une recherche parcourt TOUTE la table (full scan) -> O(n)
-- Un index permet une recherche quasi-directe -> O(log n) en général

-- Index simple sur une colonne fréquemment filtrée
CREATE INDEX idx_produits_categorie ON produits(categorie);

-- Index composite : l'ordre des colonnes compte !
-- Utile pour des requêtes filtrant sur categorie, puis sur categorie+prix
CREATE INDEX idx_produits_cat_prix ON produits(categorie, prix);
-- Cet index sert pour : WHERE categorie = 'x'
--                        WHERE categorie = 'x' AND prix > 10
-- Il ne sert PAS efficacement pour : WHERE prix > 10 (seul, sans categorie)

-- Index unique : combine intégrité et performance
CREATE UNIQUE INDEX idx_utilisateurs_email ON utilisateurs(email);

-- Index partiel : n'indexe qu'un sous-ensemble de lignes (PostgreSQL)
CREATE INDEX idx_commandes_en_attente ON commandes(cree_le)
WHERE statut = 'en_attente';   -- index plus petit, requêtes ciblées plus rapides

-- Index sur expression : indexe le résultat d'une fonction
CREATE INDEX idx_utilisateurs_email_lower ON utilisateurs(LOWER(email));
-- sert pour : WHERE LOWER(email) = 'jean@exemple.com'

-- Index de couverture (covering index) : contient toutes les colonnes de la requête
CREATE INDEX idx_commandes_covering ON commandes(client_id) INCLUDE (montant, statut);
-- PostgreSQL peut répondre sans aller lire la table (index-only scan)

-- Types d'index selon l'usage
-- B-Tree (par défaut) : égalité et comparaisons (=, <, >, BETWEEN, ORDER BY)
CREATE INDEX idx_prix ON produits USING BTREE (prix);

-- Hash : uniquement égalité stricte, plus compact que B-Tree pour ce cas
CREATE INDEX idx_produits_sku ON produits USING HASH (sku);

-- GIN : recherche plein texte, tableaux, JSONB (PostgreSQL)
CREATE INDEX idx_produits_tags ON produits USING GIN (tags);

-- Vérifier qu'un index est bien utilisé
EXPLAIN ANALYZE SELECT * FROM produits WHERE categorie = 'informatique';
-- chercher "Index Scan" (bon) vs "Seq Scan" (mauvais sur grosse table)

-- Lister les index existants (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'produits';

-- Coût des index : chaque INSERT/UPDATE/DELETE doit aussi mettre à jour les index
-- -> ne pas indexer une colonne rarement filtrée ou à trop faible cardinalité (ex: booléen)
DROP INDEX idx_produits_categorie;  -- retirer un index inutile

Résumé

Un index accélère la lecture (WHERE, JOIN, ORDER BY) mais ralentit l'écriture.
L'ordre des colonnes d'un index composite doit suivre l'ordre de filtrage le plus fréquent.
B-Tree pour les comparaisons générales, Hash pour l'égalité pure, GIN pour JSON/full-text/tableaux.
Un index partiel ou de couverture peut réduire drastiquement la taille et augmenter la vitesse.
Toujours valider l'usage réel d'un index avec EXPLAIN ANALYZE, ne jamais le supposer.

Exercices pratiques

1 disponible

Mission : sauver les connexions d'une application à 2 millions d'utilisateurs

Objectif : Créer les index adaptés à des requêtes réelles et diagnostiquer un index composite mal ordonné.

Contexte

L'application recherche chaque utilisateur par email dans une table utilisateurs de 2 millions de lignes à chaque connexion, sans aucun index sur cette colonne : les temps de connexion se dégradent depuis plusieurs semaines. Une autre équipe a créé un index composite (categorie, prix) sur produits, mais se plaint qu'il n'accélère pas leurs recherches WHERE prix > 10.

Résoudre l’exercice →