Cours MariaDB / Lire EXPLAIN et comparer les plans avant et après un index

Dans MariaDB, EXPLAIN affiche le plan d’exécution choisi par l’optimiseur. Comparez la même requête et ses résultats avant et après l’ajout d’un index, puis lisez le type d’accès, la clé retenue et les estimations de lignes en les distinguant des mesures d’exécution réelles.

Lire les champs du plan dans leur contexte

Commencez par identifier les tables lues et les clés qui peuvent limiter les lignes examinées. Un accès de type ALL n’est pas automatiquement mauvais pour une petite table. De même, Using filesort indique une opération de tri supplémentaire ; cette seule expression ne prouve pas qu’un tri a été écrit sur disque. Il faut relier ces indications au volume, à la proportion de lignes sélectionnées et au comportement observé.

Le guide des index composés et de l’ordre des colonnes explique comment proposer une clé pour une requête. Un index présent dans la définition ne sera pas nécessairement choisi : l’optimiseur compare des chemins à partir de ses informations et de ses estimations.

Champ Interprétation Point de vigilance
table Table ou alias de cette étape Une requête peut avoir plusieurs étapes
type Mode d’accès aux lignes Un parcours complet peut être raisonnable
possible_keys / key Clés candidates / clé sélectionnée Existence et sélection sont différentes
rows Nombre de lignes estimé pour l’accès Pas le nombre exact de lignes retournées
Extra Détails de filtrage, tri ou couverture Un seul libellé ne juge pas toute la requête

La référence EXPLAIN de MariaDB décrit les champs et les types d’accès. La consultation du plan peut elle-même avoir besoin de verrous de métadonnées ; ne l’assimilez pas à une opération sans aucun coût sur le serveur.

Générer mille lignes avec une répartition reproductible

Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, remplir, lire et nettoyer les objets d’exercice et y ajouter un index. Le guide du client MariaDB et des fichiers UTF-8 explique la connexion. Contrôlez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms avec SHOW TABLES;.

Vérifiez que digits_40_lab et explain_40_lab n’existent pas déjà. Sinon, examinez leur définition et leurs lignes et sauvegardez ce qui doit être conservé avant toute suppression. Le code suivant crée ses propres lignes sans lire ni modifier de commandes d’application.

Enregistrez le bloc complet de préparation en UTF-8, par exemple dans C:/mariadb-lab/c40-explain.sql, après avoir créé le dossier. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c40-explain.sql; avec le chemin réel. Exécutez les instructions SQL dans cette connexion, pas directement dans l’invite du système.

