Passer au contenu 
Grâce à la trace de l'optimiseur dans MariaDB, je comprends étape par étape pourquoi l'optimiseur choisit un plan particulier et quelles variantes il écarte. Cette trace JSON me montre Décisions sur les coûts, l'ordre des jointures et les filtres, afin de pouvoir adapter mes requêtes SQL de manière ciblée.
Points centraux
- Transparence: Une trace basée sur JSON explique les réécritures, les coûts et les plans rejetés.
- Focus sur: les commandes « join_preparation » et « join_optimization » fournissent les informations les plus importantes.
- Contrôle: Les variables de session limitent la surcharge et l'utilisation de la mémoire.
- Flux de travail: EXPLAIN/ANALYZE pour le plan, Trace pour le „ pourquoi “.
- Avantages pratiques: Ajuster de manière éclairée les index, les statistiques et l'ordre des jointures.
Qu'est-ce que la trace de l'optimiseur MariaDB ?
Depuis la version 10.4, MariaDB intègre un Optimiseur Trace, qui documente sous forme de JSON chaque phase d'optimisation majeure d'une instruction SELECT, UPDATE ou DELETE. Je peux ainsi voir comment le moteur étend les requêtes, normalise les conditions et détermine enfin l’ordre des jointures ainsi que les accès aux index. Cette analyse va nettement plus loin que la commande EXPLAIN, qui affiche principalement le plan final, et révèle les alternatives rejetées avec leurs justifications. La trace est stockée en mémoire pour chaque connexion et est disponible via information_schema.OPTIMIZER_TRACE prêt. J'obtiens ainsi une explication complète et lisible par machine des Étapes, qui ont abouti à un plan d'exécution.
Activer et consulter la trace de l'optimiseur
J'active cette fonction de manière ciblée pour chaque session afin de pouvoir effectuer des diagnostics sans surcharge globale et de garder un contrôle total sur Mémoire j'ai. En général, je mets SET SESSION optimizer_trace = 'enabled=on'; et, si nécessaire SET SESSION optimizer_trace_max_mem_size = 1048576; ou plus, si la trace devient volumineuse. Ensuite, j'exécute la requête suspecte et je lis la trace avec SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Important : le tableau n'enregistre que la dernière requête de la connexion active, et je tiens compte de champs tels que MISSING_BYTES_BEYOND_MAX_MEM_SIZE ou INSUFFICIENT_PRIVILEGES pour obtenir des indications de diagnostic. Cette méthode permet de maintenir la simplicité de l'environnement de production et facilite l'analyse avec précision.
| Variable/Champ | Objectif | Exemple de valeur |
|---|---|---|
optimizer_trace | Active la trace par session | 'enabled=on' |
optimizer_trace_max_mem_size | Espace de stockage maximal par trace | 1048576 (1 Mo) |
OPTIMIZER_TRACE.QUERY | Instruction SQL d'origine | SELECT ... |
OPTIMIZER_TRACE.TRACE | Document JSON d'optimisation | Texte JSON |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Octets tronqués lorsque la trace est trop longue | 0 ou nombre |
INSUFFICIENT_PRIVILEGES | Le droit de lecture suffit-il ? | 0 ou 1 |
Structure JSON : join_preparation et join_optimization
La structure JSON est divisée en blocs join_preparation et optimisation_des_jointures, que je consulte en premier lieu, car ce sont les plus importantes Remarques fournir. Dans la section join_preparation je reconnais la requête étendue (requête_développée) et je vérifie si et comment le moteur a transformé les conditions ou les projections. Le deuxième bloc optimisation_des_jointures enregistre les estimations par ligne, les plans examinés, l'ordre de jointure choisi et l'ajout de parties WHERE sélectives aux tables. Les sous-arbres sont particulièrement utiles estimation_lignes, plans_d'exécution_envisagés et associer_des_conditions_aux_tables, car elles renvoient directement aux hypothèses de coûts et aux paramètres de filtrage. Cela me permet d'identifier rapidement les erreurs d'évaluation ou les éléments défavorables Indices conduire à des plans sous-optimaux.
Comparaison avec EXPLAIN et ANALYZE
Pour une évaluation complète, je combine EXPLAIN, ANALYZE et le Trace selon une procédure bien définie. Je commence par utiliser EXPLAIN ou EXPLAIN FORMAT=JSON, pour afficher le plan sélectionné et les chemins clés. Ensuite, je définis EXPLAIN ANALYZE afin d'obtenir des données réelles sur la durée d'exécution et des valeurs de comptage telles que les boucles et les lignes filtrées. S'il reste des questions en suspens, j'active la trace de l'optimiseur et je vérifie quelles variantes l'optimiseur a examinées et rejetées. Cet article sur [...], me fournit une introduction concise à l'interprétation de ces données. Comprendre EXPLAIN ANALYZE, auquel je me réfère en complément si nécessaire.
Comprendre les décisions de planification : coûts, cardinalités, filtres
La logique décisionnelle repose sur les cardinalités, les modèles de coûts et le placement de Filtrer conformément au plan. Dans la trace, je vois, pour chaque ordre de jointure examiné, quels ensembles de lignes le moteur attend et comment il en déduit le coût total. Je vérifie si des statistiques obsolètes ou des corrélations défavorables entraînent une sous-estimation des balayages par plage et une préférence pour les balayages complets. Je vérifie également si le moteur associe suffisamment tôt les conditions WHERE à la table la plus sélective afin de réduire les étapes de jointure coûteuses. Je peux ainsi expliquer de manière fiable pourquoi un plan a été choisi et comment je peux l’optimiser avec Indices, les réécritures ou la gestion des statistiques.
Exemple pratique : trace d'une requête de filtrage simple
À l'adresse suivante : SELECT * FROM t1 WHERE a < 10 je vérifie sous join_preparation, si le moteur a étendu la projection et éventuellement consolidé certaines conditions, ce qui m'a donné une première Indicateurs affiche. Ensuite, je vois dans le bloc estimation_lignes, le nombre de lignes que le moteur utilise pour le balayage de plage sur a par rapport à un balayage complet de la table. Si je constate des valeurs irréalistes, j'y vois souvent le signe de statistiques obsolètes ou d'histogrammes manquants. Dans la section plans_d'exécution_envisagés Je peux ensuite vérifier si l'accès par index s'est avéré réellement plus économique que l'analyse complète. Enfin, cela montre associer_des_conditions_aux_tables, si la condition de sélection s'applique à a intervient tôt dans le programme, ce qui réduit considérablement la durée d'exécution réduit.
Fonctions JSON : extraire des extraits de manière ciblée
Comme la trace est au format JSON, je filtre de manière ciblée des sous-arborescences à l'aide de JSON_EXTRACT et je crée de petits rapports pour les tâches récurrentes Échantillon. Je me contente par exemple de parcourir la liste des plans envisagés pour vérifier si certains ordres de jointure échouent systématiquement. De même, j'extrais les champs de coût des meilleurs candidats et je les compare aux données ANALYZE afin de détecter d'éventuelles hypothèses erronées. À l’aide de vues simples ou de procédures stockées, j’automatise ces vérifications pour mes sessions de diagnostic. De cette manière, je me construis un outil léger Suivi pour les décisions de l'optimiseur sans activer le traçage permanent.
Cas d'utilisation typiques et avantages
J'utilise la trace lorsque la commande EXPLAIN indique un balayage complet inattendu et que je cherche la raison du rejet d'une Index que je souhaite connaître. De même, pour de nombreuses tables, la trace m'explique pourquoi l'ordre des jointures a été choisi, ce qui m'ouvre la voie vers d'autres plans d'exécution. Lors d’un changement de version, je sauvegarde les traces avant et après la mise à jour afin d’évaluer les modifications apportées au comportement de l’optimiseur. Pour les questions stratégiques de réglage, cette vue d’ensemble m’aide à mécanismes internes d'optimisation, que j'associe aux résultats de trace. C'est ainsi que je détermine de manière structurée si je dois modifier les index, les statistiques ou la formulation des requêtes pour vis de réglage je place.
Bonnes pratiques en matière de production
J'active systématiquement la trace sous la forme Session- Je mets fin au diagnostic de manière ordonnée dès que j'ai suffisamment de données. Pour les traces volumineuses, j'augmente optimizer_trace_max_mem_size Je ne le fais que temporairement, puis je rétablis une valeur faible. Avant de partager des fichiers JSON, je masque les constantes sensibles, les commentaires ou les indicateurs clés de performance. J'utilise la trace de manière ciblée comme outil de diagnostic, tandis que pour la surveillance continue, je privilégie les journaux de requêtes lentes, les vues de performances ou les profileurs externes. Cette discipline permet de maintenir la légèreté des systèmes et d'éviter tout Overhead dans les activités quotidiennes.
Optimizer Trace dans la gamme d'outils
Pour un optimisation globale, je reproduis la chaîne composée de la compréhension du plan, de l'analyse des causes et de la mesure du système, et je relie les Connaissances. EXPLAIN m'indique le plan d'exécution, ANALYZE confirme les coûts réels, et la trace fournit les raisons qui sous-tendent cette décision. En parallèle, j’étudie les concepts liés au plan d’exécution des requêtes afin d’identifier des schémas dans le choix des clés, les cardinalités et les stratégies de jointure. Un bon complément à cette approche est l’aperçu concis de Plans d'exécution des requêtes, auquel je me réfère pour les questions d'architecture. J'en tire des conclusions fiables Priorités pour le travail sur les index, les réécritures et les paramètres.
Approfondissons le sujet : « range_analysis » et le choix des clés
La trace contient souvent un bloc analyse_de_plage pour chaque table, afin de déterminer quels index pouvaient être utilisés pour des accès de type „ range “, „ ref “ ou „ eq-ref “. L’optimiseur compare alors des alternatives telles que « range sur idx_a », « range sur idx_b » ou « full scan », leur attribue des coûts et un nombre de lignes attendues, puis désigne la solution gagnante. Si je constate qu’un index pertinent a été écarté en raison de coûts élevés, j’examine ensuite les sélectivités et les statistiques sous-jacentes. Si les hypothèses ne sont pas correctes, un ANALYSE TABLE (le cas échéant, avec des statistiques persistantes) ou la mise en place d'un Indice de couverture annuler la décision.
Il est également utile d'examiner les découpages des index composés : la trace indique si la condition utilise uniquement la première colonne de l'index ou si des prédicats supplémentaires sont applicables et si d'autres colonnes clés entrent effectivement en jeu. Je déduis de là s'il faut que je reformule les prédicats (par exemple en évitant les fonctions) ou que j'étende l'index de manière à couvrir les filtres et tris courants.
Les jointures en détail : semi-jointures, BKA/MRR et tampons de jointure
Dans le cas des requêtes multi-tables, les sections de trace indiquent si une stratégie de semi-joint a été envisagée et, le cas échéant, laquelle (par exemple : FirstMatch, DuplicateWeedout, LooseScan ou Materialization). Je comprends ainsi pourquoi une variante a été écartée – par exemple en raison de coûts de matérialisation élevés ou d'une sélectivité insuffisante. Également Accès par lots aux clés (BKA) et Lecture multi-gamme (MRR) apparaissent dans la trace, si cette option est activée. Ces techniques regroupent les recherches de clés et améliorent la localité du cache. Si BKA/MRR n'apparaissent pas dans la trace, je vérifie optimizer_switch et des paramètres tels que join_cache_level. Dans les charges de travail comportant de nombreuses recherches aléatoires de clés, cela permet d'accélérer sensiblement la phase de jointure, ce qui peut être vérifié à l'aide de la commande EXPLAIN ANALYZE.
La taille et le type du tampon de jointure sont également déterminants : la trace permet de voir si des variantes de boucles imbriquées ont été exécutées avec ou sans tampon, et à quel endroit les filtres s'appliquent. J'évalue si la création d'index supplémentaires sur les clés de jointure ou une réécriture visant à réduire les résultats intermédiaires constituent un choix plus efficace que l'augmentation de la taille des tampons.
Sous-requêtes, tables dérivées et vues
À l'adresse suivante : join_preparation Je me demande si les sous-requêtes sous la forme EXISTS/IN dans Semi-joins ont été transformés (in_to_exists), si les tables dérivées sont fusionnées (derived_merge) ou ont été concrétisées, et si Condition Pushdown jusqu'aux tables dérivées. Ces étapes sont cruciales, car l'absence de fusion peut entraîner une matérialisation coûteuse. Si je constate à plusieurs reprises dans la trace des décisions de matérialisation très coûteuses, je vérifie s'il est possible d'utiliser un STRAIGHT_JOIN, une astuce ou une réorganisation de la requête (par exemple, des expressions de table communes avec des filtres ciblés) incite le moteur à adopter une stratégie plus efficace. Pour les vues, je vérifie si l'optimiseur décompose suffisamment le contenu de la vue ou s'il manque des index supplémentaires dans la table sous-jacente.
Partitionnement et élagage
Pour les tables partitionnées, la trace indique quelles partitions ont été exclues en fonction des clés de partition et des prédicats (Élagage des partitions). Si l'élagage attendu ne se produit pas, cela indique qu'il faut formuler les filtres plus tôt et de manière plus ciblée sur la clé de partition. Je prête également attention à l’interaction entre le partitionnement et les index : en l’absence d’index locaux ou globaux, le moteur peut, malgré l’élagage, examiner un nombre excessif de lignes, ce qui se traduit dans la trace par des coûts de balayage élevés.
Vérifier de manière ciblée les indices, les spécifications d'index et l'option `optimizer_switch`
J'utilise la fonction Trace pour évaluer l'effet des hints et des commutateurs de paramètres occupent. Si je prends par exemple. INDICE DE FORCE ou une indication d'optimisation, je peux voir dans la trace si l'alternative a réellement été imposée et comment elle a été évaluée. Via optimizer_switch Je peux activer ou désactiver temporairement des stratégies (par exemple pour les décisions de type « semijoin », « index_merge » ou « derived_merge »). La trace me sert alors de preuve pour vérifier si le moteur a pris en compte les spécifications ou si d’autres restrictions (par exemple les cardinalités) continuent de prévaloir. En option, j’utilise des indicateurs de formatage tels que one_line ou end_markers à l'adresse suivante : optimizer_trace-chaîne de caractères, afin d'adapter la lisibilité à mon outil d'analyse.
Mise à jour/SUPPRESSION et chemins d'écriture
La trace de l'optimiseur ne se limite pas aux instructions SELECT. Pour les instructions UPDATE et DELETE, je peux également voir comment les chemins d'accès sont choisis et si les filtres s'appliquent suffisamment tôt pour limiter le nombre de lignes concernées. Je vérifie si un filtre WHERE n'est pas « sargable » ou si l'absence d'index entraîne une phase de balayage étendue avant que la modification proprement dite ne soit exécutée. À partir de la trace, je détermine si un index compact (par exemple, ne contenant que les colonnes nécessaires) permet d’éviter des allers-retours inutiles et, par conséquent, de réduire les verrous et le volume du journal.
Sécurité, privilèges et instructions préparées
Pour pouvoir lire l'intégralité de la piste, j'ai besoin de privilèges d'objet suffisants ; s'ils font défaut, le champ indique INSUFFICIENT_PRIVILEGES Restrictions. Dans les scénarios proches de la production, j’utilise donc les mêmes identifiants que l’application ou un compte de diagnostic doté d’autorisations spécifiques. Dans le cas des instructions préparées, la trace affiche généralement la forme optimisée avec des paramètres déjà liés, ce qui me permet d’évaluer les sélectivités sans divulguer de constantes sensibles. Si je dois partager des traces, je masque les valeurs des paramètres ou je les remplace par des plages représentatives afin de respecter les exigences en matière de protection des données.
Automatisation : enregistrer, différencier et documenter les traces
Pour garantir la reproductibilité des analyses, j'enregistre des traces de manière aléatoire dans une table de diagnostic et je les accompagne de métadonnées telles que le schéma, la version, les variables de session et l'horodatage. Cela me permet, avant et après des modifications d'index ou des mises à niveau de version, diffen, quelles décisions ont été reportées. Il est pratique de regrouper les blocs plans_d'exécution_envisagés et estimation_lignes à enregistrer séparément afin de pouvoir comparer rapidement les variations de coûts. De petites requêtes d'aide me permettent d'extraire l'ordre de jointure choisi et les coûts calculés – par exemple avec JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – et enregistrent le résultat aux côtés des résultats des commandes EXPLAIN et ANALYZE. On obtient ainsi une documentation fiable pour chaque étape d'optimisation.
Limites, particularités de chaque version et comparaison avec MySQL
Les structures clés de la trace s'inspirent de MySQL, mais les détails et les noms de champs peuvent légèrement varier selon la version de MariaDB. Je me concentre donc sur la sémantique Les sections (réécritures, estimation des lignes, plans pris en compte, conditions associées), plutôt que de me laisser déconcentrer par des différences superficielles. Important : dans MariaDB, l'accent est mis sur la dernière instruction de la connexion active. Si vous analysez de nombreuses instructions consécutives, il est donc recommandé de les lire immédiatement après leur exécution ou de le faire de manière automatisée via un hook, afin qu’aucune trace pertinente ne soit écrasée. Pour les fichiers JSON très volumineux, je tiens compte des besoins en mémoire et je comprends MISSING_BYTES_BEYOND_MAX_MEM_SIZE comme une invitation à relever temporairement la limite et à relancer l'analyse.
Extractions JSON concrètes pour le quotidien
Pour finir, voici quelques extraits concis que j'utilise souvent dans la pratique pour aller droit au but :
- Ordre des jointures choisi et listes de candidats : je récupère les préfixes du plan et la table associée à chacun d'entre eux afin de pouvoir retracer la séquence de décision.
- Alternatives de plage et coûts : j'extrais la liste des index évalués pour les tables les plus sélectives afin d'évaluer avec précision les réécritures ou les nouveaux index.
- Filtres ajoutés dès le début : Je lis les
associer_des_conditions_aux_tables- des sections, afin de s'assurer que les prédicats puissants soient placés aussi près que possible de la source de données.
Grâce à ces quelques vues pour ces extractions, je dispose d'un outil de „ lecture “ allégé pour les décisions de l'optimiseur, que j'active si nécessaire lors des sessions de diagnostic, puis que je désactive ensuite.
Points d'achoppement fréquents et résolution des problèmes
Si des histogrammes manquent ou si les statistiques sont obsolètes, les estimations sont erronées et entraînent Plans avec des analyses complètes inutiles. Si je constate des cardinalités très divergentes dans la trace, je mets à jour les statistiques, je crée des index adaptés ou je reformule les filtres de manière plus efficace. Je repère les traces trop succinctes grâce à MISSING_BYTES_BEYOND_MAX_MEM_SIZE et je réagis en augmentant temporairement la limite. Si ANALYZE fournit de meilleurs temps d'exécution pour un autre chemin, je vérifie dans la trace quel facteur de coût a favorisé la variante choisie. Je comble ainsi progressivement mes lacunes et j'atteins Clarté sur la logique de décision.
En bref
La trace de l'optimiseur MariaDB m'explique, dans un document JSON, comment le moteur reformule les requêtes, estime le nombre de lignes, compare les plans et, enfin, génère une Ordre sélectionne. Je l'active à chaque session, je lis la piste, je vérifie join_preparation et optimisation_des_jointures et j'associe ces conclusions à EXPLAIN/ANALYZE. À partir des raisons expliquant le rejet d'index, les filtres tardifs ou les estimations erronées, je déduis des mesures concrètes : de meilleurs index, des statistiques plus récentes et une formulation claire des requêtes. À l’aide des fonctions JSON, j’extrais des extraits, identifie des modèles et documente les décisions de manière reproductible. C’est ainsi que je parviens à traiter de manière fiable même des charges de travail SQL volumineuses Performance et veille à ce que les décisions en matière de réglage soient justifiées.
