Votre application fonctionnait parfaitement avec quelques centaines de lignes, puis, avec quelques millions, une simple recherche met plusieurs secondes à répondre. Dans de très nombreux cas, la solution tient en un mot : index. Bien utilisés, les index peuvent transformer une requête lente en requête quasi instantanée. Mal utilisés, ils alourdissent la base sans rien apporter.
Dans cet article, vous allez comprendre ce qu’est un index, comment il fonctionne, quand il est utile, ce qu’il coûte et comment choisir les colonnes à indexer dans une base de données relationnelle.
L’analogie de l’index d’un livre
Imaginez que vous cherchiez le mot « photosynthèse » dans un manuel de biologie de 600 pages. Sans index, vous devez feuilleter toutes les pages. Avec l’index en fin d’ouvrage, vous trouvez le mot dans une liste triée par ordre alphabétique, qui vous indique directement les numéros de pages concernées.
Un index de base de données suit exactement la même logique : c’est une structure séparée, triée selon une ou plusieurs colonnes, qui pointe vers l’emplacement des lignes correspondantes dans la table.
Que se passe-t-il sans index ?
Prenons une table clients et la requête suivante :
SELECT * FROM clients WHERE email = 'marie@exemple.fr';
Si aucune structure n’aide le moteur, il doit lire toutes les lignes de la table une par une pour vérifier la condition. C’est ce qu’on appelle un parcours complet de table (full table scan). Sur une petite table, ce n’est pas un problème ; sur des millions de lignes, cela devient coûteux, d’autant plus si la requête est exécutée très souvent.
Créer un index en SQL
La syntaxe de base est proche dans la plupart des systèmes (MySQL, PostgreSQL, SQL Server, SQLite…) :
CREATE INDEX idx_clients_email ON clients (email);
À partir de là, le moteur peut consulter l’index pour retrouver rapidement la ligne correspondant à l’adresse e-mail recherchée, au lieu de parcourir toute la table. Pour supprimer un index, on utilise DROP INDEX, avec une syntaxe qui varie légèrement selon le système.
Comment fonctionne un index : l’arbre B
La structure la plus utilisée par les bases relationnelles pour leurs index est l’arbre B (souvent une variante appelée B+ tree). Il s’agit d’un arbre équilibré dans lequel les valeurs sont maintenues triées :
- la racine et les nœuds intermédiaires servent de panneaux indicateurs (« les valeurs de A à M sont à gauche, de N à Z à droite ») ;
- les feuilles contiennent les valeurs indexées et une référence vers les lignes de la table.
Comme chaque nœud oriente vers une petite portion des données, le nombre d’étapes nécessaires pour trouver une valeur augmente très lentement avec la taille de la table (on parle de croissance logarithmique). C’est pour cela qu’un index reste efficace même sur des tables très volumineuses.
Autre avantage : les valeurs étant triées, un arbre B est aussi utile pour les recherches par intervalle (BETWEEN, >, <) et peut aider pour certains tris (ORDER BY).
Les principaux types d’index
| Type | Rôle | Exemple |
|---|---|---|
| Index de clé primaire | Créé automatiquement par la plupart des systèmes pour garantir l’unicité de l’identifiant | PRIMARY KEY (id) |
| Index unique | Interdit les doublons dans la colonne | CREATE UNIQUE INDEX ... (email) |
| Index simple | Accélère les recherches sur une colonne | CREATE INDEX ... (ville) |
| Index composite | Porte sur plusieurs colonnes, dans un ordre précis | CREATE INDEX ... (nom, prenom) |
Il existe aussi des types plus spécialisés selon les systèmes : index de hachage, index plein texte, index géographiques, etc. Pour débuter, l’arbre B couvre la grande majorité des besoins.
L’ordre compte dans un index composite
Un index sur (nom, prenom) est trié d’abord par nom, puis par prénom à l’intérieur de chaque nom, comme un annuaire téléphonique. Conséquence :
- il est très efficace pour
WHERE nom = 'Durand'; - il est efficace pour
WHERE nom = 'Durand' AND prenom = 'Léa'; - il est généralement peu utile pour
WHERE prenom = 'Léa'seul, car les prénoms ne sont pas triés globalement.
C’est ce qu’on appelle souvent la règle du préfixe le plus à gauche : placez en premier la colonne la plus utilisée dans vos filtres.
Le coût caché des index
Si les index sont si utiles, pourquoi ne pas indexer toutes les colonnes ? Parce qu’ils ont un prix :
- Espace disque : chaque index est une structure supplémentaire à stocker.
- Écritures plus lentes : à chaque
INSERT,UPDATEouDELETE, le moteur doit aussi mettre à jour tous les index concernés. - Maintenance : trop d’index rendent le schéma plus difficile à comprendre et à optimiser.
Il s’agit donc d’un compromis : on accélère les lectures au prix d’écritures un peu plus lentes. Pour une table très souvent lue et rarement modifiée, le compromis est généralement favorable.
Quelles colonnes indexer ?
- Les colonnes souvent utilisées dans les clauses
WHERE. - Les colonnes servant aux jointures, en particulier les clés étrangères.
- Les colonnes souvent utilisées pour trier ou regrouper (
ORDER BY,GROUP BY). - Les colonnes sélectives, c’est-à-dire avec beaucoup de valeurs différentes (un e-mail est très sélectif, un champ « oui/non » beaucoup moins).
Pièges courants qui empêchent l’utilisation d’un index
- Appliquer une fonction à la colonne :
WHERE UPPER(email) = '...'peut empêcher l’usage d’un index classique suremail. - Joker en début de motif :
LIKE '%dupont'ne peut pas exploiter l’ordre de l’index, contrairement àLIKE 'dupont%'. - Conversions de type implicites : comparer une colonne texte à un nombre peut forcer une conversion qui bloque l’index.
- Tables très petites : le moteur peut choisir de lire toute la table, ce qui est parfois plus rapide que de passer par l’index.
Vérifier avec EXPLAIN
La plupart des systèmes proposent une commande EXPLAIN qui affiche le plan d’exécution d’une requête : on y voit si le moteur fait un parcours complet ou utilise un index. C’est l’outil indispensable pour mesurer l’effet d’un index plutôt que de deviner.
EXPLAIN SELECT * FROM clients WHERE email = 'marie@exemple.fr';
Conclusion
Un index est une structure triée qui permet au moteur de trouver rapidement les lignes recherchées, comme l’index d’un livre. Il accélère fortement les lectures, mais consomme de l’espace et ralentit les écritures. La bonne démarche consiste à indexer les colonnes réellement utilisées dans les filtres, les jointures et les tris, à soigner l’ordre des index composites et à vérifier les résultats avec EXPLAIN.
Pour aller plus loin, Cursa propose des cours gratuits de bases de données et de SQL, avec certificat, pour vous entraîner à concevoir et optimiser vos propres bases à votre rythme.
