MariaDB Query Optimizer forklaret internt: Grundlæggende principper, planer og praksis

Jeg forklarer den MariaDB-optimeringsmodul Fra praksis: hvordan han udarbejder planer, estimerer omkostninger, og hvorfor han nogle gange tager fejl. Sådan læser du SQL-udførelsesplanen målrettet, anvender indekser på en fornuftig måde og styrer optimeringsværktøjet med fakta i stedet for mavefornemmelse.

Centrale punkter

Til at begynde med vil jeg kort opsummere de vigtigste elementer, så du kan sætte de følgende afsnit i den rette sammenhæng og Oversigt beholder.

• Faser: Parsing, forberedelse, optimering og udførelse udgør livscyklussen for enhver forespørgsel.
• Omkostningsmodel: Tidsbaserede værdier i mikrosekunder styrer indeksvalg, scanninger og rækkefølgen af sammenkoblinger.
• Statistik: Kardinalitet og histogrammer er afgørende for estimeringen af selektivitet.
• Gennemsigtighed: EXPLAIN, EXPLAIN ANALYZE og Optimizer Trace åbner »blackboxen«.
• Indstilling: Indekser, omskrivning af forespørgsler, ANALYZE TABLE og omkostningsparametre øger hastigheden.

En forespørgsels livscyklus i MariaDB

Inden en plan udarbejdes, gennemgår en forespørgsel fire faser, som jeg målrettet tjekker i hverdagen for at Årsager at finde årsagen til langsomheden. Under parsningen omdanner MariaDB SQL til en intern struktur; her bliver syntaksfejl synlige. I forberedelsesfasen gennemgår motoren tabeller, kolonner og potentielle indekser og udfører enkle omformninger. Derefter følger optimeringen, hvor mulige planer beregnes og vurderes ved hjælp af en omkostningsmodel. Under udførelsen implementerer serveren den valgte plan trin for trin: læse, sammenføje, filtrere, returnere.

Jeg opdeler analysefejl tydeligt efter fase, fordi diagnoserne på den måde virker hurtigere, og Foranstaltninger virke målrettet. Oftest skyldes ydeevneproblemer optimeringen: forkerte skøn, manglende indekser eller ugunstige rækkefølger af sammenkoblinger. Parsing-fejl er trivielle, men forberedelsesfasen kan allerede indeholde finesser som opløsning af visninger eller omformulering af underforespørgsler. I udførelsesfasen bliver ineffektiviteter så uundgåeligt tydelige, hvis der tidligere er valgt en fuld scanning. Derfor starter jeg enhver undersøgelse med et struktureret overblik over alle fire faser.

Hvordan optimeringsværktøjet træffer sin beslutning internt

MariaDB arbejder på et omkostningsbaseret grundlag og vurderer alternative løsninger ved hjælp af en Omkostningsfunktion. For hver variant estimerer serveren antallet af læste rækker, selektiviteten af WHERE/ON, adgangstyper som f.eks. tabelscanning, indeksscanning og intervalscanning samt tidsforbruget for de enkelte operationer. Internt skelner serveren mellem join_preparation og join_optimization. I join_preparation udføres omskrivning af forespørgsler, forenkling af betingelser, omformning af underforespørgsler og opløsning af visninger. I `join_optimization` beregnes join-rækkefølger, indekskandidater kontrolleres via `ref_optimizer_key_uses`, antallet af rækker estimeres via rækkevidde-scanninger, og betingelser tildeles konkrete tabeller så tidligt som muligt.

Denne mekanisme forklarer, hvorfor et lille filter på det forkerte sted kan medføre dyre Konsekvenser har. Hvis »attaching_conditions_to_tables« sker for sent, trækker planen unødvendigt mange rækker med gennem sammenkoblingerne. Hvis statistikkerne er forældede, bliver »rows_estimation« og »Selectivity« forkerte; optimeringsmodulet vælger så adgangsveje, der ser gunstige ud, men i virkeligheden er langsomme. Det er netop disse justeringsmuligheder, jeg fokuserer på: bedre statistik, klarere prædikater, pænt sorterede sammensatte indekser. Derefter ændrer valg af plan sig ofte mærkbart.

Omkostningsmodel fra og med MariaDB 11.0

De nyeste udgivelser vurderer ikke længere arbejdet groft ud fra vægt, men ved hjælp af mikrosekunder til konkrete lagringsoperationer. Parametre som optimizer_disk_read_cost, optimizer_disk_read_ratio og optimizer_where_cost bringer modellen tættere på de faktiske køretider. På den måde sammenligner optimizeret indeks-range-scan med fuld scanning på baggrund af reelle tidsantagelser. LAST_QUERY_COST viser de estimerede samlede omkostninger og stemmer ofte betydeligt bedre overens med virkeligheden end tidligere. For dataintensive systemer betaler denne finere opdeling sig med det samme.

