Vérifier l’usage d’un index avec EXPLAIN

Une requête peut produire le bon résultat tout en examinant beaucoup plus de lignes que nécessaire. Vous avez déjà créé deux index sur emprunts : un index sur date_emprunt et un index composite sur adherent_id, date_emprunt. Dans cette leçon, vous vérifiez si MariaDB envisage ces index puis lequel il choisit pour exécuter une requête.

La présence d’un index dans le schéma ne prouve pas qu’il est utile à chaque requête. EXPLAIN affiche le plan estimé par MariaDB sans exécuter la requête de sélection ; vous pouvez donc l’utiliser avant une mesure de durée ou une modification d’index. Les plans dépendent notamment des données et de leurs statistiques : un résultat observé sur un jeu minuscule ne suffit pas à conclure pour une base chargée.

Examiner le plan de la requête

Dans MariaDB 11, placez EXPLAIN devant une requête pour obtenir une ligne de description pour chaque table concernée. Commencez par un filtre sur l’identifiant d’adhérent, qui correspond au préfixe gauche de l’index composite déjà défini. Le résultat de la requête reste inchangé : EXPLAIN décrit la manière dont MariaDB prévoit de la traiter.

-- À ajouter à sql/diagnostics.sql : vérifier le filtre par adhérent.
EXPLAIN
SELECT adherent_id, date_emprunt
FROM emprunts
WHERE adherent_id = 42;

MariaDB renvoie les colonnes du plan, notamment possible_keys, key et rows. Les valeurs exactes dépendent du schéma, des données et des statistiques de la base.

Lisez d’abord possible_keys : cette colonne indique les index que MariaDB peut envisager pour la recherche. La colonne key désigne l’index effectivement retenu ; si elle est à NULL, aucun index n’est choisi pour cette table dans ce plan. Le fait qu’un nom apparaisse dans possible_keys n’est donc pas la preuve que l’index est utilisé.

La colonne rows donne une estimation du nombre de lignes que MariaDB devra examiner pour cette étape. Ce n’est pas le nombre exact de lignes renvoyées par la requête, ni une durée. Une estimation faible est souvent préférable à un parcours large, mais interprétez-la avec les filtres, la taille réelle des tables et vos mesures de référence.

Comparer les filtres sur les index existants

Comparez ensuite un filtre sur la date seule avec le filtre combinant adhérent et date. Ces requêtes permettent de confronter le plan à vos deux index existants : l’index simple sur date_emprunt et l’index composite sur adherent_id, date_emprunt. Ne créez pas un nouvel index avant d’avoir observé le plan et vérifié les index présents, comme vous l’avez fait lors des leçons précédentes.

-- À ajouter à sql/diagnostics.sql : comparer les filtres séparément.
EXPLAIN
SELECT adherent_id, date_emprunt
FROM emprunts
WHERE date_emprunt = '2025-03-15';

-- Le filtre combine les deux colonnes de l’index composite.
EXPLAIN
SELECT adherent_id, date_emprunt
FROM emprunts
WHERE adherent_id = 42
  AND date_emprunt = '2025-03-15';

Pour chaque EXPLAIN, comparez possible_keys, key et rows. L’index retenu peut varier selon les données et les estimations de MariaDB ; aucun résultat exact n’est garanti à l’avance.

Un plan peut indiquer un balayage complet de la table, c’est-à-dire l’examen de ses lignes sans accès par un index choisi pour cette étape. Dans le résultat tabulaire, vérifiez notamment les colonnes type et key pour repérer cette situation ; ici, retenez surtout la distinction entre une recherche qui sélectionne une clé et un plan qui n’en sélectionne pas. L’interprétation détaillée des types d’accès et des étapes de jointure sera traitée dans la prochaine leçon.

Consigner une vérification reproductible

Dans sql/diagnostics.sql, conservez les commandes EXPLAIN avec un commentaire précisant la requête observée, les index concernés et les valeurs de filtre employées. Notez aussi les colonnes possible_keys, key et rows relevées, ainsi que la taille ou la nature du jeu de données. Cette trace vous permettra de refaire le diagnostic après un changement, sans modifier les index de sql/index.sql sur la seule base d’une supposition.

À retenir

• Ajoutez `EXPLAIN` devant une requête pour examiner le plan estimé sans exécuter la sélection.
• `possible_keys` liste les index envisageables ; `key` révèle l’index effectivement choisi, ou `NULL` si aucun n’est retenu.
• `rows` est une estimation des lignes examinées, pas une durée ni le nombre de résultats renvoyés.
• Vérifiez le plan sur des données représentatives et consignez vos observations dans `sql/diagnostics.sql` avant de modifier les index.

Dans la prochaine leçon, vous apprendrez à lire les étapes d’un plan d’exécution et à interpréter plus précisément les types d’accès.