MySQL EXPLAIN ANALYZE expliqué : lire les plans d’exécution et optimiser les requêtes (Guide 8.0)

1. Introduction

Plans d’exécution : essentiels pour l’optimisation des performances de la base de données

Dans les applications web et les systèmes d’entreprise, la performance de la base de données est un facteur critique qui influence directement le temps de réponse global. Lorsqu’on utilise MySQL en particulier, comprendre le « plan d’exécution » est indispensable pour évaluer l’efficacité d’une requête. La commande traditionnelle EXPLAIN affiche le plan d’exécution avant d’exécuter une instruction SQL et fournit depuis longtemps aux développeurs des informations précieuses.

« EXPLAIN ANALYZE » introduit dans MySQL 8.0

Introduit dans MySQL 8.0.18, EXPLAIN ANALYZE est une amélioration puissante du EXPLAIN traditionnel. Alors que EXPLAIN ne fournissait qu’un « plan théorique », EXPLAIN ANALYZE exécute réellement la requête et renvoie des données mesurées telles que le temps d’exécution et le nombre de lignes traitées. Cela permet d’identifier plus précisément les goulets d’étranglement et de valider les résultats d’optimisation des requêtes.

Pourquoi EXPLAIN ANALYZE est important

Par exemple, l’ordre des JOIN, l’utilisation des index et les conditions de filtrage influencent fortement le temps d’exécution. En utilisant EXPLAIN ANALYZE, vous pouvez visualiser comment une instruction SQL se comporte et déterminer où se trouvent les inefficacités et ce qui doit être optimisé. Cela s’avère particulièrement indispensable lorsqu’on travaille avec de grands ensembles de données ou des requêtes complexes.

Objectif de cet article et public cible

Cet article explique tout, des bases du EXPLAIN ANALYZE de MySQL à l’interprétation de sa sortie en passant par l’application de techniques d’optimisation concrètes. Il s’adresse aux développeurs et aux ingénieurs d’infrastructure qui utilisent régulièrement MySQL, ainsi qu’aux ingénieurs intéressés par l’ajustement des performances. Pour garantir la clarté même pour les débutants, nous incluons des explications de terminologie et des exemples concrets tout au long du texte.

2. Differences Between EXPLAIN and EXPLAIN ANALYZE

Rôle et utilisation de base de EXPLAIN

Le EXPLAIN de MySQL est un outil d’analyse utilisé pour comprendre à l’avance comment une instruction SQL (en particulier une instruction SELECT) sera exécutée. Il permet de vérifier les plans d’exécution tels que l’utilisation des index, l’ordre des jointures et les plages de recherche.

Par exemple :

EXPLAIN SELECT * FROM users WHERE age > 30;

Lorsque cette commande est exécutée, MySQL n’exécute pas réellement la requête, mais affiche plutôt la façon dont il prévoit de la traiter sous forme tabulaire. La sortie comprend des informations telles que l’index utilisé (key), la méthode d’accès (type) et le nombre de lignes estimé (rows).

Rôle et fonctionnalités de EXPLAIN ANALYZE

En revanche, EXPLAIN ANALYZE, introduit dans MySQL 8.0.18, exécute la requête et affiche le plan d’exécution basé sur des valeurs mesurées réelles. Cela rend possible la confirmation de détails qui n’étaient pas visibles avec le EXPLAIN traditionnel, comme le temps de traitement réel et le nombre de lignes effectivement traitées.

Exemple :

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Cette commande exécute la requête et renvoie une sortie incluant :

• Le temps d’exécution pour chaque étape du plan (par ex., 0.0022 sec)
• Le nombre réel de lignes lues (rows)
• La structure de traitement (facilement visualisable en utilisant le format TREE)

Résumé des principales différences

Lequel devriez‑vous utiliser ?

• Utilisez EXPLAIN lorsque vous souhaitez vérifier rapidement la structure d’une requête.
• Utilisez EXPLAIN ANALYZE lorsque vous avez besoin de détails concrets sur le temps d’exécution et le coût de la requête.

Surtout dans les scénarios d’ajustement des performances, EXPLAIN ANALYZE permet d’optimiser sur la base de données d’exécution réelles plutôt que d’estimations, ce qui en fait un outil extrêmement puissant.

3. Output Formats of EXPLAIN ANALYZE

Trois formats de sortie : TRADITIONAL, JSON et TREE

