🔎Query-Optimizer und EXPLAIN

Wird mein Index benutzt? Der Optimizer entscheidet anhand von Statistiken, welcher Zugriffsweg am billigsten ist. EXPLAIN zeigt den Plan, ANALYZE führt ihn zusätzlich aus und misst.

👈Leftmost-Prefix-Regel zum Anklicken

Ein zusammengesetzter Index (kunde_id, status, datum) ist sortiert wie ein Telefonbuch: erst nach kunde_id, innerhalb gleicher kunde_id nach status, dann nach datum. Er hilft nur, wenn die Bedingungen von links lückenlos passen – eine Bereichsbedingung beendet das nutzbare Präfix.

Abfrage anklicken

Plakette = genutzte Spalten von idx_kunde_status_datum

SELECT * FROM bestellungen WHERE kunde_id = 42 AND datum >= '2025-12-01';

Zusammengesetzter Index `idx_kunde_status_datum (kunde_id, status, datum)`

kunde_id

4 Byte · zum Suchen genutzt

→

status

42 Byte · ungenutzt

→

datum

3 Byte · nur gefiltert (ICP)

+ id (PK, implizit)

✅ Genutzt: kunde_id (key_len 4). Keine Bedingung auf status – die Lücke beendet das nutzbare Präfix.

EXPLAIN (Optimizer-Wahl über alle Indizes)

Spaltenkopf anklicken

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	bestellungen	ref	idx_kunde_status_datum,idx_datum	idx_kunde_status_datum	4	const	20	Using index condition

type: Zugriffsart, von gut nach schlecht: system/const (höchstens 1 Zeile) · eq_ref (1 Zeile pro Join-Partner) · ref (Gleichheit auf nicht eindeutigem Index) · range (Bereich/IN) · index (ganzer Index wird gelesen) · ALL (ganze Tabelle).

Kandidaten des Optimizers

Plan	type	genutzte Spalten	rows (Schätzung)	Kosten (vereinfacht)
✔ idx_kunde_status_datum	ref	kunde_id	20	40
idx_datum	range	datum	8.493	16.986
(Tabellenscan)	ALL	–	100.000	100.000

Statistik: 100.000 Zeilen, 5 000 Kunden, 4 Status-Werte, 365 Tage. Kostenmodell stark vereinfacht: Tabellenscan = Zeilenzahl, Indextreffer ohne Covering zählen doppelt (Sprung in den Clustered Index). Der echte Optimizer (seit 11.0 mit neuem Kostenmodell) rechnet feiner.

🧪EXPLAIN vs. ANALYZE

MariaDB [shop]> ANALYZE SELECT * FROM bestellungen
    ->   WHERE kunde_id = 42 AND datum >= '2025-12-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bestellungen
         type: ref
possible_keys: idx_kunde_status_datum,idx_datum
          key: idx_kunde_status_datum
      key_len: 4
          ref: const
         rows: 20        ← Schätzung
       r_rows: 20.00     ← tatsächlich gelesen
     filtered: 100.00
   r_filtered: 10.00     ← nur 2 von 20 bestehen den Filter
        Extra: Using index condition

Beispielausgabe mit synthetischen Daten (Format wie MariaDB).

💡 EXPLAIN

Zeigt nur den geplanten Weg, führt nichts aus. Auch für UPDATE/DELETE möglich. EXPLAIN FORMAT=JSON enthält Kosten und Details.

💡 ANALYZE (MariaDB)

Führt die Anweisung wirklich aus und ergänzt r_rows und r_filtered – die echten Werte neben den Schätzungen. ANALYZE FORMAT=JSON liefert zusätzlich Zeiten (r_total_time_ms). MySQL hat dafür EXPLAIN ANALYZE.

⚠️ Schätzung weit daneben?

Statistiken auffrischen: ANALYZE TABLE bestellungen; – bei schiefen Verteilungen helfen Histogramme: ANALYZE TABLE bestellungen PERSISTENT FOR COLUMNS (status) INDEXES ();

✅ Laufende Abfrage ansehen

SHOW EXPLAIN FOR <thread_id>; zeigt den Plan einer Abfrage, die gerade in einer anderen Verbindung läuft (MariaDB).

🔀Join-Reihenfolge

Bei einem Join entscheidet der Optimizer auch, welche Tabelle außen (zuerst) gelesen wird. Die Zeilen von EXPLAIN stehen in dieser Reihenfolge.

SELECT k.name, b.betrag FROM kunden k JOIN bestellungen b ON b.kunde_id = k.id WHERE k.ort = 'Kiel';

table	type	key	rows
k (kunden)	ALL	NULL	5.000
b (bestellungen)	ref	idx_kunde_status_datum	20

alle Kunden lesen, ort = 'Kiel' filtern
je Kieler Kunde (100×) über den Index

gelesene Zeilen ≈ 5.000 + 100 × 20 = 7.000

Statistik angenommen: 5 000 Kunden in 50 Orten, 100 000 Bestellungen (20 je Kunde). Ohne Index auf kunden.ort muss die erste Tabelle ganz gelesen werden – trotzdem ist die kleine Tabelle außen viel günstiger. Der Optimizer probiert Reihenfolgen durch (Suchtiefe optimizer_search_depth); erzwingen lässt sie sich mit STRAIGHT_JOIN.

✅Merksätze zur Indexnutzung

💡 Von links nach rechts

Gleichheitsbedingungen vorne, die Bereichsspalte zuletzt: Index (kunde_id, status, datum) passt zu kunde_id = ? AND status = ? AND datum > ?.

💡 Keine Funktion um die Spalte

WHERE MONTH(datum) = 6 kann den Index nicht nutzen. Ausnahme in MariaDB ab 11.1: YEAR() und DATE() im Vergleich mit Konstanten werden automatisch in Bereiche umgeschrieben.

💡 LIKE

LIKE 'abc%' ist ein Bereich, LIKE '%abc' nicht – dafür gibt es FULLTEXT-Indizes.

✅ Covering Index

Stehen alle benötigten Spalten im Index (inkl. implizitem PK), entfällt das Nachschlagen der Zeile: Extra „Using index“.