Index de base de données : c'est quoi et quand l'utiliser

Un index de base de données est une structure de données supplémentaire qui maintient les lignes d'une table triées selon une colonne ou un groupe de colonnes — un peu comme l'index à la fin d'un livre, il vous permet d'aller directement à la valeur recherchée au lieu de feuilleter chaque page. Quand votre application grandit, la réponse à « pourquoi cette requête est-elle devenue lente ? » est très souvent un index manquant ou mal conçu. Dans cet article, j'explique comment les index accélèrent les requêtes, ce qu'ils coûtent et quand il faut vraiment les utiliser, avec des exemples concrets.

Sans index : le balayage complet de table

Lorsque vous effectuez une recherche sur une colonne sans index, la base de données est obligée de faire un balayage complet de table (full table scan) : elle lit chaque ligne du début à la fin et conserve celles qui correspondent. Sur une table de 1 000 lignes, vous ne le remarquerez jamais, mais sur 5 millions de lignes, chaque requête signifie lire des millions de lignes sur le disque.

SELECT * FROM users WHERE email = 'aslain@example.com';

S'il n'y a pas d'index sur email, le moteur parcourt toute la table juste pour trouver une seule ligne. La complexité est d'environ O(n) : le temps croît linéairement avec le nombre de lignes. Avec un index, la recherche tombe à O(log n) — une différence énorme sur des millions de lignes.

Comment fonctionne réellement un index : le B-tree

L'index classique des bases de données relationnelles est un B-tree (plus précisément un B+tree). Les valeurs sont placées dans les branches en ordre trié ; chaque recherche descend de la racine vers les feuilles, et à chaque étape elle élimine plus de la moitié des candidats restants. C'est pourquoi vous atteignez votre valeur cible en seulement quelques étapes, même parmi des millions d'enregistrements.

• Les recherches d'égalité (=) et les recherches par plage (<, >, BETWEEN) sont rapides sur un B-tree.
• Le tri (ORDER BY) et le regroupement peuvent être presque gratuits car l'index est déjà trié.
• Les recherches par préfixe comme LIKE 'aslain%' profitent de l'index, mais LIKE '%aslain' (joker en tête) ne le peut pas.

Pour la recherche dans du texte (full-text) ou pour les requêtes géographiques, on utilise d'autres types d'index qu'un simple B-tree — par exemple les index FULLTEXT pour le texte.

Créer un index et mesurer son effet

Créer un index sur une seule colonne est simple :

CREATE INDEX idx_users_email ON users (email);

Ne devinez pas s'il fonctionne réellement — mesurez. Sous MySQL et PostgreSQL, la commande EXPLAIN affiche le plan de la requête :

EXPLAIN SELECT * FROM users WHERE email = 'aslain@example.com';

Dans la sortie MySQL, un type égal à ALL signifie un balayage complet (mauvais) ; ref ou const signifie qu'un index est utilisé (bon). La valeur rows estime le nombre de lignes que le moteur s'attend à parcourir ; avec le bon index en place, ce nombre chute considérablement.

Index composites et la règle du « préfixe le plus à gauche »

Quand vous interrogez plusieurs colonnes ensemble, utilisez un index composite. L'ordre des colonnes est crucial, car le B-tree trie les valeurs dans l'ordre que vous donnez :

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status);

Cet index accélère WHERE user_id = 5 et WHERE user_id = 5 AND status = 'paid'. Mais il n'accélère pas WHERE status = 'paid' seul — c'est la règle du préfixe le plus à gauche (leftmost prefix). Pensez à un annuaire trié par nom puis prénom : ne connaître que le prénom ne permet pas une recherche rapide.

Astuce : placez à gauche la colonne la plus sélective interrogée par égalité, et placez les conditions de plage à droite.

Les coûts : les index ne sont pas gratuits

Un index n'est pas un bouton magique qui règle tous les problèmes ; il a des coûts réels :

• Les écritures ralentissent : chaque INSERT, UPDATE et DELETE doit aussi mettre à jour les index concernés. Si une table a 10 index, chaque insertion signifie 10 structures supplémentaires à maintenir.
• Ils consomment disque et mémoire : les index occupent un espace séparé ; de nombreux index inutiles gonflent la base et polluent le cache.
• Ils n'aident pas en faible sélectivité : sur une colonne à deux valeurs seulement (par ex. is_active), un index n'apporte généralement aucun bénéfice ; le moteur préfère de toute façon un balayage complet.

La règle est donc simple : si les lectures sont fréquentes et la condition sélective, indexez ; n'ajoutez pas aveuglément un index à chaque colonne.

Recommandations pratiques

• Les clés primaires (PRIMARY KEY) et les contraintes d'unicité sont déjà indexées ; ne les réindexez pas.
• Indexez les colonnes de clés étrangères souvent utilisées dans les conditions JOIN et WHERE.
• Considérez comme candidats à l'index les colonnes que vous triez constamment avec ORDER BY.
• Activez le slow query log pour repérer les requêtes lentes, puis vérifiez avec EXPLAIN.
• Identifiez et supprimez les index inutilisés ; la maintenance fait aussi partie de l'optimisation.

Questions fréquentes

Est-ce une bonne idée d'ajouter un index à chaque colonne ?

Non. Les index inutiles ralentissent les écritures, consomment de l'espace disque et peuvent désorienter le planificateur de requêtes. N'indexez que les colonnes réellement interrogées et à forte sélectivité, et confirmez votre décision avec EXPLAIN.

Pourquoi l'index n'a-t-il pas accéléré ma requête ?

Causes fréquentes : vous avez enveloppé la colonne dans une fonction (WHERE YEAR(created_at) = 2026 désactive l'index), vous avez enfreint la règle du préfixe le plus à gauche sur un index composite, ou la sélectivité de la colonne est trop faible. La sortie d'EXPLAIN révèle généralement la raison.

Une clé primaire et un index, est-ce la même chose ?

Une clé primaire est un index spécial : elle est à la fois unique et non nulle (pas de NULL), et dans la plupart des moteurs elle définit l'ordre physique de la table (l'index clustered). Donc toute clé primaire est un index, mais tout index n'est pas une clé primaire.

Votre base de données ralentit et vous ne trouvez pas pourquoi ? Je peux analyser vos requêtes, concevoir la bonne stratégie d'indexation et réécrire les requêtes peu performantes. Contactez-moi pour de l'aide.