Jeg kalibrerer modellen omhyggeligt, når hardwareegenskaber strider mod standardantagelserne og dermed Planvalg forvrænge. NVMe-SSD’er, distribueret lager eller specielle cacher kan mærkbart ændre diskforholdet og læsetiderne. Små justeringer af optimizer_costs får MariaDB til at foretrække fornuftige stier. Jeg dokumenterer hver ændring og tjekker derefter EXPLAIN ANALYZE for at måle effekten. Uden målinger forbliver tuning et lotteri.

Selektivitet, statistikker og histogrammer

Gode skøn starter med præcise kardinalitet og pålidelig selektivitet. MariaDB fører statistik over forskellige værdier for hver kolonne og kan valgfrit anvende histogrammer til fordelinger. Især ujævne data – hotspots, Zipf-fordelinger, sæsonmønstre – drager fordel af histogrammer. Efter store dataændringer udfører jeg ANALYZE TABLE, så optimeringen igen kan basere sig på faktiske data. Glemmer man dette, risikerer man fuldscanninger, der objektivt set er forkerte.

Jeg planlægger ANALYZE som en regelmæssig opgave, tilpasset Ændringer i datamængden og på kritiske tabeller. Ved stærkt skæve kolonnefordelinger hjælper histogrammer med at give et realistisk billede af selektiviteten af singulære værdier. Dette reducerer fejlvurderinger ved rækkevidde-scanninger og sammenfletningsstrategier. Kombineret med passende sammensatte indekser forbedres træfsikkerheden markant. Resultat: kortere køretider og mindre I/O.

EXPLAIN og læsning af udførelsesplaner

For at gøre beslutningerne synlige bruger jeg EXPLAIN, EXPLAIN EXTENDED og FORMAT=JSON. De klassiske kolonner giver et hurtigt overblik: id, select_type, table, type, possible_keys, key, key_len, ref, rows og eventuelt filtered. En type=ALL indikerer en fuld scanning, hvilket sjældent er ønskeligt. FORMAT=JSON viser detaljeret, hvordan betingelser er blevet flyttet, og hvilke stier optimeringsværktøjet har vurderet. I forbindelse med hosting anbefaler jeg vejledningen til Kørselsplaner i hosting, for at sammenkæde planoplysninger med infrastrukturvirkninger.

For at kunne fortolke resultaterne hurtigt bruger jeg en lille tabel, der kort oversigt over de typiske værdier og dermed Fejlfortolkninger forhindres.

EXPLAIN-felt	Typisk værdi	Betydning i praksis
type	ALL, range, ref, eq_ref, const	Jo længere til højre, jo mere selektiv; ALL angiver fuld scanning.
possible_keys	Indeksliste	Indekser, der teoretisk set passer; mangler der kandidater her, mangler der struktur.
nøgle	Indeksnavn	Faktisk anvendt indeks; tomt betyder, at der ikke anvendes noget indeks.
rækker	Antal	Anslået antal læste linjer; afviger kraftigt fra virkeligheden = dårlig statistik.
filtreret	Procent	Hvor meget der sendes videre efter filteret; lavt er ofte godt.

Hvorfor optimeringsværktøjet nogle gange tager fejl

Ingen omkostningsmodel passer til alle situationer, derfor korrigerer jeg Fejl målrettet. Forældede statistikker fører til forkerte skøn over rækker og uhensigtsmæssige rækkefølger af sammenkædninger. Forkert opbyggede sammensatte indekser forhindrer indeksanvendelse ved filtrering på flere kolonner. Meget indlejrede underforespørgsler vanskeliggør effektive omskrivninger og blokerer materialisering. Manglende eller vildledende filtre tvinger motoren til at flytte mange rækker, før nyttige prædikater træder i kraft.

Jeg tjekker først, om formuleringen af forespørgslen overholder Indeks Det, der virkelig hjælper: reglen om venstre præfiks, passende sorteringsrækkefølge, undgåelse af funktioner på kolonner i WHERE-sætningen. Derefter tjekker jeg i EXPLAIN ANALYZE, om virkeligheden bekræfter estimatet. Hvis ikke, følger ANALYZE TABLE og om nødvendigt en omskrivning. Først til sidst bruger jeg FORCE INDEX eller hinting, da det kan begrænse fremtidige optimeringer.

Målrettet brug af Optimizer Trace

