Explain MySQL : Mesurer le temps d’exécution et analyser une requête

Vous avez déjà lancé une requête MySQL en vous disant que cela prenait une éternité… mais sans vraiment savoir pourquoi ? Ou peut-être que votre site semblait devenir de plus en plus lent à mesure que vos données augmentaient ? Vous n’êtes pas seul, rassurez-vous. Comprendre la performance d’une requête MySQL est un passage obligé lorsque l’on développe une application web. Et ce n’est pas réservé aux experts en bases de données. Vous allez découvrir comment mesurer le temps d’exécution d’une requête MySQL et l’analyser avec EXPLAIN.

• Comprendre comment mesurer la vitesse d’une requête pour repérer rapidement ce qui ralentit votre site.
• Savoir analyser le comportement réel d’une requête grâce à EXPLAIN pour identifier précisément où se situe le problème.
• Découvrir comment de simples optimisations peuvent transformer une requête lente en résultat instantané.

Optimiser une requête, c’est un peu comme apprendre à faire la cuisine. Vous pouvez mélanger les ingrédients dans le désordre, mettre le feu un peu trop fort et finir avec un plat immangeable… Ou alors comprendre le fonctionnement des ustensiles, la bonne température et le bon ordre. Avec MySQL, c’est pareil : il suffit de connaître les bons outils pour éviter les catastrophes et obtenir un résultat rapide et savoureux.

Dans ce tutoriel, nous allons prendre le temps de découvrir comment mesurer le temps d’exécution d’une requête et comment analyser son comportement avec l’instruction MySQL la plus magique à ce sujet : EXPLAIN. Vous allez tout comprendre, étape par étape, avec des exemples concrets. À la fin, vous serez capable de diagnostiquer vos requêtes comme un vrai chef MySQL.

Pourquoi mesurer les performances d’une requête MySQL ?

Avant de plonger dans EXPLAIN, commençons par comprendre un point essentiel : pourquoi a-t-on besoin de mesurer le temps d’exécution d’une requête MySQL ?

Lorsque vous développez un site web, chaque clic de votre visiteur déclenche souvent plusieurs requêtes. Si une seule de ces requêtes met trop de temps à s’exécuter, l’expérience utilisateur s’effondre. Et sur internet, on sait que les visiteurs n’aiment pas attendre. Une page lente, c’est un départ assuré.

L’objectif est donc d’identifier les requêtes qui consomment beaucoup de ressources, afin de les optimiser avant qu’elles ne deviennent un goulet d’étranglement pour tout votre système.

Mesurer le temps d’exécution directement dans MySQL

Première étape : mesurer. Comme lorsque vous chronométrez un ami pendant un sprint, MySQL est capable d’afficher le temps qu’il met à traiter votre requête.

Dans la majorité des outils (PHPMyAdmin, MySQL Workbench, CLI MySQL…), le temps d’exécution est souvent affiché automatiquement juste en dessous du résultat. Vous avez sûrement déjà vu quelque chose du style :

Temps d’exécution : 0.0012 sec

C’est déjà une bonne indication. Mais on peut aller plus loin.

Le chronomètre manuel en SQL

Dans MySQL, il existe une commande très simple pour mesurer le temps d’exécution : SHOW PROFILES. Cette fonctionnalité permet de suivre le temps consommé par chaque étape d’une requête.

Voici comment l’activer :

SET profiling = 1;

Ensuite, vous exécutez votre requête MySQL, par exemple :

SELECT * FROM users WHERE email = 'contact@exemple.fr';

Puis vous affichez la liste des profils enregistrés :

SHOW PROFILES;

Vous obtiendrez un tableau avec les temps d’exécution associés aux dernières requêtes lancées.

Pour aller encore plus loin, vous pouvez analyser en détail une requête spécifique :

SHOW PROFILE FOR QUERY 1;

Où 1 correspond à l’identifiant de la requête montré dans le tableau précédent.

Cela permet de voir quelle étape exactement a consommé le plus de temps : parsing, envoi, analyse, etc. Très utile quand on veut aller au fond des choses.

Pourquoi une requête peut-elle être lente ?

Avant d’apprendre à analyser une requête, voyons pourquoi elle mettrait du temps à répondre. Généralement, on retrouve toujours les mêmes suspects :

D’abord, une requête peut être lente si elle parcourt trop de lignes dans une table. Si vous faites une recherche sur une colonne non indexée, MySQL n’a pas d’autre choix que de lire chaque ligne une par une. C’est ce qu’on appelle un FULL TABLE SCAN.

