Passer au contenu 
Je vais expliquer le Optimiseur MariaDB Exemples concrets : comment il élabore ses plans, estime les coûts et pourquoi il se trompe parfois. Vous apprendrez ainsi à analyser le plan d'exécution SQL de manière ciblée, à utiliser les index à bon escient et à guider l'optimiseur en vous appuyant sur des faits plutôt que sur votre intuition.
Points centraux
Pour commencer, je vais résumer brièvement les éléments clés afin que tu puisses situer les sections suivantes dans leur contexte et que tu puisses Vue d'ensemble tu conserves.
- Phases: L'analyse, la préparation, l'optimisation et l'exécution constituent le cycle de vie de chaque requête.
- Modèle de coûts: Les valeurs temporelles en microsecondes déterminent le choix des index, les balayages et l'ordre des jointures.
- Statistiques: La cardinalité et l'histogramme déterminent l'estimation de la sélectivité.
- Transparence: EXPLAIN, EXPLAIN ANALYZE et Optimizer Trace permettent d'ouvrir la « boîte noire ».
- Tuning: Les index, la réécriture des requêtes, la commande ANALYZE TABLE et les paramètres de coût permettent d'accélérer les opérations.
Cycle de vie d'une requête dans MariaDB
Avant qu'un plan ne voie le jour, une requête passe par quatre étapes que je vérifie systématiquement au quotidien afin de Causes pour identifier les causes de lenteur. Lors de l'analyse syntaxique, MariaDB convertit le SQL en une structure interne ; c'est à ce stade que les erreurs de syntaxe sont détectées. Lors de la phase de préparation, le moteur examine les tables, les colonnes et les index potentiels, et effectue des transformations simples. Vient ensuite l’optimisation, au cours de laquelle des plans candidats sont calculés et évalués à l’aide d’un modèle de coût. Lors de l’exécution, le serveur met en œuvre le plan sélectionné étape par étape : lecture, jointure, filtrage, retour des résultats.
Je classe clairement les erreurs d'analyse par phase, car cela permet d'établir plus rapidement un diagnostic et Mesures agir de manière ciblée. La plupart du temps, les problèmes de performances trouvent leur origine dans l’optimisation : estimations erronées, indices manquants ou ordres de jointure défavorables. Les erreurs d’analyse syntaxique sont mineures, mais la phase de préparation peut déjà comporter des subtilités telles que la résolution des vues ou la transformation des sous-requêtes. Lors de l’exécution, les inefficacités apparaissent alors sans pitié si un balayage complet a été choisi auparavant. C’est pourquoi je commence chaque analyse par un examen structuré des quatre étapes.
Comment l'optimiseur prend ses décisions en interne
MariaDB fonctionne selon un modèle basé sur les coûts et évalue les exécutions alternatives à l'aide d'une Fonction de coût. Pour chaque variante, le serveur estime le nombre de lignes lues, la sélectivité des conditions WHERE/ON, les types d’accès tels que le balayage de table, le balayage d’index et le balayage de plage, ainsi que le temps nécessaire à chaque opération. En interne, le serveur fait la distinction entre `join_preparation` et `join_optimization`. Dans `join_preparation`, sont effectués la réécriture des requêtes, la simplification des conditions, la transformation des sous-requêtes et la résolution des vues. La phase « join_optimization » calcule les ordres de jointure, vérifie les index candidats via « ref_optimizer_key_uses », estime le nombre de lignes via des balayages par plage et attribue les conditions à des tables spécifiques dès que possible.
Ce mécanisme explique pourquoi un petit filtre mal placé peut entraîner des coûts élevés Suivre . Si l'opération « attaching_conditions_to_tables » est effectuée tardivement, le plan traîne inutilement un grand nombre de lignes à travers les jointures. Si les statistiques sont obsolètes, les estimations « rows_estimation » et « Selectivity » sont erronées ; l'optimiseur opte alors pour des chemins d'accès apparemment avantageux, mais en réalité lents. C’est précisément sur ces leviers que j’agis : de meilleures statistiques, des prédicats plus clairs, des index composites soigneusement triés. Après cela, le choix du plan s’en trouve souvent sensiblement modifié.
Modèle de tarification à partir de MariaDB 11.0
Les versions actuelles n'évaluent plus le travail de manière approximative en fonction des poids, mais à l'aide de microsecondes pour des opérations de stockage spécifiques. Des paramètres tels que `optimizer_disk_read_cost`, `optimizer_disk_read_ratio` et `optimizer_where_cost` permettent de rapprocher le modèle des durées d'exécution réelles. Ainsi, l'optimiseur compare le balayage par plage d'index (index-range-scan) au balayage complet (full scan) sur la base d'hypothèses de temps réelles. LAST_QUERY_COST indique le coût total estimé et correspond souvent nettement mieux à la réalité qu’auparavant. Pour les systèmes traitant de grands volumes de données, cette granularité plus fine porte immédiatement ses fruits.
Je calibre le modèle avec précaution lorsque les caractéristiques matérielles contredisent les hypothèses par défaut et, par conséquent, les Sélection du forfait fausser. Les SSD NVMe, les mémoires distribuées ou les caches spécialisés peuvent modifier sensiblement le rapport disque et les temps de lecture. De légers ajustements des valeurs « optimizer_costs » amènent MariaDB à privilégier des chemins d'accès pertinents. Je documente chaque modification, puis je vérifie EXPLAIN ANALYZE afin d’en mesurer l’impact. Sans mesure, l’optimisation reste un coup de poker.
Sélectivité, statistiques et histogrammes
Pour obtenir de bonnes estimations, il faut commencer par des données fiables cardinalité et une sélectivité fiable. MariaDB conserve des statistiques sur les différentes valeurs de chaque colonne et peut, en option, utiliser des histogrammes pour les distributions. Ce sont justement les données hétérogènes – les « hotspots », les distributions de Zipf, les tendances saisonnières – qui tirent profit des histogrammes. Après des modifications importantes des données, j’exécute la commande ANALYZE TABLE afin que l’optimisation puisse à nouveau s’appuyer sur des données réelles. Ceux qui oublient de le faire risquent de subir des analyses complètes qui sont objectivement erronées.
Je programme ANALYZE en tant que tâche régulière, en fonction de Modifications en termes de volume de données et sur les tables critiques. Lorsque la répartition des colonnes est fortement asymétrique, les histogrammes permettent d'évaluer de manière réaliste la sélectivité des valeurs singulières. Cela réduit les erreurs d’estimation lors des balayages par plage et des stratégies de fusion. Associée à des index composites adaptés, la précision des résultats s’améliore considérablement. Résultat : des durées d’exécution plus courtes et moins d’E/S.
Lire les instructions EXPLAIN et les plans d'exécution
Pour visualiser les décisions, j'utilise EXPLAIN, EXPLAIN EXTENDED et FORMAT=JSON. Les colonnes classiques offrent un aperçu rapide : id, select_type, table, type, possible_keys, key, key_len, ref, rows et, le cas échéant, filtered. Une valeur type=ALL indique un balayage complet, ce qui est rarement souhaitable. FORMAT=JSON montre en détail comment les conditions ont été déplacées et quels chemins l’optimiseur a évalués. Dans le contexte de l’hébergement, je recommande le guide sur Plans d'exécution dans l'hébergement, afin de relier les informations relatives aux projets aux incidences sur les infrastructures.
Pour une interprétation rapide, je m'aide d'un petit tableau qui répertorie brièvement les valeurs typiques et qui permet ainsi Mauvaises interprétations empêché.
| Champ EXPLAIN | Valeur typique | Importance dans la pratique |
|---|---|---|
| type | ALL, range, ref, eq_ref, const | Plus on va vers la droite, plus la sélection est stricte ; « ALL » indique un balayage complet. |
| possible_keys | Liste des index | Des indices qui correspondent en théorie ; s'il manque des candidats ici, il manque de la structure. |
| clé | Nom de l'index | Index réellement utilisé ; la valeur « vide » signifie qu'aucun index n'est utilisé. |
| lignes | Nombre | Nombre estimé de lignes lues ; écart important par rapport à la réalité = statistiques peu fiables. |
| filtré | Pourcentage | Quelle quantité est transmise après le filtre ; une faible quantité est souvent préférable. |
Pourquoi l'optimiseur se trompe parfois
Aucun modèle de coûts ne convient à toutes les situations, c'est pourquoi j'apporte des corrections erreurs de manière ciblée. Des statistiques obsolètes entraînent des estimations erronées du nombre de lignes et des ordres de jointure défavorables. Des index composites mal structurés empêchent l’utilisation des index lors de filtres sur plusieurs colonnes. Les sous-requêtes très imbriquées compliquent les réécritures efficaces et bloquent la matérialisation. Des filtres manquants ou trompeurs obligent le moteur à déplacer de nombreuses lignes avant que des prédicats utiles ne s’appliquent.
Je vérifie tout d'abord si la formulation de la requête respecte les Index Ce qui fonctionne vraiment : règle de préfixe à gauche, ordre de tri adapté, éviter d'utiliser des fonctions sur les colonnes dans la clause WHERE. Ensuite, je vérifie dans EXPLAIN ANALYZE si la réalité corrobore l'estimation. Si ce n'est pas le cas, j'exécute ANALYZE TABLE et, si nécessaire, je réécris la requête. Ce n’est qu’en dernier recours que j’utilise FORCE INDEX ou des hints, car cela peut limiter les optimisations futures.
Utiliser la trace de l'optimiseur de manière ciblée
Si EXPLAIN ne suffit pas, j'active la trace de l'optimiseur et je surveille Décisions dans le journal JSON. Je peux y voir quels plans ont été envisagés, rejetés ou acceptés. Je comprends pourquoi une condition s'applique tardivement ou pourquoi un index n'a pas été retenu. Le journal indique également comment les conditions ont été réorganisées. Cette vue d'ensemble permet d'affiner la compréhension et fournit des leviers concrets pour le prochain optimisation.
J'enregistre les sections pertinentes de la trace avec le hachage de la requête et Paramètresévaluer. Cela me permettra ensuite de comparer l'effet de chaque modification. La documentation du serveur MariaDB et diverses présentations au sein de l'écosystème décrivent ces champs en détail (source : documentation du serveur MariaDB sur l'optimiseur de requêtes et la trace de l'optimiseur). Cet outil me permet de détecter plus rapidement les hypothèses erronées qu’en procédant par essais et erreurs. Je gagne surtout du temps sur les jointures complexes.
Pratique : l'optimisation des bases de données, étape par étape
Je commence chaque optimisation par une Mesure. J'identifie les problèmes grâce à la surveillance et à cela Journal des requêtes lent. Ensuite, je compare EXPLAIN avec EXPLAIN ANALYZE afin de mettre côte à côte le plan et la réalité. J’adapte la stratégie d’indexation aux clauses WHERE, JOIN et ORDER BY ; j’oriente les index composites vers les points d’accès les plus fréquents. Je n’utilise FORCE INDEX que lorsque l’optimiseur choisit le mauvais candidat malgré des statistiques correctes.
Chaque étape implique de veiller à la Statistiques: ANALYZE TABLE sur des tables très sollicitées, histogrammes pour les distributions asymétriques. Je simplifie les sous-requêtes inutiles, je matérialise les résultats intermédiaires si nécessaire et je supprime les anciennes solutions de contournement. En cas de matériel spécifique, je vérifie les optimizer_costs afin que le modèle en microsecondes soit correct. Je documente chaque modification à l'aide de valeurs « avant/après » afin que son effet reste traçable à long terme.
Problèmes courants liés aux optimiseurs et leurs solutions
Si EXPLAIN type=ALL s'affiche alors que le champ « possible_keys » est rempli, je commence par vérifier Sélectivité. Il arrive souvent que l'ordre des colonnes dans l'index composite ne soit pas adapté ou qu'une fonction empêche l'utilisation de l'index. Dans ce cas, j’inverse l’ordre, je supprime les fonctions gênantes ou je fractionne les prédicats. Si l’ordre des jointures est incorrect, je vérifie s’il est possible d’effectuer un filtrage en amont, par exemple en faisant passer en premier la table la plus sélective. Je convertis les sous-requêtes, lorsque cela s’avère judicieux, en jointures ou en tables TEMPORARY.
Je reconnais également les mauvaises décisions à des résultats très divergents lignes entre le plan et la réalité. Dans ce cas, la commande ANALYZE TABLE ou un histogramme sur la colonne concernée peut s'avérer utile. Si même des statistiques correctes ne permettent pas d'atteindre l'objectif, j'envisage d'utiliser des hints explicites. Au préalable, je m’assure de disposer de contre-vérifications et de valeurs mesurées afin que les versions ultérieures de l’optimiseur ne soient pas ralenties par des paramètres enregistrés. La rigueur dans la documentation porte ici ses fruits.
Contexte de l'hébergement et aspects opérationnels
La qualité des requêtes et l'infrastructure doivent être adaptées l'une à l'autre, sinon l'application ne sert à rien Potentiel. Des SSD rapides, des caches cohérents et une configuration soignée constituent la base sur laquelle l'Optimizer prend de bonnes décisions. Un trafic intense ne tolère pas les analyses complètes ; quelques requêtes mal formulées suffisent à ralentir des systèmes entiers. Pour les environnements MySQL/MariaDB en production, voici quelques conseils pratiques tels que Optimiseur MySQL Des pistes de réflexion utiles sur la combinaison entre plan et plateforme. En prenant en compte cet aspect, on évite les goulots d'étranglement avant qu'ils ne s'aggravent.
J'associe toujours l'analyse des prévisions à des indicateurs relatifs à E/S, la latence et la concurrence. Si les valeurs ne correspondent pas au modèle de coûts prévu, je vérifie les paramètres. Ensuite, j'examine la taille des tampons, les charges de travail parallèles et la répartition des ensembles les plus consultés. Cette approche permet d'assurer un fonctionnement harmonieux des requêtes et des ressources, et de maîtriser les pics de trafic.
Les jointures et les chemins d'accès dans la pratique
Je dissipe de nombreux malentendus en expliquant que Types d'accès les mettrais délibérément en balance. Un plage- ou bien ref-L'accès fonctionne presque toujours TOUT. Dans le cas de relations avec des clés uniques (eq_ref) les plans sont particulièrement solides. Je vérifie également si un Indice de couverture qui traite intégralement la requête : si toutes les colonnes nécessaires figurent dans l'index, MariaDB évite ainsi des accès coûteux aux tables. Index Condition Pushdown (ICP) permet de vérifier des conditions WHERE supplémentaires dès la phase d'indexation, ce qui réduit le nombre de lignes renvoyées et les opérations d'E/S.
Sur Fusion d'index MariaDB peut combiner plusieurs index (intersection/union). Cela s'avère utile pour les prédicats « OR » ou plusieurs conditions de sélection, mais s'avère souvent plus lent qu'un index composite bien choisi. J'évalue également MRR (lecture multi-portée) et BKA (Batched Key Access). MRR trie les clés primaires à lire afin de lisser les E/S aléatoires ; BKA regroupe les recherches de jointure et s'avère particulièrement efficace dans le cas de jointures non couvertes. En pratique, je teste BKA/MRR via optimizer_switch et je vérifie avec EXPLAIN ANALYZE si les profils d’E/S diminuent. Si, en revanche, MariaDB recourt au Boucle imbriquée de blocs (BNL), il est généralement plus avantageux d'augmenter la taille du tampon de jointure (join_buffer_size) – ou de procéder à une réécriture permettant des jointures par index.
-- Exemple : index composite pour jointure + filtre + tri
CREATE INDEX ix_orders_cust_status_created
ON orders (customer_id, status, created_at);
-- Accès type
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
Grâce à l'index ci-dessus, l'optimiseur peut choisir l'ordre le plus sélectif, évaluer les filtres dès le début et effectuer le tri souvent sans avoir recours à un tri sur fichier supplémentaire.
ORDER BY, GROUP BY, tri sur fichier et tables temporaires
Le tri et l'agrégation prennent du temps. Je veille à ce que ORDER BY et GROUP BY peuvent s'exécuter selon l'ordre de l'index. Cela fonctionne si le préfixe et la direction correspondent exactement. Sinon, une Tri de fichiers avec un tampon de tri (sort_buffer_size) et, le cas échéant, une table temporaire. Si l'ensemble des résultats contient des colonnes TEXT/BLOB larges, MariaDB est plus rapide. sur disque Les tables TEMP (Aria). Je prends des mesures préventives en ne sélectionnant que les colonnes nécessaires, en ne rechargeant les champs volumineux qu'à la fin ou en utilisant des préfixes dont la longueur est limitée.
Pour les agrégations, j'utilise, dans la mesure du possible, Analyse par index libre (par exemple, GROUP BY sur la partie principale de l'index) et je choisis des index composites le long du regroupement. Lorsque les résultats intermédiaires deviennent volumineux, une matérialisation avec des clés pertinentes s'adapte mieux qu'une seule méga-jointure. Je mesure régulièrement les métriques des gestionnaires et les compteurs Created_tmp_* afin de détecter les points chauds liés au tri et aux tables temporaires.
Sous-requêtes, semi-jointures et matérialisation
De nombreuses sous-requêtes peuvent être reformulées efficacement lors de la préparation. Les constructions IN/EXISTS peuvent être remplacées par Semi-jointure fonctionnent, avec des stratégies telles que la matérialisation ou LooseScan. Je vérifie si l'optimiseur est un derived_merge a pu être exécutée : si une table dérivée (ou une CTE WITH) est intégrée dans le plan externe, ses index sont directement disponibles. Si cela ne fonctionne pas, la sous-requête se retrouve dans une table temporaire – je lui attribue alors, si possible, une clé (par exemple via SELECT DISTINCT/ORDER BY sur des colonnes clés), afin que les jointures ne se perdent pas dans le néant.
-- Exemple : EXISTS à la place de IN et table dérivée compatible avec la fusion
SELECT o.id
FROM orders o
WHERE EXISTS (
SELECT 1 FROM payments p
WHERE p.order_id = o.id AND p.state = 'captured'
);
-- Dérivation avec des clés uniques
WITH paid_orders AS (
SELECT DISTINCT order_id
FROM payments
WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;
J'utilise la commande EXPLAIN FORMAT=JSON pour vérifier si matérialisé ou sous-requête dépendante a été sélectionné et si des conditions (poussée conditionnelle) agir suffisamment tôt.
Partitionnement et élagage
Le partitionnement ne remplace pas les index, mais il peut améliorer la Volume de données par accès réduire considérablement. L'optimiseur n'effectue une élagage correct que si le prédicat satisfait le Clé de partition correspond clairement et n'est pas masqué par des fonctions. J'évite donc d'utiliser des expressions telles que DATE(created_at) dans la clause WHERE sur des tables partitionnées et j'utilise plutôt des limites de plage. La commande EXPLAIN indique quelles partitions sont lues ; des plages trop larges sont le signe d'un mauvais élagage.
Un trop grand nombre de petites partitions augmente la charge de travail liée à la planification. Je choisis donc une granularité pertinente (par exemple, mensuelle plutôt que quotidienne), je veille à ce que les statistiques de chaque partition soient à jour (ANALYZE PARTITION) et je vérifie si les index importants sont présents localement dans les partitions. Dans le cadre de projets de migration, je tiens compte de l’impact sur la réplication et la sauvegarde : ces deux facteurs influencent le degré d’agressivité de ma partitionnalisation.
Sargabilité et modèles de réécriture
Le levier le plus simple reste Sargabilité – Conditions permettant d'exploiter les index. J'évite d'utiliser des fonctions sur les colonnes dans la clause WHERE, je ramène les constantes du côté des colonnes et, si nécessaire, je décompose les conditions « OR » en UNION ALL. Pour les recherches LIKE sans ancrage initial (" %foo "), un index BTREE ne sert à rien ; dans ce cas, j'envisage d'utiliser la recherche plein texte ou un service de recherche adapté. Pour les calculs, j'utilise colonnes générées indexées, afin que l'optimiseur puisse retrouver la logique dans l'index.
-- Anti-modèle : fonction sur une colonne
WHERE DATE(created_at) = '2026-08-01'
-- Mieux : plage sur la valeur brute
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
-- Anti-modèle : l'opérateur OR empêche l'utilisation d'un index
WHERE status = 'open' OR customer_id = 42
-- Meilleure solution : deux requêtes avec UNION ALL et chacune son propre index
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');
En ce qui concerne les indices composites, je considère que règle du préfixe de gauche Respectez scrupuleusement cette règle : triez les colonnes en fonction de leur sélectivité et du tri qui sera nécessaire par la suite. Si j’ai besoin d’un ORDER BY décroissant, j’en tiens compte dans la structure de l’index – cela m’évite ainsi le tri sur fichier.
Commutateur d'optimisation et réglage fin des coûts
Avant de me pencher sur les requêtes, je vérifie optimizer_switch et des tampons de mémoire. Des fonctionnalités telles que mrr, batched_key_access, index_merge, semijoin, derived_merge ou condition_pushdown_for_derived peuvent être ajustés à chaque session. J'active des candidats de manière ciblée pour une session de test, j'effectue des mesures avec EXPLAIN ANALYZE et je reviens en arrière si l'effet escompté ne se produit pas. Le chemin de jointure bénéficie d'une quantité suffisante de join_buffer_size; grandes variétés de sort_buffer_size. Parallèlement, je surveille les tampons par rapport à la concurrence, afin que le serveur ne se retrouve pas en situation de swap sous une charge parallèle.
Au niveau des coûts, j'ajuste, si nécessaire, les éléments déjà mentionnés coûts_de_l'optimiseur en microsecondes. Ma ligne directrice : des étapes modestes et réversibles, avec des points de mesure documentés. J'utilise LAST_QUERY_COST pour vérifier la plausibilité et refaire des mesures avec des valeurs de paramètres réalistes, car les plans peuvent dépendre fortement de constantes concrètes.
Stabilité des plans, régressions et flux de travail en équipe
Même un bon plan peut être compromis par l'augmentation du volume de données ou un changement de version basculer. Je m'assure donc de disposer d'informations sur les plans d'exécution : hachages de requêtes, JSON EXPLAIN, extraits de traces de l'optimiseur et durées d'exécution d'EXPLAIN ANALYZE. Les modifications apportées aux index et les réécritures sont effectuées sous forme de pull requests, accompagnées de justificatifs « avant/après ». Dans les environnements CI/CD, je teste automatiquement les requêtes critiques sur des ensembles de données représentatifs. C'est ainsi que je Plans de régression tôt.
Pour les cas délicats, je considère que Conseils (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch par requête) sont disponibles en dernier recours, mais utilisez-les avec parcimonie et en leur fixant une date d'expiration. Il vaut mieux remédier aux causes profondes : statistiques, index, formulation. Au sein des équipes, un guide succinct sur la « sargability », la conception des index et la rigueur des mesures garantit que les nouvelles fonctionnalités n’intègrent pas, à l’insu de tous, des sources de frustration en matière de performances.
Bilan succinct : du projet à la réalisation
Celui qui a Plan permet de comprendre et de contrôler les performances. Les phases « Parsing », « Preparing », « Optimizing » et « Executing » permettent d'identifier les sources de perte de temps. Le modèle de coût basé sur le temps disponible à partir de la version 11.0, ainsi que des statistiques et des histogrammes régulièrement mis à jour, rendent les estimations fiables. EXPLAIN, EXPLAIN ANALYZE et l’Optimizer Trace apportent une transparence que je traduis en mesures concrètes. Grâce à une stratégie d’indexation rigoureuse, une conception claire des requêtes et une infrastructure adaptée, les requêtes MariaDB fournissent systématiquement des réponses rapides.
