Hoppa till innehåll 
Med optimizer trace i MariaDB förstår jag steg för steg varför optimeraren väljer en viss plan och vilka alternativ den förkastar. Denna JSON-spårning visar mig Beslut om kostnader, sammanfogningsordning och filter, så att jag kan anpassa SQL-frågorna på ett målinriktat sätt.
Centrala punkter
- Öppenhet: En JSON-baserad spårning redogör för omskrivningar, kostnader och förkastade planer.
- Fokus: join_preparation och join_optimization ger de viktigaste insikterna.
- Styrsystem: Sessionsvariabler begränsar overhead och minnesanvändning.
- Arbetsflöde: EXPLAIN/ANALYZE för planen, Trace för „varför“.
- Praktiska fördelar: Justera index, statistik och sammanfogningsordning på ett välgrundat sätt.
Vad är MariaDB Optimizer Trace?
MariaDB har sedan version 10.4 infört en Optimiserare Trace, som dokumenterar varje större optimeringsfas i ett SELECT-, UPDATE- eller DELETE-uttryck i JSON-format. Där kan jag se hur motorn utvidgar frågorna, normaliserar villkoren och slutligen fastställer sammanfogningsordningen inklusive indexåtkomst. Denna inblick går betydligt djupare än EXPLAIN, som främst visar slutplanen, och avslöjar förkastade alternativ med motiveringar. Spåret finns i minnet för varje anslutning och är tillgängligt via information_schema.OPTIMIZER_TRACE klar. På så sätt får jag en fullständig, maskinläsbar redogörelse för de interna Steg, som har lett fram till en genomförandeplan.
Aktivera och läsa av Optimizer Trace
Jag aktiverar funktionen specifikt för varje session, så att jag kan köra diagnostik utan global overhead och ha full kontroll över Minne har. Vanligtvis sätter jag SET SESSION optimizer_trace = 'enabled=on'; och om så krävs SET SESSION optimizer_trace_max_mem_size = 1048576; eller högre, om spårningen blir omfattande. Därefter kör jag den misstänkta frågan och läser spårningen med SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Viktigt: Tabellen lagrar endast den senaste frågan från den aktiva anslutningen, och jag tar hänsyn till fält som MISSING_BYTES_BEYOND_MAX_MEM_SIZE eller . INSUFFICIENT_PRIVILEGES för diagnostiska ledtrådar. Detta arbetssätt håller produktionsmiljön smidig och underlättar analysen korrekt.
| Variabel/fält | Syfte | Exempel på värde |
|---|---|---|
optimizer_trace | Aktiverar spårningen per session | 'enabled=on' |
optimizer_trace_max_mem_size | Maximal lagringskapacitet per spår | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Ursprunglig SQL-sats | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON-dokument för optimeringen | JSON-text |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Bytes som klipps bort när spårningen är för stor | 0 eller antal |
INSUFFICIENT_PRIVILEGES | Räcker det med läsbehörighet? | 0 eller 1 |
JSON-struktur: join_preparation och join_optimization
JSON-strukturen är indelad i följande block join_preparation och join-optimering, som jag tittar igenom först eftersom de är de viktigaste Anteckningar leverera. I avsnittet join_preparation ser jag den utökade sökningen (expanded_query) och ser om och hur motorn har omformat villkor eller prognoser. Det andra blocket join-optimering loggar raduppskattningar, granskade planer, den valda sammanfogningsordningen och tillägg av selektiva WHERE-delar till tabeller. Särskilt användbara är underträd rows_estimation, beaktade_körningsplaner och att koppla villkor till tabeller, eftersom de direkt hänvisar till kostnadsantaganden och filterpositioner. På så sätt kan jag snabbt upptäcka var det finns felbedömningar eller ogynnsamma Index leda till suboptimala planer.
Jämförelse med EXPLAIN och ANALYZE
För en fullständig utvärdering kombinerar jag EXPLAIN, ANALYZE och Spår enligt ett fast förfarande. Först använder jag FÖRKLARA eller . EXPLAIN FORMAT=JSON, för att se den valda planen och nyckelvägarna. Därefter ställer jag in EXPLAIN ANALYZE för att få fram faktiska körningstidsdata och räknevärden som loopar och filtrerade rader. Om det fortfarande finns frågor aktiverar jag Optimizer Trace och ser vilka varianter optimeraren har granskat och förkastat. Den här artikeln ger mig en kortfattad introduktion till tolkningen av Förstå EXPLAIN ANALYZE, som jag vid behov använder som komplement.
Förstå planeringsbeslut: kostnader, kardinaliteter, filter
Beslutslogiken bygger på kardinaliteter, kostnadsmodeller och placeringen av Filter enligt planen. I spårningen ser jag för varje granskad join-sekvens vilka radmängder motorn förväntar sig och hur den därifrån beräknar den totala kostnaden. Jag kontrollerar om föråldrad statistik eller ogynnsamma korrelationer leder till att intervallsökningar underskattas och att fullständiga sökningar föredras. Dessutom tittar jag på om motorn kopplar WHERE-villkor till den mest selektiva tabellen tillräckligt tidigt för att minska kostsamma join-steg. På så sätt kan jag göra välgrundade bedömningar om varför en plan valdes och hur jag kan optimera den med Index, omskrivningar eller uppdatering av statistik.
Praktisk övning: Spåra en enkel filterfråga
Med SELECT * FROM t1 WHERE a < 10 kontrollerar jag under join_preparation, om motorn har utökat projektionen och eventuellt konsoliderat villkoren, vilket gav mig en första Indikatorer levererar. Därefter ser jag i blocket rows_estimation, hur många rader motorn använder för Range-Scan på a jämfört med en fullständig tabellgenomgång. Om jag stöter på orealistiska värden tolkar jag det ofta som ett tecken på föråldrade statistiska uppgifter eller saknade histogram. I avsnittet beaktade_körningsplaner Då ser jag om indexåtkomsten verkligen har beräknats som billigare än en fullständig genomsökning. Slutligen visar att koppla villkor till tabeller, om det selektiva villkoret gäller för a kommer igång tidigare än planerat, vilket avsevärt förkortar drifttiden sänker.
JSON-funktioner: Extrahera specifika delar
Eftersom spårningen finns i JSON-format filtrerar jag specifikt ut delträd med JSON_EXTRACT och skapa små analyser för återkommande Prov. Jag läser till exempel bara upp listan över de planer som övervägs för att kontrollera om vissa join-sekvenser systematiskt misslyckas. På samma sätt extraherar jag kostnadsfält från de främsta kandidaterna och jämför dem med ANALYZE-data för att upptäcka felaktiga antaganden. Med hjälp av enkla vyer eller lagrade procedurer automatiserar jag dessa kontroller för mina diagnossessioner. På detta sätt bygger jag upp ett enkelt Övervakning för optimeringsbeslut utan att aktivera permanent spårning.
Typiska användningsområden och fördelar
Jag använder Trace när EXPLAIN visar en oväntad fullständig genomsökning och jag vill ta reda på orsaken till att en Index vill ta reda på. Dessutom ger spårningen mig, när det gäller många tabeller, en förklaring till den valda sammanfogningsordningen, vilket visar mig vägen till alternativa planer. Vid versionsbyten sparar jag spårningar före och efter uppdateringen för att kunna utvärdera förändringar i optimerarens beteende. När det gäller strategiska frågor om finjustering hjälper denna översikt mig att interna optimeringsmekanismer, som jag kopplar till spårningsresultaten. På så sätt fattar jag ett strukturerat beslut om jag ska justera index, statistik eller formuleringen av sökfrågorna ställskruv sätt.
Bästa praxis för produktion
Jag aktiverar spårningen konsekvent som Session-Avsluta och avsluta diagnosen ordentligt så snart jag har tillräckligt med data. För stora spår ökar jag optimizer_trace_max_mem_size bara under en kortare period och ställer sedan tillbaka värdet till ett lägre. Innan jag delar JSON-filer döljer jag känsliga konstanter, kommentarer eller affärsnyckeltal. Jag använder spårningen specifikt som ett diagnostiskt verktyg, medan jag för kontinuerlig övervakning föredrar loggar för långsamma frågor, prestandavyer eller externa profilerare. Denna disciplin håller systemen smidiga och förhindrar onödig Overhead i den dagliga verksamheten.
Optimizer Trace i verktygsmixen
För en helhetsinriktad optimering kartlägger jag kedjan bestående av planförståelse, orsaksanalys och systemmätning och kopplar samman Resultat. EXPLAIN visar mig planen, ANALYZE bekräftar de faktiska kostnaderna och spårningen ger bakgrunden till beslutet. Samtidigt tittar jag på begrepp inom query execution plan för att identifiera mönster i nyckelval, kardinaliteter och join-strategier. Ett bra komplement till detta perspektiv är den kortfattade översikten över Planer för körning av frågor, som jag använder när det gäller arkitektoniska frågor. Utifrån detta drar jag tillförlitliga Prioriteringar för indexering, omskrivningar och parametrar.
Fördjupa dig ytterligare: range_analysis och nyckelval
I spårningen finns ofta en blockering intervallanalys för varje tabell, där jag kan se vilka index som var aktuella för Range-, Ref- eller EQ-Ref-åtkomst. Optimiseraren jämför där alternativ som „range på idx_a“, „range på idx_b“ eller „full scan“, tilldelar dem kostnader och förväntat antal rader och markerar vinnaren. Om jag ser att ett lämpligt index har förkastats på grund av höga kostnader tittar jag därefter på de underliggande selektiviteterna och statistikerna. Om antagandena inte stämmer kan en ANALYSERA TABELL (ev. med kontinuerlig statistik) eller upprättandet av en mer målinriktad Täckningsindex upphäva beslutet.
Det är också användbart att titta på uppdelningar av sammansatta index: Spårningen visar om villkoret endast använder indexets första kolumn eller om ytterligare predikat är sökbara och fler nyckelkolumner blir relevanta. Utifrån detta avgör jag om jag ska omformulera predikat (till exempel undvika funktioner) eller utöka indexet så att vanliga filter och sorteringar täcks in.
Join-operationer i detalj: Semijoins, BKA/MRR och join-buffertar
Vid frågor som omfattar flera tabeller visar spårningsavsnitten om och vilken semijoin-strategi som övervägdes (t.ex. FirstMatch, DuplicateWeedout, LooseScan eller Materialization). Där kan jag se varför en variant har förkastats – till exempel på grund av höga materialiseringskostnader eller för låg selektivitet. Även Batchad nyckelåtkomst (BKA) och Multi-Range Read (MRR) dyker upp i spårningen, om de är aktiverade. Dessa tekniker samordnar nyckelsökningar och förbättrar cache-lokaliteten. Om BKA/MRR inte visas i spårningen, kontrollerar jag optimizer_switch och parametrar som join_cache_level. I arbetsbelastningar med många slumpmässiga nyckelsökningar kan join-fasen på så sätt påtagligt påskyndas, vilket kan verifieras med EXPLAIN ANALYZE.
Storleken och typen av join-bufferten är också avgörande: spårningen visar om Nested Loop-varianter med eller utan buffert har körts och vid vilken punkt filtren träder i kraft. Jag bedömer om ytterligare index på join-nycklar eller en omskrivning för att minska antalet delresultat är ett effektivare val än att öka buffertstorlekarna.
Underfrågor, härledda tabeller och vyer
På join_preparation Jag tycker att om underfrågor i EXISTS/IN-form i Semijoins omformades (in_to_exists), om härledda tabeller har slås samman (derived_merge) eller har förverkligats och om Kondition Pushdown sker ända ner till härledda tabeller. Dessa steg är avgörande, eftersom en utebliven sammanfogning kan leda till en kostsam materialisering. Om jag i spårloggen upprepade gånger ser materialiseringsbeslut med höga kostnader, testar jag om en explicit STRAIGHT_JOIN, ett tips eller en omstrukturering av frågan (t.ex. Common Table Expressions med riktade filter) som får motorn att välja en bättre strategi. När det gäller vyer kontrollerar jag om optimeraren bryter ner vyinnehållet tillräckligt eller om det saknas ytterligare index i den underliggande tabellen.
Partitionering och beskärning
För partitionerade tabeller visar spårningen vilka partitioner som har uteslutits på grundval av partitionsnycklar och predikat (Partitionsbeskärning). Om den förväntade gallringen uteblir är det ett tecken på att filtren bör formuleras tidigare och på ett mer effektivt sätt utifrån partitionsnyckeln. Jag är dessutom uppmärksam på samspelet mellan partitionering och index: Om lokala eller globala index saknas kan motorn, trots gallring, behöva kontrollera alltför många rader, vilket syns i spårningen genom höga skanningskostnader.
Verifiera tips, indexspecifikationer och optimizer_switch på ett målinriktat sätt
Jag använder spårningen för att undersöka effekten av tips och parameteromkopplare för att ockupera. Om jag t.ex. sätter. FORCE-INDEX eller en optimeringshint kan jag i spårningen se om alternativet verkligen tvingades fram och hur det värderades. Via optimizer_switch kan jag tillfälligt aktivera eller inaktivera strategier (t.ex. för semijoin-, index_merge- eller derived_merge-beslut). Spårningen fungerar då som bevis på om motorn har accepterat specifikationerna eller om andra begränsningar (t.ex. kardinaliteter) fortfarande dominerar. Valfritt använder jag formateringsflaggor som one_line eller . end_markers på optimizer_trace-sträng för att anpassa läsbarheten till mitt analysverktyg.
Update/DELETE och skrivvägar
Optimizer Trace är inte begränsat till SELECT. Även vid UPDATE- och DELETE-satser kan jag se hur åtkomstvägar väljs och om filtren träder i kraft tillräckligt tidigt för att hålla antalet berörda rader lågt. Jag kontrollerar om ett WHERE-filter inte är sargable eller om en saknad index leder till en omfattande skanningsfas innan den faktiska ändringen utförs. Utifrån spårningen kan jag avgöra om ett kompakt index (t.ex. endast de kolumner som behövs) undviker onödiga fram-och-tillbaka-åtkomster och därmed minskar låsningar och loggvolym.
Säkerhet, behörigheter och förberedda satser
För att jag ska kunna läsa spåret i sin helhet behöver jag tillräckliga objektbehörigheter – om dessa saknas visar fältet INSUFFICIENT_PRIVILEGES Begränsningar. I produktionsnära scenarier använder jag därför samma inloggningsuppgifter som applikationen eller ett särskilt behörigt diagnoskonto. När det gäller förberedda satser visar spårningen vanligtvis redan den optimerade formen med bundna parametrar, vilket gör att jag kan utvärdera selektiviteten utan att avslöja känsliga konstanter. Om jag måste dela spårningar maskerar jag parametervärden eller ersätter dem med representativa intervall för att uppfylla dataskyddskraven.
Automatisering: Registrera, särskilja och dokumentera spår
För att kunna göra reproducerbara analyser sparar jag spår i slumpmässiga urval i en diagnostiktabell och förser dem med metadata såsom schema, version, sessionsvariabler och tidsstämplar. På så sätt kan jag före och efter indexändringar eller versionsuppgraderingar diffen, vilka beslut som har skjutits upp. Det är praktiskt att dela in blocken beaktade_körningsplaner och rows_estimation lagra separat för att snabbt kunna jämföra kostnadsförändringar. Mindre hjälpfrågor extraherar den valda sammanfogningsordningen och de beräknade kostnaderna – till exempel med JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – och sparar resultatet tillsammans med utdata från EXPLAIN och ANALYZE. På så sätt skapas en tillförlitlig dokumentation för varje optimeringssteg.
Begränsningar, versionsspecifika egenskaper och jämförelse med MySQL
Spårningens nyckelstrukturer är baserade på MySQL, men detaljerna och fältnamnen kan variera något beroende på vilken version av MariaDB som används. Därför riktar jag min uppmärksamhet mot semantiska Avsnitt (Rewrites, Rows-Estimation, beaktade planer, Condition-Attachments), istället för att låta mig distraheras av ytliga skillnader. Viktigt: I MariaDB ligger fokus på det sista uttrycket i den aktiva anslutningen. Den som analyserar många på varandra följande satser läser därför ut dem omedelbart efter körning eller automatiskt via en hook, så att inga relevanta spår skrivs över. För mycket stora JSON-filer räknar jag in minnesbehovet och förstår MISSING_BYTES_BEYOND_MAX_MEM_SIZE som en uppmaning att tillfälligt höja gränsen och köra analysen på nytt.
Konkreta JSON-utdrag för vardagen
Avslutningsvis några kortfattade utdrag som jag ofta använder i praktiken för att snabbt komma till saken:
- Val av sammanfogningsordning och kandidatlistor: Jag hämtar planprefixen och respektive tillhörande tabell för att kunna följa beslutsprocessen.
- Alternativ för intervall och kostnader: Jag extraherar listan över utvärderade index för de mest selektiva tabellerna för att kunna göra en träffsäker utvärdering av omskrivningar eller nya index.
- Filter som läggs till tidigt: Jag läser
att koppla villkor till tabeller-avsnitt för att säkerställa att starka predikat placeras så nära datakällan som möjligt.
Med ett fåtal vyer för dessa extraktioner har jag ett smidigt „läsglasögon“ för optimeringsbeslut, som jag aktiverar vid behov under diagnossessioner och sedan stänger av igen.
Frekventa stötestenar och felsökning
Om histogram saknas eller om statistiken är inaktuell, blir uppskattningarna felaktiga och leder till Planer med onödiga fullskanningar. Om jag ser kraftigt avvikande kardinaliteter i spårningen uppdaterar jag statistiken, skapar lämpliga index eller omformulerar filtren så att de går att lagras i SARG. Jag upptäcker för knapphändiga spårningar genom MISSING_BYTES_BEYOND_MAX_MEM_SIZE och reagerar med en tillfälligt höjd gräns. Om ANALYZE ger bättre körtider för en alternativ väg, kontrollerar jag i spårningen vilken kostnadsfaktor som har gynnat den valda varianten. På så sätt fyller jag steg för steg i kunskapsluckorna och uppnår Klarhet om beslutslogiken.
Kortfattat sammanfattat
MariaDB Optimizer Trace förklarar för mig i ett JSON-dokument hur motorn omformulerar frågor, uppskattar rader, jämför planer och slutligen en Sekvens väljer. Jag aktiverar den per session, läser av spåret, kontrollerar join_preparation och join-optimering och kopplar samman insikterna med EXPLAIN/ANALYZE. Utifrån orsakerna till avvisade index, sena filter eller felaktiga uppskattningar drar jag slutsatser om konkreta åtgärder: bättre index, mer aktuella statistikvärden och tydligare formuleringar av frågorna. Med hjälp av JSON-funktioner extraherar jag utdrag, identifierar mönster och dokumenterar beslut på ett reproducerbart sätt. På så sätt kan jag även hantera omfattande SQL-arbetsbelastningar på ett tillförlitligt sätt Effekt och se till att beslut om tuning är begripliga.