Ensuite, la requête peut devenir complexe si elle manipule de nombreux calculs, tri, regroupements ou sous-requêtes. Chaque opération ajoute du travail à MySQL.

Et enfin, les jointures, lorsqu’elles sont mal pensées, peuvent devenir de véritables casse-têtes pour la base de données, surtout si les colonnes utilisées pour relier les tables ne sont pas optimisées.

Lors d’un projet, un étudiant avait conçu une requête avec six jointures. Tout se passait bien… jusqu’à ce que la base dépasse les 5 000 lignes. Le serveur, pourtant costaud, se mettait à souffler. Il a fini par découvrir que les colonnes liées n’avaient aucun index. Une fois ajoutés, la requête est passée de 20 secondes… à moins d’une demi-seconde.

EXPLAIN : votre arme secrète pour analyser une requête MySQL

Voilà maintenant la partie la plus passionnante et la plus utile : EXPLAIN.

Cette commande permet de voir ce que MySQL va faire lorsqu’il exécute une requête. C’est un peu comme regarder les coulisses d’un spectacle avant que le rideau ne se lève. Vous saurez exactement comment MySQL va chercher vos données.

Pour l’utiliser, il suffit simplement de placer EXPLAIN devant une requête :

EXPLAIN SELECT * FROM users WHERE email = 'contact@exemple.fr';

Vous allez obtenir un tableau. Il ne paie peut-être pas de mine au premier regard, mais il contient des informations précieuses : quel type d’accès MySQL utilise, combien de lignes il prévoit de lire, quels index seront exploités, etc.

C’est en décortiquant ces informations que l’on peut détecter les points faibles d’une requête.

Comprendre les colonnes du résultat d’EXPLAIN

Vous venez de lancer EXPLAIN et MySQL vous affiche plusieurs colonnes. Pas de panique, nous allons les découvrir ensemble, pas à pas comment interpréter ces fameuses colonnes : table, type, possible_keys, key, rows, Extra…

Le tableau affiché vous semble peut-être un peu austère. Pourtant, chaque colonne raconte une petite histoire sur la manière dont MySQL va traiter votre requête. Prenons le temps de les analyser tranquillement.

La colonne table

C’est la table concernée par cette ligne d’analyse. Si votre requête fait plusieurs jointures, vous verrez une ligne par table. Cela permet d’observer dans quel ordre MySQL va parcourir les données.

Par exemple :

EXPLAIN SELECT * FROM commandes
JOIN clients ON commandes.id_client = clients.id;

Le résultat indiquera d’abord qu’il va chercher dans la table commandes, puis dans la table clients (à moins qu’il décide de faire l’inverse selon les index). L’ordre compte, car il détermine combien de fois MySQL aura besoin de parcourir une table.

La colonne type

C’est l’une des plus importantes. Elle décrit le type d’accès utilisé pour chercher les données. Pour simplifier, plus on se rapproche du haut de ce classement, mieux la requête est optimisée. Voici les principaux types, du plus performant au moins performant :

• system ou const : ultra rapide, car MySQL sait exactement où chercher
• eq_ref, ref : index utilisé efficacement
• range : lecture d’une portion de la table via un index
• index : lecture de tout l’index
• ALL : lecture complète de la table (le pire cas)

Si vous voyez ALL, cela signifie que MySQL va scanner toutes les lignes de la table. Quand la table contient 200 lignes, ça passe. Mais si elle en contient 2 millions, c’est une autre histoire.

Voici un exemple volontairement mauvais :

EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'contact@exemple.fr';

Parce que l’on applique une fonction sur la colonne email, MySQL ne peut pas exploiter l’index (s’il existe). Et là, le type devient souvent ALL. Une petite modification simple :

EXPLAIN SELECT * FROM users WHERE email = 'contact@exemple.fr';

Et hop ! MySQL peut exploiter l’index sur email. Le type passe en ref, le nombre de lignes parcourues devient minime. C’est le genre de petites améliorations qui changent tout.

La colonne rows

Celle-ci indique combien de lignes MySQL pense devoir lire pour trouver votre résultat. Si ce nombre explose, cela doit immédiatement vous alerter. Ce n’est qu’une estimation statistique, mais en général elle est assez fiable.

Si vous voyez rows = 500 000 sur une requête, vous savez instantanément que MySQL va parcourir un marathon. Ce chiffre doit être le plus bas possible, grâce à de bons index et une condition pertinente.

