EXPLAIN ANALYZE: Query-Pläne wirklich lesen lernen

1. Warum Query-Pläne lesen eine Kernkompetenz ist

Ein Query-Plan ist die Entscheidung des Optimizers, wie eine Abfrage ausgeführt wird: welche Indizes genutzt werden, in welcher Reihenfolge Tabellen verbunden werden, und ob eine Sortierung im Index oder nachträglich im Speicher stattfindet. Wer EXPLAIN nicht liest, optimiert Abfragen nach Gefühl, und Gefühl trifft bei Datenmengen im Millionenbereich regelmäßig daneben. Der Query-Plan ist die einzige verlässliche Quelle dafür, was MySQL bei einer konkreten Abfrage tatsächlich vorhat.

In der Praxis entscheidet der Unterschied zwischen einem Index-Zugriff auf wenige hundert Zeilen und einem Volltabellenscan über Millionen Zeilen über Millisekunden gegen Sekunden Antwortzeit. Diese Entscheidung ist im Query-Plan sichtbar, lange bevor sie zum Problem in Produktion wird. Die folgenden Abschnitte zeigen, wie man die klassische EXPLAIN-Ausgabe liest, wie EXPLAIN ANALYZE zusätzlich echte Ausführungszahlen liefert, und wie man daraus systematisch Optimierungen ableitet.

Der entscheidende Denkfehler beim Lesen von Query-Plänen ist, EXPLAIN als reine Bestätigung zu nutzen ("ein Index wird verwendet, also ist alles gut") statt als Diagnosewerkzeug. Ein Index wird zu verwenden ist notwendig, aber nicht hinreichend für eine schnelle Abfrage. Erst das Zusammenspiel aus type, rows und Extra zeigt, ob der Query-Plan tatsächlich effizient ist.

2. Die klassische EXPLAIN-Ausgabe: type, key und rows

Die Spalte type im Query-Plan zeigt die Zugriffsmethode, sortiert von schnell nach langsam: const, eq_ref, ref, range, index, ALL. const bedeutet, dass höchstens eine Zeile über einen Primary- oder Unique-Key gefunden wird, meist bei einem Lookup über die ID. ALL bedeutet einen vollständigen Tabellenscan, bei dem jede Zeile geprüft wird. Zwischen diesen Extremen liegen ref für Index-Zugriffe mit Gleichheitsbedingung und range für Bereichsabfragen über einen Index.

Die Spalte key zeigt, welcher Index tatsächlich verwendet wurde, im Gegensatz zu possible_keys, das nur die theoretisch infrage kommenden Indizes auflistet. Ein leerer Wert bei key trotz gefüllter possible_keys ist ein deutliches Warnsignal: Der Optimizer hat einen Index als möglich erkannt, sich aber gegen dessen Nutzung entschieden, meist weil die Statistiken einen Volltabellenscan als günstiger einschätzen.

Die Spalte rows ist eine Schätzung, keine exakte Zahl, basierend auf den Tabellenstatistiken. Sie gibt an, wie viele Zeilen der Optimizer für diesen Schritt des Query-Plans durchsuchen muss, bevor Filterbedingungen angewendet werden. Ein niedriger rows-Wert bei einem JOIN über mehrere Tabellen ist entscheidend, weil sich die Werte pro verschachtelter Schleife multiplizieren können und aus einer harmlos aussehenden Zahl schnell ein Millionen-Zeilen-Problem wird.

EXPLAIN SELECT o.id, c.email FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 2 AND o.created_at > '2026-01-01'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: range
possible_keys: idx_status_created,idx_customer_id
          key: idx_status_created
      key_len: 4
          ref: NULL
         rows: 1840
        Extra: Using index condition
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: shop.o.customer_id
         rows: 1
        Extra: NULL

3. Join-Typen richtig einordnen

Im Query-Plan eines JOIN erscheint pro beteiligter Tabelle eine eigene Zeile, und die Reihenfolge dieser Zeilen zeigt die tatsächliche Verarbeitungsreihenfolge, die nicht zwingend der Reihenfolge im SQL-Statement entspricht. Der Optimizer wählt in der Regel, die Tabelle mit der geringsten geschätzten Zeilenzahl zuerst zu durchsuchen und danach über einen Index in die nächste Tabelle zu springen. Ein eq_ref für die zweite Tabelle bedeutet, dass pro Zeile der ersten Tabelle über einen Unique- oder Primary-Key genau eine passende Zeile gefunden wird, was ein sehr effizienter JOIN-Typ ist.

