MariaDB Query Optimizer intern erklärt: Grundlagen, Pläne und Praxis

Ich erkläre den MariaDB Optimizer aus der Praxis: wie er Pläne baut, Kosten schätzt und warum er manchmal danebenliegt. So liest du den sql execution plan zielgerichtet, setzt Indizes sinnvoll ein und lenkst den Optimizer mit Fakten statt Bauchgefühl.

Zentrale Punkte

Zum Start fasse ich die wichtigsten Bausteine kurz zusammen, damit du die folgenden Abschnitte gezielt einordnest und den Überblick behältst.

• Phasen: Parsing, Preparing, Optimizing, Executing formen den Lebenszyklus jeder Abfrage.
• Kostenmodell: Zeitbasierte Mikrosekunden-Werte steuern Indexwahl, Scans und Join-Reihenfolgen.
• Statistik: Kardinalität und Histograms bestimmen die Selektivitätsschätzung.
• Transparenz: EXPLAIN, EXPLAIN ANALYZE und Optimizer Trace öffnen die Blackbox.
• Tuning: Indizes, Query-Rewrites, ANALYZE TABLE und Kostenparameter bringen Tempo.

Lebenszyklus einer Abfrage in MariaDB

Bevor ein Plan entsteht, durchläuft eine Query vier Etappen, die ich im Alltag gezielt prüfe, um Ursachen für Langsamkeit zu finden. Beim Parsing wandelt MariaDB SQL in eine interne Struktur; Syntaxfehler fallen hier auf. In der Vorbereitung prüft die Engine Tabellen, Spalten und potenzielle Indizes und führt einfache Umformungen durch. Jetzt folgt die Optimierung, in der Kandidaten-Pläne berechnet und mit einem Kostenmodell bewertet werden. In der Ausführung setzt der Server den gewählten Plan schrittweise um: lesen, joinen, filtern, zurückgeben.

Ich trenne Analysefehler klar nach Phase, weil so Diagnosen schneller greifen und Maßnahmen zielgerichtet wirken. Meist wurzeln Performanceprobleme in der Optimierung: falsche Schätzungen, fehlende Indizes oder ungünstige Join-Reihenfolgen. Parsing-Fehler sind trivial, aber Preparing kann bereits Kniffe wie View-Auflösung oder Subquery-Umformungen enthalten. In der Execution fallen dann Ineffizienzen gnadenlos auf, wenn zuvor ein Vollscan gewählt wurde. Deshalb starte ich jede Untersuchung mit einem strukturierten Blick über alle vier Stationen.

Wie der Optimizer intern entscheidet

MariaDB arbeitet kostenbasiert und bewertet alternative Ausführungen über eine Kostenfunktion. Für jede Variante schätzt der Server gelesene Zeilen, Selektivität von WHERE/ON, Zugriffsarten wie Table Scan, Index Scan, Range Scan sowie die Zeitkosten einzelner Operationen. Intern unterscheidet der Server join_preparation und join_optimization. In join_preparation laufen Query-Rewrites, Bedingungsvereinfachungen, Subquery-Umformungen und View-Auflösungen. join_optimization berechnet Join-Ordnungen, prüft Indexkandidaten über ref_optimizer_key_uses, schätzt rows über Range-Scans und ordnet Bedingungen möglichst früh konkreten Tabellen zu.

Diese Mechanik erklärt, warum ein kleiner Filter an der falschen Stelle teure Folgen hat. Wenn attaching_conditions_to_tables spät passiert, schleppt der Plan unnötig viele Zeilen durch Joins. Sind Statistiken veraltet, fallen rows_estimation und Selectivity falsch aus; der Optimizer greift dann zu günstigen, aber real langsamen Zugriffspfaden. Genau an diesen Stellschrauben setze ich an: bessere Statistik, klarere Prädikate, sauber sortierte Composite-Indizes. Danach kippt die Wahl des Plans oft spürbar.

Kostenmodell ab MariaDB 11.0

Aktuelle Releases bewerten Arbeit nicht mehr grob über Gewichte, sondern mit Mikrosekunden für konkrete Storage-Operationen. Parameter wie optimizer_disk_read_cost, optimizer_disk_read_ratio und optimizer_where_cost bringen das Modell näher an reale Laufzeiten. So vergleicht der Optimizer Index-Range-Scan versus Vollscan auf Basis echter Zeitannahmen. LAST_QUERY_COST zeigt die geschätzten Gesamtkosten und korreliert häufig deutlich besser mit der Realität als früher. Für datenintensive Systeme zahlt sich dieses feinere Raster sofort aus.

