MySQL EXPLAIN expliqué : comment lire les plans d’exécution et optimiser les requêtes SQL

1. Qu’est‑ce que MySQL EXPLAIN ? Pourquoi devriez‑vous l’utiliser ?

Qu’est‑ce que EXPLAIN ? Une commande pour visualiser les plans d’exécution

Dans MySQL, EXPLAIN est une commande utilisée pour visualiser la façon dont une requête SQL est exécutée. Elle est particulièrement utile pour comprendre comment les données sont récupérées dans les instructions SELECT, et elle affiche le plan d’exécution de la requête.

Par exemple, lors de l’exécution d’une requête telle que SELECT * FROM users WHERE age > 30, EXPLAIN vous permet de voir des détails internes comme l’index que MySQL utilise et dans quel ordre les tables sont parcourues.

L’utilisation est simple — il suffit d’ajouter EXPLAIN au début de votre requête.

EXPLAIN SELECT * FROM users WHERE age > 30;

Lorsque vous écrivez ainsi, plusieurs colonnes décrivant le plan d’exécution de la requête seront affichées. Chaque élément sera expliqué en détail dans les sections suivantes.

Pourquoi l’utiliser : rendre visibles les causes de requêtes lentes

Une erreur courante que font de nombreux développeurs est de supposer que « si le SQL fonctionne, il n’y a pas de problème ». Cependant, l’exécution lente des requêtes peut nuire aux performances globales de l’application.

Dans les systèmes qui manipulent de gros volumes de données, même une seule requête inefficace peut devenir un goulot d’étranglement et imposer une charge importante au serveur.

C’est là qu’EXPLAIN devient extrêmement utile. En examinant le plan d’exécution, vous pouvez clairement voir si un scan complet de table est effectué ou si les index sont correctement exploités.

En d’autres termes, l’utilisation d’EXPLAIN vous permet d’identifier les goulots d’étranglement de performance et de déterminer comment les optimiser. L’efficacité des index, en particulier, devient beaucoup plus claire lors de l’analyse de la sortie d’EXPLAIN.

Instructions SQL prises en charge par EXPLAIN (SELECT, UPDATE, etc.)

EXPLAIN fonctionne non seulement avec les instructions SELECT mais aussi avec les instructions SQL suivantes :

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE

Par exemple, lors de l’exécution d’une instruction DELETE sur un grand jeu de données, si les index ne sont pas correctement utilisés, MySQL peut effectuer un scan complet de la table, augmentant considérablement le temps d’exécution. Pour éviter de tels problèmes, il est très efficace de vérifier le plan d’exécution avec EXPLAIN avant d’exécuter des instructions DELETE ou UPDATE.

Selon votre version de MySQL, vous pouvez également utiliser EXPLAIN ANALYZE, qui fournit des informations d’exécution encore plus détaillées. Cela sera abordé plus loin dans l’article.

2. Comprendre les colonnes de sortie d’EXPLAIN (avec image illustrée)

Liste et explication des colonnes de sortie de base

La sortie d’EXPLAIN comprend les colonnes suivantes (légèrement différentes selon la version de MySQL) :

Parmi celles‑ci, les quatre colonnes les plus importantes pour l’optimisation des performances sont type / key / rows / Extra.

Comment lire les quatre colonnes clés : type / key / rows / Extra

1. type (méthode d’accès)

Cette colonne indique comment MySQL accède à la table. Elle influence directement les performances.

Si type = ALL, cela signifie qu’aucun index n’est utilisé et que toutes les lignes sont parcourues — la méthode d’accès la plus lente. Idéalement, vous devriez optimiser les requêtes vers ref ou const.

2. key (index utilisé)

Cette colonne affiche le nom de l’index réellement utilisé.
Si rien n’est affiché, la requête ne semble probablement pas utiliser d’index.

3. rows (lignes estimées à scanner)

Elle indique le nombre de lignes que MySQL estime devoir parcourir. Plus le nombre est élevé, plus le temps d’exécution tend à être long. L’objectif est d’optimiser votre requête afin que rows soit aussi proche de 1 que possible.

4. Extra (informations supplémentaires)

La colonne Extra comprend des détails additionnels tels que des opérations de tri ou l’utilisation de tables temporaires.

Si vous voyez Using temporary ou Using filesort, vous devez revoir votre instruction SQL ou la conception de vos index.

[Illustration] Exemple de sortie EXPLAIN

EXPLAIN SELECT * FROM users WHERE age > 30;

Dans cet exemple, bien que l’index (age_index) existe, il n’est pas réellement utilisé, ce qui entraîne ALL (scan complet de la table). Cela indique qu’il y a de la place pour optimisation.

3. Apprendre par l’exemple : Comment utiliser EXPLAIN et interpréter les résultats

