Een interne uitleg over de MariaDB Query Optimizer: basisprincipes, plannen en praktijk

Ik leg het uit MariaDB-optimalisator uit de praktijk: hoe hij plannen opstelt, kosten inschat en waarom hij er soms naast zit. Zo lees je het SQL-uitvoeringsplan doelgericht, zet je indexen op een zinvolle manier in en stuur je de optimizer aan met feiten in plaats van op gevoel.

Centrale punten

Om te beginnen zal ik de belangrijkste onderdelen kort samenvatten, zodat je de volgende paragrafen beter kunt plaatsen en de Overzicht behoudt.

• Fasen: Parsen, voorbereiden, optimaliseren en uitvoeren vormen de levenscyclus van elke query.
• Kostenmodel: Op tijd gebaseerde waarden in microseconden bepalen de indexkeuze, scans en de volgorde van joins.
• Statistieken: De cardinaliteit en het histogram zijn bepalend voor de schatting van de selectiviteit.
• Transparantie: EXPLAIN, EXPLAIN ANALYZE en Optimizer Trace openen de blackbox.
• Afstemmen: Indexen, query-rewrites, ANALYZE TABLE en kostenparameters zorgen voor snelheid.

Levenscyclus van een query in MariaDB

Voordat er een plan ontstaat, doorloopt een query vier fasen, die ik in de dagelijkse praktijk doelgericht controleer om Oorzaken om traagheid op te sporen. Tijdens het parseren zet MariaDB SQL om in een interne structuur; syntaxisfouten vallen hier op. In de voorbereidingsfase controleert de engine tabellen, kolommen en mogelijke indexen en voert eenvoudige transformaties uit. Vervolgens vindt de optimalisatie plaats, waarbij kandidaat-plannen worden berekend en aan de hand van een kostenmodel worden beoordeeld. Tijdens de uitvoering voert de server het gekozen plan stap voor stap uit: lezen, samenvoegen, filteren, teruggeven.

Ik maak een duidelijk onderscheid tussen analysefouten per fase, omdat diagnoses op die manier sneller effect sorteren en Maatregelen doelgericht te werk gaan. Meestal liggen prestatieproblemen in de optimalisatie: verkeerde schattingen, ontbrekende indexen of ongunstige volgordes van joins. Parsingfouten zijn triviaal, maar de voorbereidingsfase kan al finesses bevatten zoals het oplossen van views of het herschikken van subquery’s. Tijdens de uitvoering vallen inefficiënties genadeloos op als er eerder voor een volledige scan is gekozen. Daarom begin ik elk onderzoek met een gestructureerd overzicht van alle vier de fasen.

Hoe de Optimizer intern beslissingen neemt

MariaDB hanteert een op kosten gebaseerde aanpak en beoordeelt alternatieve uitvoeringen aan de hand van een Kostenfunctie. Voor elke variant maakt de server een schatting van het aantal gelezen rijen, de selectiviteit van WHERE/ON, toegangsmethoden zoals table scan, index scan en range scan, en de tijd die afzonderlijke bewerkingen in beslag nemen. Intern maakt de server onderscheid tussen join_preparation en join_optimization. In join_preparation vinden query-herschrijvingen, vereenvoudigingen van voorwaarden, herformuleringen van subquery's en het oplossen van views plaats. join_optimization berekent join-volgordes, controleert indexkandidaten via ref_optimizer_key_uses, schat het aantal rijen via range-scans en wijst voorwaarden zo vroeg mogelijk toe aan specifieke tabellen.

Dit mechanisme verklaart waarom een klein filter op de verkeerde plek dure Gevolgen heeft. Als `attaching_conditions_to_tables` laat plaatsvindt, sleept het plan onnodig veel rijen door joins. Als statistieken verouderd zijn, komen `rows_estimation` en `Selectivity` verkeerd uit; de optimizer kiest dan voor gunstige, maar in werkelijkheid trage toegangspaden. Ik pak precies deze knoppen aan: betere statistieken, duidelijkere predicaten, netjes gesorteerde samengestelde indexen. Daarna verandert de keuze van het plan vaak merkbaar.