USE shopcourse;
CREATE TABLE digits_40_lab(n TINYINT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO digits_40_lab VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE explain_40_lab(
 order_id INT PRIMARY KEY,customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO explain_40_lab
SELECT 1+a.n+10*b.n+100*d.n,
 MOD(a.n+10*b.n+100*d.n,100)+1,
 DATE_ADD('2026-01-01',INTERVAL (a.n+10*b.n+100*d.n) MINUTE),1000
FROM digits_40_lab a CROSS JOIN digits_40_lab b CROSS JOIN digits_40_lab d;

Les trois chiffres de 0 à 9 donnent mille combinaisons, numérotées de 0 à 999 avant l’ajout de 1 à order_id. La formule MOD répartit customer_id entre 1 et 100 : chaque client possède dix lignes. Les horaires commencent le 1er janvier 2026 à minuit et progressent d’une minute ; chaque montant vaut 1000.

Vérifiez que SELECT COUNT(*) FROM explain_40_lab; renvoie 1000. Cette répartition artificielle facilite la reproduction d’un plan ; elle ne représente pas les déséquilibres de clientèle, les volumes ou la charge d’une application réelle.

Comparer le même EXPLAIN avant et après l’index

Commencez par ce plan sans index secondaire sur le client et la date. Conservez sa sortie et, pour vérifier les résultats, exécutez aussi le SELECT complet présenté dans la section suivante avant l’ajout de l’index.

EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

Ajoutez ensuite la clé composée et relancez exactement le même EXPLAIN :

CREATE INDEX idx_e40_customer_date
ON explain_40_lab(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

Le premier plan ne dispose pas de clé secondaire client-horaire ; le second peut considérer la nouvelle clé. Comparez réellement key, type, rows et Extra. La version, les statistiques et les données peuvent modifier le plan et les estimations : ne mémorisez pas un nombre comme une obligation universelle.

Voici un exemple observé avec ces mille lignes sur un serveur d’apprentissage isolé MariaDB 12.3.3. Les deux plans ont été consultés avec la même requête ; le tableau ne constitue pas une mesure comparative de latence.

Étape type key rows Extra
Avant l’index ALL NULL 1000 Using where; Using filesort
Après l’index range idx_e40_customer_date 10 Using where; Using index

Dans cette observation, le parcours complet laisse place à un accès par plage client-horaire et l’estimation passe de 1000 à 10. Cela ne signifie pas que la requête est devenue cent fois plus rapide. Vérifiez séparément les dix commandes retournées et, si nécessaire, les informations issues d’une véritable exécution.

Vérifier les lignes retournées indépendamment des estimations

Le SELECT complet à comparer avant et après l’index est le suivant :

SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

Les identifiants attendus sont 47, 147, 247, 347, 447, 547, 647, 747, 847 et 947, dans cet ordre. Comparez aussi les horaires, pas seulement le nombre de lignes. L’index doit préserver les résultats et le sens du tri. Le champ rows d’EXPLAIN estime le travail de l’étape d’accès ; ce n’est pas un COUNT(*) du résultat final et il peut en différer.

Un plan ne garantit pas une durée. Pour une comparaison de temps pertinente, contrôlez la requête, les données, la charge simultanée et l’état du cache, et faites plusieurs observations. Une petite différence de temps sur cet exercice ne justifie pas un pourcentage d’amélioration pour un service réel.

Distinguer l’exécution par ANALYZE de la mise à jour des statistiques

Dans MariaDB, la famille d’instructions ANALYZE exécute la requête pour obtenir des informations réelles. Ne la traitez pas comme un simple affichage de plan en l’ajoutant sans examen devant UPDATE ou DELETE. Pour cet exercice, appliquez l’analyse uniquement au SELECT d’apprentissage ci-dessous, sur une version qui prend en charge cette syntaxe et avec les droits adaptés.

ANALYZE TABLE a un autre rôle : mettre à jour les statistiques de table. Des statistiques obsolètes peuvent fausser les estimations, mais leur mise à jour a elle aussi des coûts et des conditions de verrouillage selon la version. Examinez la forme de la requête et la répartition des données avant de chercher à obtenir un plan voulu avec FORCE INDEX.

ANALYZE FORMAT=JSON
SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;

La sortie JSON combine estimations et mesures de cette exécution. Selon le nœud, r_rows décrit le nombre moyen de lignes lues, r_loops le nombre d’exécutions du nœud et r_total_time_ms son temps mesuré. Ne confondez pas ces champs avec rows et ne donnez pas à la durée une valeur attendue fixe. La référence ANALYZE FORMAT=JSON détaille les champs et leurs conditions de présence.

Conserver les preuves de comparaison et nettoyer l’exercice

Conservez ensemble la version du serveur, les définitions de tables et d’index, le code qui génère les données, le SELECT comparé et les plans avant et après. Si vous utilisez ANALYZE, gardez aussi sa sortie et distinguez les champs estimés des champs mesurés. Ces éléments permettent de comprendre un résultat différent lors d’une autre exécution.

Après avoir vérifié qu’aucune donnée n’est à conserver, supprimez seulement les deux objets d’exercice ci-dessous. Ils n’ont pas de relation de clé étrangère ; les éventuelles tables de commandes d’application ne font pas partie du nettoyage. DROP TABLE supprime structure et données et n’est pas annulé par un ROLLBACK ordinaire.

DROP TABLE shopcourse.explain_40_lab;
DROP TABLE shopcourse.digits_40_lab;