Problematisch wird ein Query-Plan, wenn eine spätere Tabelle im JOIN mit type: ALL erscheint, denn dann wird für jede Zeile der vorherigen Tabelle ein kompletter Scan der nächsten Tabelle durchgeführt, was sich multiplikativ auswirkt. Bei zehntausend Zeilen aus der ersten Tabelle und einem Volltabellenscan über hunderttausend Zeilen in der zweiten Tabelle entstehen im schlimmsten Fall Milliarden geprüfter Kombinationen, auch wenn das Endergebnis nur wenige Zeilen umfasst.

Der Optimizer entscheidet die JOIN-Reihenfolge grundsätzlich selbst und ignoriert dabei die Reihenfolge der Tabellen im FROM- und JOIN-Teil des SQL-Statements. Wer im Query-Plan eine unerwartete Reihenfolge sieht, sollte das nicht als Fehler interpretieren, sondern als Ergebnis der Kostenschätzung des Optimizers, die wiederum von aktuellen Tabellenstatistiken abhängt.

4. Die Extra-Spalte entschlüsseln

Die Spalte Extra enthält die wichtigsten Warnsignale im gesamten Query-Plan. Using filesort bedeutet, dass MySQL die Ergebnismenge nicht über einen Index sortiert ausgeben kann und stattdessen eine separate Sortieroperation im Speicher oder auf Festplatte durchführt. Using temporary bedeutet, dass eine temporäre Tabelle für GROUP BY, DISTINCT oder komplexe Unterabfragen angelegt wird, was bei großen Zwischenergebnissen erheblichen Overhead verursacht.

Using index ist dagegen ein positives Signal: Alle benötigten Spalten werden direkt aus dem Index gelesen, ohne die eigentliche Tabelle anzufassen. Das ist der sogenannte Covering Index, der in einem eigenen Artikel vertieft wird. Using where zeigt, dass nach dem Index-Zugriff zusätzlich eine Filterbedingung im Storage Engine oder Server-Layer geprüft wird, was bei niedriger Selektivität des Index viele überflüssig gelesene Zeilen bedeuten kann.

Mehrere Einträge in der Extra-Spalte, durch Semikolon getrennt, sind normal und beschreiben aufeinanderfolgende Verarbeitungsschritte für dieselbe Tabelle im Query-Plan. Die Kombination Using where; Using index etwa bedeutet, dass sowohl ein Covering Index als auch eine zusätzliche Filterbedingung innerhalb des Index angewendet werden, ohne dass die eigentliche Tabelle gelesen werden muss.

EXPLAIN SELECT customer_id, COUNT(*) FROM orders
WHERE status = 2
GROUP BY customer_id
ORDER BY COUNT(*) DESC\G

*************************** 1. row ***************************
        table: orders
         type: ref
          key: idx_status
         rows: 48210
        Extra: Using where; Using temporary; Using filesort
-- Using temporary: a temp table is built for GROUP BY
-- Using filesort: the aggregated result is sorted separately afterwards

5. EXPLAIN ANALYZE: echte Ausführungsstatistiken

Der entscheidende Unterschied zwischen EXPLAIN und EXPLAIN ANALYZE: EXPLAIN zeigt einen Plan, der niemals ausgeführt wird, während EXPLAIN ANALYZE die Abfrage tatsächlich ausführt und dabei die echten Laufzeiten und Zeilenzahlen pro Schritt des Query-Plans misst. Seit MySQL 8.0.18 liefert EXPLAIN ANALYZE eine baumartige Ausgabe mit actual time, das die tatsächliche Startzeit und Gesamtdauer jedes Operators in Millisekunden angibt, sowie actual rows für die real verarbeitete Zeilenzahl.

Weil EXPLAIN ANALYZE die Abfrage wirklich ausführt, sollte es niemals unkontrolliert auf produktiven UPDATE- oder DELETE-Statements verwendet werden, ohne sich der Nebenwirkungen bewusst zu sein. Für SELECT-Abfragen ist das Risiko gering, aber bei sehr großen Ergebnismengen kann EXPLAIN ANALYZE selbst spürbare Last erzeugen, weil die komplette Abfrage bis zum Ende durchlaufen wird, auch wenn eigentlich nur ein LIMIT von zehn Zeilen im ursprünglichen Query-Plan gefragt war.

EXPLAIN ANALYZE
SELECT o.id, c.email FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 2 AND o.created_at > '2026-01-01';