Kostenmodel vanaf MariaDB 11.0

In de nieuwste releases wordt het werk niet meer grofweg op basis van gewichten beoordeeld, maar met microseconden voor specifieke opslagbewerkingen. Parameters zoals optimizer_disk_read_cost, optimizer_disk_read_ratio en optimizer_where_cost brengen het model dichter bij de werkelijke uitvoeringstijden. Zo vergelijkt de optimizer een index-range-scan met een volledige scan op basis van reële tijdsveronderstellingen. LAST_QUERY_COST geeft de geschatte totale kosten weer en komt vaak aanzienlijk beter overeen met de werkelijkheid dan voorheen. Voor gegevensintensieve systemen loont dit fijnmazigere raster zich onmiddellijk.

Ik kalibreer het model zorgvuldig wanneer hardware-eigenschappen de standaardaannames tegenspreken en daarmee de Plankeuze vervormen. NVMe-SSD’s, gedistribueerde opslag of speciale caches kunnen de schijverhouding en leestijden merkbaar beïnvloeden. Kleine aanpassingen aan `optimizer_costs` zorgen ervoor dat MariaDB de voorkeur geeft aan zinvolle paden. Ik documenteer elke wijziging en controleer vervolgens EXPLAIN ANALYZE om het effect te meten. Zonder metingen blijft tuning een gok.

Selectiviteit, statistieken en histogrammen

Goede schattingen beginnen met een goede cardinaliteit en betrouwbare selectiviteit. MariaDB houdt statistieken bij van verschillende waarden per kolom en kan optioneel gebruikmaken van histogrammen voor verdelingen. Juist ongelijkmatige gegevens – hotspots, Zipf-verdelingen, seizoenspatronen – profiteren van histogrammen. Na grote gegevenswijzigingen voer ik ANALYZE TABLE uit, zodat de optimalisatie weer op basis van feiten werkt. Wie dit vergeet, riskeert volledige scans die objectief gezien onjuist zijn.

Ik plan ANALYZE in als een regelmatige taak, afgestemd op Veranderingen in het gegevensvolume en op kritieke tabellen. Bij sterk scheve kolomverdelingen helpen histogrammen om de selectiviteit van afwijkende waarden realistisch in kaart te brengen. Dit vermindert verkeerde inschattingen bij range-scans en merge-strategieën. In combinatie met geschikte samengestelde indexen neemt de trefnauwkeurigheid drastisch toe. Resultaat: kortere looptijden en minder I/O.

EXPLAIN en uitvoeringsplannen lezen

Om beslissingen zichtbaar te maken, gebruik ik EXPLAIN, EXPLAIN EXTENDED en FORMAT=JSON. De klassieke kolommen geven een snel overzicht: id, select_type, table, type, possible_keys, key, key_len, ref, rows en eventueel filtered. Een type=ALL duidt op een volledige scan, wat zelden gewenst is. FORMAT=JSON toont gedetailleerd hoe voorwaarden zijn verplaatst en welke paden de optimizer heeft geëvalueerd. In de context van hosting raad ik de handleiding aan over Uitvoeringsplannen bij hosting, om informatie over plannen te koppelen aan infrastructuureffecten.

Om snel een beeld te krijgen, gebruik ik een kleine tabel waarin de typische waarden kort worden weergegeven, en daarmee Misinterpretaties voorkomen.

EXPLAIN-veld	Typische waarde	Betekenis in de praktijk
type	ALL, range, ref, eq_ref, const	Hoe verder naar rechts, hoe selectiever; ALL geeft aan dat er een volledige scan wordt uitgevoerd.
possible_keys	Indexlijst	Indexen die in theorie kloppen; als er hier kandidaten ontbreken, ontbreekt er structuur.
sleutel	Indexnaam	Daadwerkelijk gebruikte index; leeg betekent dat er geen index wordt gebruikt.
rijen	Aantal	Geschat aantal gelezen regels; wijkt sterk af van de werkelijkheid = slechte statistiek.
gefilterd	Procent	Hoeveel er na het filter wordt doorgelaten; weinig is vaak goed.