Les colonnes possible_keys et key

Ici, les choses deviennent vraiment intéressantes.

• possible_keys indique les index que MySQL pourrait utiliser
• key indique l’index réellement utilisé

Si possible_keys contient un index pertinent mais que key reste vide, cela signifie que MySQL refuse de l’utiliser. Il ne le juge pas adapté pour cette requête.

C’est un peu comme si vous aviez une Ferrari dans votre garage, mais que vous décidiez d’aller au travail à pied. C’est dommage… et surtout, c’est lent.

La colonne Extra

C’est une boîte à informations complémentaires. Parmi les mentions les plus importantes, retenez :

• Using index : parfait, le résultat peut être renvoyé sans lire la table
• Using where : MySQL filtre les résultats
• Using temporary : MySQL crée une table temporaire (souvent mauvais signe)
• Using filesort : MySQL doit trier manuellement les résultats (peut devenir très coûteux)

Lorsque vous voyez Using filesort, cela signifie que MySQL ne peut pas exploiter un index pour trier les données. Un ORDER BY mal optimisé est l’une des causes les plus courantes de lenteur sur un site web.

Voici un exemple typique à éviter :

EXPLAIN SELECT * FROM produits ORDER BY nom;

Si la colonne nom n’est pas indexée, MySQL trie tout « à l’ancienne ». Avec des milliers de produits, c’est un cauchemar.

Un simple index sur nom résout souvent le problème :

ALTER TABLE produits ADD INDEX (nom);

Et EXPLAIN devient soudainement beaucoup plus souriant.

Exemple complet d’analyse avec EXPLAIN

Prenons une requête un peu plus réaliste :

EXPLAIN SELECT commandes.id, clients.nom
FROM commandes
JOIN clients ON commandes.id_client = clients.id
WHERE commandes.status = 'en cours'
ORDER BY commandes.date_creation DESC;

On se rend compte que l’on cumule plusieurs éléments :

• une condition WHERE
• une jointure
• un tri

Avec EXPLAIN, on peut directement voir si MySQL exploite :

• un index sur commandes.status
• un index sur commandes.id_client
• un index sur commandes.date_creation
• la clé primaire sur clients.id

Si l’un d’eux manque, le diagnostic saute aux yeux. Et vous savez exactement où agir. Ce n’est plus l’optimisation à l’aveugle, mais du travail chirurgical.

Le lien essentiel entre les index et les performances

À ce stade, vous l’avez sans doute compris : les index sont le cœur de l’optimisation des requêtes MySQL.

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?

Un index, c’est comme un annuaire trié par nom. Sans index, vous seriez obligé de lire toutes les pages pour trouver quelqu’un. Avec, deux secondes suffisent. MySQL fonctionne exactement comme ça.

Mais attention, il ne faut pas ajouter des index n’importe comment. Trop d’index ralentissent l’écriture (INSERT, UPDATE). Ce serait comme multiplier les tables d’un restaurant : on sert plus vite, mais on n’a plus de place pour circuler.

La règle est simple :

• Indexez uniquement les colonnes utilisées
• dans WHERE, JOIN, ORDER BY ou GROUP BY

Vous gagnerez en performance… sans sacrifier les ressources.

Pour aller plus loins : Optimiser l’indexation MySQL

Mesurer à nouveau après une optimisation

Après chaque modification (ajout d’un index, simplification d’une jointure, réécriture d’une requête), il ne faut jamais se contenter de dire « ça doit être plus rapide ». Il faut vérifier.

Vous lancez à nouveau SHOW PROFILES, vous regardez le temps d’exécution, vous relancez EXPLAIN. Et surtout, vous observez si :

• le type d’accès s’améliore
• le nombre estimé de lignes diminue
• les mentions négatives disparaissent de Extra

C’est comme tester une recette après chaque ajustement : un peu moins de sel ici, une cuisson plus douce là… jusqu’à ce que ce soit parfait.

Aller encore plus loin : EXPLAIN ANALYZE

Depuis quelques versions de MySQL (et MariaDB), il existe une évolution d’EXPLAIN qui donne des informations encore plus détaillées : EXPLAIN ANALYZE.

Là où EXPLAIN classique montre « ce que MySQL compte faire », EXPLAIN ANALYZE montre ce que MySQL a réellement fait, avec le temps utilisé pour chaque étape.

Exemple d’utilisation :

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'contact@exemple.fr';

Et là, MySQL va vous afficher :