-> Nested loop inner join  (cost=612.40 rows=1840) (actual time=0.089..4.812 rows=1793 loops=1)
    -> Index range scan on o using idx_status_created
       (cost=201.20 rows=1840) (actual time=0.061..1.204 rows=1840 loops=1)
    -> Single-row index lookup on c using PRIMARY (id=o.customer_id)
       (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1840)

6. Geschätzte gegen tatsächliche Zeilen: Statistik-Drift erkennen

Der wertvollste Vergleich beim Lesen von EXPLAIN ANALYZE ist die geschätzte gegen die tatsächliche Zeilenzahl. Weichen beide Werte um mehr als das Zehnfache voneinander ab, spricht man von Statistik-Drift: Die vom Optimizer verwendeten Tabellenstatistiken sind veraltet, und der gewählte Query-Plan basiert auf falschen Annahmen über die Datenverteilung. Das führt häufig zu einem suboptimalen JOIN-Reihenfolge oder zur falschen Wahl zwischen Index-Zugriff und Volltabellenscan.

Statistik-Drift entsteht typischerweise nach großen Bulk-Inserts, nach dem Löschen großer Datenmengen oder einfach durch die persistente Natur der InnoDB-Statistiken, die standardmäßig nicht bei jeder Änderung automatisch neu berechnet werden. Ein ANALYZE TABLE nach signifikanten Datenänderungen aktualisiert die Statistiken und ist oft die einfachste Lösung, wenn EXPLAIN ANALYZE eine deutliche Abweichung zwischen geschätzter und tatsächlicher Zeilenzahl zeigt.

-- Before: optimizer estimate is far off from reality
EXPLAIN ANALYZE SELECT id FROM orders WHERE status = 2;
-> Index range scan on orders using idx_status
   (cost=201.20 rows=1840) (actual time=0.05..38.220 rows=48210 loops=1)
-- estimated 1840 rows, actually 48210 -- statistics drift, factor 26

ANALYZE TABLE orders;
-- Table statistics rebuilt from a fresh sample of the table

-- After: estimate matches reality much more closely
EXPLAIN ANALYZE SELECT id FROM orders WHERE status = 2;
-> Index range scan on orders using idx_status
   (cost=4820.10 rows=47990) (actual time=0.05..38.010 rows=48210 loops=1)

7. Volltabellenscans und Filesorts gezielt aufspüren

Ein systematischer Weg, problematische Query-Pläne in einer bestehenden Anwendung zu finden, führt über das Slow Query Log kombiniert mit pt-query-digest oder die Performance-Schema-Tabelle events_statements_summary_by_digest. Beide identifizieren die häufigsten und teuersten Abfragen, die man dann gezielt mit EXPLAIN ANALYZE untersucht. Volltabellenscans erkennt man an type: ALL in Kombination mit einer hohen rows-Zahl, Filesorts an Using filesort in der Extra-Spalte.

Nicht jeder Volltabellenscan ist ein Problem: Bei kleinen Lookup-Tabellen mit wenigen hundert Zeilen ist ein Scan oft schneller als der Overhead eines Index-Zugriffs. Die entscheidende Frage ist immer die absolute Zeilenzahl in Kombination mit der Häufigkeit der Abfrage. Ein Filesort über zwanzig Zeilen bei einer selten ausgeführten Admin-Abfrage ist unkritisch, derselbe Filesort über eine Million Zeilen bei jeder Produktseiten-Anfrage eines Shops ist ein akuter Performance-Killer.

Ein pragmatischer Schwellenwert für die erste Priorisierung: Abfragen mit mehr als zehntausend geprüften Zeilen laut rows, kombiniert mit mehr als hundert Ausführungen pro Minute laut Performance Schema, verdienen die erste Aufmerksamkeit. Alles darunter kann in der Regel warten, bis die größeren Baustellen im Query-Plan behoben sind.

8. EXPLAIN FORMAT=JSON für tiefe Analyse

Für komplexe Query-Pläne mit mehreren Unterabfragen, Derived Tables oder Window-Funktionen liefert EXPLAIN FORMAT=JSON zusätzliche Details, die in der klassischen Tabellenform fehlen, etwa geschätzte Kosten pro Schritt (cost_info), verwendete Sortieralgorithmen und die genaue Struktur verschachtelter Abfragen. Kombiniert mit ANALYZE als EXPLAIN ANALYZE FORMAT=JSON erhält man die vollständigste verfügbare Sicht auf einen Query-Plan inklusive echter Laufzeiten pro verschachteltem Element.

