Les Index SQL : Accélérer vos Requêtes

CREATE INDEX, types d’index, EXPLAIN, quand indexer, quand ne pas indexer

Sections

20+

Exemples

Perf

Optimisation

📚 Navigation

🏠 Hub Programmation
🗄️ Cours SQL
🏗️ CREATE TABLE
🔗 Jointures SQL

📋 Sommaire

1. C’est quoi un index ?
6. EXPLAIN — analyser les requêtes
2. Créer et supprimer un index
7. Quand NE PAS indexer
3. Types d’index
8. Erreurs fréquentes
4. Index composites
9. Questions fréquentes
5. Quand indexer

SECTION 01

C’est quoi un index ?

📖 L’index d’un livre, pour ta base de données

Un index SQL fonctionne comme l’index d’un livre. Au lieu de lire toutes les pages (scan complet de la table), tu regardes l’index pour trouver directement la bonne page (la bonne ligne).

— SANS index : la base parcourt TOUTES les lignes (full table scan)
SELECT * FROM clients WHERE email = ‘alice@email.com’;
— 1 000 000 lignes parcourues → lent

— AVEC index sur email : la base va directement à la bonne ligne
— 3-4 lectures dans l’arbre B-Tree → rapide

Un index accélère les lectures (SELECT, WHERE, JOIN, ORDER BY) mais ralentit les écritures (INSERT, UPDATE, DELETE) car l’index doit être mis à jour à chaque modification. C’est un compromis lecture/écriture.

SECTION 02

Créer et supprimer un index

— Créer un index simple
CREATE INDEX idx_clients_email ON clients(email);

— Créer un index unique (interdit les doublons)
CREATE UNIQUE INDEX idx_clients_email_unique ON clients(email);

— Index composite (plusieurs colonnes)
CREATE INDEX idx_ventes_date_vendeur ON ventes(date, vendeur);

— Voir les index d’une table
SHOW INDEX FROM clients; — MySQL
— \d clients — PostgreSQL

— Supprimer un index
DROP INDEX idx_clients_email ON clients; — MySQL
DROP INDEX idx_clients_email; — PostgreSQL

Convention de nommage : idx_table_colonne (ex : idx_clients_email). Pour les index uniques : uq_table_colonne. Un bon nommage facilite la maintenance.

SECTION 03

Types d’index

Type	Structure	Usage	SGBD
B-Tree	Arbre équilibré	Par défaut — =, <, >, BETWEEN, ORDER BY, LIKE ‘abc%’	Tous
Hash	Table de hachage	Égalité stricte (=) uniquement, très rapide	PostgreSQL, MySQL (Memory)
GIN	Generalized Inverted	Recherche full-text, arrays, JSON	PostgreSQL
GiST	Generalized Search Tree	Géométrie, géolocalisation, ranges	PostgreSQL
FULLTEXT	Index de mots	Recherche de texte libre (MATCH AGAINST)	MySQL, PostgreSQL
SPATIAL	R-Tree	Données géographiques	MySQL, PostgreSQL

— B-Tree (par défaut, pas besoin de le spécifier)
CREATE INDEX idx_nom ON clients(nom);

— Hash (PostgreSQL)
CREATE INDEX idx_email_hash ON clients USING hash(email);

— FULLTEXT (MySQL)
CREATE FULLTEXT INDEX idx_bio_ft ON clients(bio);
SELECT * FROM clients WHERE MATCH(bio) AGAINST(‘développeur javascript’);

— GIN pour JSON (PostgreSQL)
CREATE INDEX idx_metadata ON produits USING gin(metadata);

Dans 90% des cas, le B-Tree par défaut suffit. N’utilise les index spécialisés que si tu as un besoin spécifique (recherche full-text, données géo, requêtes JSON).

SECTION 04

Index composites (multi-colonnes)

📚 L’ordre des colonnes est crucial

— Index composite sur (categorie, date)
CREATE INDEX idx_ventes_cat_date ON ventes(categorie, date);

— ✅ Cet index est utilisé pour :
WHERE categorie = ‘Tech’; — colonne 1
WHERE categorie = ‘Tech’ AND date > ‘2025-01-01’; — colonnes 1+2

— ❌ Cet index n’est PAS utilisé pour :
WHERE date > ‘2025-01-01’; — colonne 2 seule

Règle du préfixe gauche : un index composite (A, B, C) est utilisable pour les requêtes sur A, sur A+B, ou sur A+B+C. Mais pas pour B seul ni C seul. Place la colonne la plus filtrée en premier.

🏗️ Covering index

— Si l’index contient TOUTES les colonnes du SELECT,
— la base n’a même pas besoin de lire la table → « index-only scan »

CREATE INDEX idx_ventes_cover ON ventes(categorie, montant);

— Cette requête est entièrement résolue par l’index
SELECT categorie, SUM(montant)
FROM ventes
GROUP BY categorie;

SECTION 05

Quand indexer

