Videre til indhold 
Med optimizer trace i MariaDB kan jeg trin for trin forstå, hvorfor optimizer vælger en bestemt plan, og hvilke varianter den fravælger. Dette JSON-spor viser mig Beslutninger om omkostninger, sammenkædningsrækkefølger og filtrering, så jeg kan tilpasse SQL-forespørgsler målrettet.
Centrale punkter
- Gennemsigtighed: En JSON-baseret oversigt forklarer omskrivninger, omkostninger og forkastede planer.
- Fokus: join_preparation og join_optimization giver de vigtigste indsigter.
- Kontrolsystem: Sessionsvariabler begrænser overhead og hukommelsesforbrug.
- Arbejdsgang: EXPLAIN/ANALYZE til at se planen, Trace til at finde ud af „hvorfor“.
- Praktiske fordele: Tilpas indekser, statistikker og sammenkædningsrækkefølger på et velunderbygget grundlag.
Hvad er MariaDB Optimizer Trace?
MariaDB har siden version 10.4 indført en Optimering Trace, der dokumenterer hver større optimeringsfase i en SELECT-, UPDATE- eller DELETE-sætning som JSON. Her kan jeg se, hvordan motoren udvider forespørgsler, normaliserer betingelser og til sidst fastlægger sammenkædningsrækkefølgen samt indeksadgangene. Dette indblik går betydeligt dybere end EXPLAIN, der primært viser slutplanen, og afslører forkastede alternativer med begrundelser. Sporet ligger i hukommelsen for hver forbindelse og er tilgængeligt via information_schema.OPTIMIZER_TRACE klar. Så får jeg en fuldstændig, maskinlæsbar beskrivelse af de interne Trin, som har ført til en gennemførelsesplan.
Aktivér og udlæs Optimizer Trace
Jeg aktiverer funktionen målrettet for hver session, så jeg kan køre diagnoser uden global overhead og har fuld kontrol over Hukommelse har. Normalt sætter jeg SET SESSION optimizer_trace = 'enabled=on'; og hvis det er nødvendigt SET SESSION optimizer_trace_max_mem_size = 1048576; eller højere, hvis sporet bliver omfattende. Derefter kører jeg den mistænkelige forespørgsel og læser sporet med SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Vigtigt: Tabellen gemmer kun den seneste forespørgsel fra den aktive forbindelse, og jeg tager højde for felter som MISSING_BYTES_BEYOND_MAX_MEM_SIZE eller INSUFFICIENT_PRIVILEGES til diagnostiske oplysninger. Denne fremgangsmåde holder produktionsmiljøet strømlinet og gør analysen præcis.
| Variabel/felt | Formål | Eksempel på værdi |
|---|---|---|
optimizer_trace | Aktiverer sporing pr. session | 'enabled=on' |
optimizer_trace_max_mem_size | Maksimal lagerplads pr. spor | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Oprindelig SQL-sætning | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON-dokument om optimeringen | JSON-tekst |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Bytes, der er blevet afskåret, når sporet er for stort | 0 eller antal |
INSUFFICIENT_PRIVILEGES | Er læseadgangen tilstrækkelig? | 0 eller 1 |
JSON-struktur: join_preparation og join_optimization
JSON-strukturen er opdelt i følgende blokke join_preparation og join-optimering, som jeg gennemgår først, fordi de er de vigtigste Noter levere. I afsnittet join_preparation Jeg genkender den udvidede forespørgsel (udvidet_søgning) og ser, om og hvordan motoren har omformet betingelser eller fremskrivninger. Den anden blok join-optimering registrerer linjeestimater, de gennemgåede planer, den valgte sammenkædningsrækkefølge og tilføjelsen af selektive WHERE-dele til tabeller. Undertræerne er særligt nyttige rows_estimation, overvejede_udførelsesplaner og tilknytning_af_betingelser_til_tabeller, fordi de henviser direkte til omkostningsantagelser og filterpositioner. På den måde kan jeg hurtigt se, hvor der er fejlvurderinger eller ugunstige Indekser føre til suboptimale planer.
Sammenligning med EXPLAIN og ANALYZE
For at få en fuldstændig vurdering kombinerer jeg EXPLAIN, ANALYZE og Spor i en fast rækkefølge. Først bruger jeg FORKLAR eller EXPLAIN FORMAT=JSON, for at se den valgte plan og nøglebanerne. Derefter indstiller jeg FORKLAR ANALYSE for at få reelle kørselstidsdata og tællerværdier såsom loops og filtrerede linjer. Hvis der stadig er ubesvarede spørgsmål, aktiverer jeg Optimizer Trace og ser, hvilke varianter optimeringsværktøjet har undersøgt og afvist. Denne artikel giver mig en kortfattet introduktion til fortolkningen af At forstå EXPLAIN ANALYZE, som jeg bruger som supplement, når det er nødvendigt.
Forstå planbeslutninger: Omkostninger, kardinaliteter, filtre
Beslutningslogikken bygger på kardinaliteter, omkostningsmodeller og placeringen af Filter i henhold til planen. I sporingen kan jeg for hver betragtet join-rækkefølge se, hvilke mængder af rækker motoren forventer, og hvordan den udleder de samlede omkostninger heraf. Jeg kontrollerer, om forældede statistikker eller ugunstige korrelationer medfører, at range-scans undervurderes, og at full-scans foretrækkes. Desuden undersøger jeg, om motoren knytter WHERE-betingelser tidligt nok til den mest selektive tabel for at reducere dyre join-trin. På den måde kan jeg komme med pålidelige konklusioner om, hvorfor en plan blev valgt, og hvordan jeg kan optimere den med Indekser, omskrivninger eller opdatering af statistikker.
Praksis: Spor en enkel filterforespørgsel
Med SELECT * FROM t1 WHERE a < 10 tjekker jeg under join_preparation, om motoren har udvidet projektionen og eventuelt konsolideret betingelserne, hvilket gav mig en første Indikatorer leverer. Derefter ser jeg i blokken rows_estimation, hvor mange linjer motoren bruger til range-scan på a i forhold til en fuld tabelscanning. Hvis der findes urealistiske værdier, tolker jeg det ofte som et tegn på forældede statistikker eller manglende histogrammer. I afsnittet overvejede_udførelsesplaner Derefter kan jeg se, om indeksadgangen virkelig er blevet beregnet til at være billigere end en fuld scanning. Til sidst viser tilknytning_af_betingelser_til_tabeller, om den selektive betingelse gælder for a kommer tidligt i gang, hvilket reducerer løbetiden betydeligt sænker.
JSON-funktioner: Målrettet udtrækning af uddrag
Da sporet foreligger som JSON, filtrerer jeg målrettet deltræer med JSON_EXTRACT og udarbejder små analyser af tilbagevendende Prøve. Jeg gennemgår for eksempel blot listen over de overvejede planer for at kontrollere, om bestemte join-rækkefølger systematisk mislykkes. Ligeledes udtrækker jeg omkostningsfelter fra de bedste kandidater og sammenligner dem med ANALYZE-data for at afdække fejlagtige antagelser. Ved hjælp af enkle visninger eller gemte procedurer automatiserer jeg disse kontroller til mine diagnosesessioner. På denne måde opbygger jeg et let Overvågning til optimeringsbeslutninger uden at aktivere permanent sporing.
Typiske anvendelsestilfælde og fordele
Jeg bruger Trace, når EXPLAIN viser en uventet fuld scanning, og jeg vil finde årsagen til, at en Indeks vil finde ud af. Ligeledes giver sporingen mig for mange tabeller en begrundelse for den valgte rækkefølge af sammenkoblinger, hvilket viser mig vejen til alternative planer. Ved versionsskift gemmer jeg traces før og efter opdateringen for at kunne vurdere ændringer i optimizerens adfærd. I forbindelse med strategiske tuning-spørgsmål hjælper dette overblik mig med at interne optimeringsmekanismer, som jeg knytter til sporingsresultaterne. På den måde træffer jeg en struktureret beslutning om, hvorvidt jeg skal justere indekser, statistikker eller formuleringen af forespørgslerne justeringsskrue sæt.
Bedste praksis inden for produktion
Jeg aktiverer sporing konsekvent som Session-Indstil og afslut diagnosen ordentligt, så snart jeg har nok data. Ved store spor øger jeg optimizer_trace_max_mem_size kun kortvarigt, og indstiller værdien til et lavt niveau igen bagefter. Inden jeg deler JSON-filer, maskerer jeg følsomme konstanter, kommentartekster eller forretningsmæssige nøgletal. Jeg bruger sporing målrettet som et diagnosticeringsværktøj, mens jeg til løbende overvågning foretrækker logfiler over langsomme forespørgsler, præstationsoversigter eller eksterne profilere. Denne disciplin holder systemerne strømlinede og forhindrer unødvendig Overhead i den daglige drift.
Optimizer Trace i værktøjssamlingen
For at opnå en helhedsorienteret optimering kortlægger jeg kæden bestående af forståelse af planen, årsagsanalyse og systemmåling og sammenkæder disse Resultater. EXPLAIN viser mig planen, ANALYZE bekræfter de faktiske omkostninger, og sporet giver baggrunden for beslutningen. Samtidig ser jeg på koncepter inden for query-execution-plan for at kunne indordne mønstre i nøglevalg, kardinaliteter og join-strategier. Et godt supplement til denne vinkel er det kompakte overblik over Planer for udførelse af forespørgsler, som jeg bruger som reference i arkitektoniske spørgsmål. Herfra udleder jeg pålidelige Prioriteringer til indeksering, omskrivninger og parametre.
Dyk dybere ned: range_analysis og valg af nøgler
Der er ofte en blok i sporet range_analysis for hver tabel, hvor jeg kan se, hvilke indekser der kom i betragtning til Range-, Ref- eller EQ-Ref-adgang. Optimizeren sammenligner her alternativer som „range på idx_a“, „range på idx_b“ eller „fuld scanning“, tildeler dem omkostninger og forventede rækker og markerer vinderen. Hvis jeg ser, at et fornuftigt indeks er blevet forkastet på grund af høje omkostninger, ser jeg som det næste på de underliggende selektiviteter og statistikker. Hvis antagelserne ikke stemmer, kan en ANALYSE TABLE (evt. med vedvarende statistik) eller oprettelsen af en mere målrettet Dækningsindeks omstøde afgørelsen.
Det er også nyttigt at se på opdelinger af sammensatte indekser: Sporet dokumenterer, om betingelsen kun bruger indeksets første kolonne, eller om yderligere prædikater kan anvendes, og andre nøglekolonner dermed bliver relevante. Ud fra dette afgør jeg, om jeg skal omformulere prædikaterne (f.eks. undgå funktioner) eller udvide indekset, så typiske filtreringer og sorteringer dækkes.
Join-operationer i detaljer: Semijoins, BKA/MRR og join-buffere
Ved forespørgsler på flere tabeller viser sporingsafsnittene, om og hvilken semijoin-strategi der er blevet overvejet (f.eks. FirstMatch, DuplicateWeedout, LooseScan eller Materialization). Der kan jeg se, hvorfor en variant blev forkastet – f.eks. på grund af høje materialiseringsomkostninger eller for lav selektivitet. Også Batched Key Access (BKA) og Læsning af flere områder (MRR) vises i sporet, hvis de er aktiveret. Disse teknikker samler nøgleopslag og forbedrer cache-lokaliteten. Hvis BKA/MRR ikke vises i sporet, tjekker jeg optimizer_switch og parametre som join_cache_level. I arbejdsbelastninger med mange tilfældige nøgleopslag kan join-fasen dermed fremskyndes mærkbart, hvilket kan bekræftes med EXPLAIN ANALYZE.
Desuden er størrelsen og typen af join-bufferen afgørende: Sporing viser, om der er kørt nested-loop-varianter med eller uden buffer, og hvor filtrene træder i kraft. Jeg vurderer, om yderligere indekser på join-nøgler eller en omskrivning for at reducere mellemsresultaterne er det mere effektive valg end at øge bufferstørrelserne.
Underforespørgsler, afledte tabeller og visninger
På join_preparation Jeg synes, at subforespørgsler i EXISTS/IN-form i Semijoins blev omdannet (in_to_exists), om afledte tabeller er sammenlagt (afledt_sammenfletning) eller er blevet realiseret, og om Kondition Pushdown helt ned til afledte tabeller. Disse trin er afgørende, fordi en manglende sammenfletning kan føre til en kostbar materialisering. Hvis jeg i sporingloggen gentagne gange ser materialiseringsbeslutninger med høje omkostninger, tester jeg, om en eksplicit STRAIGHT_JOIN, et tip eller en omstrukturering af forespørgslen (f.eks. Common Table Expressions med målrettede filtre) får motoren til at vælge en mere effektiv strategi. Når det gælder visninger, tjekker jeg, om optimeringsværktøjet opløser visningens indhold tilstrækkeligt, eller om der mangler yderligere indekser i den underliggende tabel.
Partitionering og beskæring
For partitionerede tabeller viser sporet, hvilke partitioner der er blevet udelukket på grundlag af partitionsnøgler og prædikater (Partitionsbeskæring). Hvis den forventede pruning udebliver, er det et tegn på, at filtrene bør formuleres tidligere og på en måde, der tager højde for partitionsnøglen. Jeg er desuden opmærksom på samspillet mellem partitionering og indekser: Mangler der lokale eller globale indekser, kan motoren trods pruning tjekke et uforholdsmæssigt stort antal rækker, hvilket kan ses i sporet som høje scanningsomkostninger.
Målrettet verifikation af hints, indeksangivelser og optimizer_switch
Jeg bruger Trace til at undersøge effekten af hints og parameteromskiftere for at besætte. Hvis jeg f.eks. sætter. FORCE-INDEKS eller et optimeringsforslag kan jeg i sporingen se, om alternativet virkelig blev tvunget, og hvordan det blev vurderet. Via optimizer_switch kan jeg midlertidigt aktivere eller deaktivere strategier (f.eks. for semijoin-, index_merge- eller derived_merge-beslutninger). Trace-udskriften fungerer derefter som bevis for, om motoren har accepteret specifikationerne, eller om andre begrænsninger (f.eks. kardinaliteter) fortsat er afgørende. Valgfrit bruger jeg formateringsflag som one_line eller end_markers på optimizer_trace-streng for at tilpasse læsbarheden til mit analyseværktøj.
Update/DELETE og skrivestier
Optimizer Trace er ikke begrænset til SELECT. Ved UPDATE- og DELETE-sætninger kan jeg også se, hvordan adgangsveje vælges, og om filtre træder i kraft tidligt nok til at holde antallet af berørte rækker lavt. Jeg kontrollerer, om et WHERE-filter ikke er sargable, eller om et manglende indeks fører til en bred scanningsfase, inden den egentlige ændring udføres. Ud fra sporet kan jeg afgøre, om et kompakt indeks (f.eks. kun de nødvendige kolonner) undgår unødvendige frem-og-tilbage-adgange og dermed reducerer låsninger og logvolumen.
Sikkerhed, privilegier og foruddefinerede sætninger
For at jeg kan læse sporet fuldt ud, skal jeg have tilstrækkelige objektrettigheder – hvis disse mangler, angiver feltet INSUFFICIENT_PRIVILEGES Begrænsninger. I produktionsnære scenarier bruger jeg derfor de samme loginoplysninger som applikationen eller en specielt autoriseret diagnosekonto. Ved forberedte sætninger viser sporingstjenesten typisk allerede den optimerede form med bundne parametre, hvilket giver mig mulighed for at vurdere selektiviteter uden at afsløre følsomme konstanter. Hvis jeg er nødt til at dele sporingstjenester, maskerer jeg parameterværdier eller erstatter dem med repræsentative intervaller for at overholde kravene til databeskyttelse.
Automatisering: Registrering, differentiering og dokumentation af spor
For at sikre reproducerbare analyser gemmer jeg spor stikprøvevis i en diagnosetabel og forsyner dem med metadata såsom skema, version, sessionsvariabler og tidsstempler. På den måde kan jeg før og efter indeksændringer eller versionsopgraderinger diffen, hvilke beslutninger der er blevet udsat. Det er praktisk at inddele blokkene overvejede_udførelsesplaner og rows_estimation at gemme separat, så jeg hurtigt kan sammenligne ændringer i omkostningerne. Mindre hjælpeforespørgsler udtrækker den valgte sammenkædningsrækkefølge og de beregnede omkostninger – for eksempel med JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – og gemmer resultatet sammen med EXPLAIN- og ANALYZE-udskrifterne. På den måde opstår der en pålidelig dokumentation for hvert optimeringstrin.
Begrænsninger, versionsspecifikke egenskaber og sammenligning med MySQL
Tracens nøglestrukturer er baseret på MySQL, men detaljer og feltnavne kan variere en smule afhængigt af MariaDB-versionen. Derfor retter jeg min opmærksomhed mod semantiske Afsnit (Rewrites, Rows-Estimation, betragtede planer, Condition-Attachments), i stedet for at lade mig irritere af kosmetiske forskelle. Vigtigt: I MariaDB er fokus på den sidste instruktion i den aktive forbindelse. Hvis man analyserer mange på hinanden følgende sætninger, skal man derfor læse dem umiddelbart efter udførelsen eller automatisk via en hook, så ingen relevante spor overskrives. Ved meget store JSON-filer tager jeg højde for hukommelsesbehovet og forstår MISSING_BYTES_BEYOND_MAX_MEM_SIZE som en opfordring til midlertidigt at hæve grænsen og køre analysen igen.
Konkrete JSON-udtræk til hverdagen
Til sidst et par korte uddrag, som jeg ofte bruger i praksis for hurtigt at komme til sagens kerne:
- Valgt sammenkædningsrækkefølge og kandidatlister: Jeg henter planpræfikserne og den tilhørende tabel for at kunne følge beslutningsforløbet.
- Range-alternativer og omkostninger: Jeg udtrækker listen over de indekser, der er blevet vurderet, for de mest selektive tabeller, for målrettet at kunne vurdere omskrivninger eller nye indekser.
- Filtre, der er tilføjet tidligt: Jeg læser
tilknytning_af_betingelser_til_tabeller-sektioner for at sikre, at stærke prædikater placeres så tæt på datakilden som muligt.
Med få visninger af disse udtræk har jeg et overskueligt „overblik“ over optimeringsbeslutningerne, som jeg aktiverer efter behov under diagnosesessioner og derefter deaktiverer igen.
Hyppige snublesten og fejlfinding
Hvis der mangler histogrammer, eller hvis statistikkerne er forældede, er skønningerne forkerte og medfører Planer med unødvendige fuldskanninger. Hvis jeg ser store afvigelser i kardinaliteterne i sporet, opdaterer jeg statistikkerne, opretter passende indekser eller omformulerer filtre, så de kan lagres. For korte spor genkender jeg ved hjælp af MISSING_BYTES_BEYOND_MAX_MEM_SIZE og reagerer med en midlertidigt højere grænse. Hvis ANALYZE giver bedre køretider for en alternativ rute, tjekker jeg i sporing, hvilken omkostningsfaktor der har gjort den valgte variant til den foretrukne. På den måde udfylder jeg trin for trin hullerne i min viden og opnår Klarhed om beslutningslogikken.
Kort opsummeret
MariaDB Optimizer Trace forklarer mig i et JSON-dokument, hvordan motoren omformer forespørgsler, estimerer rækker, sammenligner planer og til sidst en Sekvens vælger. Jeg aktiverer den pr. session, læser sporet ud, kontrollerer join_preparation og join-optimering og kobler disse indsigter sammen med EXPLAIN/ANALYZE. Ud fra årsagerne til afviste indekser, forsinkede filtre eller fejlagtige skøn udleder jeg konkrete tiltag: bedre indekser, mere opdaterede statistikker og klare formuleringer af forespørgsler. Ved hjælp af JSON-funktioner udtrækker jeg uddrag, genkender mønstre og dokumenterer beslutninger på en reproducerbar måde. På den måde sikrer jeg, at selv omfattende SQL-arbejdsbelastninger udføres pålideligt Strøm og sørg for, at beslutninger om tuning er gennemsigtige.
