High Performance MySQL [Ch. 5 : Indexer pour la performance]

Ce qu'est vraiment un index

Pensez à l'index à la fin d'un livre : on cherche le terme, on obtient un numéro de page, on va à la page. Un index fait la même chose pour les lignes, sauf que les « pages » sont des pages InnoDB de 16 Ko organisées en B+Tree. Les pages racine et internes ne contiennent que des clés séparatrices et des pointeurs vers les pages enfants ; toutes les valeurs vivent dans les pages feuilles, triées, et chaque feuille est chaînée à ses voisines dans les deux sens. Toutes les feuilles sont à la même profondeur, donc chaque recherche coûte les mêmes quelques lectures de page : trois ou quatre, même avec des millions de lignes.

Comme les clés sont triées, le moteur peut descendre jusqu'à une valeur puis avancer latéralement pour parcourir une plage ; ce parcours explique pourquoi les plages sont peu coûteuses et les prédicats arbitraires non. Les liens vont dans les deux sens, donc le même parcours fonctionne à rebours pour ORDER BY … DESC.

Le contenu d'une entrée de feuille dépend de l'index. Dans la clé primaire, c'est la ligne entière. Dans un index secondaire, ce sont les colonnes indexées plus la clé primaire de la ligne, jamais une adresse sur disque. Gardez-le en tête : cela explique presque tout ce qui suit.

Types d'index, et pourquoi le B-Tree l'emporte

Quand on dit « index » dans MySQL, on parle presque toujours d'un B-Tree. Les autres existent pour des tâches plus étroites.

Un index hash stocke un hash de la clé complète et un pointeur vers la ligne. Il est donc compact et rapide pour l'égalité, mais les hashes sont dans un ordre aléatoire : il ne sert ni pour une plage ni pour un ORDER BY, et un index sur (a, b) hache les deux colonnes ensemble, donc une requête sur a seule ne peut pas l'utiliser. Les colonnes à faible cardinalité sont son pire cas : beaucoup de lignes partagent un hash, et le moteur parcourt une chaîne de lignes à chaque recherche.

On ne peut déclarer des index hash que sur des tables MEMORY, mais InnoDB peut en construire un de lui-même : l'index hash adaptatif repère les pages B-Tree consultées très souvent et ajoute un hash en mémoire par-dessus. Il est activé par défaut dans MySQL 8.0 et désactivé par défaut depuis la 8.4, et il ne demande aucune modification du schéma.

Les trois choses qu'un index peut faire

La notation trois étoiles du livre : un index gagne une étoile pour chaque travail qu'il accomplit pour une requête donnée. L'essentiel du gain vient de la première.

La première étoile change aussi la façon dont le disque est lu : les lignes voisines dans l'index sont lues page après page au lieu de sauter partout dans la table, et un index qui fournit l'ordre évite la table temporaire et le tri dont GROUP BY et ORDER BY auraient sinon besoin.

Les index ne sont pas gratuits. Chaque INSERT, UPDATE et DELETE met à jour tous les index de la table, chaque index occupe de la mémoire dans le buffer pool, et l'optimiseur les évalue tous en planifiant une requête. Sur une toute petite table qui tient en quelques pages, un parcours est déjà peu coûteux et un index apporte peu. Plus la table est grande, plus un index bien choisi rentabilise son entretien.

La règle du préfixe à gauche

Un index multicolonne trie par la première colonne, puis par la deuxième à l'intérieur, et ainsi de suite. Les recherches doivent commencer à gauche et ne peuvent pas sauter une colonne. Avec idx_orders_customer_status_placed (customer_id, status, placed_at) :

-- utilise l'index
WHERE customer_id = 42
WHERE customer_id = 42 AND status = 'paid'
WHERE customer_id = 42 AND status = 'paid' AND placed_at >= '2026-01-01'

-- ne peut pas l'utiliser
WHERE status = 'paid'                      -- saute la première colonne
WHERE customer_id = 42 AND placed_at > ?   -- status sauté, placed_at inutilisable
WHERE DATE(placed_at) = '2026-01-01'       -- colonne enveloppée dans une fonction

Autrement dit, le même index peut répondre à cinq formes de question, et toutes commencent par la gauche :

La limite se cache dans la dernière ligne : rien à droite de la première plage ne peut réduire la recherche. Dans customer_id = 42 AND status LIKE 'p%' AND placed_at = '2026-01-04', le LIKE est une plage : MySQL parcourt tous les status « p… » du client 42 et ne filtre placed_at que sur les entrées déjà lues.