Exemple 1 : Sortie EXPLAIN pour une requête SELECT simple (Avec explication)

Commençons par une requête SELECT simple sur une seule table.

EXPLAIN SELECT * FROM users WHERE age > 30;

Supposons que la sortie EXPLAIN ressemble à ceci :

Explication :

• type: ALL → Balayage complet de la table. Aucun index n’est utilisé.
• key: NULL → Aucun index n’est réellement utilisé.
• rows: 5000 → MySQL estime qu’il balayera environ 5 000 lignes.

Comment améliorer :

En ajoutant un index à la colonne age, vous pouvez améliorer significativement les performances de la requête.

CREATE INDEX idx_age ON users(age);

Si vous exécutez à nouveau EXPLAIN, vous devriez voir type changer en range ou ref, confirmant que l’index est maintenant utilisé.

Exemple 2 : Analyser la sortie EXPLAIN pour une requête avec JOIN

Ensuite, examinons un exemple qui joint plusieurs tables.

EXPLAIN
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Sortie d’exemple :

Explication :

• La table users effectue un balayage complet ( ALL ), c’est donc la partie à améliorer.
• Pendant ce temps, la table orders utilise un index avec ref , ce qui est efficace.

Points d’optimisation :

• Ajouter un index sur users.age peut accélérer le balayage de la table users.
• La clé est de concevoir des index de manière à ce que la clause WHERE puisse filtrer les lignes avant le JOIN .

Quand les index ne sont pas utilisés (Mauvais exemple → Bon exemple)

Mauvais exemple : Clause WHERE utilisant une fonction

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

Avec une requête comme celle-ci, l’index devient inutilisable car la fonction DATE() transforme la valeur de la colonne, empêchant MySQL d’utiliser l’index efficacement.

Exemple amélioré : Spécifier une plage sans utiliser de fonction

SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

Cela active l’index sur la colonne created_at, permettant à MySQL de récupérer les données efficacement.

Conclusion : Utiliser de vrais exemples EXPLAIN pour diagnostiquer les performances

En analysant la sortie EXPLAIN dans des requêtes réelles, vous pouvez identifier clairement où se trouvent les goulots d’étranglement et comment les optimiser.

• ALL → Balayage complet. Envisagez d’ajouter ou d’ajuster des index.
• key = NULL → Index non utilisé. Nécessite une investigation.
• Extra contient Using temporary → Avertissement de performance.
• Utiliser des fonctions ou des calculs dans les conditions peut désactiver l’utilisation des index.

Garder ces points à l’esprit vous aidera à améliorer continuellement les performances des requêtes avec EXPLAIN.

4. Techniques pratiques d’optimisation de requêtes basées sur les résultats EXPLAIN

Bases de conception d’index pour éviter « type: ALL »

Si EXPLAIN affiche type: ALL, cela signifie que MySQL effectue un balayage complet de la table. C’est une opération très coûteuse, et elle devient un goulot d’étranglement majeur pour les tables contenant des milliers à des millions de lignes.

Comment l’éviter :

• Ajouter des index aux colonnes utilisées dans la clause WHERE

  CREATE INDEX idx_age ON users(age);
  

• Si vous avez plusieurs conditions, envisagez un index composite

  CREATE INDEX idx_status_created ON orders(status, created_at);
  

• Éviter les motifs LIKE qui ne commencent pas par un préfixe

  -- Bad example (index won’t work)
  WHERE name LIKE '%tanaka%'
  
  -- Good example (index may work)
  WHERE name LIKE 'tanaka%'
  

Ce que signifie « Extra: Using temporary » et comment le corriger

Si la colonne Extra affiche « Using temporary », cela signifie que MySQL crée une table temporaire en interne pour traiter la requête. Cela se produit souvent lorsque des opérations telles que GROUP BY ou ORDER BY ne peuvent pas être gérées par les index seuls, de sorte que MySQL doit utiliser un stockage temporaire pour organiser les données manuellement.

Comment le corriger :

• Appliquer des index aux colonnes utilisées dans GROUP BY et ORDER BY

  CREATE INDEX idx_group_col ON sales(department_id);
  

• Supprimez le tri ou le GROUP BY inutiles de votre SQL
• Utilisez LIMIT ou des sous‑requêtes pour réduire les données cibles

Comprendre ce que les « rows » et « key » vous indiquent pour améliorer les performances

La colonne rows indique combien de lignes MySQL prévoit de lire dans la table. Par exemple, une requête affichant rows = 100000 peut avoir un impact significatif sur les performances.

Lorsque cette valeur est élevée, vous devez probablement appliquer des index qui réduisent le nombre de lignes scannées ou réécrire vos conditions.

