Les index SQL : à quoi servent-ils et comment fonctionnent-ils ?

Les index font partie des principaux outils pour améliorer les performances d'une base de données.

Dans cet article, vous découvrirez :

• Ce qu'est un index SQL
• Pourquoi il peut accélérer les requêtes
• Comment en créer un
• Un exemple de différence de performances
• Son fonctionnement interne

Qu'est-ce qu'un index ?

Un index SQL est une structure de données, souvent un arbre B, qui permet de retrouver des lignes sans parcourir toute la table.

Vous pouvez l'imaginer comme :

• Une liste de valeurs triées
• Des références reliant ces valeurs aux lignes de la table
• Une organisation permettant une recherche rapide, souvent en temps logarithmique

Sans index utilisable, la base peut devoir examiner chaque ligne. Avec un index adapté, elle peut accéder directement aux valeurs recherchées.

Pourquoi les index SQL existent-ils ?

Ils répondent à un besoin :

Accélérer les requêtes qui recherchent des valeurs précises.

Parcourir une table de dix lignes est rapide. Répéter cette opération sur dix millions de lignes peut devenir très coûteux.

Les index peuvent accélérer :

• Les recherches
• Les tris avec ORDER BY
• Les jointures
• Les contrôles d'unicité, avec un index unique

PostgreSQL, MySQL et SQLite s'appuient tous sur ces structures.

Exemple avec une jointure

Prenons les tables users et orders :

SELECT users.name, orders.product
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.email = 'bob@example.com';

Index à envisager

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Avant de créer un index, vérifiez ceux qui existent déjà : une contrainte UNIQUE sur email, par exemple, peut déjà fournir un index adapté.

Sans index : parcours des tables

Sans index sur users.email et orders.user_id, la base peut devoir :

• Parcourir users pour trouver l'adresse recherchée
• Parcourir orders pour retrouver les commandes des utilisateurs concernés

C'est un parcours séquentiel, aussi appelé full table scan.

Avec 100 000 utilisateurs et 1 000 000 de commandes, cela peut représenter beaucoup de lignes à examiner, même si le résultat final est très réduit. Le plan exact dépend toutefois de l'optimiseur et des données.

À titre d'illustration, l'article anglais donne des temps de l'ordre de 300 à 600 ms sans index, contre 1 à 3 ms avec index. Ces chiffres ne constituent pas un benchmark reproductible : le gain réel doit être mesuré sur votre base, par exemple avec EXPLAIN ANALYZE dans PostgreSQL.

Avec index : recherche ciblée

• idx_users_email aide à retrouver l'utilisateur correspondant à l'adresse.
• idx_orders_user_id aide à retrouver ses commandes.

La base peut ainsi réduire le nombre de lignes à lire. L'utilisation d'un index reste une décision de l'optimiseur : pour une petite table ou une requête retournant une grande partie des lignes, un parcours séquentiel peut être préférable.

Quels facteurs influencent les performances ?

Les index permettent donc à de nombreuses recherches et jointures de mieux supporter la croissance des données.

Comment fonctionnent-ils en interne ?

Les arbres B utilisés pour de nombreux index :

• Maintiennent les valeurs dans un ordre exploitable
• Permettent d'éliminer rapidement des portions de la recherche
• Conservent une faible profondeur, souvent de quelques niveaux même avec beaucoup de données

Retrouver une valeur demande ainsi peu d'étapes. Le coût de recherche augmente généralement de manière logarithmique plutôt que linéaire.

L'idée à retenir : chercher dans une structure triée est souvent bien plus rapide que parcourir un million de lignes non triées.

Quand utiliser un index ?

Envisagez un index lorsque vous utilisez fréquemment une colonne pour :

• Filtrer avec WHERE
• Trier avec ORDER BY
• Relier des tables par une jointure
• Vérifier l'unicité
• Chercher une valeur ou une plage de valeurs

Exemples de colonnes souvent concernées :

• Adresse e-mail
• Nom d'utilisateur
• Date de création (created_at)
• Clés étrangères (user_id, post_id)
• Identifiants d'URL (slugs)

Quand éviter un index ?

Les index ont un coût :

• Ils occupent de l'espace disque.
• Ils peuvent ralentir les écritures, car les opérations INSERT, UPDATE et DELETE doivent aussi les mettre à jour.

Soyez prudent pour les colonnes rarement interrogées, les textes très volumineux ou les colonnes contenant très peu de valeurs distinctes, comme un booléen. Ces cas ne constituent pas des interdictions absolues : un index partiel ou composé peut parfois être pertinent.

Choisissez les index à partir des requêtes réelles et mesurez leur effet.