MySQL 8.0 assouplit la règle « on ne peut pas sauter la première colonne » dans un cas. Quand la première colonne a très peu de valeurs distinctes et que toutes les colonnes dont la requête a besoin sont dans l'index, l'optimiseur peut faire un skip scan : une plage par valeur de la première colonne. EXPLAIN affiche Using index for skip scan. Il lit plus qu'un préfixe fixé, et disparaît dès que vous sélectionnez une colonne hors de l'index.

Laisser la colonne seule

MySQL ne peut chercher dans un index que sur la colonne elle-même. Enveloppez la colonne dans un calcul, une fonction ou une conversion, et l'ordre ne sert plus : le moteur doit calculer l'expression pour chaque ligne et comparer. Déplacez le travail du côté de la constante.

-- la colonne est dans une expression : parcours complet
WHERE id + 1 = 5
WHERE YEAR(reported_at) = 2020
WHERE DATEDIFF(CURRENT_DATE, reported_at) <= 10

-- mêmes lignes, la colonne seule : plage sur l'index
WHERE id = 4
WHERE reported_at >= '2020-01-01' AND reported_at < '2021-01-01'
WHERE reported_at >= CURRENT_DATE - INTERVAL 10 DAY

Certaines variantes de l'erreur sont plus difficiles à voir :

• Conversions implicites. Comparer une colonne texte à un nombre (WHERE phone = 5551234 sur un VARCHAR) oblige MySQL à convertir en nombre la valeur de chaque ligne, et l'index ne sert plus. Mettez le littéral entre guillemets. L'inverse, une colonne numérique comparée à une chaîne, ne pose pas de problème : seule la constante est convertie.
• Jokers en tête. LIKE 'abc%' est une plage sur l'index ; LIKE '%abc' peut commencer n'importe où, c'est donc un parcours complet.
• Collations ou jeux de caractères différents dans une jointure. Joindre deux colonnes texte de collations différentes force une conversion d'un côté, et ce côté perd son index.

Si vous devez vraiment chercher sur une expression, MySQL 8.0.13 et suivants peuvent l'indexer directement, avec une partie de clé fonctionnelle comme INDEX ((YEAR(reported_at))). La requête doit alors utiliser exactement cette expression.

Index multicolonnes et ordre des colonnes

Trois erreurs expliquent la plupart des mauvais index multicolonnes :

• Un index par colonne. Des index séparés sur store_id et status ne forment pas un index composite. Le mieux que MySQL puisse faire est un index merge : lire les deux, puis intersecter ou unir les clés primaires, ce qu'EXPLAIN affiche comme index_merge. Cela fonctionne, mais coûte plus qu'un index sur (store_id, status), et c'est souvent le signe que cet index manque.
• Indexer toutes les colonnes du WHERE. Un seul index large dans un ordre quelconque n'est pas meilleur : seul son préfixe à gauche aide, et chaque colonne en plus se paie à l'écriture.
• Le mauvais ordre. Les bonnes colonnes dans le mauvais ordre donnent un index long et presque inutile.

La règle connue, la colonne la plus sélective d'abord, ne vaut que si tous les prédicats sont des égalités. La règle la plus solide est colonnes d'égalité d'abord, colonne de plage en dernier. Le premier prédicat de plage épuise l'ordre utilisable de l'index ; les colonnes à sa droite peuvent filtrer des lignes déjà lues, mais ne réduisent pas le parcours.

-- requête : WHERE store_id = 7 AND status = 'late' AND rental_date >= '2025-06-01'
KEY (store_id, status, rental_date)   -- une seule plage étroite
KEY (rental_date, store_id, status)   -- parcourt toutes les locations depuis juin

La sélectivité se mesure, elle ne se devine pas : COUNT(DISTINCT col) / COUNT(*). Vérifiez le pire cas autant que la moyenne : une colonne sélective en apparence peut avoir une valeur qui couvre un tiers de la table. Et laissez décider les requêtes que vous exécutez réellement : un index qui sert aussi l'ORDER BY ou le GROUP BY peut valoir plus qu'un index un peu plus sélectif.

Index clusterisés : la clé primaire est la table

InnoDB stocke les lignes dans le B-Tree de la clé primaire. Deux conséquences dominent la conception du schéma.

• Les index secondaires stockent la clé primaire, pas un pointeur de ligne. Trouver une ligne par leur intermédiaire coûte deux descentes : l'arbre secondaire, puis l'arbre clusterisé. Ce second saut explique pourquoi une clé primaire large gonfle tous les index de la table.
• L'ordre d'insertion est l'ordre physique. Les clés monotones s'ajoutent à la dernière page. Les clés aléatoires s'insèrent au milieu, scindent les pages, fragmentent le tablespace et salissent bien plus de buffer pool par écriture.

Ce que le clustering apporte : les lignes aux clés voisines partagent des pages, donc une plage de clé primaire lit peu de pages, et une recherche par clé primaire trouve la ligne au bout d'une seule descente. Ce qu'il coûte : changer la valeur d'une clé primaire déplace physiquement la ligne, et la clé primaire est copiée dans chaque index secondaire.