En revanche, la colonne key indique l’index réellement utilisé. Si elle est NULL, c’est un avertissement qu’aucun index n’est utilisé.

Liste de contrôle d’optimisation :

• Si rows est grand → Vos filtres sont‑ils efficaces ? Les index sont‑ils correctement utilisés ?
• Si key = NULL → Utilisez‑vous des motifs dans WHERE/JOIN qui empêchent l’utilisation d’un index ?

Faire de EXPLAIN et de l’optimisation une habitude

Pour optimiser les requêtes efficacement, l’approche de base consiste à répéter ce cycle : écrire → vérifier avec EXPLAIN → améliorer → vérifier à nouveau.

Gardez ce flux de travail à l’esprit :

1. Écrivez la requête normalement
2. Vérifiez le plan d’exécution avec EXPLAIN
3. Examinez type, key, rows et Extra
4. S’il y a un goulot d’étranglement, révisez les index ou réécrivez la requête
5. Exécutez à nouveau EXPLAIN pour confirmer les améliorations

Les performances des requêtes sont influencées non seulement par les index mais aussi par la façon dont la requête est écrite. Des comparaisons simples (au lieu de fonctions) et des conditions directes peuvent être étonnamment efficaces.

5. Analyse visuelle avec MySQL Workbench Visual EXPLAIN

Vérifier les plans d’exécution visuellement avec un outil GUI

MySQL Workbench est un outil GUI spécialisé dans l’administration et le développement MySQL. L’un de ses plus grands avantages est qu’il peut afficher visuellement les plans d’exécution, souvent difficiles à lire dans la sortie du terminal.

Avec Visual EXPLAIN, vous pouvez examiner les informations suivantes dans une structure arborescente :

• Ordre d’accès de chaque table
• Type de JOIN utilisé
• État d’utilisation des index
• Indication d’un scan complet de table
• Opérations de filtrage et de tri des données

Parce que le plan est affiché graphiquement, même les débutants peuvent identifier plus facilement où se trouvent les goulots d’étranglement de performance.

[With Images] Comment utiliser et lire Visual EXPLAIN (étape par étape)

Suivez ces étapes pour utiliser Visual EXPLAIN :

1. Lancez MySQL Workbench et ouvrez votre connexion à la base de données → Assurez‑vous que la connexion est configurée à l’avance.
2. Saisissez votre requête cible dans l’éditeur SQL

   SELECT * FROM users WHERE age > 30;
   

3. Cliquez sur l’icône “EXPLAIN VISUAL” à côté du bouton EXPLAIN → Ou faites un clic droit et sélectionnez “Visual Explain” dans le menu.
4. Le plan d’exécution sera affiché visuellement Lorsque vous cliquez sur chaque nœud (table), des informations détaillées telles que les suivantes apparaissent :

• Méthode d’accès (ALL, ref, range, etc.)
• Index utilisé
• Lignes estimées (rows)
• Conditions de filtrage et méthode de JOIN

Note :
Dans Visual EXPLAIN, les couleurs et icônes des nœuds aident à mettre en évidence les opérations lourdes ou les parties inefficaces.
Portez une attention particulière aux nœuds surlignés en rouge, car ils indiquent généralement des problèmes de performance.

Même les débutants peuvent facilement trouver les goulots d’étranglement

La sortie EXPLAIN basée sur du texte peut sembler accablante au départ, mais Visual EXPLAIN fait ressortir visuellement les zones problématiques.

Par exemple, il devient plus facile d’identifier :

• Tables utilisant type: ALL
• Blocs de requête affichant Using temporary
• Modèles avec des JOINs inutiles
• Tables où les index ne sont pas utilisés

Avec son interface GUI, vous pouvez rapidement formuler des hypothèses d’optimisation, et c’est aussi utile pour partager et examiner les performances SQL au sein d’une équipe.

Visual EXPLAIN est particulièrement précieux pour les utilisateurs SQL débutants à intermédiaires.
Si vous n’êtes pas sûr de la façon d’interpréter les résultats d’EXPLAIN, essayez d’utiliser cette fonctionnalité.

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

Q1. Quand devrais‑je utiliser EXPLAIN ?

A. Vous devez utiliser EXPLAIN chaque fois que vous avez un doute sur la vitesse d’exécution d’une requête — surtout si une requête « semble lente ». C’est également utile lorsque vous voulez vérifier si une requête nouvellement créée utilise correctement les index.

En vérifiant le plan d’exécution avant le déploiement, vous pouvez identifier les risques de performance tôt.

Q2. La sortie indique type = ALL. Que faire ?

A. type: ALL signifie que MySQL effectue un scan complet de la table. C’est une opération coûteuse qui peut dégrader considérablement les performances, surtout sur les grandes tables.

Envisagez les actions suivantes :

