Mit dem optimizer trace in MariaDB verstehe ich Schritt für Schritt, warum der Optimizer einen bestimmten Plan wählt und welche Varianten er verwirft. Diese JSON-Spur zeigt mir Entscheidungen zu Kosten, Join-Reihenfolgen und Filtern, sodass ich SQL-Abfragen zielgerichtet anpasse.
Zentrale Punkte
- Transparenz: JSON-basierte Spur erklärt Rewrites, Kosten und verworfene Pläne.
- Fokus: join_preparation und join_optimization liefern die wichtigsten Einblicke.
- Steuerung: Session-Variablen begrenzen Overhead und Speicher.
- Workflow: EXPLAIN/ANALYZE für den Plan, Trace für das „Warum“.
- Praxisnutzen: Indizes, Statistiken und Join-Reihenfolgen fundiert anpassen.
Was ist der MariaDB Optimizer Trace?
MariaDB führt seit Version 10.4 einen Optimizer Trace, der jede größere Optimierungsphase einer SELECT-, UPDATE- oder DELETE-Anweisung als JSON dokumentiert. Ich sehe darin, wie die Engine Abfragen erweitert, Bedingungen normalisiert und schließlich die Join-Reihenfolge samt Indexzugriffen festlegt. Dieser Blick geht deutlich tiefer als EXPLAIN, das vor allem den Endplan zeigt, und offenbart verworfene Alternativen mit Begründungen. Die Spur liegt pro Verbindung im Speicher und steht über information_schema.OPTIMIZER_TRACE bereit. So erhalte ich eine vollständige, maschinenlesbare Erklärung der internen Schritte, die zu einem Ausführungsplan geführt haben.
Optimizer Trace aktivieren und auslesen
Ich aktiviere die Funktion gezielt pro Sitzung, damit ich Diagnosen ohne globalen Overhead fahren kann und volle Kontrolle über Speicher habe. Typisch setze ich SET SESSION optimizer_trace = 'enabled=on'; und bei Bedarf SET SESSION optimizer_trace_max_mem_size = 1048576; oder höher, wenn die Spur umfangreich wird. Danach führe ich die verdächtige Abfrage aus und lese den Trace mit SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. Wichtig: Die Tabelle speichert nur die letzte Abfrage der aktiven Verbindung, und ich beachte Felder wie MISSING_BYTES_BEYOND_MAX_MEM_SIZE oder INSUFFICIENT_PRIVILEGES für Diagnose-Hinweise. Diese Arbeitsweise hält die Produktionsumgebung schlank und macht die Analyse zielgenau.
| Variable/Feld | Zweck | Beispielwert |
|---|---|---|
optimizer_trace | Aktiviert den Trace pro Session | 'enabled=on' |
optimizer_trace_max_mem_size | Maximaler Speicher pro Trace | 1048576 (1 MB) |
OPTIMIZER_TRACE.QUERY | Originale SQL-Anweisung | SELECT ... |
OPTIMIZER_TRACE.TRACE | JSON-Dokument der Optimierung | JSON-Text |
MISSING_BYTES_BEYOND_MAX_MEM_SIZE | Abgeschnittene Bytes bei zu großem Trace | 0 oder Anzahl |
INSUFFICIENT_PRIVILEGES | Reicht die Berechtigung zum Lesen? | 0 oder 1 |
JSON-Struktur: join_preparation und join_optimization
Die JSON-Struktur gliedert sich in die Blöcke join_preparation und join_optimization, die ich als erstes durchsehe, weil sie die wichtigsten Hinweise liefern. Im Abschnitt join_preparation erkenne ich die erweiterte Abfrage (expanded_query) und sehe, ob und wie die Engine Bedingungen oder Projektionen umgeformt hat. Der zweite Block join_optimization protokolliert Zeilenschätzungen, betrachtete Pläne, die gewählte Join-Reihenfolge und das Anhängen selektiver WHERE-Teile an Tabellen. Besonders nützlich sind die Unterbäume rows_estimation, considered_execution_plans und attaching_conditions_to_tables, weil sie direkt auf Kostenannahmen und Filterpositionen verweisen. Dadurch erkenne ich schnell, wo Fehleinschätzungen oder ungünstige Indizes zu suboptimalen Plänen führen.
Abgleich mit EXPLAIN und ANALYZE
Für eine vollständige Bewertung kombiniere ich EXPLAIN, ANALYZE und den Trace in einem festen Ablauf. Zuerst nutze ich EXPLAIN oder EXPLAIN FORMAT=JSON, um den gewählten Plan und Schlüsselpfade zu sehen. Danach setze ich EXPLAIN ANALYZE ein, um reale Laufzeitdaten und Zählwerte wie Loops und gefilterte Zeilen zu erhalten. Wenn Fragen offen bleiben, aktiviere ich den Optimizer Trace und lese aus, welche Varianten der Optimizer geprüft und verworfen hat. Eine kompakte Einführung zur Interpretation bietet mir dieser Beitrag zu EXPLAIN ANALYZE verstehen, den ich bei Bedarf ergänzend heranziehe.
Plan-Entscheidungen nachvollziehen: Kosten, Kardinalitäten, Filter
Die Entscheidungslogik stützt sich auf Kardinalitäten, Kostenmodelle und die Platzierung von Filtern entlang des Plans. Im Trace sehe ich für jede betrachtete Join-Reihenfolge, welche Zeilenmengen die Engine erwartet und wie sie daraus die Gesamtkosten ableitet. Ich prüfe, ob veraltete Statistiken oder ungünstige Korrelationen dazu führen, dass Range-Scans unterschätzt und Full-Scans bevorzugt werden. Außerdem schaue ich, ob die Engine WHERE-Bedingungen früh genug an die selektivste Tabelle hängt, um teure Join-Schritte zu verkleinern. So treffe ich belastbare Aussagen, warum ein Plan gewählt wurde und wie ich ihn mit Indizes, Rewrites oder Statistikpflege beeinflusse.
Praxis: Trace einer einfachen Filterabfrage
Bei SELECT * FROM t1 WHERE a < 10 prüfe ich unter join_preparation, ob die Engine die Projektion erweitert und eventuell Bedingungen konsolidiert hat, was mir erste Indikatoren liefert. Danach sehe ich im Block rows_estimation, wie viele Zeilen die Engine für Range-Scan auf a im Vergleich zum Full-Table-Scan erwartet. Finden sich unrealistische Werte, deute ich das oft als Zeichen für veraltete Statistiken oder fehlende Histograms. Im Abschnitt considered_execution_plans erkenne ich anschließend, ob der Indexzugriff wirklich günstiger kalkuliert wurde als der Vollscan. Abschließend zeigt attaching_conditions_to_tables, ob die selektive Bedingung auf a früh im Plan greift, was die Laufzeit deutlich senkt.
JSON-Funktionen: Ausschnitte gezielt extrahieren
Weil der Trace als JSON vorliegt, filtere ich gezielt Teilbäume mit JSON_EXTRACT und baue kleine Auswertungen für wiederkehrende Muster. Ich lese beispielsweise nur die Liste der erwogenen Pläne aus, um zu prüfen, ob bestimmte Join-Reihenfolgen systematisch scheitern. Ebenso extrahiere ich Kostenfelder der Top-Kandidaten und vergleiche sie mit ANALYZE-Daten, um Fehlannahmen zu entdecken. Über einfache Views oder Stored Procedures automatisiere ich diese Prüfungen für meine Diagnosesessions. Auf diese Weise baue ich mir ein leichtes Monitoring für Optimizer-Entscheidungen auf, ohne dauerhaftes Tracing zu aktivieren.
Typische Anwendungsfälle und Nutzen
Ich greife zum Trace, wenn EXPLAIN einen unerwarteten Full-Scan zeigt und ich den Grund für die Ablehnung eines Index erfahren will. Ebenso liefert mir der Trace bei vielen Tabellen die Begründung für die gewählte Join-Reihenfolge, was mir den Weg zu alternativen Plänen aufzeigt. Beim Versionswechsel sichere ich mir Traces vor und nach dem Update, um Änderungen im Optimizer-Verhalten einzuschätzen. Für strategische Tuning-Fragen hilft mir dieser Überblick zu internen Optimizer-Mechanismen, den ich mit Trace-Befunden verknüpfe. So entscheide ich strukturiert, ob ich an Indizes, Statistiken oder Query-Formulierungen die Stellschraube setze.
Best Practices für Produktion
Ich aktiviere den Trace konsequent als Session-Einstellung und beende die Diagnose sauber, sobald ich genug Daten habe. Für große Traces erhöhe ich optimizer_trace_max_mem_size nur kurzfristig und stelle den Wert danach wieder klein ein. Bevor ich JSON-Dateien teile, maskiere ich sensible Konstanten, Kommentartexte oder geschäftliche Kennzahlen. Ich nutze den Trace gezielt als Diagnosewerkzeug, während ich für Dauerbeobachtung Slow-Query-Logs, Performance-Ansichten oder externe Profiler bevorzuge. Diese Disziplin hält Systeme schlank und verhindert unnötigen Overhead im Tagesgeschäft.
Optimizer Trace im Werkzeug-Mix
Für ein ganzheitliches Tuning bilde ich die Kette aus Plan-Verständnis, Ursachenanalyse und Systemmessung ab und verknüpfe die Erkenntnisse. EXPLAIN zeigt mir den Plan, ANALYZE bestätigt reale Kosten, und der Trace liefert die Hintergründe der Entscheidung. Parallel schaue ich mir Query-Execution-Plan-Konzepte an, um Muster in Schlüsselauswahl, Kardinalitäten und Join-Strategien einzuordnen. Eine gute Ergänzung für diese Perspektive ist der kompakte Überblick zu Query-Execution-Plänen, den ich bei Architekturfragen heranziehe. Daraus leite ich belastbare Prioritäten für Indexarbeit, Rewrites und Parameter ab.
Tiefer eintauchen: range_analysis und Schlüsselwahl
Im Trace steckt häufig ein Block range_analysis für jede Tabelle, an der ich erkenne, welche Indizes für Range-, Ref- oder EQ-Ref-Zugriffe in Frage kamen. Der Optimizer vergleicht dort Alternativen wie „range auf idx_a“, „range auf idx_b“ oder „full scan“, ordnet ihnen Kosten und erwartete Zeilen zu und markiert den Gewinner. Sehe ich, dass ein sinnvoller Index wegen hoher Kosten verworfen wurde, schaue ich als Nächstes auf die zugrunde liegenden Selektivitäten und Statistiken. Stimmen die Annahmen nicht, kann ein ANALYZE TABLE (ggf. mit persistenter Statistik) oder die Anlage eines gezielteren Covering-Index die Entscheidung kippen.
Nützlich ist auch der Blick auf Splits von zusammengesetzten Indizes: Der Trace dokumentiert, ob die Bedingung nur die erste Spalte des Index nutzt oder ob zusätzliche Prädikate sargable sind und weitere Schlüsselspalten effektiv werden. Daraus leite ich ab, ob ich Prädikate umformuliere (zum Beispiel Funktionen vermeiden) oder den Index so erweitere, dass typische Filter und Sortierungen abgedeckt sind.
Joins im Detail: Semijoins, BKA/MRR und Join-Puffer
Bei Multi-Table-Queries zeigen die Trace-Abschnitte, ob und welche Semijoin-Strategie in Betracht gezogen wurde (z. B. FirstMatch, DuplicateWeedout, LooseScan oder Materialization). Ich erkenne dort, warum eine Variante verworfen wurde – etwa wegen hoher Materialisierungskosten oder zu geringer Selektivität. Auch Batched Key Access (BKA) und Multi-Range Read (MRR) tauchen im Trace auf, sofern aktiviert. Diese Techniken bündeln Key-Lookups und verbessern die Cache-Lokalität. Wenn BKA/MRR im Trace nicht erscheinen, prüfe ich optimizer_switch und Parameter wie join_cache_level. In Workloads mit vielen zufälligen Key-Lookups lässt sich so die Join-Phase spürbar beschleunigen, was sich mit EXPLAIN ANALYZE validieren lässt.
Entscheidend ist außerdem die Größe und Art des Join-Puffers: Der Trace macht sichtbar, ob Nested-Loop-Varianten mit oder ohne Puffer gefahren wurden und an welcher Stelle Filter greifen. Ich bewerte, ob zusätzliche Indexe auf Join-Schlüsseln oder ein Rewrite zur Reduzierung der Zwischenergebnisse die effizientere Wahl ist als das Hochdrehen von Puffergrößen.
Subqueries, Derived Tables und Views
Unter join_preparation finde ich, ob Subqueries in EXISTS/IN-Form in Semijoins umgeformt wurden (in_to_exists), ob Derived Tables gemerged (derived_merge) oder materialisiert wurden und ob Condition Pushdown bis in abgeleitete Tabellen stattfindet. Diese Schritte sind entscheidend, weil ein fehlender Merge zu einer kostspieligen Materialisierung führen kann. Sehe ich im Trace wiederholt Materialization-Entscheidungen mit hohen Kosten, teste ich, ob ein expliziter STRAIGHT_JOIN, ein Hint oder eine Reorganisation der Abfrage (z. B. Common Table Expressions mit gezielten Filtern) die Engine zu einer günstigeren Strategie bewegt. Bei Views prüfe ich, ob der Optimizer die View-Inhalte ausreichend auflöst oder ob zusätzliche Indizes in der zugrunde liegenden Tabelle fehlen.
Partitionierung und Pruning
Bei partitionierten Tabellen zeigt der Trace, welche Partitionen aufgrund von Partitionsschlüsseln und Prädikaten ausgeschlossen wurden (Partition Pruning). Wenn erwartetes Pruning ausbleibt, ist das ein Signal, Filter frühzeitiger und sargable auf den Partitionsschlüssel zu formulieren. Ich achte außerdem auf das Zusammenspiel von Partitionierung und Indizes: Fehlen lokale oder globale Indizes, kann die Engine trotz Pruning übermäßig viele Zeilen prüfen, was im Trace durch hohe Scan-Kosten sichtbar wird.
Hints, Indexvorgaben und optimizer_switch gezielt verifizieren
Ich nutze den Trace, um die Wirkung von Hints und Parameter-Schaltern zu belegen. Setze ich z. B. FORCE INDEX oder einen Optimizer-Hint, sehe ich im Trace, ob die Alternative wirklich erzwungen und wie sie bewertet wurde. Über optimizer_switch kann ich Strategien temporär an- oder abschalten (z. B. für semijoin-, index_merge- oder derived_merge-Entscheidungen). Der Trace dient mir dann als Nachweis, ob die Engine die Vorgaben angenommen hat oder ob andere Restriktionen (z. B. Kardinalitäten) weiterhin dominieren. Optional nutze ich Formatierungs-Flags wie one_line oder end_markers im optimizer_trace-String, um die Lesbarkeit an mein Auswertungswerkzeug anzupassen.
Update/DELETE und Schreibpfade
Der Optimizer Trace ist nicht auf SELECT beschränkt. Bei UPDATE- und DELETE-Statements sehe ich ebenfalls, wie Zugriffswege gewählt werden und ob Filter ausreichend früh greifen, um die Anzahl der betroffenen Zeilen klein zu halten. Ich prüfe, ob ein WHERE-Filter nicht sargable ist oder ob ein fehlender Index zu einer breiten Scan-Phase führt, bevor die eigentliche Änderung ausgeführt wird. Aus dem Trace leite ich ab, ob ein kompakter Index (z. B. nur die benötigten Spalten) unnötige Back-and-Forth-Zugriffe vermeidet und damit Locks und Log-Volumen reduziert.
Sicherheit, Privilegien und Prepared Statements
Damit ich die Spur vollständig lesen kann, benötige ich ausreichende Objekt-Privilegien – fehlen sie, signalisiert das Feld INSUFFICIENT_PRIVILEGES Beschränkungen. In produktionsnahen Szenarien nutze ich daher dieselben Logins wie die Anwendung oder ein speziell berechtigtes Diagnosekonto. Bei Prepared Statements zeigt der Trace die optimierte Form typischerweise bereits mit gebundenen Parametern, was mir die Bewertung von Selektivitäten erlaubt, ohne sensible Konstanten offenzulegen. Muss ich Traces teilen, maskiere ich Parameterwerte oder ersetze sie durch Repräsentantenbereiche, um Datenschutzanforderungen einzuhalten.
Automatisierung: Traces erfassen, differenzieren, dokumentieren
Für reproduzierbare Analysen speichere ich Traces stichprobenartig in einer Diagnosetabelle und versehe sie mit Metadaten wie Schema, Version, Session-Variablen und Zeitstempel. So kann ich vor/nach Indexänderungen oder Versionsupgrades diffen, welche Entscheidungen sich verschoben haben. Praktisch ist es, die Blöcke considered_execution_plans und rows_estimation separat abzulegen, um Kostenänderungen schnell zu vergleichen. Kleinere Helfer-Queries extrahieren mir die gewählte Join-Reihenfolge und berechnete Kosten – etwa mit JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – und speichern das Resultat neben EXPLAIN- und ANALYZE-Ausgaben. So entsteht eine belastbare Dokumentation je Tuning-Schritt.
Grenzen, Versionseigenheiten und MySQL-Vergleich
Die Schlüsselstrukturen des Traces orientieren sich an MySQL, Details und Feldnamen können je nach MariaDB-Version jedoch leicht differieren. Ich richte meinen Blick deshalb auf die semantischen Abschnitte (Rewrites, Rows-Estimation, betrachtete Pläne, Condition-Attachments), statt mich von kosmetischen Unterschieden irritieren zu lassen. Wichtig: In MariaDB steht im Fokus die letzte Anweisung der aktiven Verbindung. Wer viele aufeinanderfolgende Statements analysiert, liest daher unmittelbar nach Ausführung oder automatisiert per Hook aus, damit keine relevanten Spuren überschrieben werden. Für sehr große JSONs kalkuliere ich den Speicherbedarf ein und begreife MISSING_BYTES_BEYOND_MAX_MEM_SIZE als Einladung, das Limit temporär anzuheben und die Analyse erneut zu fahren.
Konkrete JSON-Extraktionen für den Alltag
Zum Schluss ein paar kompakte Ausschnitte, die ich in der Praxis oft nutze, um schnell auf den Punkt zu kommen:
- Gewählte Join-Reihenfolge und Kandidatenlisten: Ich ziehe mir die Planpräfixe und die jeweils angehängte Tabelle, um die Entscheidungsfolge nachvollziehen zu können.
- Range-Alternativen und Kosten: Ich extrahiere die Liste der bewerteten Indizes für die selektivsten Tabellen, um Rewrites oder neue Indizes zielgenau zu bewerten.
- Früh angehängte Filter: Ich lese die
attaching_conditions_to_tables-Abschnitte, um sicherzustellen, dass starke Prädikate so nah wie möglich an der Datenquelle sitzen.
Mit wenigen Views für diese Extraktionen habe ich eine schlanke „Lesebrille“ für Optimizer-Entscheidungen, die ich bei Bedarf in Diagnosesitzungen zuschalte und danach wieder abschalte.
Häufige Stolpersteine und Troubleshooting
Fehlen Histograms oder sind Statistiken veraltet, liegen Schätzungen daneben und erzeugen Pläne mit unnötigen Vollscans. Sehe ich im Trace stark abweichende Kardinalitäten, aktualisiere ich Statistiken, setze geeignete Indizes oder formuliere Filter sargable um. Zu knappe Traces erkenne ich über MISSING_BYTES_BEYOND_MAX_MEM_SIZE und reagiere mit temporär höherem Limit. Liefert ANALYZE bessere Laufzeiten für einen alternativen Pfad, prüfe ich im Trace, welcher Kostenfaktor die gewählte Variante bevorzugt hat. So schließe ich Schritt für Schritt Wissenslücken und erreiche Klarheit über die Entscheidungslogik.
Kurz zusammengefasst
Der MariaDB Optimizer Trace erklärt mir in einem JSON-Dokument, wie die Engine Abfragen umformt, Zeilen schätzt, Pläne vergleicht und schließlich eine Reihenfolge wählt. Ich aktiviere ihn pro Session, lese die Spur aus, prüfe join_preparation und join_optimization und verbinde die Erkenntnisse mit EXPLAIN/ANALYZE. Aus den Gründen für abgelehnte Indizes, späte Filter oder fehlerhafte Schätzungen leite ich konkrete Schritte ab: bessere Indizes, aktuellere Statistiken und klare Query-Formulierungen. Mit JSON-Funktionen extrahiere ich Ausschnitte, erkenne Muster und dokumentiere Entscheidungen reproduzierbar. So bringe ich auch umfangreiche SQL-Workloads auf verlässliche Leistung und halte Tuning-Entscheidungen nachvollziehbar.