Das JSON-Format eignet sich besonders für automatisierte Auswertung, etwa wenn Query-Pläne in CI-Pipelines gegen Schwellenwerte für rows oder cost geprüft werden sollen, um Regression bei neuen Migrationen frühzeitig zu erkennen, bevor eine Abfrage in Produktion langsam wird.

EXPLAIN FORMAT=JSON SELECT id FROM orders WHERE status = 2\G
{
  "query_block": {
    "select_id": 1,
    "cost_info": { "query_cost": "4820.10" },
    "table": {
      "table_name": "orders",
      "access_type": "ref",
      "possible_keys": ["idx_status"],
      "key": "idx_status",
      "rows_examined_per_scan": 47990,
      "filtered": "100.00"
    }
  }
}

9. Praxis-Workflow: von der langsamen Query zum Fix

Der praktische Workflow beginnt mit dem Slow Query Log, um die Abfrage mit der höchsten kumulierten Laufzeit zu identifizieren. Danach folgt EXPLAIN ANALYZE für die exakte Abfrage mit realistischen Parametern, denn ein Query-Plan kann sich je nach übergebenen Werten unterscheiden. Man prüft zuerst type auf ALL oder index als Warnsignal, dann Extra auf Using filesort oder Using temporary, und vergleicht abschließend geschätzte mit tatsächlichen Zeilen auf Statistik-Drift.

Nach der Diagnose folgt die gezielte Maßnahme: ein neuer Composite-Index, eine Umformulierung der Abfrage, oder ANALYZE TABLE bei veralteten Statistiken. Der Fix wird erneut mit EXPLAIN ANALYZE verifiziert, um sicherzustellen, dass die tatsächliche Laufzeit sich verbessert hat, nicht nur der theoretische Query-Plan besser aussieht. Diese Wiederholung ist wichtig, weil ein neuer Index in seltenen Fällen einen anderen, ebenfalls suboptimalen Plan erzeugen kann.

Zur schnellen Priorisierung hilft eine Übersicht der wichtigsten Signale im Query-Plan, sortiert nach Schweregrad.

Diese Signale ersetzen keine vollständige Analyse, geben aber eine schnelle erste Einschätzung, welche Abfragen aus einem langen Slow-Query-Log priorisiert untersucht werden sollten. Kombiniert mit EXPLAIN ANALYZE und den tatsächlichen Zeilenzahlen ergibt sich ein vollständiges Bild des Query-Plans.

Wichtig ist auch, den Query-Plan nach jeder Schema-Änderung erneut zu prüfen. Ein neuer Composite-Index kann bestehende Abfragen beschleunigen, aber auch dazu führen, dass der Optimizer für eine andere Abfrage plötzlich einen ungünstigeren Plan wählt, weil sich die relativen Kosten zwischen mehreren möglichen Indizes verschoben haben. Ein wiederkehrender Blick auf EXPLAIN ANALYZE nach jedem Index-Wechsel gehört deshalb ebenso zum Workflow wie die anfängliche Diagnose.

10. Zusammenfassung

Der Query-Plan ist die einzige verlässliche Quelle dafür, wie MySQL eine Abfrage tatsächlich ausführt. type, key und rows zeigen die Zugriffsmethode und die geschätzte Zeilenzahl, während die Extra-Spalte mit Using filesort und Using temporary die teuersten Zusatzoperationen offenlegt. EXPLAIN ANALYZE ergänzt diese Schätzungen um echte Laufzeiten und tatsächliche Zeilenzahlen pro Schritt und macht Statistik-Drift sichtbar.

Der systematische Workflow aus Slow Query Log, EXPLAIN ANALYZE und gezielter Maßnahme, gefolgt von erneuter Verifikation, verwandelt das Lesen von Query-Plänen von einer Kunst in einen wiederholbaren Prozess. Wer type, key, rows und Extra sicher interpretiert, findet Volltabellenscans und Filesorts, lange bevor sie in Produktion zu Timeouts führen.

Am Ende zahlt sich vor allem die Gewohnheit aus, jede neue oder geänderte Abfrage vor dem Deployment einmal durch EXPLAIN ANALYZE laufen zu lassen. Ein Query-Plan, der im Entwicklungssystem mit wenigen tausend Testzeilen unauffällig aussieht, kann bei Millionen Zeilen in Produktion ein völlig anderes Verhalten zeigen, insbesondere wenn Statistiken oder Datenverteilung stark abweichen.

11. FAQ: EXPLAIN ANALYZE und Query-Pläne