Les Index SQL : Accélérer vos Requêtes
CREATE INDEX, types d’index, EXPLAIN, quand indexer, quand ne pas indexer
C’est quoi un index ?
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).
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.
Créer et supprimer un index
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.
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 |
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).
Index composites (multi-colonnes)
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.
— 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;
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.
EXPLAIN — analyser les requêtes
EXPLAIN SELECT * FROM clients WHERE email = ‘alice@email.com’;
— Résultat (colonnes clés) :
— type | possible_keys | key | rows
— ref | idx_clients_email | idx_clients_email | 1
— 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.
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.
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 |
Questions fréquentes
🏗️ 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