Ich kalibriere das Modell vorsichtig, wenn Hardware-Eigenschaften die Standardannahmen konterkarieren und damit die Planwahl verzerren. NVMe-SSDs, verteilte Speicher oder spezielle Caches können die Disk-Ratio und Lesezeiten merklich verschieben. Kleine Anpassungen an optimizer_costs bewirken, dass MariaDB sinnvolle Pfade bevorzugt. Ich dokumentiere jede Änderung und prüfe anschließend EXPLAIN ANALYZE, um die Auswirkung zu messen. Ohne Messung bleibt Tuning Glücksspiel.

Selektivität, Statistiken und Histograms

Gute Schätzungen beginnen mit sauberer Kardinalität und verlässlicher Selektivität. MariaDB hält Statistiken über unterschiedliche Werte je Spalte und kann optional Histograms für Verteilungen nutzen. Gerade ungleichmäßige Daten – Hotspots, Zipf-Verteilungen, saisonale Muster – profitieren von Histograms. Nach großen Datenänderungen führe ich ANALYZE TABLE aus, damit die Optimierung wieder mit Fakten arbeitet. Wer das vergisst, riskiert Vollscans, die objektiv falsch sind.

Ich plane ANALYZE als regelmäßigen Job ein, abgestimmt auf Änderungen im Datenvolumen und auf kritische Tabellen. Bei stark verzerrten Spaltenverteilungen helfen Histograms, die Selektivität singulärer Werte realistisch zu fassen. Das reduziert Fehleinschätzungen bei Range-Scans und Merge-Strategien. Kombiniert mit passenden Composite-Indizes hebt sich die Treffergenauigkeit drastisch. Ergebnis: kürzere Laufzeiten und weniger I/O.

EXPLAIN und Execution-Pläne lesen

Um Entscheidungen sichtbar zu machen, verwende ich EXPLAIN, EXPLAIN EXTENDED und FORMAT=JSON. Die klassischen Spalten liefern eine schnelle Übersicht: id, select_type, table, type, possible_keys, key, key_len, ref, rows und ggf. filtered. Ein type=ALL weist auf einen Vollscan hin, der selten gewünscht ist. FORMAT=JSON zeigt detailliert, wie Bedingungen verschoben wurden und welche Pfade der Optimizer bewertet hat. Im Hosting-Kontext empfehle ich den Leitfaden zu Execution-Pläne im Hosting, um Planinformationen mit Infrastruktur-Effekten zu verknüpfen.

Zur schnellen Interpretation hilft mir eine kleine Tabelle, die typische Werte kurz einordnet und damit Fehlinterpretationen verhindert.

EXPLAIN-Feld	Typischer Wert	Bedeutung in der Praxis
type	ALL, range, ref, eq_ref, const	Je weiter rechts, desto selektiver; ALL signalisiert Vollscan.
possible_keys	Indexliste	Indizes, die theoretisch passen; fehlen hier Kandidaten, fehlt Struktur.
key	Indexname	Tatsächlich genutzter Index; leer bedeutet Index-Verzicht.
rows	Zahl	Geschätzte gelesene Zeilen; stark abweichend von Realität = schlechte Statistik.
filtered	Prozent	Wie viel nach Filter weitergereicht wird; niedrig ist oft gut.

Warum der Optimizer manchmal danebenliegt

Kein Kostenmodell trifft jede Situation, daher korrigiere ich Fehlgriffe gezielt. Veraltete Statistiken führen zu falschen rows-Schätzungen und ungünstigen Join-Reihenfolgen. Falsch aufgebaute Composite-Indizes verhindern Index-Nutzung bei Mehrspalten-Filtern. Sehr verschachtelte Unterabfragen erschweren effektive Rewrites und blockieren Materialisierung. Fehlende oder irreführende Filter zwingen die Engine, viele Zeilen zu bewegen, bevor nützliche Prädikate greifen.

Ich prüfe zunächst, ob die Abfrageformulierung den Index wirklich nutzt: linke Präfixregel, passende Sortierfolge, Vermeidung von Funktionen auf Spalten in WHERE. Danach schaue ich in EXPLAIN ANALYZE, ob die Realität die Schätzung stützt. Wenn nicht, folgt ANALYZE TABLE und bei Bedarf ein Rewrite. Erst zuletzt greife ich zu FORCE INDEX oder Hinting, weil das künftige Optimierungen einschränken kann.

Optimizer Trace gezielt nutzen

