Comment lire l'EXPLAIN de MySQL : possible_keys, key, Extra et l'ordre des colonnes d'un index

Au début de ma carrière de DBA, j'ai beaucoup souffert avec les requêtes lentes. Je voyais qu'une requête prenait du temps, mais je ne savais pas comment trouver ce qu'on pouvait y optimiser. Avec le temps, j'ai appris à lire EXPLAIN, qui est la façon de demander au moteur comment il compte exécuter une requête avant de l'exécuter, et cet article est ce que j'aurais aimé qu'on m'explique à l'époque.

Contrairement au précédent, celui-ci ne parle que de MySQL. MariaDB, né de MySQL, renvoie presque le même tableau, et je signale les endroits où il diffère.

Les tables des exemples

Deux cent mille commandes de cinq mille clients : 90 % livrées, 7 % expédiées et 3 % en attente, avec des dates réparties entre 2022 et 2025. Les lignes viennent d'une série générée avec une CTE récursive, que j'ai expliquée dans l'article sur la façon de remplir une table avec des données de test :

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    pais   CHAR(2)      NOT NULL
);

CREATE TABLE pedidos (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT           NOT NULL,
    estado     VARCHAR(20)   NOT NULL,
    fecha      DATE          NOT NULL,
    total      DECIMAL(10,2) NOT NULL
);

SET SESSION cte_max_recursion_depth = 200000;

INSERT INTO clientes (nombre, pais)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 5000
)
SELECT CONCAT('Cliente ', n), ELT(1 + n % 5, 'CR', 'MX', 'ES', 'AR', 'CO')
FROM serie;

INSERT INTO pedidos (cliente_id, estado, fecha, total)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 200000
)
SELECT 1 + FLOOR(RAND() * 5000),
       CASE WHEN n % 100 < 90 THEN 'entregado'
            WHEN n % 100 < 97 THEN 'enviado'
            ELSE 'pendiente' END,
       DATE '2022-01-01' + INTERVAL FLOOR(RAND() * 1400) DAY,
       ROUND(RAND() * 1000, 2)
FROM serie;

Dans MariaDB, la variable du SET s'appelle max_recursive_iterations.

Pour l'instant, pedidos n'a que sa clé primaire. Je crée les index au fur et à mesure, pour voir ce que chacun change dans le plan. Les données sont aléatoires : si vous refaites les exemples, vos valeurs de rows ne seront pas exactement les miennes. Les sorties viennent de MySQL 8.0.

Demander le plan à MySQL

Il suffit d'écrire EXPLAIN devant le SELECT :

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1234;

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | pedidos | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 200000 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

EXPLAIN n'exécute pas la requête : il renvoie le plan, avec une ligne pour chaque table que la requête lit. Ce que dit chaque colonne :