Hvis EXPLAIN ikke er nok, aktiverer jeg Optimizer Trace og følger med i Beslutninger i JSON-loggen. Her kan jeg se, hvilke planer der er blevet overvejet, forkastet eller godkendt. Jeg kan se, hvorfor en betingelse træder i kraft for sent, eller hvorfor et indeks ikke kom med i den endelige udvælgelse. Loggen viser også, hvordan betingelserne er blevet omarrangeret. Dette overblik skærper forståelsen og giver konkrete redskaber til den næste optimering.

Jeg gemmer relevante afsnit af sporet sammen med query-hash og Parametre. På den måde kan jeg senere sammenligne, hvilken ændring der havde hvilken effekt. Dokumentationen til MariaDB-serveren og forskellige foredrag i økosystemet beskriver felterne udførligt (kilde: MariaDB-serverens dokumentation om Query Optimizer og Optimizer Trace). Med dette værktøj finder jeg fejlagtige antagelser hurtigere end ved trial-and-error. Jeg sparer især tid ved komplekse sammenkoblinger.

Praksis: Databasetuning trin for trin

Jeg starter enhver optimering med en klar Måling. Jeg identificerer problemforespørgsler via overvågning og det Langsom forespørgselslog. Derefter sammenligner jeg EXPLAIN med EXPLAIN ANALYZE for at sammenligne den planlagte og den faktiske udførelse. Jeg tilpasser indeksstrategien til WHERE, JOIN og ORDER BY; sammensatte indekser retter jeg ind efter de hyppigste adgangspunkter. Jeg bruger kun FORCE INDEX, hvis optimeringsværktøjet vælger den forkerte kandidat på trods af korrekte statistikker.

Hvert trin indebærer pleje af Statistik: ANALYZE TABLE på tabeller med stor aktivitet, histogrammer for skæve fordelinger. Jeg forenkler unødvendige underforespørgsler, materialiserer mellemresultater efter behov og rydder op i gamle midlertidige løsninger. Ved brug af specialhardware tjekker jeg optimizer_costs, så mikrosekundmodellen holder stik. Jeg dokumenterer hver ændring med før- og efter-værdier, så effekten forbliver sporbar på lang sigt.

Typiske optimeringsproblemer og løsninger

Hvis EXPLAIN viser type=ALL, selvom possible_keys er udfyldt, ser jeg først på Selektivitet. Ofte passer kolonnerækkefølgen i det sammensatte indeks ikke, eller en funktion forhindrer brugen af indekset. I så fald vender jeg rækkefølgen, fjerner forstyrrende funktioner eller opdeler prædikater. Ved forkert join-rækkefølge undersøger jeg, om det er muligt at filtrere tidligt, f.eks. ved at placere den mere selektive tabel først. Subqueries omdanner jeg, hvor det giver mening, til joins eller TEMPORARY-tabeller.

Jeg kan også genkende forkerte beslutninger på stærkt afvigende rækker mellem plan og virkelighed. I så fald kan ANALYZE TABLE eller et histogram for den pågældende kolonne være en hjælp. Hvis selv korrekte statistikker ikke fører til det ønskede resultat, overvejer jeg at bruge eksplicitte hints. Først sikrer jeg mig, at der er kontrolmålinger og måleværdier, så senere versioner af optimeringsværktøjet ikke bliver bremset af de gemte data. Her betaler det sig at være disciplineret med dokumentationen.

Hosting-sammenhæng og driftsmæssige aspekter

Forespørgselskvaliteten og infrastrukturen skal passe sammen, ellers går applikationen til spilde Potentiale. Hurtige SSD’er, konsistente cacher og en velfungerende konfiguration er grundlaget for, at optimeringsværktøjet kan træffe gode beslutninger. Høj trafik tillader ikke fuldstændige scanninger; blot få dårlige forespørgsler kan bremse hele systemer. Til MySQL/MariaDB-miljøer i produktiv drift giver praktiske råd som MySQL-optimeringsværktøj Nyttige tanker om kombinationen af plan og platform. Hvis man tager højde for dette niveau, kan man forhindre flaskehalse, før de eskalerer.

Jeg kombinerer altid plananalyse med nøgletal vedrørende I/O, latenstid og parallelitet. Hvis værdierne ikke stemmer overens med den antagne omkostningsmodel, tjekker jeg parametrene. Derefter ser jeg på bufferstørrelser, parallelle arbejdsbelastninger og fordelingen af hotsets. Med dette overblik lykkes det at drive forespørgsler og ressourcer harmonisk og holde spidsbelastninger under kontrol.

Join- og adgangsstier i praksis