Waarom de Optimizer er soms naast zit

Geen enkel kostenmodel is geschikt voor elke situatie, daarom pas ik het aan Fouten gericht. Verouderde statistieken leiden tot onjuiste schattingen van het aantal rijen en ongunstige volgordes van joins. Verkeerd opgebouwde samengestelde indexen verhinderen het gebruik van indexen bij filters met meerdere kolommen. Zeer geneste subquery's bemoeilijken effectieve herschrijvingen en blokkeren materialisatie. Ontbrekende of misleidende filters dwingen de engine om veel rijen te verplaatsen voordat bruikbare predicaten van kracht worden.

Ik controleer eerst of de formulering van de zoekopdracht voldoet aan de Index Wat echt helpt: de regel voor linkse prefixen, de juiste sorteervolgorde, het vermijden van functies op kolommen in de WHERE-clausule. Daarna kijk ik in EXPLAIN ANALYZE of de werkelijkheid de schatting bevestigt. Zo niet, dan volg ik met ANALYZE TABLE en, indien nodig, een herschrijving. Pas als laatste gebruik ik FORCE INDEX of hinting, omdat dit toekomstige optimalisaties kan beperken.

Optimizer Trace doelgericht gebruiken

Als EXPLAIN niet volstaat, schakel ik de Optimizer Trace in en volg ik Beslissingen in het JSON-logboek. Daarin zie ik welke plannen zijn overwogen, afgewezen of geaccepteerd. Ik begrijp waarom een voorwaarde pas laat van kracht wordt of waarom een index niet in aanmerking is gekomen. Het logboek laat ook zien hoe voorwaarden zijn herschikt. Dit inzicht verscherpt het begrip en biedt concrete aanknopingspunten voor de volgende optimalisatie.

Ik sla relevante delen van de trace op, samen met de query-hash en Parameterswaarderen. Zo kan ik later vergelijken welke wijziging welk effect heeft gehad. De documentatie van de MariaDB-server en diverse presentaties binnen het ecosysteem beschrijven de velden uitvoerig (bron: MariaDB-serverdocumentatie over de Query Optimizer en Optimizer Trace). Met deze tool vind ik onjuiste aannames sneller dan met trial-and-error. Ik bespaar vooral tijd bij complexe joins.

Praktijk: Database-optimalisatie stap voor stap

Ik begin elke optimalisatie met een duidelijke Meting. Ik identificeer probleemmeldingen via monitoring en het Logboek langzame zoekopdrachten. Vervolgens vergelijk ik EXPLAIN met EXPLAIN ANALYZE om het plan en de werkelijkheid naast elkaar te leggen. Ik pas de indexstrategie aan op WHERE, JOIN en ORDER BY; samengestelde indexen richt ik af op de meest voorkomende toegangspunten. FORCE INDEX gebruik ik alleen als de optimizer ondanks correcte statistieken de verkeerde kandidaat kiest.

Bij elke stap hoort het onderhoud van de Statistieken: ANALYZE TABLE op tabellen met veel activiteit, histogrammen voor scheve verdelingen. Ik vereenvoudig overbodige subquery’s, materialiseer tussentijdse resultaten indien nodig en ruim oude workarounds op. Bij speciale hardware controleer ik de optimizer_costs, zodat het microsecondenmodel klopt. Elke wijziging documenteer ik met waarden van voor en na, zodat het effect blijvend traceerbaar blijft.

Typische problemen met optimalisatieprogramma's en oplossingen

