Ga naar de inhoud 
Met de optimizer trace in MariaDB begrijp ik stap voor stap waarom de optimizer een bepaald plan kiest en welke varianten hij afwijst. Dit JSON-spoor laat me zien Beslissingen over kosten, join-volgordes en filters, zodat ik SQL-query’s doelgericht kan aanpassen.
Centrale punten
- Transparantie: Een op JSON gebaseerde analyse geeft uitleg over herschrijvingen, kosten en afgewezen plannen.
- Focus: join_preparation en join_optimization bieden de belangrijkste inzichten.
- Besturingssysteem: Sessievariabelen beperken de overhead en het geheugengebruik.
- Werkstroom: EXPLAIN/ANALYZE voor het plan, Trace voor het „waarom“.
- Praktische voordelen: Indexen, statistieken en join-volgordes op een weloverwogen manier aanpassen.
Wat is de MariaDB Optimizer Trace?
MariaDB heeft sinds versie 10.4 een Optimizer Trace, dat elke grotere optimalisatiefase van een SELECT-, UPDATE- of DELETE-instructie als JSON documenteert. Hierin zie ik hoe de engine query's uitbreidt, voorwaarden normaliseert en uiteindelijk de volgorde van de joins, inclusief indextoegang, vaststelt. Dit inzicht gaat aanzienlijk dieper dan EXPLAIN, dat vooral het eindplan laat zien, en onthult afgewezen alternatieven met onderbouwing. Het trace-bestand wordt per verbinding in het geheugen opgeslagen en is beschikbaar via information_schema.OPTIMIZER_TRACE klaar. Zo krijg ik een volledige, machinaal leesbare beschrijving van de interne Stappen, die tot een uitvoeringsplan hebben geleid.
Optimizer Trace inschakelen en uitlezen
Ik schakel de functie doelgericht per sessie in, zodat ik diagnoses kan uitvoeren zonder algemene overhead en volledige controle heb over Geheugen heb. Meestal gebruik ik SET SESSION optimizer_trace = 'enabled=on'; en indien nodig SET SESSION optimizer_trace_max_mem_size = 1048576; of hoger, als de trace omvangrijk wordt. Daarna voer ik de verdachte query uit en lees ik de trace met SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Belangrijk: de tabel slaat alleen de laatste query van de actieve verbinding op, en ik let op velden zoals MISSING_BYTES_BEYOND_MAX_MEM_SIZE of ONVOLDOENDE_RECHTEN voor diagnostische aanwijzingen. Deze werkwijze houdt de productieomgeving gestroomlijnd en maakt de analyse nauwkeurig.
| Variabele/veld | Doel | Voorbeeldwaarde |
|---|---|---|
optimizer_trace | Schakelt de trace per sessie in | 'enabled=on' |
optimizer_trace_max_mem_size | Maximale opslagcapaciteit per trace | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Originele SQL-opdracht | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON-document van de optimalisatie | JSON-tekst |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Verloren bytes bij een te grote trace | 0 of aantal |
ONVOLDOENDE_RECHTEN | Is de leesbevoegdheid voldoende? | 0 of 1 |
JSON-structuur: join_preparation en join_optimization
De JSON-structuur is onderverdeeld in de volgende blokken join_preparation en join-optimalisatie, die ik als eerste doorneem, omdat ze de belangrijkste zijn Opmerkingen leveren. In de paragraaf join_preparation Ik herken de uitgebreide zoekopdracht (uitgebreide zoekopdracht) en kijk of en hoe de engine voorwaarden of projecties heeft aangepast. Het tweede blok join-optimalisatie registreert rijschattingen, bekeken plannen, de gekozen volgorde van de join-bewerkingen en het toevoegen van selectieve WHERE-delen aan tabellen. Bijzonder nuttig zijn de subbomen rows_estimation, overwogen_uitvoeringsplannen en voorwaarden_aan_tabellen_koppelen, omdat ze rechtstreeks verwijzen naar kostenramingen en filterposities. Daardoor zie ik snel waar er sprake is van verkeerde inschattingen of ongunstige Indices tot suboptimale plannen leiden.
Vergelijking met EXPLAIN en ANALYZE
Voor een volledige analyse combineer ik EXPLAIN, ANALYZE en de Spoor volgens een vaste procedure. Eerst gebruik ik UITLEGGEN of EXPLAIN FORMAT=JSON, om het geselecteerde plan en de sleutelpaden te bekijken. Daarna stel ik EXPLAIN ANALYZE om concrete uitvoeringsduurgegevens en telwaarden, zoals loops en gefilterde regels, te verkrijgen. Als er nog vragen openstaan, schakel ik de Optimizer Trace in en bekijk ik welke varianten de optimizer heeft gecontroleerd en afgewezen. Dit artikel biedt mij een beknopte inleiding tot de interpretatie van EXPLAIN ANALYZE begrijpen, die ik indien nodig als aanvulling raadpleeg.
Beslissingen over plannen begrijpen: kosten, kardinaliteiten, filters
De besluitvormingslogica is gebaseerd op kardinaliteiten, kostenmodellen en de plaatsing van Filter volgens het plan. In de trace zie ik voor elke onderzochte join-volgorde welke hoeveelheden rijen de engine verwacht en hoe deze daaruit de totale kosten afleidt. Ik controleer of verouderde statistieken of ongunstige correlaties ertoe leiden dat range-scans worden onderschat en full-scans de voorkeur krijgen. Daarnaast kijk ik of de engine WHERE-voorwaarden vroeg genoeg aan de meest selectieve tabel koppelt om dure join-stappen te beperken. Zo kan ik onderbouwde conclusies trekken over waarom een plan is gekozen en hoe ik het met Indices, herschrijvingen of het bijhouden van statistieken beïnvloedt.
Praktijk: trace van een eenvoudige filterquery
Op SELECT * FROM t1 WHERE a < 10 ik controleer onder join_preparation, of de engine de projectie heeft uitgebreid en eventueel voorwaarden heeft samengevoegd, wat mij in eerste instantie Indicatoren levert. Daarna zie ik in het blok rows_estimation, hoeveel regels de engine voor range-scan gebruikt a in vergelijking met de volledige tabel-scan. Als er onrealistische waarden worden aangetroffen, beschouw ik dat vaak als een teken van verouderde statistieken of ontbrekende histogrammen. In de paragraaf overwogen_uitvoeringsplannen daarna zie ik of de indextoegang inderdaad goedkoper is uitgevallen dan de volledige scan. Tot slot laat voorwaarden_aan_tabellen_koppelen, of de selectieve voorwaarde geldt voor a loopt voor op schema, wat de looptijd aanzienlijk verlaagt.
JSON-functies: gericht fragmenten extraheren
Omdat de trace in JSON-formaat is, filter ik gericht subbomen met JSON_EXTRACT en maak kleine analyses voor terugkerende Voorbeeld. Ik bekijk bijvoorbeeld alleen de lijst met overwogen plannen om te controleren of bepaalde join-volgordes systematisch mislukken. Ook haal ik kostenvelden van de beste kandidaten eruit en vergelijk ik die met ANALYZE-gegevens om verkeerde aannames op te sporen. Via eenvoudige views of opgeslagen procedures automatiseer ik deze controles voor mijn diagnosesessies. Op deze manier bouw ik een eenvoudig Controle voor beslissingen van de optimizer, zonder permanente tracing in te schakelen.
Typische toepassingen en voordelen
Ik gebruik de trace wanneer EXPLAIN een onverwachte volledige scan aangeeft en ik de reden wil achterhalen waarom een Index wil weten. Ook geeft de trace bij veel tabellen de reden voor de gekozen volgorde van de joins, wat mij de weg wijst naar alternatieve plannen. Bij een versiewisseling maak ik traces voor en na de update om wijzigingen in het gedrag van de optimizer te beoordelen. Voor strategische tuningvragen helpt dit overzicht mij om interne optimalisatiemechanismen, die ik in verband breng met trace-resultaten. Zo beslis ik op een gestructureerde manier of ik aan indexen, statistieken of queryformuleringen de stelschroef zet.
Best practices voor de productie
Ik schakel de trace consequent in als Sessie-Ik stel het in en sluit de diagnose netjes af zodra ik genoeg gegevens heb. Voor grote traces verhoog ik optimizer_trace_max_mem_size alleen tijdelijk en stel de waarde daarna weer laag in. Voordat ik JSON-bestanden deel, maskeer ik gevoelige constanten, commentaarteksten of bedrijfscijfers. Ik gebruik de trace doelgericht als diagnose-instrument, terwijl ik voor continue monitoring de voorkeur geef aan slow-query-logs, prestatieoverzichten of externe profilers. Deze werkwijze houdt systemen slank en voorkomt onnodige Overhead in de dagelijkse praktijk.
Optimizer Trace in de gereedschapsmix
Voor een holistische afstemming breng ik het proces in kaart, bestaande uit het begrijpen van het plan, de oorzakenanalyse en de systeemmeting, en koppel ik de Bevindingen. EXPLAIN toont me het plan, ANALYZE bevestigt de werkelijke kosten en de trace geeft uitleg over de achtergronden van de beslissing. Tegelijkertijd verdiep ik me in concepten rond query-uitvoeringsplannen om patronen in sleutelkeuze, cardinaliteiten en join-strategieën te herkennen. Een goede aanvulling op dit perspectief is het beknopte overzicht van Uitvoeringsplannen voor query's, waar ik bij architectuurvragen mijn toevlucht toe neem. Daaruit leid ik betrouwbare Prioriteiten voor indexwerk, herschrijvingen en parameters.
Dieper ingaan op: range_analysis en sleutelkeuze
In de trace zit vaak een blok range_analysis voor elke tabel, waarbij ik kan zien welke indexen in aanmerking kwamen voor Range-, Ref- of EQ-Ref-toegang. De optimizer vergelijkt daar alternatieven zoals „range op idx_a“, „range op idx_b“ of „full scan“, wijst ze kosten en verwachte rijen toe en markeert de winnaar. Als ik zie dat een zinvolle index vanwege hoge kosten is afgewezen, kijk ik vervolgens naar de onderliggende selectiviteiten en statistieken. Als de aannames niet kloppen, kan een TABEL ANALYSEREN (eventueel met doorlopende statistieken) of het opstellen van een gerichtere Covering-index de beslissing ongedaan maken.
Het is ook nuttig om te kijken naar splitsingen van samengestelde indexen: de trace laat zien of de voorwaarde alleen de eerste kolom van de index gebruikt, of dat er aanvullende predicaten zijn die in aanmerking komen en andere sleutelkolommen effectief worden. Op basis daarvan bepaal ik of ik predicaten moet herformuleren (bijvoorbeeld door functies te vermijden) of de index zodanig moet uitbreiden dat typische filters en sorteringen worden gedekt.
Joins in detail: semijoins, BKA/MRR en join-buffers
Bij multi-table-query’s laten de trace-segmenten zien of en welke semijoin-strategie in overweging is genomen (bijv. FirstMatch, DuplicateWeedout, LooseScan of Materialization). Ik zie daar waarom een variant is afgewezen – bijvoorbeeld vanwege hoge materialisatiekosten of een te lage selectiviteit. Ook Gebatchte Sleuteltoegang (BKA) en Multi-bereik lezen (MRR) verschijnen in de trace, indien geactiveerd. Deze technieken bundelen key-lookups en verbeteren de cache-localiteit. Als BKA/MRR niet in de trace verschijnen, controleer ik optimizer_switch en parameters zoals join_cache_level. Bij workloads met veel willekeurige key-lookups kan de join-fase op deze manier merkbaar worden versneld, wat met EXPLAIN ANALYZE kan worden geverifieerd.
Ook de grootte en het type van de join-buffer zijn van cruciaal belang: de trace laat zien of er nested-loop-varianten met of zonder buffer zijn uitgevoerd en op welk punt filters worden toegepast. Ik beoordeel of extra indexen op join-sleutels of een herschrijving om het aantal tussenresultaten te verminderen de efficiëntere keuze is dan het vergroten van de buffergroottes.
Subquery's, afgeleide tabellen en views
Op join_preparation Ik vind dat, of subquery’s in EXISTS/IN-vorm in Semijoins zijn omgevormd (in_to_exists), of afgeleide tabellen zijn samengevoegd (afgeleide samenvoeging) of daadwerkelijk zijn gerealiseerd en of Conditie Pushdown tot in afgeleide tabellen plaatsvindt. Deze stappen zijn cruciaal, omdat het ontbreken van een merge tot een kostbare materialisatie kan leiden. Als ik in de trace herhaaldelijk materialisatiebeslissingen met hoge kosten zie, test ik of een expliciete STRAIGHT_JOIN, een tip of een herschikking van de query (bijvoorbeeld Common Table Expressions met gerichte filters) zorgt ervoor dat de engine een gunstigere strategie kiest. Bij views controleer ik of de optimizer de inhoud van de view voldoende ontleedt of dat er aanvullende indexen ontbreken in de onderliggende tabel.
Partitionering en snoeien
Bij gepartitioneerde tabellen laat de trace zien welke partities op basis van partitiesleutels en predicaten zijn uitgesloten (Partitie-opschoning). Als de verwachte pruning uitblijft, is dat een teken dat filters eerder en op een meer ‘sargable’ manier op de partitiesleutel moeten worden geformuleerd. Ik let bovendien op de wisselwerking tussen partitionering en indexen: als lokale of globale indexen ontbreken, kan de engine ondanks pruning buitensporig veel rijen controleren, wat in de trace zichtbaar wordt door hoge scan-kosten.
Hints, indexspecificaties en optimizer_switch gericht controleren
Ik gebruik de trace om het effect van hints en parameterschakelaars te bezetten. Als ik bijvoorbeeld. FORCE-INDEX of een optimizer-hint, zie ik in de trace of het alternatief daadwerkelijk is afgedwongen en hoe het is beoordeeld. Via optimizer_switch kan ik strategieën tijdelijk in- of uitschakelen (bijvoorbeeld voor semijoin-, index_merge- of derived_merge-beslissingen). De trace dient mij dan als bewijs om te zien of de engine de specificaties heeft overgenomen of dat andere beperkingen (bijvoorbeeld kardinaliteiten) nog steeds de overhand hebben. Optioneel gebruik ik opmaakvlaggen zoals one_line of end_markers op optimizer_trace-string om de leesbaarheid aan te passen aan mijn analyseprogramma.
Update/DELETE en schrijfpaden
De Optimizer Trace is niet beperkt tot SELECT-opdrachten. Ook bij UPDATE- en DELETE-opdrachten zie ik hoe de toegangsroutes worden gekozen en of filters vroeg genoeg worden toegepast om het aantal betrokken rijen laag te houden. Ik controleer of een WHERE-filter niet sargable is of dat een ontbrekende index leidt tot een brede scanfase, voordat de daadwerkelijke wijziging wordt uitgevoerd. Uit de trace leid ik af of een compacte index (bijv. alleen de benodigde kolommen) onnodige heen-en-weer-toegangen voorkomt en daarmee locks en logvolume vermindert.
Beveiliging, rechten en prepared statements
Om de track volledig te kunnen lezen, heb ik voldoende objectrechten nodig – als die ontbreken, geeft het veld een melding ONVOLDOENDE_RECHTEN Beperkingen. In productiegerelateerde scenario’s gebruik ik daarom dezelfde inloggegevens als de applicatie of een speciaal geautoriseerd diagnoseaccount. Bij prepared statements toont de trace de geoptimaliseerde vorm doorgaans al met gebonden parameters, waardoor ik de selectiviteit kan beoordelen zonder gevoelige constanten bloot te geven. Als ik traces moet delen, maskeer ik parameterwaarden of vervang ik ze door representatieve bereiken om te voldoen aan de vereisten inzake gegevensbescherming.
Automatisering: traces vastleggen, onderscheiden, documenteren
Om reproduceerbare analyses mogelijk te maken, sla ik steekproefsgewijs traces op in een diagnosetabel en voorzie ik ze van metadata zoals schema, versie, sessievariabelen en tijdstempels. Zo kan ik vóór en na indexwijzigingen of versie-upgrades diffen, welke beslissingen zijn uitgesteld. Het is handig om de blokken overwogen_uitvoeringsplannen en rows_estimation apart opslaan, om kostenwijzigingen snel te kunnen vergelijken. Kleinere hulpquery’s halen voor mij de gekozen volgorde van de joins en de berekende kosten eruit – bijvoorbeeld met JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – en slaan het resultaat op naast de uitvoer van EXPLAIN en ANALYZE. Zo ontstaat er een betrouwbare documentatie voor elke afstemmingsstap.
Beperkingen, versie-specifieke kenmerken en vergelijking met MySQL
De belangrijkste structuren van de trace zijn gebaseerd op MySQL, maar details en veldnamen kunnen per MariaDB-versie enigszins afwijken. Daarom richt ik mijn aandacht op de semantische Secties (Rewrites, Rows-Estimation, bekeken plannen, Condition-Attachments), in plaats van me te laten afleiden door oppervlakkige verschillen. Belangrijk: in MariaDB ligt de focus op de laatste instructie van de actieve verbinding. Wie veel opeenvolgende statements analyseert, leest deze daarom direct na uitvoering uit of automatisch via een hook, zodat er geen relevante sporen worden overschreven. Voor zeer grote JSON-bestanden houd ik rekening met de benodigde opslagruimte en begrijp ik MISSING_BYTES_BEYOND_MAX_MEM_SIZE als uitnodiging om de limiet tijdelijk te verhogen en de analyse opnieuw uit te voeren.
Concrete JSON-extracties voor dagelijks gebruik
Tot slot nog een paar korte fragmenten die ik in de praktijk vaak gebruik om snel ter zake te komen:
- Gekozen volgorde van joins en kandidatenlijsten: ik haal de planprefixen en de bijbehorende tabellen op, zodat ik de beslissingsvolgorde kan volgen.
- Alternatieven voor range-indexen en kosten: ik haal de lijst met geëvalueerde indexen voor de meest selectieve tabellen op om herschrijvingen of nieuwe indexen nauwkeurig te kunnen beoordelen.
- Vroeg toegevoegde filters: Ik lees de
voorwaarden_aan_tabellen_koppelen-secties, om ervoor te zorgen dat krachtige predikaten zo dicht mogelijk bij de gegevensbron staan.
Met slechts enkele views voor deze extracties beschik ik over een overzichtelijke „leesbril“ voor optimalisatiebeslissingen, die ik indien nodig tijdens diagnosesessies inschakel en daarna weer uitschakel.
Frequente struikelblokken en probleemoplossing
Als er histogrammen ontbreken of als statistieken verouderd zijn, kloppen de schattingen niet en leiden ze tot Plannen met onnodige volledige scans. Als ik in de trace sterk afwijkende cardinaliteiten zie, werk ik de statistieken bij, stel ik geschikte indexen in of herschrijf ik filters zodat ze met Sargable kunnen worden gebruikt. Te beknopte traces herken ik aan MISSING_BYTES_BEYOND_MAX_MEM_SIZE en reageer met een tijdelijk hogere limiet. Als ANALYZE betere uitvoeringstijden oplevert voor een alternatief pad, controleer ik in de trace welke kostenfactor de voorkeur heeft gegeven aan de gekozen variant. Zo vul ik stap voor stap mijn kennislacunes in en bereik ik Duidelijkheid over de beslissingslogica.
Kort samengevat
De MariaDB Optimizer Trace legt me in een JSON-document uit hoe de engine query’s herschikt, het aantal rijen schat, plannen vergelijkt en uiteindelijk een Volgorde kiest. Ik activeer hem per sessie, lees het spoor uit, controleer join_preparation en join-optimalisatie en koppel de bevindingen aan EXPLAIN/ANALYZE. Op basis van de redenen voor afgewezen indexen, late filters of foutieve schattingen leid ik concrete stappen af: betere indexen, actuelere statistieken en duidelijke queryformuleringen. Met JSON-functies haal ik fragmenten eruit, herken ik patronen en documenteer ik beslissingen op een reproduceerbare manier. Zo breng ik ook omvangrijke SQL-workloads op betrouwbare Prestaties en zorg ervoor dat beslissingen over tuning begrijpelijk zijn.