Le EXPLAIN ANALYZE de MySQL peut produire les résultats dans différents formats selon votre objectif. Dans MySQL 8.0 et versions ultérieures, les trois formats suivants sont disponibles.

Examinons de plus près les différences.

TRADITIONAL Format (Default)

La sortie TRADITIONAL est similaire au style classique EXPLAIN et vous permet de consulter les plans d’exécution sous une forme familière. Si vous exécutez EXPLAIN ANALYZE sans spécifier de format, le résultat est généralement affiché dans ce format.

Exemple de sortie (extrait) :

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : coût estimé
• actual time : temps mesuré
• rows : nombre estimé de lignes traitées (avant exécution)
• loops : nombre de boucles (particulièrement important pour les JOIN)

Le format TRADITIONAL est facile à parcourir et à comprendre pour les humains, ce qui le rend adapté aux débutants et aux vérifications rapides.

Format JSON

Le format JSON est plus détaillé et plus facile à manipuler programmatiquement. La sortie est structurée, chaque nœud étant représenté comme un objet imbriqué.

Commande :

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Une partie de la sortie (formatée) :

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

Ce format est moins lisible visuellement, mais il est extrêmement pratique lorsque vous souhaitez analyser les données et les injecter dans des outils d’analyse ou des tableaux de bord.

Format ARBRE (Lisible et Idéal pour Visualiser la Structure)

Le format ARBRE affiche la structure d’exécution de la requête sous forme d’arbre, ce qui facilite la compréhension de l’ordre de traitement des JOIN et des sous‑requêtes.

Commande :

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Exemple de sortie (simplifié) :

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

Pour les requêtes complexes, l’imbrication peut apparaître ainsi :

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

Le format ARBRE est particulièrement utile pour les requêtes comportant de nombreux JOIN ou une imbrication complexe, où il faut saisir le flux de traitement.

Quel Format Devriez‑Vous Utiliser ?

Choisissez le format qui correspond le mieux à votre objectif, et examinez le plan d’exécution dans le style le plus lisible et analysable.

4. Comment Interpréter les Plans d’Exécution

Pourquoi Vous Devez Lire les Plans d’Exécution

Les performances des requêtes MySQL peuvent varier considérablement en fonction du volume de données et de la disponibilité des index. En interprétant correctement la sortie du plan d’exécution provenant de EXPLAIN ANALYZE, vous pouvez identifier objectivement où le travail est gaspillé et ce qui doit être amélioré. Cette compétence est un pilier de l’optimisation des performances, notamment pour les requêtes qui traitent de grands ensembles de données ou des jointures complexes.

Structure de Base d’un Plan d’Exécution

La sortie de EXPLAIN ANALYZE comprend des informations telles que les suivantes (expliquées ici sur la base du format TRADITIONAL) :

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

Cette ligne unique contient plusieurs champs importants.

Comment Lire les Champs Clés

1. cost vs. actual time

• cost est une estimation interne calculée par MySQL et utilisée pour l’évaluation relative.
• actual time reflète le temps réel écoulé et est plus important pour l’analyse des performances.

Par exemple :

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

Si les estimations et les mesures correspondent étroitement, le plan d’exécution est probablement précis. Si l’écart est important, les statistiques de la table peuvent être inexactes.

2. rows vs. actual rows

• rows est le nombre de lignes que MySQL prévoit de lire.
• actual rows est le nombre de lignes réellement lues (indiqué entre parenthèses dans la sortie au format TRADITIONAL).

S’il existe une grande différence, il peut être nécessaire d’actualiser les statistiques ou de reconsidérer la conception des index.

3. loops

If loops=1, l’étape s’exécute une fois. Avec les JOINs ou les sous‑requêtes, vous pouvez voir loops=10 ou loops=1000. Plus la valeur est grande, plus il est probable que des boucles imbriquées provoquent un traitement lourd.

Comprendre la structure imbriquée des plans d’exécution

Lorsque plusieurs tables sont jointes, le plan d’exécution est affiché sous forme d’arbre (particulièrement clair au format ARBRE).

Exemple:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Problème

• Les deux tables sont entièrement parcourues, ce qui entraîne un coût de jointure élevé.

Contre‑mesure

• Ajoutez un index sur users.age et filtrez plus tôt pour réduire la charge de la jointure.

Comment identifier les goulets d’étranglement de performance

Se concentrer sur les points suivants facilite la détection des goulets d’étranglement :