Als EXPLAIN type=ALL aangeeft dat possible_keys gevuld is, kijk ik eerst naar Selectiviteit. Vaak klopt de kolomvolgorde in de samengestelde index niet, of verhindert een functie het gebruik van de index. Dan draai ik de volgorde om, verwijder ik storende functies of splits ik predicaten. Bij een verkeerde volgorde van joins controleer ik of vroegtijdig filteren mogelijk is, bijvoorbeeld door de selectievere tabel eerder te plaatsen. Subquery’s zet ik, waar zinvol, om in joins of TEMPORARY-tabellen.

Ik herken verkeerde beslissingen ook aan sterk afwijkende rijen tussen plan en werkelijkheid. Dan helpt `ANALYZE TABLE` of een histogram voor de betreffende kolom. Als zelfs correcte statistieken niet tot het gewenste resultaat leiden, overweeg ik expliciete hints. Vooraf zorg ik voor een controlemeting en meetwaarden, zodat latere versies van de optimizer niet onnodig worden afgeremd. Discipline bij het documenteren loont hier de moeite.

Hostingcontext en operationele aspecten

De kwaliteit van de zoekopdrachten en de infrastructuur moeten op elkaar zijn afgestemd, anders gaat de toepassing zonde verloren Potentieel. Snelle SSD’s, consistente caches en een nette configuratie vormen de basis waarop de Optimizer goede beslissingen neemt. Bij veel verkeer is er geen ruimte voor volledige scans; een paar slechte query’s kunnen hele systemen vertragen. Voor MySQL/MariaDB-omgevingen in productie bieden we praktische tips zoals MySQL-optimalisator nuttige aandachtspunten over de combinatie van plan en platform. Wie op dit niveau meedenkt, voorkomt knelpunten voordat ze uit de hand lopen.

Ik koppel plananalyse altijd aan statistieken over I/O, latentie en gelijktijdigheid. Als de waarden niet overeenkomen met het aangenomen kostenmodel, controleer ik de parameters. Daarna kijk ik naar de grootte van de buffers, parallelle workloads en de verdeling van de hotsets. Op deze manier lukt het om query's en resources op een evenwichtige manier te laten draaien en pieken onder controle te houden.

Join- en toegangspaden in de praktijk

Veel misverstanden neem ik weg door de Toegangstypen doelgericht tegen elkaar afwegen. Een bereik- of ref-Toegang lukt bijna altijd ALLES. Bij logische koppelingen op unieke sleutels (eq_ref) zijn de plannen bijzonder stabiel. Ik controleer bovendien of een Dekkingsindex de query volledig afhandelt: als alle benodigde kolommen in de index staan, bespaart MariaDB dure tabeltoegangen. Indexvoorwaarde Pushdown (ICP) helpt om extra WHERE-voorwaarden al in de index te controleren – dit vermindert het aantal geretourneerde rijen en de I/O.

Over Index samenvoegen kan MariaDB meerdere indexen combineren (intersectie/unie). Dat is handig bij OR-predicaten of meerdere selectievoorwaarden, maar vaak langzamer dan een goed gekozen samengestelde index. Ik evalueer bovendien MRR (Multi-Range Read) en BKA (Batched Key Access). MRR sorteert de te lezen primaire sleutels om willekeurige I/O te egaliseren; BKA bundelt join-lookups en levert vooral voordelen op bij niet-overlappende joins. In de praktijk test ik BKA/MRR via optimizer_switch en controleer ik met EXPLAIN ANALYZE of de I/O-patronen afnemen. Als MariaDB daarentegen kiest voor de Blok met geneste lussen (BNL), is het meestal de moeite waard om de join-buffer (join_buffer_size) te vergroten – of een rewrite te gebruiken die echte index-joins mogelijk maakt.

-- Voorbeeld: samengestelde index voor join + filter + sortering
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Typische opvraging
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;

Met bovenstaande index kan de optimizer de meest selectieve volgorde kiezen, filters vroegtijdig evalueren en de sortering vaak uitvoeren zonder extra bestandssortering.