Wenn EXPLAIN nicht reicht, aktiviere ich den Optimizer Trace und verfolge Entscheidungen im JSON-Log. Darin sehe ich, welche Pläne erwogen, verworfen oder akzeptiert wurden. Ich erkenne, warum eine Bedingung spät greift oder warum ein Index nicht in die engere Wahl kam. Das Log zeigt auch, wie Bedingungen neu angeordnet wurden. Diese Sicht schärft das Verständnis und liefert konkrete Hebel für das nächste Tuning.

Ich speichere relevante Abschnitte des Traces zusammen mit Query-Hash und Parameterwerten. So kann ich später vergleichen, welche Änderung welchen Effekt hatte. Die Dokumentation des MariaDB Servers und diverse Vorträge im Ökosystem beschreiben die Felder ausführlich (Quelle: MariaDB Server Dokumentation zu Query Optimizer und Optimizer Trace). Mit diesem Werkzeug finde ich fehlerhafte Annahmen schneller als mit Trial-and-Error. Zeit spare ich vor allem bei vielschichtigen Joins.

Praxis: Database Tuning Schritt für Schritt

Ich starte jede Optimierung mit einer klaren Messung. Problemabfragen identifiziere ich über Monitoring und das Slow Query Log. Danach vergleiche ich EXPLAIN mit EXPLAIN ANALYZE, um Plan und Realität nebeneinander zu legen. Die Indexstrategie passe ich an WHERE, JOIN und ORDER BY an; Composite-Indizes richte ich an den häufigsten Zugriffspunkten aus. FORCE INDEX setze ich nur ein, wenn der Optimizer trotz korrekter Statistiken den falschen Kandidaten wählt.

Zu jedem Schritt gehört die Pflege der Statistik: ANALYZE TABLE auf stark bewegten Tabellen, Histograms für schiefe Verteilungen. Ich vereinfache unnötige Subqueries, materialisiere Zwischenergebnisse bei Bedarf und räume alte Workarounds auf. Bei Sonderhardware prüfe ich optimizer_costs, damit das Mikrosekunden-Modell stimmt. Jede Änderung belege ich mit Vorher/Nachher-Werten, damit die Wirkung dauerhaft nachvollziehbar bleibt.

Typische Optimizer-Probleme und Lösungen

Zeigt EXPLAIN type=ALL, obwohl possible_keys gefüllt ist, schaue ich zuerst auf Selektivität. Oft passt die Spaltenreihenfolge im Composite-Index nicht oder eine Funktion verhindert die Index-Nutzung. Dann drehe ich die Reihenfolge, entferne störende Funktionen oder splitte Prädikate. Bei falscher Join-Reihenfolge prüfe ich, ob frühe Filterung möglich ist, etwa durch Vorziehen der selektiveren Tabelle. Subqueries wandle ich, wo sinnvoll, in Joins oder TEMPORARY-Tabellen.

Ich erkenne Fehlentscheidungen auch an stark abweichenden rows zwischen Plan und Realität. Dann hilft ANALYZE TABLE oder ein Histogram auf der betroffenen Spalte. Führen selbst korrekte Statistiken nicht zum Ziel, ziehe ich explizite Hints in Erwägung. Vorher sichere ich Gegenprobe und Messwerte, damit spätere Versionen des Optimizers nicht hinterlegt ausgebremst werden. Disziplin bei der Dokumentation zahlt sich hier aus.

Hosting-Kontext und Betriebsaspekte

Abfragequalität und Infrastruktur müssen zusammenpassen, sonst verschenkt die Anwendung Potenzial. Schnelle SSDs, konsistente Caches und saubere Konfiguration sind die Grundlage, auf der der Optimizer gute Entscheidungen trifft. Hoher Traffic verzeiht keine Vollscans; wenige schlechte Queries bremsen ganze Systeme aus. Für MySQL/MariaDB-Umgebungen im produktiven Betrieb liefern praxisnahe Hinweise wie MySQL Optimizer hilfreiche Denkanstöße zur Kombination aus Plan und Plattform. Wer diese Ebene mitdenkt, verhindert Bottlenecks, bevor sie eskalieren.

Ich verknüpfe Plananalyse stets mit Metriken zu I/O, Latenz und Concurrency. Passen die Werte nicht zum angenommenen Kostenmodell, prüfe ich die Parameter. Danach schaue ich auf Puffergrößen, parallele Workloads und Verteilung der Hotsets. Mit diesem Blick gelingt es, Abfragen und Ressourcen harmonisch zu betreiben und Spitzenzeiten kontrollierbar zu halten.

Join- und Zugriffspfade in der Praxis

