EXPLAIN lesen und Query-Pläne wirklich verstehen

Jede langsame SQL-Abfrage hat einen Grund — und EXPLAIN deckt ihn auf. Das MySQL-Kommando zeigt den Ausführungsplan: welche Tabellen in welcher Reihenfolge gelesen werden, welche Indexes MySQL nutzt (oder eben nicht nutzt), wie viele Zeilen geschätzt werden und welche internen Operationen stattfinden. Wer MySQL EXPLAIN lesen kann, findet langsame Queries in Magento-Datenbanken in Minuten statt Stunden. Dieser Artikel erklärt alle relevanten Spalten, zeigt die wichtigsten Signalwörter und führt durch EXPLAIN ANALYZE für echte Messwerte.

Grundlegende EXPLAIN-Syntax

EXPLAIN wird einfach vor eine SELECT-Anweisung gestellt. MySQL führt die Abfrage dabei nicht aus — es zeigt nur, wie es die Query ausführen würde. Das macht MySQL EXPLAIN vollkommen sicher für Produktionsserver.

-- Grundsyntax: EXPLAIN vor die Query stellen
EXPLAIN SELECT e.entity_id, e.sku
FROM catalog_product_entity e
INNER JOIN catalog_product_entity_varchar v
    ON v.entity_id = e.entity_id AND v.attribute_id = 73 AND v.store_id = 0
WHERE e.type_id = 'simple'
ORDER BY e.entity_id DESC
LIMIT 20;

-- Spalten im Output:
-- id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

-- JSON-Format (mehr Details, z.B. cost_info):
EXPLAIN FORMAT=JSON SELECT ...;