• Combien de lignes ont été lues réellement
• Combien de temps chaque opération a pris
• Pourquoi un index n’a pas été utilisé (si c’est le cas)

C’est un peu comme si vous assistiez en direct à la trajectoire de la requête dans la base. On ne se contente plus de suppositions, on a des mesures réelles.

C’est un outil extrêmement puissant pour les requêtes qui se comportent bizarrement. Parfois, MySQL prévoit de lire 200 lignes… mais en réalité il en lit 10 000. Et là, vous savez que quelque chose cloche.

Les pièges classiques dans l’analyse des requêtes

Analyser une requête, c’est aussi éviter certains pièges qui peuvent induire en erreur. Voici quelques situations fréquentes que vous rencontrerez sûrement un jour.

Penser qu’une requête rapide est forcément optimisée

Sur une petite base, tout est rapide. Une requête qui parcourt 50 000 lignes ne prendra peut-être qu’une milliseconde. Mais lorsque votre base en contiendra 20 millions, vous ne rigolerez plus.

Moralité : testez toujours sur un volume réaliste, ou du moins considérez ce qui pourrait arriver lorsque la base grandira.

Ajouter des index partout

C’est très tentant. Mais souvenez-vous, un index prend de la place et ralentit les opérations d’écriture. Si vous indexez 15 colonnes dans une table qui fait un million de lignes, votre serveur va grimacer.

Il faut trouver le bon équilibre : ne garder que les index réellement utiles aux requêtes fréquentes ou critiques.

Se fier uniquement au temps affiché

Un temps d’exécution peut varier selon

• le cache de MySQL
• la charge du serveur
• les E/S disque
• le réseau (si base distante)

C’est pour cela que EXPLAIN (ou EXPLAIN ANALYZE) est indispensable : il montre la logique interne, qui ne change pas selon l’humeur du serveur.

Une méthode simple et efficace pour optimiser une requête MySQL

Pour que ce guide soit vraiment utile, voici une démarche claire et reproductible. Vous pouvez l’utiliser pour diagnostiquer n’importe quelle requête :

1. Lancer la requête et noter le temps d’exécution
2. Faire un EXPLAIN de la requête
3. Identifier les points faibles :
   • type = ALL
   • rows trop élevé
   • Using filesort / Using temporary
   • index ignorés
4. Modifier la requête ou ajouter un index
5. Mesurer à nouveau avec SHOW PROFILES et EXPLAIN ANALYZE si possible
6. Répéter jusqu’à un résultat satisfaisant

Vous voyez ? Rien de magique. Juste de la logique, un peu de patience… et les bons outils.

Exemple complet avant / après optimisation

C’est maintenant que l’on va vraiment voir l’intérêt de cette démarche.

Imaginons une base d’articles de blog comme celle de votre Créa-Blog, avec une table articles. On veut afficher les 20 articles les plus récents publiés par un auteur donné.

Voici la requête initiale :

SELECT * FROM articles
WHERE auteur = 'Alban'
ORDER BY date_creation DESC
LIMIT 20;

On lance EXPLAIN, et le verdict tombe :

• type = ALL
• rows = 250000
• Extra = Using filesort

MySQL va donc lire tous les articles, puis trier par date, puis filtrer par auteur. Et seulement ensuite, il prendra les 20 premiers. Ce n’est pas efficace.

On ajoute deux index :

ALTER TABLE articles ADD INDEX (auteur);
ALTER TABLE articles ADD INDEX (date_creation);

On relance EXPLAIN :

• type = ref (MySQL filtre d’abord par auteur)
• rows = 200 (au lieu de 250000)
• Using where + Using index (plus de filesort)

La requête est maintenant optimisée, même si elle reste perfectible. On pourrait créer un index composite pour encore plus d’efficacité :

ALTER TABLE articles ADD INDEX(auteur, date_creation);

MySQL lit directement les articles d’Alban dans l’ordre désiré, sans tri ni lecture complète. Le temps passe de plusieurs centaines de millisecondes à quelques millisecondes.

Voilà la magie de l’analyse + index bien pensés.

Autre cas : optimiser une jointure

Prenons un exemple encore plus fréquent : lister les commandes avec le nom du client.

Requête de départ :

SELECT c.id, u.nom
FROM commandes c
JOIN users u ON c.id_client = u.id;

Si c.id_client n’est pas indexé (ou si u.id ne l’est pas, mais heureusement id est souvent la clé primaire), EXPLAIN affichera probablement :

• type = ALL sur la table commandes
• rows énorme (selon le volume)
• Possible_keys vide