Jeg afklarer mange misforståelser ved at Adgangstyper afveje dem målrettet mod hinanden. En interval- eller ref-Adgang lykkes næsten altid ALL. Ved logiske sammenkoblinger på entydige nøgler (eq_ref) er planerne særligt stabile. Jeg tjekker desuden, om en Dækningsindeks der fuldt ud dækker forespørgslen: Hvis alle de nødvendige kolonner er med i indekset, sparer MariaDB dyre tabelladgange. Pushdown i indekstilstand (ICP) hjælper med at kontrollere yderligere WHERE-betingelser allerede i indekset – det reducerer antallet af returnerede rækker og I/O.

Omkring Indeksfusion MariaDB kan kombinere flere indekser (skæringsmængde/union). Det er nyttigt ved OR-prædikater eller flere selektive betingelser, men ofte langsommere end et velvalgt sammensat indeks. Jeg vurderer desuden MRR (Multi-Range Read) og BKA (Batched Key Access). MRR sorterer de primærnøgler, der skal læses, for at udjævne tilfældig I/O; BKA samler join-opslag og giver især fordele ved ikke-overlappende joins. I praksis tester jeg BKA/MRR via optimizer_switch og kontrollerer med EXPLAIN ANALYZE, om I/O-mønstrene falder. Hvis MariaDB derimod anvender Blok med indlejret sløjfe (BNL) er det som regel mere fordelagtigt at bruge flere join-buffere (join_buffer_size) – eller en omskrivning, der muliggør ægte indeks-joins.

-- Eksempel: Sammensat indeks til sammenføjning + filtrering + sortering
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Typisk forespørgsel
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;

Med ovenstående indeks kan optimeringsmodulet vælge den mest selektive rækkefølge, udvurdere filtre tidligt og ofte udføre sorteringen uden yderligere filsortering.

ORDER BY, GROUP BY, fil-sortering og midlertidige tabeller

Sortering og sammenfatning tager tid. Jeg sørger for, at ORDER BY og GROUP BY kan køre i indeksrækkefølgen. Det fungerer, hvis præfikset og retningen passer nøjagtigt. Ellers træder en Filsortering med sorteringsbuffer (sort_buffer_size) og eventuelt en midlertidig tabel. Hvis resultatsættet indeholder brede TEXT-/BLOB-kolonner, mærkes MariaDB hurtigere på disk TEMP-tabeller (Aria). Jeg forebygger dette ved kun at vælge de kolonner, jeg har brug for, ved først at indlæse store felter til sidst eller ved at bruge præfikser med begrænset længde.

Når jeg laver aggregeringer, bruger jeg, hvor det er muligt, Løs indeksscanning (f.eks. GROUP BY på den primære indeksdel) og vælg sammensatte indekser i overensstemmelse med grupperingen. Når mellemresultaterne bliver store, skalerer en materialisering med fornuftige nøgler bedre end en enkelt mega-sammenføjning. Jeg måler regelmæssigt handler-metrikker og Created_tmp_*-tællere for at afdække hotspots i sortering og midlertidige tabeller.

Underforespørgsler, semi-join og materialisering

Mange underforespørgsler kan omformuleres effektivt i forberedelsesfasen. IN/EXISTS-konstruktioner kan bruges som Semi-Join kører med strategier som materialisering eller LooseScan. Jeg tjekker, om optimeringsprogrammet er et afledt_sammenfletning kunne udføre: Hvis en afledt tabel (eller en WITH-CTE) indsættes i den ydre plan, er dens indekser umiddelbart tilgængelige. Lykkes det ikke, havner underforespørgslen i en midlertidig tabel – jeg giver den så, hvis det er muligt, en nøgle (f.eks. ved hjælp af SELECT DISTINCT/ORDER BY på nøglekolonner), så sammenkædninger ikke ender i intetheden.

-- Eksempel: EXISTS i stedet for IN og en afledt tabel, der kan bruges i en merge-sætning
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Afledning med entydige nøgler
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;

Jeg bruger EXPLAIN FORMAT=JSON til at kontrollere, om materialiseret eller afhængig underforespørgsel blev valgt, og om der er betingelser (condition pushdown) handle i god tid.

Partitionering og beskæring

Partitionering er ikke et alternativ til indekser, men kan Datamængde pr. adgang reducere drastisk. Optimizeren foretager kun en korrekt beskæring, hvis prædikatet opfylder Partitionsnøgle rammer entydigt og ikke forvanskes af funktioner. Derfor undgår jeg udtryk som DATE(created_at) i WHERE-sætningen på partitionerede tabeller og arbejder i stedet med intervalgrænser. EXPLAIN viser, hvilke partitioner der læses; brede intervaller tyder på dårlig pruning.