Viele Missverständnisse kläre ich, indem ich die Zugriffsarten gezielt gegeneinander abwäge. Ein range– oder ref-Zugriff schlägt fast immer ALL. Bei Gleichverknüpfungen auf eindeutigen Schlüsseln (eq_ref) sind Pläne besonders stabil. Ich prüfe außerdem, ob ein Covering Index die Query vollständig bedient: Stehen alle benötigten Spalten im Index, spart MariaDB teure Tabellenzugriffe. Index Condition Pushdown (ICP) hilft, zusätzliche WHERE-Bedingungen schon im Index zu prüfen – das reduziert zurückgelieferte Zeilen und I/O.

Über Index Merge kann MariaDB mehrere Indizes kombinieren (Schnittmenge/Union). Das ist nützlich bei OR-Prädikaten oder mehreren selektiven Bedingungen, aber oft langsamer als ein gut gewählter Composite-Index. Ich evaluiere außerdem MRR (Multi-Range Read) und BKA (Batched Key Access). MRR sortiert zu lesende Primärschlüssel, um Random I/O zu glätten; BKA bündelt Join-Lookups und gewinnt vor allem bei nicht überdeckten Joins. In der Praxis teste ich BKA/MRR über optimizer_switch und kontrolliere mit EXPLAIN ANALYZE, ob die I/O-Muster sinken. Greift MariaDB hingegen zum Block Nested Loop (BNL), lohnt meist mehr Join-Puffer (join_buffer_size) – oder ein Rewrite, der echte Index-Joins ermöglicht.

-- Beispiel: Composite-Index für Join + Filter + Order
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Typischer Zugriff
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;

Mit obigem Index kann der Optimizer die selektivste Reihenfolge wählen, Filter früh auswerten und die Sortierung häufig ohne zusätzliche Filesort bedienen.

ORDER BY, GROUP BY, Filesort und temporäre Tabellen

Sortieren und Aggregieren kosten Zeit. Ich sorge dafür, dass ORDER BY und GROUP BY auf der Indexreihenfolge laufen können. Das klappt, wenn Präfix und Richtung exakt passen. Sonst greift eine Filesort mit Sortierpuffer (sort_buffer_size) und ggf. temporärer Tabelle. Enthält die Ergebnismenge breite TEXT/BLOB-Spalten, fällt MariaDB schneller auf on-disk TEMP-Tables (Aria) zurück. Ich beuge vor, indem ich nur benötigte Spalten selektiere, große Felder erst am Ende nachlade oder mit längenbegrenzten Präfixen arbeite.

Bei Aggregationen nutze ich, wo möglich, Loose Index Scan (z. B. GROUP BY auf dem führenden Indexteil) und wähle Composite-Indizes entlang der Gruppierung. Wenn Zwischenstände groß werden, skaliert eine Materialisierung mit sinnvollen Schlüsseln besser als ein einziger Mega-Join. Ich messe regelmäßig Handler-Metriken und Created_tmp_* Zähler, um Sort- und Temp-Table-Hotspots aufzudecken.

Unterabfragen, Semi-Join und Materialisierung

Viele Subqueries lassen sich in der Vorbereitung effizient umformen. IN/EXISTS-Konstrukte können als Semi-Join laufen, mit Strategien wie Materialisierung oder LooseScan. Ich prüfe, ob der Optimizer ein derived_merge durchführen konnte: Wird eine abgeleitete Tabelle (oder ein WITH-CTE) in den äußeren Plan hineingeschoben, stehen ihre Indizes direkt zur Verfügung. Gelingt das nicht, landet die Unterabfrage in einer temporären Tabelle – ich gebe ihr dann, falls machbar, einen Schlüssel (z. B. durch SELECT DISTINCT/ORDER BY auf Schlüsselspalten), damit Joins darauf nicht im Nirwana enden.

-- Beispiel: EXISTS statt IN und Merge-fähige abgeleitete Tabelle
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- Ableitung mit klaren Schlüsseln
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;

Ich kontrolliere mit EXPLAIN FORMAT=JSON, ob materialized oder dependent subquery gewählt wurde und ob Bedingungen (condition pushdown) früh genug greifen.

Partitionierung und Pruning

Partitionierung ersetzt keine Indizes, kann aber die Datenmenge je Zugriff drastisch senken. Der Optimizer pruned nur dann sauber, wenn das Prädikat den Partitionsschlüssel eindeutig trifft und nicht durch Funktionen unkenntlich gemacht wird. Ich meide deshalb Ausdrücke wie DATE(created_at) im WHERE auf partitionierten Tabellen und arbeite stattdessen mit Bereichsgrenzen. EXPLAIN zeigt, welche Partitionen gelesen werden; weite Spannen deuten auf schlechtes Pruning hin.