• id et select_type : à quel SELECT appartient la ligne. Elles comptent quand il y a des sous-requêtes ou un UNION ; dans une requête simple, elles valent 1 et SIMPLE.
• table : la table, ou son alias.
• type : comment on y accède. Du meilleur au pire, les plus fréquents sont const (une seule ligne, par clé), eq_ref (une ligne pour chaque ligne de la table précédente), ref (les lignes ayant une valeur de l'index), range (une portion de l'index), index (l'index entier) et ALL (la table entière).
• possible_keys et key : les index qui pourraient servir et celui qu'il a choisi. Ils ont leur section plus bas.
• key_len : combien d'octets de l'index il utilise ; avec un index sur plusieurs colonnes, cela dit combien de colonnes il a utilisées.
• ref : ce à quoi l'index est comparé : une constante (const) ou une colonne d'une autre table.
• rows : combien de lignes il pense examiner. C'est une estimation tirée des statistiques de la table, pas un comptage.
• filtered : quel pourcentage de ces lignes il pense voir passer le WHERE.
• Extra : ce qui ne tient pas dans les autres colonnes : s'il filtre, s'il trie, s'il crée une table temporaire.

Ici, type vaut ALL, key est vide et rows vaut 200 000 : pour renvoyer les 40 commandes d'un client, MySQL va lire les deux cent mille.

Si votre MySQL renvoie un arbre de texte au lieu de ce tableau, c'est que la variable explain_format (elle existe depuis MySQL 8.0.32) est à TREE ; le MySQL le plus récent de mon banc d'essai est configuré ainsi par défaut. EXPLAIN FORMAT=TRADITIONAL demande le tableau.

possible_keys et key : avoir des index n'est pas les utiliser

Ce sont les deux colonnes que j'ai eu le plus de mal à comprendre. possible_keys, ce sont les index que l'optimiseur envisage pour la requête ; key, celui qu'il va vraiment utiliser. Je peux avoir dix index dans possible_keys : si aucun n'apparaît dans key, la requête n'utilise pas d'index.

Il y a deux raisons pour que key soit vide. La première, c'est qu'aucun index ne sert, et alors possible_keys est vide aussi, comme dans l'exemple ci-dessus. La seconde, c'est qu'il y en a un et que l'optimiseur décide de ne pas l'utiliser. Pour le voir, je crée un index sur la date :

CREATE INDEX idx_fecha ON pedidos (fecha);

Les commandes d'un mois l'utilisent :

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+

Celles d'une année entière, non :

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 200000 |    50.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

idx_fecha est dans possible_keys, mais key est vide et type revient à ALL. Une année, c'est un quart des commandes, et lire un quart de la table par sauts, en cherchant chaque ligne depuis l'index, lui coûte plus cher que de la lire entière dans l'ordre. Ce n'est pas une erreur : c'est une décision, et parfois c'est la bonne. Regardez aussi filtered : il estime 50 % alors que le compte réel est de 26 %. Ce sont des estimations, et elles peuvent se tromper.

Le piège le plus courant est ailleurs : avoir l'index et écrire la condition de façon qu'il ne puisse pas servir. Les mêmes commandes d'octobre, demandées par année et par mois :

EXPLAIN SELECT * FROM pedidos
WHERE YEAR(fecha) = 2025 AND MONTH(fecha) = 10;

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | NULL          | NULL | NULL    | NULL | 200000 |   100.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

Maintenant possible_keys est vide. L'index range les dates dans l'ordre, pas leurs années ni leurs mois, et pour savoir quelles dates vérifient YEAR(fecha) = 2025, MySQL doit le calculer ligne par ligne. C'est pareil avec n'importe quelle fonction ou opération sur la colonne : DATE(fecha), LOWER(email), total * 1.13. La solution est d'écrire la condition comme un intervalle sur la colonne, comme dans le premier exemple d'octobre.

La colonne Extra

C'est la dernière colonne, et celle qui en dit le plus quand tout le reste semble en ordre. Les valeurs que je rencontre le plus souvent :

• Using where : le WHERE s'applique aux lignes après les avoir lues. Avec type à ALL, cela veut dire qu'il lit toute la table et écarte ce qui ne correspond pas.
• Using index : tout ce que demande la requête se trouve dans l'index, donc il ne lit pas la table. C'est ce qui peut arriver de mieux ici.
• Using index condition : une partie du WHERE est vérifiée dans l'index, avant d'aller chercher la ligne.
• Using filesort : il doit trier le résultat à part, parce qu'il ne le lit pas déjà trié dans un index. Le nom est trompeur : cela ne veut pas dire qu'il écrit sur le disque.
• Using temporary : il crée une table temporaire pour résoudre la requête, typiquement avec un GROUP BY et un ORDER BY sur des colonnes différentes.

Compter les commandes d'octobre se fait sans toucher la table, parce que la date est dans l'index et qu'il n'a besoin de rien d'autre :

EXPLAIN SELECT COUNT(*) FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using where; Using index |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+

Les commandes d'un jour, de la plus chère à la moins chère, se lisent par l'index de la date mais doivent être triées par total :

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10'
ORDER BY total DESC;

+------+---------------+-----------+---------+-------+------+----------+----------------+
| type | possible_keys | key       | key_len | ref   | rows | filtered | Extra          |
+------+---------------+-----------+---------+-------+------+----------+----------------+
| ref  | idx_fecha     | idx_fecha | 3       | const |  124 |   100.00 | Using filesort |
+------+---------------+-----------+---------+-------+------+----------+----------------+

Et les dix clients qui ont le plus dépensé en octobre demandent les deux : une table temporaire pour faire la somme par client et un tri sur cette somme :

EXPLAIN SELECT cliente_id, SUM(total) AS gastado
FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01'
GROUP BY cliente_id
ORDER BY gastado DESC
LIMIT 10;

+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                                  |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition; Using temporary; Using filesort |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+

Aucun des deux n'est mauvais en soi. Ce que je regarde, c'est s'ils apparaissent dans une requête exécutée des milliers de fois par jour, ou sur beaucoup de lignes.

Quand la requête lit plusieurs tables, le plan a une ligne pour chacune, dans l'ordre où il va les lire :

EXPLAIN SELECT c.nombre, p.fecha, p.total
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
WHERE p.fecha = '2024-05-10';

+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| table | type   | possible_keys | key       | key_len | ref                            | rows | filtered | Extra |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| p     | ref    | idx_fecha     | idx_fecha | 3       | const                          |  124 |   100.00 | NULL  |
| c     | eq_ref | PRIMARY       | PRIMARY   | 4       | tienda.p.cliente_id |    1 |   100.00 | NULL  |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+

D'abord les commandes du jour, par idx_fecha ; puis, pour chacune, son client par la clé primaire (eq_ref), comparée à p.cliente_id, comme l'indique ref.

L'ordre des colonnes d'un index

Un index peut avoir plusieurs colonnes. J'en crée un à trois colonnes, pensé pour chercher les commandes d'un client dans un état et à une date :

CREATE INDEX idx_cliente_estado_fecha ON pedidos (cliente_id, estado, fecha);

Avec les trois colonnes dans le WHERE, il l'utilise en entier :

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente' AND fecha = '2024-05-10';

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Pendant longtemps, j'ai cru que les conditions du WHERE devaient être dans le même ordre que les colonnes de l'index, et que sinon l'index ne servait à rien. Je me trompais : l'optimiseur associe chaque condition à sa colonne, où qu'on l'écrive. Avec l'ordre inversé, le plan est identique :

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10' AND estado = 'pendiente' AND cliente_id = 1234;

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Ce qui compte, en revanche, c'est l'ordre des colonnes dans l'index. Un index sur plusieurs colonnes est trié par la première ; pour chaque valeur de la première, par la deuxième ; et ainsi de suite. C'est comme un annuaire trié par nom puis par prénom : il permet de chercher un nom, ou un nom avec son prénom, mais pas toutes les personnes qui s'appellent Ana. Le manuel de MySQL appelle cela le préfixe gauche : un index sur (cliente_id, estado, fecha) sert à chercher par cliente_id, par cliente_id et estado, ou par les trois. Il ne sert pas à chercher par estado ou par fecha sans cliente_id.

Les commandes en attente de cette année, sans préciser le client :

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 199728 |     4.34 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

La table a un index qui contient estado et fecha, mais il commence par cliente_id, et il n'apparaît même pas dans possible_keys. Seul idx_fecha y est, et l'optimiseur l'écarte pour la même raison qu'avant : presque une année de commandes.

key_len : combien de colonnes il a utilisées

Quand l'index a plusieurs colonnes, key_len dit jusqu'où il est allé. Chaque colonne occupe un nombre fixe d'octets dans l'index : cliente_id est un INT, 4 octets ; estado est un VARCHAR(20) en utf8mb4, 20 caractères de 4 octets au plus, plus 2 pour la longueur, soit 82 ; et fecha est un DATE, 3. Les trois ensemble font les 89 ci-dessus. Une colonne qui accepte NULL ajoute un octet.

Avec le client et l'état, sans la date, il utilise les deux premières colonnes, 4 + 82 :

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente';

+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| ref  | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 86      | const,const |    1 |   100.00 | NULL  |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

Avec le client et la date, en sautant l'état, il ne peut utiliser que la première :

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND fecha >= '2024-01-01';

+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| type | possible_keys                      | key                      | key_len | ref   | rows | filtered | Extra                 |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 4       | const |   40 |    50.00 | Using index condition |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+

key indique idx_cliente_estado_fecha, et quelqu'un qui ne regarderait que cette colonne croirait que l'index fonctionne en entier. key_len indique 4 : il cherche par cliente_id et rien d'autre. La date est vérifiée dans l'index (Using index condition), mais elle ne sert pas à restreindre la recherche, parce que l'état se trouve entre le client et la date.

Quand key a un nom et qu'il lit quand même tout

Il reste un cas utile à connaître. Si la requête ne demande que des colonnes présentes dans l'index, MySQL peut lire l'index entier au lieu de la table, même si le filtre ne commence pas par la première colonne :

EXPLAIN SELECT cliente_id, estado, fecha FROM pedidos
WHERE estado = 'pendiente';

+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| type  | possible_keys            | key                      | key_len | ref  | rows   | filtered | Extra                    |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| index | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | NULL | 199728 |    10.00 | Using where; Using index |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+

key a un nom, mais type vaut index et rows vaut 199 728 : il parcourt les deux cent mille entrées de l'index, une par une. C'est plus rapide que de parcourir la table, parce que l'index est plus petit, mais ce n'est pas une recherche. Dans MariaDB, ce même plan sort avec possible_keys vide et key renseigné, ce qui est exactement le cas que décrit le manuel de MySQL : l'index ne sert pas à trouver les lignes, mais il contient toutes les colonnes demandées. C'est pourquoi key ne se lit jamais seule : elle va avec type et rows.

La solution

Si la requête fréquente porte sur l'état et la date, l'index doit commencer par l'état :

CREATE INDEX idx_estado_fecha ON pedidos (estado, fecha);

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| type  | possible_keys              | key              | key_len | ref  | rows | filtered | Extra                 |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| range | idx_fecha,idx_estado_fecha | idx_estado_fecha | 85      | NULL | 1321 |   100.00 | Using index condition |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+

De 199 728 lignes à 1 321, et key_len 85 : l'état entier, 82, plus la date, 3. La règle que je suis pour choisir l'ordre est de mettre d'abord les colonnes comparées avec = et en dernier celle comparée avec un intervalle (>=, BETWEEN) : après un intervalle, les colonnes suivantes de l'index ne restreignent plus la recherche.

Est-ce pareil dans les autres moteurs ?

Oui. Le préfixe gauche est une propriété des index B-tree, ceux par défaut dans tous les moteurs relationnels, et la documentation de PostgreSQL, de SQLite et d'Oracle l'explique de la même façon. Dans aucun d'eux l'ordre dans lequel on écrit les conditions du WHERE n'a d'importance.

Ce que certains ont appris avec les années, c'est à sauter la première colonne quand elle a très peu de valeurs distinctes : ils cherchent par la deuxième une fois pour chaque valeur de la première. Cela s'appelle le skip scan, et on le trouve dans MySQL depuis la 8.0.13, dans PostgreSQL depuis la 18, dans Oracle et dans SQLite (dans SQLite seulement si ANALYZE a été exécuté). Dans MySQL, il apparaît dans Extra sous la forme Using index for skip scan, et seulement quand la requête lit une seule table et ne demande que des colonnes de l'index, entre autres conditions. Ici, il n'aurait servi à rien : la première colonne est le client, et il y en a cinq mille.

Mesurer au lieu d'estimer

Tout ce qui précède, ce sont des estimations : EXPLAIN n'exécute pas la requête. Depuis MySQL 8.0.18, EXPLAIN ANALYZE, lui, l'exécute, et place à côté de chaque estimation ce qui s'est réellement passé :

EXPLAIN ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

-> Index range scan on pedidos using idx_estado_fecha over (estado = 'pendiente' AND '2025-01-01' <= fecha), with index condition: ((pedidos.estado = 'pendiente') and (pedidos.fecha >= DATE'2025-01-01'))  (cost=595 rows=1321) (actual time=1.01..12.6 rows=1321 loops=1)

rows=1321 dans les premières parenthèses, c'est ce qu'il a estimé ; actual time et rows=1321 dans les secondes, le temps pris en millisecondes et les lignes lues. Quand les deux nombres de lignes se ressemblent peu, les statistiques de la table sont périmées, et ANALYZE TABLE pedidos les recalcule.

MariaDB n'a pas EXPLAIN ANALYZE. Son équivalent est ANALYZE devant la requête, depuis la 10.1, qui renvoie le tableau habituel avec deux colonnes de plus, r_rows et r_filtered, avec ce qui s'est passé :

ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

Les deux exécutent la requête, donc elles prennent autant de temps qu'elle. Et dans MariaDB, un ANALYZE UPDATE ou un ANALYZE DELETE modifie vraiment les données.

Dans Calíope

Dans l'Éditeur SQL, la requête écrite, le bouton EXPLAIN visuel de la barre demande le plan au serveur sans exécuter la requête et le dessine sous forme d'arbre, avec une couleur par type d'accès : rouge pour le parcours complet d'une table, orange pour celui d'un index entier, jaune pour une portion d'index et vert pour une recherche par clé. Il le demande en JSON, donc il ne dépend pas de la valeur de votre explain_format. Et le bouton Index de la barre des résultats ouvre le Conseiller d'index, qui cherche dans ce plan les parcours complets et, si la requête filtre sur une colonne qu'aucun index ne couvre, propose le CREATE INDEX.

Ce que je regarde en premier

Quand une requête est lente, voici l'ordre dans lequel je lis son EXPLAIN :

• key : si elle est vide, aucun index n'est utilisé. Si possible_keys en contient un, l'optimiseur l'a écarté ; si elle est vide aussi, aucun ne sert, ou la condition ne permet pas de l'utiliser.
• type et rows ensemble : ALL ou index avec beaucoup de lignes, c'est lire la table ou l'index en entier, même si key a un nom.
• key_len, si l'index a plusieurs colonnes : elle dit combien il en a vraiment utilisé.
• Extra : Using temporary et Using filesort dans une requête exécutée souvent méritent un second regard.