Si vous avez déjà essayé d'accélérer une requête lente, on vous a probablement conseillé de « regarder le plan d'exécution ». Un bon conseil, certes, mais si vous n'en avez jamais lu, le résultat peut paraître incompréhensible. Cet article explique ce que sont les plans d'exécution, comment les interpréter et comment les outils modernes peuvent simplifier considérablement ce processus.
Qu'est-ce qu'un plan d'exécution de requête ?
Lorsque vous exécutez une requête SQL, votre base de données ne se met pas en marche subitement en parcourant les tables au hasard. Au contraire, l'optimiseur de requêtes, un composant intégré à tous les principaux moteurs de base de données, évalue des dizaines, voire des centaines, de stratégies possibles pour récupérer vos données, estime le coût de chacune et choisit la plus rapide. Le plan d'exécution est la trace écrite de cette décision dans la base de données.
On peut le comparer à un itinéraire GPS. Si vous demandez à votre GPS de vous rendre d'un point A à un point B, il calculera le meilleur itinéraire ; les instructions détaillées qu'il vous donne constituent le plan. Comprendre ce plan vous permet de savoir si vous êtes sur une autoroute ou un chemin de terre, et surtout, où se trouvent les goulots d'étranglement.
Comment générer un plan d'exécution
Dans la plupart des bases de données, un plan d'exécution est généré en préfixant la requête par le mot-clé EXPLAIN. MySQL, PostgreSQL, MariaDB et SQLite prennent en charge cette syntaxe, tandis que SQL Server utilise la commande SET SHOWPLAN_ALL ON ou le bouton d'affichage du plan d'exécution graphique. Le résultat varie selon la base de données, mais les concepts sous-jacents restent les mêmes.
Par exemple, la requête EXPLAIN SELECT * FROM orders WHERE customer_id = 42 n'exécutera pas la requête elle-même. Elle renverra plutôt une description de la manière dont la base de données prévoit de l'exécuter. Vous pouvez ensuite utiliser cette description pour déterminer si une optimisation est nécessaire.
Points clés à surveiller
Les plans d'exécution peuvent être impressionnants au premier abord, car ils contiennent une grande quantité d'informations. Heureusement, la plupart des problèmes de performance se manifestent par quelques signes révélateurs :
Il faut d'abord vérifier la présence d'une analyse complète de la table, souvent appelée ALL dans MySQL ou Seq Scan dans PostgreSQL. Cela signifie que la base de données lit chaque ligne d'une table pour trouver celles dont elle a besoin — l'équivalent de fouiller toute une bibliothèque livre par livre plutôt que d'utiliser le catalogue. Sur les petites tables, cela est sans conséquence, mais sur les grandes, c'est presque toujours un problème.
Ensuite, il convient de prêter attention au nombre estimé de lignes. Les estimations de l'optimiseur déterminent ses décisions ; par conséquent, si l'estimation est très éloignée de la réalité, le plan choisi risque d'être inefficace. Un écart important indique généralement que les statistiques sont obsolètes, ce que vous pouvez corriger en exécutant une commande ANALYZE ou UPDATE STATISTICS selon votre base de données.
Enfin, examinez les types de jointures. Les jointures par boucles imbriquées sont performantes lorsque l'un des côtés de la jointure est petit, tandis que les jointures par hachage sont plus adaptées aux grands ensembles de données. Si le plan d'exécution choisit une boucle imbriquée entre deux grandes tables, l'ajout d'un index approprié ou la restructuration de la requête peuvent inciter l'optimiseur à utiliser une stratégie plus efficace.
Fonctionnalité d'explication visuelle de Navicat
La lecture des résultats bruts de la commande EXPLAIN sous forme de texte brut exige de la pratique et de la patience. Navicat 17 résout ce problème grâce à une fonctionnalité d'explication visuelle considérablement améliorée qui transforme les données du plan d'exécution en une représentation graphique, facilitant ainsi leur compréhension en un coup d'œil.
Au lieu d'analyser des lignes de texte, vous voyez le plan d'exécution représenté sous forme de flux visuel, chaque opération étant affichée sous forme de nœud. Navicat met en évidence les opérations coûteuses ou inefficaces, de sorte que votre regard est immédiatement attiré vers les parties du plan qui méritent votre attention – aucun calcul mental n’est nécessaire ! Pour ceux qui préfèrent travailler avec des données brutes, Navicat présente également le même plan dans plusieurs formats : visuel, JSON, texte et statistiques sont disponibles, vous permettant de passer de l'un à l'autre selon vos besoins.
Cette approche multiformat est particulièrement utile lorsque vous travaillez sur différentes plateformes de bases de données, car chaque moteur formate ses résultats EXPLAIN différemment. Navicat uniformise l'expérience de sorte que, que vous travailliez avec MySQL, PostgreSQL ou une autre base de données prise en charge, le flux de travail pour examiner les plans d'exécution reste cohérent.
Une approche pratique de l'analyse des plans d'exécution
Pour bien comprendre les plans d'exécution, commencez par une requête que vous maîtrisez déjà, générez son plan, puis suivez-le pas à pas. Ajoutez un index et exécutez à nouveau EXPLAIN. Vous verrez le plan évoluer en temps réel, ce qui vous permettra de comprendre intuitivement comment l'optimiseur réagit aux modifications de schéma.
Avec le temps, repérer une analyse complète de table ou une mauvaise estimation de ligne deviendra un réflexe. Le plan d'exécution ne sera plus perçu comme un document obscur contenant des informations internes, mais comme une explication claire du fonctionnement de votre base de données et de ses raisons.
Conclusion
Les plans d'exécution sont parmi les outils les plus puissants à la disposition des développeurs et des administrateurs de bases de données pour optimiser les performances. Leur utilisation est bien plus intuitive qu'il n'y paraît. Une fois que vous savez à quoi ressemble un balayage complet de la table, ce que signifie une estimation du nombre de lignes et en quoi les types de jointures diffèrent, vous disposez de l'essentiel pour diagnostiquer la plupart des problèmes de performances des requêtes. Associez ces connaissances à un outil visuel tel que la visualisation « explain » intégrée à Navicat, et vous passerez de la confusion à un diagnostic sûr en bien moins de temps que vous ne le pensez.