• Ajouter des index aux colonnes utilisées dans la clause WHERE
• Éviter les fonctions ou opérations qui désactivent l’utilisation des index
• Éviter SELECT * et ne récupérer que les colonnes nécessaires

Q3. « Using temporary » dans la colonne Extra est‑il un problème ?

A. Using temporary indique que MySQL crée en interne une table temporaire pour traiter la requête. Cela se produit souvent avec GROUP BY ou ORDER BY, et cela peut augmenter les coûts de mémoire et d’E/S disque.

Les solutions possibles incluent :

• Ajouter des index aux colonnes utilisées dans GROUP BY / ORDER BY
• Réduire le tri ou l’agrégation inutiles
• Utiliser LIMIT ou des sous‑requêtes pour réduire le jeu de données

Q4. Comment utiliser Visual EXPLAIN ?

A. Vous pouvez utiliser l’outil officiel MySQL « MySQL Workbench » pour visualiser facilement les résultats d’EXPLAIN dans une interface graphique. Il suffit de saisir votre requête et de cliquer sur le bouton « Visual Explain ».

Ceci est particulièrement recommandé pour :

• Les utilisateurs qui trouvent la sortie texte d’EXPLAIN difficile à lire
• Ceux qui souhaitent comprendre visuellement des JOIN complexes
• Les équipes qui examinent les performances SQL ensemble

Q5. Pourquoi mon index n’est‑il pas utilisé alors qu’il existe ?

A. Même si un index existe, MySQL ne l’utilise pas toujours. Les index peuvent être ignorés dans les cas suivants :

• Utiliser des fonctions ou expressions dans la clause WHERE (par ex., WHERE YEAR(created_at) = 2024 )
• Faible cardinalité (distribution de valeurs faible), où un scan complet est considéré plus rapide
• L’ordre des colonnes ne correspond pas à la définition d’un index composite

Pour confirmer qu’un index est utilisé correctement, vérifiez toujours la colonne key dans EXPLAIN.

7. Résumé : Utilisez EXPLAIN pour découvrir les opportunités d’optimisation SQL

L’optimisation des performances dans MySQL ne consiste pas seulement à ajouter des index.
L’outil essentiel pour identifier quelles requêtes sont des goulets d’étranglement, pourquoi elles sont lentes et comment les corriger est EXPLAIN.

Dans cet article, nous avons couvert les points clés suivants :

✅ Le rôle et l’utilisation de base d’EXPLAIN

• Ajoutez simplement EXPLAIN avant une requête pour vérifier son plan d’exécution
• Des problèmes tels que les scans complets (ALL) et Using temporary deviennent visibles

✅ Comment lire les colonnes de sortie et évaluer les performances

• Les quatre colonnes les plus importantes sont type, key, rows et Extra
• Évitez les scans complets de table et visez une utilisation correcte des index
• Soyez prudent lorsque vous voyez Using temporary ou Using filesort

✅ Diagnostic pratique et optimisation à travers des exemples réels

• Il ne s’agit pas seulement d’ajouter des index, mais aussi d’améliorer la syntaxe SQL
• Même les requêtes complexes avec JOIN ou sous‑requêtes peuvent être analysées avec EXPLAIN
• Affiner continuellement les requêtes en fonction des plans d’exécution est le moyen le plus rapide d’améliorer les performances

✅ Utiliser des outils GUI pour une confirmation visuelle

• Utilisez « Visual EXPLAIN » dans MySQL Workbench pour visualiser graphiquement les plans d’exécution
• Plus facile pour les débutants d’identifier visuellement les goulets d’étranglement
• Utile pour les discussions d’équipe et les revues de performance SQL

✅ Couverture FAQ pour les scénarios réels

• Explication des causes et solutions pour des problèmes comme type=ALL et key=NULL
• Fourniture d’exemples expliquant pourquoi les index peuvent ne pas être utilisés

✍️ Faites d’EXPLAIN une habitude pour améliorer vos compétences SQL

Si vous prenez l’habitude de vérifier les requêtes avec EXPLAIN chaque fois que vous écrivez du SQL, vous commencerez naturellement à écrire des requêtes plus rapides et plus efficaces.

Ce n’est pas seulement une astuce technique — c’est une partie du développement d’une maîtrise professionnelle du SQL.

• Exécutez EXPLAIN immédiatement après avoir rédigé une requête
• Corrigez immédiatement les plans d’exécution suspects
• Concevez soigneusement des index efficaces

En maîtrisant ce cycle, vos compétences MySQL s’amélioreront progressivement.

Nous espérons que cet article devienne votre première étape vers une meilleure optimisation des requêtes.

Si vous avez des questions ou si vous souhaitez que d’autres sujets soient abordés, n’hésitez pas à laisser un commentaire!