On ajoute un index :

ALTER TABLE commandes ADD INDEX (id_client);

Puis on relance EXPLAIN. Cette seule action peut diviser le temps d’exécution par 10, 100, voire beaucoup plus selon les données.

Ce genre de situation arrive plus souvent que l’on croit. On pense que parce qu’une colonne sert à une jointure, elle est automatiquement indexée. Mais non. Et c’est une erreur qui coûte cher sur le long terme.

J’ai déjà vu un développeur paniquer parce que son API mettait 30 secondes à renvoyer les commandes d’un client. Il avait passé des jours à chercher une solution compliquée : optimiser le code PHP, ajouter du caching, changer de serveur… Une seule commande SQL, un EXPLAIN, un index ajouté. Moins de 50 millisecondes après.

La morale ? Toujours commencer par analyser la requête.

Garder de bonnes habitudes pour le futur

Optimiser une requête MySQL n’est pas un exercice ponctuel. C’est une routine à intégrer à votre façon de développer. Dès que vous travaillez avec des données, vous avez intérêt à garder en tête quelques bons réflexes.

D’abord, avant d’ajouter une nouvelle requête dans votre projet, posez-vous une seule question : y a-t-il un risque qu’elle devienne lente si la base grandit ? Si la réponse est oui, prenez le temps de vérifier l’existence d’un index adéquat, ou au moins de réfléchir à la structure des données.

Ensuite, souvenez-vous que le comportement d’une requête peut changer au fil du temps. Une requête rapide au lancement du projet peut devenir très agressive pour MySQL six mois plus tard. Revenir faire un EXPLAIN et un coup d’œil sur SHOW PROFILES ne fait jamais de mal.

Enfin, rappelez-vous que le but n’est pas de rendre toutes les requêtes parfaites, mais de bien identifier celles qui sont réellement stratégiques : affichage d’une page vue des milliers de fois par jour, mise à jour importante, module essentiel… C’est là que vous gagnez réellement en performance.

Apprendre à maîtriser EXPLAIN et l’analyse de requête MySQL, c’est un peu comme apprendre à lire une carte pour naviguer. D’abord, on avance lentement, on cherche son chemin. Puis, à force d’observer, tout devient plus clair, plus évident, presque naturel.

Une vision globale de l’analyse de requêtes MySQL

À présent, vous pouvez dire que vous savez :

• Comment mesurer le temps d’exécution d’une requête avec SHOW PROFILES
• Comment analyser une requête MySQL en détail grâce à EXPLAIN
• Pourquoi un index peut transformer une requête lente en un éclair
• Comment déjouer les pièges classiques (full table scan, filesort, jointures mal pensées)
• Quand utiliser EXPLAIN ANALYZE pour voir ce qui se passe réellement

À travers les exemples, nous avons vu que souvent, une simple ligne SQL ajoutée ou modifiée suffit à faire la différence. L’important est d’adopter une méthode : mesurer, analyser, ajuster, tester à nouveau.

Avec le temps et la pratique, vous finirez par repérer rapidement les requêtes dangereuses, parfois même avant de les exécuter. Et ça, c’est un vrai signe de maturité dans le développement d’applications.

Vous venez de franchir une étape essentielle dans votre compréhension de MySQL. Beaucoup pensent que la performance est un sujet réservé aux experts, mais vous avez désormais vu que grâce à quelques outils bien choisis, tout devient accessible, même lorsque l’on débute.

Analyser une requête MySQL avec EXPLAIN, c’est comme ouvrir un livre en découvrant les coulisses d’une histoire. On comprend pourquoi elle est lente, quel chemin elle emprunte, où elle trébuche. Et surtout, on apprend à l’aider à aller droit au but. Mesurer le temps d’exécution devient alors un réflexe naturel, et les optimisations deviennent un terrain de jeu où l’on progresse pas à pas.

Je vous souhaite d’aborder vos prochaines requêtes comme un enquêteur passionné. Lorsque vous verrez une page s’afficher plus vite grâce à un index bien placé ou une condition revue intelligemment, vous aurez ce petit sentiment de victoire qui rend le métier de développeur tellement satisfaisant.

Et surtout, souvenez-vous de cette idée simple : une bonne requête MySQL se construit avec méthode, curiosité… et un EXPLAIN toujours à portée de main.

Alban

Fondateur de l’agence Créa-troyes, affiliée France Num
Intervenant en Freelance.
Contactez-moi

crea-troyes.fr