ORDER BY, GROUP BY, Filesort en tijdelijke tabellen

Sorteren en samenvoegen kost tijd. Ik zorg ervoor dat ORDER BY en GROEP DOOR op de indexvolgorde kunnen lopen. Dit lukt als het voorvoegsel en de richting exact overeenkomen. Anders treedt een Bestands sorteren met sorteerbuffer (sort_buffer_size) en eventueel een tijdelijke tabel. Als de resultaten set brede TEXT/BLOB-kolommen bevat, valt MariaDB sneller op op schijf TEMP-tabellen (Aria). Ik neem dit voor door alleen de benodigde kolommen te selecteren, grote velden pas aan het einde te laden of met prefixen van beperkte lengte te werken.

Bij aggregaties gebruik ik, waar mogelijk, Losse indexscan (bijv. GROUP BY op het leidende indexgedeelte) en kies samengestelde indexen langs de groepering. Wanneer tussentijdse resultaten groot worden, schaalt een materialisatie met zinvolle sleutels beter dan één enkele mega-join. Ik meet regelmatig handler-statistieken en Created_tmp_*-tellers om hotspots bij het sorteren en in tijdelijke tabellen op te sporen.

Subquery's, semi-join en materialisatie

Veel subquery’s kunnen tijdens de voorbereiding efficiënt worden herschreven. IN/EXISTS-constructies kunnen worden omgezet in Semi-join werken, met strategieën zoals materialisatie of LooseScan. Ik controleer of de optimizer een afgeleide samenvoeging kon uitvoeren: als een afgeleide tabel (of een WITH-CTE) in het buitenste plan wordt opgenomen, zijn de bijbehorende indexen direct beschikbaar. Als dat niet lukt, komt de subquery in een tijdelijke tabel terecht – ik geef deze dan, indien mogelijk, een sleutel (bijvoorbeeld door SELECT DISTINCT/ORDER BY op sleutelkolommen), zodat joins daarop niet in het niets verdwijnen.

-- Voorbeeld: EXISTS in plaats van IN en een afgeleide tabel die geschikt is voor een merge
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Afgeleide tabel met unieke sleutels
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;

Ik controleer met EXPLAIN FORMAT=JSON of gematerialiseerd of afhankelijke subquery werd gekozen en of er voorwaarden (condition pushdown) er vroeg genoeg bij zijn.

Partitionering en snoeien

Partitionering is geen vervanging voor indexen, maar kan de Hoeveelheid gegevens per toegang drastisch verminderen. De Optimizer voert alleen een zuivere pruning uit als het predikaat de Partitiesleutel duidelijk overeenkomt en niet door functies onherkenbaar wordt gemaakt. Ik vermijd daarom uitdrukkingen als DATE(created_at) in de WHERE-clausule bij gepartitioneerde tabellen en werk in plaats daarvan met bereikgrenzen. EXPLAIN laat zien welke partities worden gelezen; brede bereiken duiden op slechte pruning.

Te veel kleine partities verhogen de planningsoverhead. Ik kies daarom voor een zinvolle granulariteit (bijvoorbeeld maandelijks in plaats van dagelijks), houd de statistieken per partitie up-to-date (ANALYZE PARTITION) en controleer of belangrijke indexen lokaal in de partities aanwezig zijn. Bij migratieprojecten houd ik rekening met de invloed op replicatie en back-up – beide factoren bepalen hoe agressief ik partitioneer.

Sargability en rewrite-patronen

De eenvoudigste hefboom blijft Sargability – Voorwaarden die indexen bruikbaar maken. Ik vermijd functies op kolommen in de WHERE-clausule, breng constanten terug naar de kolomzijde en splits OR-voorwaarden indien nodig op in UNIE ALLE. Voor LIKE-zoekopdrachten zonder voorvoegsel ("%foo") is een BTREE-index niet geschikt; hiervoor ben ik van plan om full-text of een geschikte zoekdienst te gebruiken. Voor berekeningen maak ik gebruik van geïndexeerde gegenereerde kolommen, zodat de optimizer de logica in de index kan terugvinden.