Zu viele kleine Partitionen erhöhen den Planungs-Overhead. Ich wähle daher sinnvolle Granularität (z. B. monatlich statt täglich), halte Statistiken pro Partition frisch (ANALYZE PARTITION) und prüfe, ob wichtige Indizes lokal in den Partitionen vorhanden sind. Bei Migrationsprojekten kalkuliere ich den Einfluss auf Replikation und Backup mit ein – beides beeinflusst, wie aggressiv ich partitioniere.

Sargability und Rewrite-Muster

Der einfachste Hebel bleibt Sargability – Bedingungen, die Indizes nutzbar machen. Ich vermeide Funktionen auf Spalten im WHERE, führe Konstanten auf Spaltseite zurück und entzerre OR-Bedingungen bei Bedarf in UNION ALL. Für LIKE-Suchen ohne führenden Anker ("%foo") taugt ein BTREE-Index nichts; hier plane ich Fulltext oder einen passenden Suchdienst. Bei Berechnungen nutze ich indizierte generierte Spalten, damit der Optimizer die Logik im Index wiederfindet.

-- Anti-Pattern: Funktion auf Spalte
WHERE DATE(created_at) = '2026-08-01'
-- Besser: Bereich auf Rohwert
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- Anti-Pattern: OR verhindert Index
WHERE status = 'open' OR customer_id = 42
-- Besser: zwei Suchen mit UNION ALL und je eigenem Index
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

Bei Composite-Indizes halte ich die linke Präfixregel strikt ein, ordne Spalten nach Selektivität und nach der später benötigten Sortierung. Benötige ich eine absteigende ORDER BY, berücksichtige ich das im Index-Layout – so spare ich mir die Filesort.

Optimizer-Schalter und Kosten-Feintuning

Bevor ich an Abfragen säge, prüfe ich optimizer_switch und Speicherpuffer. Features wie mrr, batched_key_access, index_merge, semijoin, derived_merge oder condition_pushdown_for_derived lassen sich pro Session anpassen. Ich aktiviere Kandidaten gezielt für eine Testsession, messe mit EXPLAIN ANALYZE und rolle zurück, wenn der Effekt ausbleibt. Der Join-Pfad profitiert von ausreichender join_buffer_size; große Sorts von sort_buffer_size. Gleichzeitig halte ich die Puffer im Verhältnis zur Concurrency im Blick, damit der Server unter Parallel-Last nicht ins Swappen gerät.

Auf Kostenebene justiere ich, falls nötig, die bereits erwähnten optimizer_costs in Mikrosekunden. Mein Leitfaden: kleine, reversible Schritte mit dokumentierten Messpunkten. Ich nutze LAST_QUERY_COST zur Plausibilisierung und wiederhole Messungen mit realistischen Parameterwerten, weil Pläne stark von konkreten Literalen abhängen können.

Planstabilität, Regressionen und Team-Workflow

Auch ein guter Plan kann durch Datenwachstum oder Versionwechsel kippen. Ich sichere mir deshalb Planwissen: Query-Hashes, EXPLAIN-JSON, Optimizer-Trace-Ausschnitte und EXPLAIN ANALYZE-Laufzeiten. Änderungen an Indizes und Rewrites laufen bei mir als Pull Request mit Vorher/Nachher-Belegen. In CI/CD-Umgebungen prüfe ich kritische Abfragen automatisiert gegen repräsentative Datenstände. So fange ich Regressionspläne früh ab.

Für heikle Fälle halte ich Hints (FORCE INDEX, STRAIGHT_JOIN, optimizer_switch pro Query) als letzte Option bereit, nutze sie aber sparsam und mit Ablaufdatum. Besser ist es, die Ursachen – Statistik, Indizes, Formulierung – zu heilen. In Teams sorgt ein leichtgewichtiger Leitfaden für Sargability, Index-Design und Messdisziplin dafür, dass neue Features nicht unbemerkt Performancefrust einbauen.

Kurzbilanz: Vom Plan zur Leistung

Wer den Plan versteht, steuert die Leistung. Die Phasen Parsing, Preparing, Optimizing und Executing erklären, wo Zeit verloren geht. Das zeitbasierte Kostenmodell ab 11.0, gepflegte Statistiken und Histograms machen Schätzungen belastbar. EXPLAIN, EXPLAIN ANALYZE und der Optimizer Trace verschaffen Transparenz, die ich in konkrete Maßnahmen übersetze. Mit sauberer Indexstrategie, klarem Query-Design und passender Infrastruktur liefern MariaDB-Abfragen konstant flotte Antworten.