Situation	Index recommandé
Colonnes dans le WHERE	✅ Oui — surtout si la table a > 1 000 lignes
Colonnes dans le JOIN ON	✅ Oui — les FK devraient toujours être indexées
Colonnes dans le ORDER BY	✅ Oui — évite un tri en mémoire
Colonnes dans le GROUP BY	✅ Oui — accélère le regroupement
PRIMARY KEY	Automatique — index créé par le SGBD
UNIQUE	Automatique — index unique créé par le SGBD
FOREIGN KEY	⚠️ Pas automatique en MySQL — à créer manuellement

⚠️ En MySQL, les FOREIGN KEY ne créent pas d’index automatiquement sur la colonne référençante (sauf si un index existe déjà). Crée toujours un index sur les colonnes FK. PostgreSQL et SQL Server les créent automatiquement dans certains cas.

SECTION 06

EXPLAIN — analyser les requêtes

🔍 Vérifier si ton index est utilisé

— MySQL
EXPLAIN SELECT * FROM clients WHERE email = ‘alice@email.com’;

— PostgreSQL
EXPLAIN ANALYZE SELECT * FROM clients WHERE email = ‘alice@email.com’;
— Index Scan using idx_clients_email on clients
— Execution Time: 0.042 ms

Type (MySQL EXPLAIN)	Signification	Performance
const	Clé primaire ou unique, 1 ligne	🟢 Excellent
eq_ref	JOIN sur index unique	🟢 Excellent
ref	Index non unique	🟢 Bon
range	Plage de valeurs (BETWEEN, <, >)	🟡 Correct
index	Scan complet de l’index	🟠 Moyen
ALL	Full table scan (pas d’index)	🔴 Mauvais

Si tu vois type: ALL sur une grosse table, c’est un signal d’alarme. La requête parcourt toutes les lignes. Ajoute un index sur la ou les colonnes du WHERE/JOIN.

SECTION 07

Quand NE PAS indexer

Situation	Pourquoi pas d’index
Petites tables (< 1 000 lignes)	Full scan est aussi rapide qu’un index
Colonnes avec très peu de valeurs distinctes	Ex : booléen (TRUE/FALSE) — index peu sélectif
Tables avec beaucoup d’INSERT/UPDATE	Chaque index ralentit les écritures
Colonnes rarement filtrées	Index inutile = espace disque gaspillé
Expressions dans WHERE	WHERE YEAR(date) = 2025 — l’index sur date n’est pas utilisé

Un index sur une colonne booléenne (TRUE/FALSE) est quasiment inutile — il ne filtre que 50% des lignes. L’optimiseur préfère un full scan. Exception : si la distribution est très déséquilibrée (ex : 99% FALSE, 1% TRUE), un index peut aider pour chercher les TRUE.

SECTION 08

Erreurs fréquentes

Erreur	Problème	Solution
Fonction sur colonne indexée	WHERE UPPER(nom) = ‘ALICE’ ignore l’index	Index fonctionnel ou stocker en minuscule
LIKE ‘%alice%’	Le % au début empêche l’index B-Tree	Utiliser un index FULLTEXT
Trop d’index sur une table	INSERT/UPDATE très lents	Max 5-8 index par table en règle générale
Index composite dans le mauvais ordre	Index pas utilisé (règle du préfixe gauche)	Colonne la plus filtrée en premier
Index sur FK oublié	JOIN très lent	Toujours indexer les colonnes FOREIGN KEY
Index jamais vérifié	Index inutilisé qui gaspille de l’espace	Vérifier avec EXPLAIN, supprimer les inutiles

SECTION 09

Questions fréquentes

Combien d’index maximum par table ?

Il n’y a pas de limite stricte (MySQL supporte jusqu’à 64), mais en pratique, 5 à 8 index par table est un bon équilibre. Chaque index supplémentaire ralentit les INSERT/UPDATE et consomme de l’espace disque. Concentre-toi sur les requêtes les plus fréquentes.

PRIMARY KEY crée-t-il un index ?

Oui, automatiquement. La PRIMARY KEY est un index unique clustered (en MySQL InnoDB, la table est physiquement ordonnée par la PK). Les contraintes UNIQUE créent aussi un index automatiquement. Les FOREIGN KEY, en revanche, ne créent pas toujours d’index.

Un index accélère-t-il COUNT(*) ?

Pas directement. COUNT(*) sans WHERE doit compter toutes les lignes. L’optimiseur peut utiliser le plus petit index disponible pour le scan, mais il doit quand même parcourir toutes les entrées. Un COUNT(*) WHERE colonne_indexée = valeur est en revanche très rapide grâce à l’index.

Index fonctionnel — c’est quoi ?

Un index sur une expression, pas sur une colonne brute : CREATE INDEX idx_lower_email ON clients(LOWER(email)). Permet d’utiliser l’index pour WHERE LOWER(email) = ‘alice@email.com’. Supporté par PostgreSQL nativement et MySQL 8.0.13+.

📚 Fiches SQL liées

🏗️ CREATE TABLE
🔗 Jointures SQL
📑 ORDER BY & LIMIT
✏️ INSERT, UPDATE, DELETE
📊 GROUP BY & HAVING
🗄️ Cours SQL complet
🏠 Hub Programmation

Les index SQL — Accélérer vos requêtes

Référence : sql.sh Index