• Nœuds avec un temps réel long et de nombreuses lignes : Ils consomment la majeure partie du temps d’exécution
• Emplacements où un scan complet de table se produit : Probablement des index manquants ou inutilisés
• Étapes avec de nombreuses boucles : Indique un ordre de JOIN inefficace ou un imbriquement
• Grand écart entre les lignes prévues et les lignes réelles : Suggère des statistiques inexactes ou un accès excessif aux données

Utilisez ces informations comme base pour les techniques d’« Optimisation de requêtes » présentées dans la section suivante.

5. Exemples pratiques d’optimisation de requêtes

Qu’est-ce que l’optimisation de requêtes ?

L’optimisation de requêtes consiste à examiner et améliorer les instructions SQL afin qu’elles puissent être exécutées plus efficacement. En fonction de la façon dont MySQL traite les requêtes en interne (plans d’exécution), vous appliquez des améliorations telles que l’ajout d’index, l’ajustement de l’ordre des jointures et l’élimination des traitements inutiles.

Ici, nous montrons comment améliorer les requêtes en utilisant EXPLAIN ANALYZE avec des exemples concrets.

Exemple 1 : Amélioration de la vitesse grâce aux index

Avant optimisation

SELECT * FROM users WHERE email = 'example@example.com';

Plan d’exécution (extrait)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Problème

• La sortie indique Table scan, ce qui signifie qu’un scan complet de la table est effectué. Avec de grands ensembles de données, cela entraîne des retards importants.

Solution : ajouter un index

CREATE INDEX idx_email ON users(email);

Plan d’exécution après optimisation

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Résultat

• Le temps d’exécution est considérablement réduit.
• Le scan complet de la table est évité grâce à l’index.

Exemple 2 : Optimisation de l’ordre des jointures

Avant optimisation

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Plan d’exécution (extrait)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Problème

• Les deux tables sont entièrement parcourues, ce qui entraîne des coûts de jointure élevés.

Solution

• Ajoutez un index sur users.age et filtrez d’abord pour réduire la taille de la cible de jointure.

  CREATE INDEX idx_age ON users(age);
  

Plan d’exécution après optimisation

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Résultat

• Les cibles de JOIN sont filtrées en premier, réduisant la charge de traitement globale.

Exemple 3 : Révision d’une sous‑requête

Avant optimisation

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Problème

• La sous‑requête peut être évaluée à plusieurs reprises, dégradant les performances.

Solution : réécrire en tant que JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Résultat

• Le plan d’exécution est optimisé pour le traitement des JOIN, et les index sont plus susceptibles d’être utilisés.

L’importance de la comparaison avant/après

En utilisant EXPLAIN ANALYZE, vous pouvez vérifier les résultats d’optimisation avec des valeurs mesurées réelles. En comparant le temps d’exécution et le nombre de lignes avant et après les améliorations, vous vous assurez que les efforts d’ajustement sont basés sur de véritables gains de performance plutôt que sur des suppositions.

Considérations importantes en optimisation

• Ajouter trop d’index peut être contre-productif (performance d’INSERT/UPDATE plus lente).
• Les plans d’exécution dépendent du volume de données et des statistiques, il est donc nécessaire de valider pour chaque environnement.
• Une optimisation résout rarement tout. L’analyse des goulets d’étranglement vient en premier.

6. Précautions et bonnes pratiques

Remarques importantes lors de l’utilisation de EXPLAIN ANALYZE

Bien que EXPLAIN ANALYZE soit extrêmement puissant, une utilisation inappropriée peut entraîner des malentendus voire des risques opérationnels. Garder les points suivants à l’esprit garantit une analyse de requête sûre et efficace.

1. Évitez de l’exécuter imprudemment en production

Parce que EXPLAIN ANALYZE exécute réellement la requête, l’utiliser par erreur avec des instructions de modification (INSERT/UPDATE/DELETE) peut modifier les données.

• En général, ne l’utilisez qu’avec des instructions SELECT.
• Préférez l’exécuter dans un environnement de préproduction ou de test plutôt qu’en production.

2. Prenez en compte l’impact du cache

MySQL peut renvoyer des résultats depuis le cache si la même requête est exécutée à plusieurs reprises. En conséquence, le temps d’exécution rapporté par EXPLAIN ANALYZE peut différer du comportement réel.

Contremesures :

• Videz le cache avant l’exécution (RESET QUERY CACHE;).
• Exécutez plusieurs fois et évaluez sur la base des valeurs moyennes.