C'est pourquoi une clé de substitution AUTO_INCREMENT est le choix habituel. Une clé naturelle comme un e-mail, ou un UUID aléatoire, est plus large et arrive dans un ordre aléatoire. Si vous avez besoin d'UUID, stockez-les en BINARY(16) avec UUID_TO_BIN(uuid, 1), qui place la partie temporelle en tête pour que les nouvelles clés arrivent près de la fin. Les clés séquentielles ont leur propre coût sous de fortes insertions concurrentes : tous les écrivains visent la même dernière page. La valeur par défaut de MySQL 8.0, innodb_autoinc_lock_mode = 2, empêche le compteur lui-même de devenir un goulot d'étranglement. Après beaucoup d'insertions ou de suppressions aléatoires, OPTIMIZE TABLE reconstruit la table et ses index de façon compacte.

Index couvrants

Si un index contient toutes les colonnes qu'une requête touche (liste de sélection, filtres, jointures, tri), le moteur répond depuis l'index sans jamais lire de ligne. EXPLAIN affiche Using index. Les index sont bien plus petits que leurs tables : c'est le plus gros gain possible sans changer la question posée.

-- couvert par (customer_id, placed_at) : aucune lecture de ligne
SELECT customer_id, placed_at FROM orders WHERE customer_id = 42;

-- une descente dans l'arbre clusterisé par ligne correspondante
SELECT customer_id, placed_at, notes FROM orders WHERE customer_id = 42;