-- Anti-patroon: functie op kolom
WHERE DATE(created_at) = '2026-08-01'
-- Beter: bereik op ruwe waarde
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-patroon: OR verhindert index
WHERE status = 'open' OR customer_id = 42
-- Beter: twee zoekopdrachten met UNION ALL en elk een eigen index
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

Wat samengestelde indexen betreft, ben ik van mening dat de regel voor het linkervoorvoegsel Houd je strikt aan de regels: rangschik kolommen op selectiviteit en op de sortering die later nodig is. Als ik een aflopende ORDER BY nodig heb, houd ik daar rekening mee in de indexindeling – zo bespaar ik mezelf de bestandssortering.

Optimizer-schakelaar en fijnafstemming van de kosten

Voordat ik aan query's ga werken, controleer ik optimizer_switch en opslagbuffers. Functies zoals mrr, batched_key_access, index_merge, semijoin, afgeleide samenvoeging of condition_pushdown_for_afgeleide kunnen per sessie worden aangepast. Ik activeer kandidaten gericht voor een testsessie, meet met EXPLAIN ANALYZE en draai de wijzigingen terug als het gewenste effect uitblijft. Het join-pad profiteert van voldoende samenvoegen_buffer_grootte; grote soorten van sorteer_buffer_grootte. Tegelijkertijd houd ik de buffers in de gaten in verhouding tot de gelijktijdigheid, zodat de server bij parallelle belasting niet gaat swappen.

Op kostenvlak pas ik, indien nodig, de eerder genoemde optimalisatiekosten in microseconden. Mijn aanpak: kleine, omkeerbare stappen met gedocumenteerde meetpunten. Ik maak gebruik van LAST_QUERY_COST om de plausibiliteit te controleren en herhaal metingen met realistische parameterwaarden, omdat plannen sterk afhankelijk kunnen zijn van concrete letterlijke waarden.

Planstabiliteit, regressies en teamworkflow

Zelfs een goed plan kan door de toename van gegevens of een versiewisseling omvallen. Daarom verzamel ik informatie over de uitvoeringsplannen: query-hashes, EXPLAIN-JSON, fragmenten uit de optimizer-trace en de uitvoeringstijden van EXPLAIN ANALYZE. Wijzigingen aan indexen en herschrijvingen voer ik uit via een pull-request, met voor-en-na-bewijsmateriaal. In CI/CD-omgevingen toets ik kritieke query's automatisch aan de hand van representatieve gegevenssets. Zo pak ik Regressieplannen vroeg.

Voor lastige gevallen houd ik Tips (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch per query) zijn beschikbaar als laatste optie, maar gebruik ze spaarzaam en met een vervaldatum. Het is beter om de oorzaken – statistieken, indexen, formulering – aan te pakken. In teams zorgt een beknopte richtlijn voor schaalbaarheid, indexontwerp en meetdiscipline ervoor dat nieuwe functies niet onopgemerkt prestatieproblemen veroorzaken.

Korte samenvatting: van plan naar resultaat

Wie kan de Plan begrijpt, stuurt de prestaties. De fasen ‘Parsing’, ‘Preparing’, ‘Optimizing’ en ‘Executing’ maken duidelijk waar tijd verloren gaat. Het op tijd gebaseerde kostenmodel vanaf versie 11.0, goed bijgehouden statistieken en histogrammen zorgen ervoor dat schattingen betrouwbaar zijn. EXPLAIN, EXPLAIN ANALYZE en de Optimizer Trace zorgen voor transparantie, die ik vertaal naar concrete maatregelen. Met een strakke indexstrategie, een duidelijk query-ontwerp en de juiste infrastructuur leveren MariaDB-query’s constant snelle antwoorden op.