For mange små partitioner øger planlægningsomkostningerne. Derfor vælger jeg en fornuftig granularitet (f.eks. månedligt i stedet for dagligt), holder statistikkerne opdaterede for hver partition (ANALYZE PARTITION) og kontrollerer, om vigtige indekser findes lokalt i partitionerne. I migrationsprojekter tager jeg højde for indvirkningen på replikering og backup – begge dele påvirker, hvor aggressivt jeg partitionerer.

Sargability og rewrite-mønstre

Den enkleste løsning er stadig Sargability – Betingelser, der gør det muligt at bruge indekser. Jeg undgår funktioner på kolonner i WHERE-sætningen, reducerer konstante udtryk på kolonnens side og opdeler OR-betingelser efter behov i UNION ALL. Til LIKE-søgninger uden indledende anker ("%foo") er et BTREE-indeks ikke egnet; her planlægger jeg at bruge fuldtekstsøgning eller en passende søgetjeneste. Til beregninger bruger jeg indekserede genererede kolonner, så optimeringsværktøjet kan genfinde logikken i indekset.

-- Anti-mønster: Funktion på kolonne
WHERE DATE(created_at) = '2026-08-01'
-- Bedre: Interval baseret på råværdi
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-mønster: OR forhindrer indeksering
WHERE status = 'open' OR customer_id = 42
-- Bedre: to søgninger med UNION ALL og hver med sit eget indeks
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

Når det gælder sammensatte indekser, mener jeg, at reglen om venstre præfiks Overhold dette nøje: Sorter kolonnerne efter selektivitet og efter den sortering, der senere skal bruges. Hvis jeg har brug for en faldende ORDER BY, tager jeg højde for det i indeksopbygningen – på den måde undgår jeg en fil-sortering.

Optimizer-kontakt og finjustering af omkostninger

Inden jeg begynder at se på forespørgsler, tjekker jeg optimizer_switch og bufferhukommelse. Funktioner som mrr, batched_key_access, index_merge, semijoin, afledt_sammenfletning eller condition_pushdown_for_afledt kan tilpasses pr. session. Jeg aktiverer kandidater målrettet til en testsession, måler med EXPLAIN ANALYZE og ruller tilbage, hvis der ikke opnås nogen effekt. Join-stien drager fordel af tilstrækkelig join_buffer_size; store sorter af sort_buffer_size. Samtidig holder jeg øje med bufferne i forhold til samtidigheden, så serveren ikke begynder at swappe under parallel belastning.

På omkostningsniveau justerer jeg, hvis det er nødvendigt, de allerede nævnte optimizer_costs i mikrosekunder. Min fremgangsmåde: små, reversible trin med dokumenterede målepunkter. Jeg bruger LAST_QUERY_COST til at kontrollere rimeligheden og gentage målinger med realistiske parameterværdier, da planer i høj grad kan afhænge af konkrete værdier.

Planstabilitet, regressioner og team-workflow

Selv en god plan kan blive påvirket af stigende datamængder eller versionsskift vippe. Derfor sikrer jeg mig viden om planlægningen: Query-hashes, EXPLAIN-JSON, uddrag af optimizer-traces og EXPLAIN ANALYZE-køretider. Ændringer af indekser og omskrivninger foregår hos mig som pull requests med før-og-efter-dokumentation. I CI/CD-miljøer tester jeg automatisk kritiske forespørgsler mod repræsentative datatilstande. På den måde opdager jeg Regressionsplaner tidligt.

I vanskelige tilfælde mener jeg, at Tips (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch pr. forespørgsel) er tilgængelige som en sidste udvej, men brug dem sparsomt og med en udløbsdato. Det er bedre at løse årsagerne – statistik, indekser, formulering. I teams sikrer en letforståelig vejledning om skalerbarhed, indeksdesign og måledisciplin, at nye funktioner ikke ubemærket medfører ydeevneproblemer.

Kort oversigt: Fra plan til resultat

Hvem kan bruge Planlæg Forståelse styrer ydeevnen. Faser som parsing, forberedelse, optimering og udførelse forklarer, hvor tiden går tabt. Den tidsbaserede omkostningsmodel fra version 11.0, opdaterede statistikker og histogrammer gør estimaterne pålidelige. EXPLAIN, EXPLAIN ANALYZE og Optimizer Trace skaber gennemsigtighed, som jeg omsætter til konkrete tiltag. Med en velfungerende indeksstrategi, et klart forespørgselsdesign og en passende infrastruktur leverer MariaDB-forespørgsler konstant hurtige svar.