3. Maintenez les statistiques à jour

MySQL construit les plans d’exécution à partir des statistiques des tables et des index. Si les statistiques sont obsolètes, EXPLAIN et EXPLAIN ANALYZE peuvent fournir des informations trompeuses.

Après de grandes opérations INSERT ou DELETE, mettez à jour les statistiques avec ANALYZE TABLE.

ANALYZE TABLE users;

4. Les index ne sont pas une solution miracle

Bien que les index améliorent souvent les performances, trop d’index ralentissent les opérations d’écriture.

Choisir entre des index composites et des index à colonne unique est également important. Concevez les index soigneusement en fonction des modèles de requêtes et de la fréquence d’utilisation.

5. Ne jugez pas uniquement sur le temps d’exécution

Les résultats de EXPLAIN ANALYZE reflètent uniquement la performance d’une requête unique. Dans les applications réelles, la latence réseau ou le traitement côté serveur peuvent être le véritable goulet d’étranglement.

Par conséquent, analysez les requêtes dans le contexte de l’architecture globale du système.

Résumé des meilleures pratiques

7. Questions fréquemment posées (FAQ)

Q1. Depuis quelle version EXPLAIN ANALYZE est‑il disponible ?

R. EXPLAIN ANALYZE de MySQL a été introduit dans la version 8.0.18 et suivantes. Il n’est pas supporté dans les versions antérieures à 8.0, il faut donc vérifier votre version de MySQL avant de l’utiliser.

Q2. L’exécution de EXPLAIN ANALYZE peut‑elle modifier des données ?

R. EXPLAIN ANALYZE exécute la requête en interne. Lorsqu’il est utilisé avec une instruction SELECT, il ne modifie pas les données.

Ainsi, lorsqu’il est utilisé avec une instruction SELECT, il ne modifie pas les données.

Cependant, si vous l’utilisez par erreur avec INSERT, UPDATE ou DELETE, les données seront modifiées comme avec une requête normale.

Par mesure de sécurité, il est recommandé de faire les analyses dans une base de données de test ou de préproduction plutôt qu’en production.

Q3. EXPLAIN seul ne suffit‑il pas ?

R. EXPLAIN suffit pour examiner le plan d’exécution « estimé ». Cependant, il ne fournit pas de valeurs mesurées comme le temps d’exécution réel ou le nombre réel de lignes.

Si vous avez besoin d’un réglage sérieux des requêtes ou de vérifier les effets d’optimisation, EXPLAIN ANALYZE est plus utile.

Q4. Quelle est la précision des valeurs comme « loops » et « actual time » ?

R. Les valeurs comme actual time et loops sont des métriques d’exécution réelles mesurées en interne par MySQL. Cependant, elles peuvent fluctuer légèrement selon les conditions du système d’exploitation, l’état du cache et la charge du serveur.

Pour cette raison, ne vous fiez pas à une seule mesure. Au lieu de cela, exécutez la requête plusieurs fois et évaluez les tendances.

Q5. Que représente exactement le « coût » ?

A.
cost est une valeur estimée calculée par le modèle de coût interne de MySQL. Elle reflète une évaluation relative des coûts CPU et I/O. Elle n’est pas exprimée en secondes.

Par exemple, si vous voyez (cost=0.3) et (cost=2.5), ce dernier est estimé comme étant plus coûteux en termes relatifs.

Q6. Quels sont les avantages d’utiliser le format JSON ou TREE ?

A.

• Format JSON : Sortie structurée facile à analyser programmatique. Utile pour les outils d’automatisation et les tableaux de bord.
• Format TREE : Rend le flux d’exécution et l’imbrication visuellement clairs. Idéal pour comprendre les requêtes complexes et l’ordre des JOIN.

Choisissez le format qui correspond le mieux à votre objectif.

Q7. Que faire si je ne peux pas améliorer les performances après avoir examiné le plan d’exécution ?

A.
Envisagez des approches supplémentaires telles que :

• Redéfinir les index (index composites ou index couvrants)
• Réécrire les requêtes (sous‑requêtes → JOINs, suppression des colonnes SELECT inutiles)
• Utiliser des vues ou des tables temporaires
• Revoir la configuration de MySQL (tailles des tampons, allocation de mémoire, etc.)

L’optimisation des performances réussit rarement avec une seule technique. Une approche globale et itérative est essentielle.