Comment fonctionnent les index B-tree, composites et couvrants, et qu'est-ce que la règle du préfixe le plus à gauche (leftmost-prefix) ?

Question

IT Interview Practice · Accepted Answer

Les index InnoDB sont des **B+trees** : des arbres équilibrés où chaque feuille se situe à la même profondeur et où les feuilles sont chaînées pour des parcours par plage rapides. Une recherche est en O(log n) — une poignée de lectures de pages même sur des tables énormes.

## Index composites et préfixe le plus à gauche

Un index composite `(a, b, c)` trie les lignes par `a`, puis `b`, puis `c`. Vous ne pouvez l'utiliser que pour un **préfixe le plus à gauche** des colonnes : `a`, `(a, b)` ou `(a, b, c)` — mais **pas** `b` seul ni `(b, c)`, car ces colonnes ne sont pas triées tant que `a` n'est pas fixé.

```sql
CREATE INDEX idx_abc ON events (user_id, status, created_at);

-- UTILISE l'index (préfixe le plus à gauche, plage sur la dernière colonne)
SELECT * FROM events
WHERE user_id = 7 AND status = 'open' AND created_at > '2026-01-01';

-- Ne l'utilise PAS : saute la colonne la plus à gauche
SELECT * FROM events WHERE status = 'open';
```

Ordonnez les colonnes de la plus sélective / égalité d'abord, la colonne de plage en dernier : dès que vous rencontrez une plage (`>`, `BETWEEN`), les colonnes suivantes ne peuvent plus servir à la recherche.

## Index couvrant

Si un index contient **toutes les colonnes que la requête lit**, InnoDB répond à partir du seul index — sans retour au clustered index. EXPLAIN affiche `Using index`.

```sql
CREATE INDEX idx_cover ON events (user_id, status, id);

EXPLAIN SELECT id FROM events WHERE user_id = 7 AND status = 'open';
-- Extra: Using index      <- couvrant, pas de recherche dans le clustered index
```

## Pourquoi c'est important

Les index composites et couvrants sont ce qui permet de transformer une requête multi-conditions lente en une simple recherche d'index. Se tromper dans l'ordre des colonnes — ou s'attendre à ce qu'une requête sur la deuxième colonne utilise l'index — est la raison la plus courante pour laquelle un index qui "existe" n'est quand même pas utilisé.

Index composites et préfixe le plus à gauche

Index couvrant

Pourquoi c'est important

Questions connexes