Quand la lecture de ligne est inévitable mais que le filtre est faible, une jointure différée (une sous-requête sur l'index seul pour trouver les clés primaires, puis une jointure pour les colonnes larges) est rentable sur les grands offsets.

Trier avec un index

Un index satisfait un ORDER BY uniquement si les colonnes de tri forment un préfixe à gauche et que les directions concordent, avec une exception : les colonnes fixées à des constantes par des prédicats d'égalité peuvent être sautées. Mélangez croissant et décroissant, ou triez sur des colonnes de deux tables, et MySQL se rabat sur un filesort. Sur un gros résultat intermédiaire, c'est ce filesort, pas le parcours, qui coûte.

La table rentals utilisée par plusieurs défis a UNIQUE KEY rental_date (rental_date, inventory_id, customer_id) et KEY (store_id, status, rental_date). Ces requêtes lisent les lignes directement dans l'ordre de l'index :

-- première colonne fixée, tri sur les deux suivantes
WHERE rental_date = '2025-05-25' ORDER BY inventory_id, customer_id

-- plage sur la première colonne, le tri commence par cette colonne
WHERE rental_date > '2025-05-25' ORDER BY rental_date, inventory_id

-- toutes les colonnes décroissantes : l'index est lu à rebours
ORDER BY rental_date DESC, inventory_id DESC, customer_id DESC LIMIT 10

-- deux colonnes fixées, tri sur la troisième
WHERE store_id = 7 AND status = 'late' ORDER BY rental_date DESC LIMIT 10

Celles-ci demandent un filesort :

-- directions mélangées
WHERE rental_date = '2025-05-25' ORDER BY inventory_id DESC, customer_id ASC

-- store_id n'est pas dans cet index
WHERE rental_date = '2025-05-25' ORDER BY inventory_id, store_id

-- inventory_id est sautée et non fixée
WHERE rental_date = '2025-05-25' ORDER BY customer_id

-- plage sur la première colonne, le tri la saute
WHERE rental_date > '2025-05-25' ORDER BY inventory_id, customer_id

-- ici IN est une plage : plusieurs blocs, chacun trié séparément
WHERE store_id = 7 AND status IN ('late', 'lost') ORDER BY rental_date DESC

Quelques règles découlent de ces exemples :

• EXPLAIN vous le dit. Using filesort dans Extra signifie que MySQL trie après la lecture ; Backward index scan signifie qu'il parcourt l'index depuis la fin.
• Un filesort ignore LIMIT pendant la lecture. Pour renvoyer les 10 premiers, il doit d'abord lire toutes les lignes qui correspondent. Avec l'ordre de l'index, LIMIT 10 s'arrête après une dizaine d'entrées.
• Des directions mélangées demandent un index assorti. Depuis MySQL 8.0, un index peut être déclaré (inventory_id DESC, customer_id ASC), et ce tri mixte devient gratuit.
• Dans une jointure, seul l'index de la première table peut trier. Trier sur une colonne d'une table suivante implique toujours un filesort : la table sur laquelle vous triez doit mener la jointure. STRAIGHT_JOIN impose cet ordre quand l'optimiseur en choisit un autre.
• Avec IN, découpez la requête. Une branche ordonnée par l'index pour chaque valeur, combinées par UNION ALL puis triées à la fin, lit bien moins de lignes qu'un filesort de tout ce qui correspond.
• La clé primaire est la dernière colonne cachée. KEY (customer_id) est stocké comme (customer_id, id), donc WHERE customer_id = 42 ORDER BY id DESC suit déjà l'ordre de l'index.

Index de préfixe sur les chaînes longues

Indexer les n premiers caractères garde l'arbre petit. Choisissez n en l'augmentant jusqu'à ce que la sélectivité du préfixe approche celle de la colonne entière, puis vérifiez le pire cas plutôt que la moyenne. Le coût est réel : un index de préfixe ne peut pas couvrir une requête ni servir au tri ou au regroupement.

SELECT COUNT(DISTINCT LEFT(title, 8))  / COUNT(*) AS sel8,
       COUNT(DISTINCT LEFT(title, 16)) / COUNT(*) AS sel16,
       COUNT(DISTINCT title)           / COUNT(*) AS full_sel
FROM bugs;

ALTER TABLE bugs ADD INDEX idx_title (title(16));

Pour les colonnes TEXT et BLOB, ce n'est pas facultatif : MySQL ne peut les indexer qu'avec une longueur de préfixe.

Index redondants, dupliqués et inutilisés

Chaque index se paie à chaque écriture et dans chaque décision de l'optimiseur. Un index sur (A) est redondant quand (A, B) existe ; étendre l'index le plus large vaut généralement mieux qu'en ajouter un second. Les doublons arrivent discrètement, via des déclarations UNIQUE, PRIMARY KEY et INDEX séparées sur la même colonne. Et un index merge dans EXPLAIN n'a rien d'une victoire : c'est l'optimiseur qui signale qu'aucun index ne correspond à la requête.

Attention à un cas : (A, id) sur une table InnoDB semble redondant, puisque (A) se termine déjà par la clé primaire. Mais remplacer (A) par (A, B) casse les requêtes qui comptaient sur WHERE A = ? ORDER BY id.

MySQL 8.0 aide des deux côtés du problème :

-- index rendus redondants par un autre index
SELECT * FROM sys.schema_redundant_indexes;

-- index inutilisés depuis le démarrage du serveur
SELECT * FROM sys.schema_unused_indexes;

-- cacher un index à l'optimiseur avant de le supprimer
ALTER TABLE rentals ALTER INDEX idx_customer INVISIBLE;

Un index invisible reste maintenu à chaque écriture, mais l'optimiseur l'ignore. Si rien ne ralentit, supprimez-le ; sinon, rendez-le VISIBLE instantanément, sans reconstruction. Lisez la liste des index inutilisés avec prudence : elle ne couvre que la période depuis le dernier redémarrage, et un rapport mensuel n'a peut-être pas encore tourné.

Index et verrous

InnoDB verrouille les entrées d'index qu'il lit, pas les lignes que vous vouliez modifier. Un UPDATE ou un SELECT … FOR UPDATE qui parcourt 10 000 entrées d'index pour trouver 10 lignes peut verrouiller les 10 000, et les autres transactions qui les touchent attendent.

• REPEATABLE READ, le niveau par défaut, pose des verrous next-key : l'entrée plus l'intervalle qui la précède, si bien qu'aucune nouvelle ligne ne peut être insérée dans la plage parcourue avant la fin de la transaction.
• READ COMMITTED libère les verrous des lignes qui se sont révélées non correspondantes, et ne pose de verrous d'intervalle que pour les contrôles de clés étrangères et de doublons.
• Index condition pushdown (depuis 5.6) évalue les conditions sur les colonnes de l'index sans quitter l'index, avant de lire la ligne. EXPLAIN affiche Using index condition. Cela ne fonctionne que sur les index secondaires, et signifie que moins de lignes sont lues et verrouillées.

Un index étroit est donc aussi un outil de concurrence : moins une écriture parcourt d'entrées, moins elle verrouille de lignes. Le sandbox QueryCrack n'exécute que de simples requêtes SELECT, vous n'y poserez donc aucun verrou, mais le nombre de lectures que vous réduisez est le même chiffre qui décide combien une écriture verrouillerait.

Comment cela apparaît dans votre verdict

Vous ne pouvez pas ajouter d'index ici : chaque défi fournit un schéma fixe. Ce que vous changez, c'est si la requête permet au moteur de les utiliser, et le correcteur le lit directement dans les compteurs handler. Pour voir avec quoi vous travaillez, exécutez SHOW INDEX FROM bugs, puis faites un EXPLAIN de votre requête pour voir quel index apparaît dans la colonne key.

Questions fréquentes