-- EXPLAIN ANALYZE: Query wirklich ausführen und echte Zeiten messen (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT ...;
-- Zeigt: (actual time=X..Y rows=Z loops=1) neben den Schätzwerten

Die wichtigste Spalte: type — Zugriffstyp

Die type-Spalte ist die wichtigste Information im EXPLAIN-Output. Sie zeigt, wie MySQL auf die Tabelle zugreift. Die Reihenfolge von effizient bis teuer:

• system / const: Genau eine Zeile — MySQL kennt das Ergebnis zur Optimierungszeit. Idealfall.
• eq_ref: Für jede Zeile der äußeren Tabelle wird genau eine Zeile per Unique/Primary Key gelesen. Optimal für JOINs.
• ref: Index-Zugriff, der mehrere Zeilen zurückgeben kann. Gut — typisch für WHERE auf einem normalen Index.
• range: Index-Bereichsscan (BETWEEN, >, <, IN). Akzeptabel wenn der Bereich überschaubar ist.
• index: Gesamter Index wird gescannt. Schneller als ALL, aber bei großen Indexes problematisch.
• ALL: Full Table Scan — jede Zeile der Tabelle wird gelesen. Kritisch bei großen Tabellen!

-- type=const: Primary Key Zugriff — schnellstmöglich
EXPLAIN SELECT * FROM sales_order WHERE entity_id = 42;
-- type: const, key: PRIMARY, rows: 1

-- type=ref: normaler Index
EXPLAIN SELECT * FROM sales_order WHERE customer_id = 100;
-- type: ref, key: CUSTOMER_ID, rows: ~15

-- type=range: Bereichsscan
EXPLAIN SELECT * FROM sales_order
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';
-- type: range, key: CREATED_AT, rows: ~500

-- type=ALL: Full Table Scan — Problem!
EXPLAIN SELECT * FROM sales_order WHERE status = 'pending';
-- type: ALL, key: NULL, rows: 50000

-- Lösung: Index auf status erstellen
CREATE INDEX idx_sales_order_status ON sales_order (status);
-- Nach dem Index: type: ref, key: idx_sales_order_status, rows: ~300

key, key_len und possible_keys

possible_keys listet alle Indexes, die MySQL theoretisch nutzen könnte. key zeigt den tatsächlich gewählten Index. key_len gibt in Bytes an, wie viel vom Index genutzt wird — bei Composite Indexes wichtig.

-- key=NULL: Index vorhanden, aber nicht nutzbar
EXPLAIN SELECT * FROM customer_entity WHERE email LIKE '%@example.com';
-- possible_keys: EMAIL_ADDRESS
-- key: NULL  ← führender Wildcard verhindert Index-Nutzung!

-- key_len bei Composite Index aufschlüsseln
-- Index: (customer_id INT, store_id SMALLINT) auf sales_order
EXPLAIN SELECT * FROM sales_order WHERE customer_id = 42 AND store_id = 1;
-- key_len: 6  (4 Bytes INT + 2 Bytes SMALLINT → beide Teile genutzt ✓)

-- Wenn nur erste Spalte in WHERE:
EXPLAIN SELECT * FROM sales_order WHERE customer_id = 42;
-- key_len: 4  (nur erste Index-Spalte genutzt — korrekt und erwartet)

-- ref zeigt womit der Index verglichen wird:
-- ref: const → konstanter Wert (42) — optimal
-- ref: func  → Ergebnis einer Funktion → Index meist nicht nutzbar!

rows: Geschätzte Lesekosten

Die rows-Spalte zeigt MySQL's Schätzung, wie viele Zeilen gelesen werden müssen. Bei mehreren JOINs werden die Zahlen multipliziert. Falsche Statistiken führen zu falschen Schätzungen — ANALYZE TABLE hilft.

-- rows-Wert bei einem JOIN interpretieren
EXPLAIN SELECT o.*, oi.*
FROM sales_order o
INNER JOIN sales_order_item oi ON oi.order_id = o.entity_id
WHERE o.customer_id = 42;
-- table=o:  type=ref, rows=15
-- table=oi: type=ref, rows=3
-- Gesamtaufwand: ~15 × 3 = ~45 Zeilenzugriffe — sehr effizient!

-- Statistiken aktualisieren wenn rows-Schätzungen zu stark abweichen
ANALYZE TABLE sales_order;
ANALYZE TABLE sales_order_item;

-- Schätzung vs. Realität mit EXPLAIN ANALYZE prüfen
EXPLAIN ANALYZE SELECT * FROM sales_order WHERE customer_id = 42;
-- Schätzung rows=15, EXPLAIN ANALYZE: actual rows=18
-- Differenz klein → Statistik aktuell ✓
-- Schätzung rows=1500, actual rows=18
-- Starke Abweichung → ANALYZE TABLE ausführen!

Extra: Die versteckten Performance-Signale

Die Extra-Spalte ist oft wichtiger als alle anderen, weil sie zeigt, was MySQL intern tut. Die wichtigsten Werte:

-- Using index: Covering Index — schnellstmöglich
-- MySQL liest nur den Index, NICHT die Tabelle
EXPLAIN SELECT entity_id, customer_id FROM sales_order WHERE customer_id = 42;
-- Extra: Using index (wenn entity_id Teil des Index ist)

-- Using where: MySQL filtert nach dem Lesen (normal, meist unproblematisch)
EXPLAIN SELECT * FROM sales_order WHERE customer_id = 42 AND status = 'complete';
-- Extra: Using where  (status-Filter nach Index-Zugriff — das ist OK)

-- Using filesort: nachträgliche Sortierung — oft optimierbar
EXPLAIN SELECT * FROM sales_order WHERE customer_id = 42 ORDER BY created_at DESC;
-- Extra: Using filesort ← bei großen Tabellen problematisch!
-- Lösung: Composite Index (customer_id, created_at)
CREATE INDEX idx_customer_created ON sales_order (customer_id, created_at);

-- Using temporary: temporäre Tabelle für GROUP BY/DISTINCT
EXPLAIN SELECT status, COUNT(*) FROM sales_order GROUP BY status;
-- Extra: Using temporary; Using filesort ← doppeltes Problem
-- Lösung: Index auf (status) erstellen

-- Using index condition (Index Condition Pushdown):
-- MySQL überträgt WHERE-Bedingungen in den Index-Scan → effizienter
EXPLAIN SELECT * FROM catalog_product_entity_varchar
WHERE attribute_id = 73 AND store_id = 0 AND value LIKE 'Produkt%';
-- Extra: Using index condition ← ICP aktiv, gut!

EXPLAIN ANALYZE: Echte Messwerte

EXPLAIN ANALYZE führt die Query wirklich aus und misst die Ausführungszeit jedes Schritts. Unverzichtbar wenn MySQL's Schätzungen unzuverlässig sind.

-- EXPLAIN ANALYZE: tatsächliche Ausführung mit Timing
EXPLAIN ANALYZE
SELECT o.entity_id, o.grand_total, i.name
FROM sales_order o
INNER JOIN sales_order_item i ON i.order_id = o.entity_id
WHERE o.customer_id = 42 AND o.status = 'complete'
ORDER BY o.created_at DESC
LIMIT 10;

-- Beispiel-Output (vereinfacht):
-- -> Limit: 10 row(s)  (actual time=1.234..1.234 rows=10 loops=1)
--    -> Sort: o.created_at DESC  (actual time=1.220..1.222 rows=10 loops=1)
--       -> Filter: (o.status='complete')  (actual time=0.089..1.180 rows=18 loops=1)
--          -> Nested loop join  (actual time=0.085..1.134 rows=42 loops=1)
--             -> Index lookup on o using CUSTOMER_ID  (actual time=0.043..0.062 rows=18 loops=1)
--             -> Index lookup on i using ORDER_ID     (actual time=0.042..0.057 rows=2 loops=18)

-- Interpretation:
-- actual time=X..Y: X = Zeitpunkt erster Zeile, Y = Zeitpunkt letzter Zeile (in ms)
-- rows=18: tatsächlich 18 Zeilen (nicht Schätzung)
-- loops=18: dieser Schritt wurde 18× ausgeführt (ein Mal pro äußere Zeile)
-- Der Sort-Schritt (1.220ms) ist teuerster → Index auf (customer_id, created_at) prüfen

Typische Magento-Probleme mit EXPLAIN identifizieren

In Magento gibt es wiederkehrende Muster, die in EXPLAIN als Performance-Probleme sichtbar werden:

-- Problem 1: Funktion auf WHERE-Spalte verhindert Index
-- Schlecht: DATE() auf indizierten Timestamp
EXPLAIN SELECT * FROM sales_order WHERE DATE(created_at) = '2025-01-15';
-- type: ALL, key: NULL ← Full Table Scan!

-- Gut: Bereich statt Funktion
EXPLAIN SELECT * FROM sales_order
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00';
-- type: range, key: CREATED_AT ← Index genutzt ✓

-- Problem 2: EAV-JOIN ohne attribute_id
EXPLAIN SELECT cpe.entity_id
FROM catalog_product_entity cpe
INNER JOIN catalog_product_entity_varchar v ON v.entity_id = cpe.entity_id
WHERE v.value = 'Mein Produkt';
-- type: ALL auf catalog_product_entity_varchar ← ohne attribute_id kein selektiver Zugriff!

-- Gut: attribute_id immer angeben
EXPLAIN SELECT cpe.entity_id
FROM catalog_product_entity cpe
INNER JOIN catalog_product_entity_varchar v
    ON v.entity_id = cpe.entity_id AND v.attribute_id = 73 AND v.store_id = 0
WHERE v.value = 'Mein Produkt';
-- type: ref ← viel besser, Index wird genutzt ✓

-- Problem 3: Veraltete Statistiken führen zu falschem Queryplan
-- MySQL wählt einen schlechten Index weil die Schätzungen veraltet sind
ANALYZE TABLE catalog_product_entity;
ANALYZE TABLE catalog_product_entity_varchar;
-- Danach EXPLAIN erneut prüfen

EXPLAIN für JOINs und Subqueries lesen

Bei Queries mit mehreren Tabellen oder Subqueries gibt EXPLAIN mehrere Zeilen aus — eine pro Tabelle. Die id-Spalte zeigt die logische Ausführungsreihenfolge.

-- EXPLAIN für CTE
EXPLAIN WITH top_kunden AS (
    SELECT customer_id, SUM(grand_total) AS total
    FROM sales_order
    WHERE status = 'complete'
    GROUP BY customer_id
    ORDER BY total DESC
    LIMIT 100
)
SELECT ce.email, tk.total
FROM top_kunden tk
INNER JOIN customer_entity ce ON ce.entity_id = tk.customer_id;

-- Output-Zeilen:
-- id=1, select_type=PRIMARY:  Haupt-Query (JOIN mit CTE-Ergebnis)
-- id=2, select_type=DERIVED:  CTE top_kunden — wird materialisiert
-- Extra: Using temporary, Using filesort → CTE hat ORDER BY + LIMIT → normal

-- DEPENDENT SUBQUERY: ein häufiges Magento-Performance-Problem
EXPLAIN SELECT * FROM sales_order
WHERE customer_id IN (
    SELECT entity_id FROM customer_entity WHERE email LIKE '%@test.com'
);
-- select_type=DEPENDENT SUBQUERY → wird für JEDE Zeile des äußeren Selects neu ausgeführt → langsam!

-- Besser: JOIN statt korrelierter Subquery
EXPLAIN SELECT o.*
FROM sales_order o
INNER JOIN customer_entity ce ON ce.entity_id = o.customer_id
WHERE ce.email LIKE '%@test.com';
-- select_type=SIMPLE → einmalige Ausführung ✓

Häufige Fragen zu MySQL EXPLAIN