EXPLAIN: der Abfrageplan
Kurz gesagt: EXPLAIN zeigt den Abfrageplan, also wie die Datenbank eine Abfrage ausführen wird: ob sie die ganze Tabelle liest oder über einen Index sucht (in SQLite sagt EXPLAIN QUERY PLAN dazu SCAN oder SEARCH).
Problem
„Der Bericht ist langsam“
Ein Kollege sagt, der Bericht über die Besuche eines Kunden sei langsam. Du könntest raten: Vielleicht ist die Tabelle groß, vielleicht ist die Bedingung kompliziert, vielleicht fehlt ein Index. Oder du fragst die Datenbank, wie genau sie die Abfrage ausführen will.
Jede Datenbank macht zuerst einen Abfrageplan: mit welcher Tabelle sie anfängt, ob sie alles liest oder über einen Index sucht, ob sie sortiert. Erst dann führt sie ihn aus. Den Plan kann man sich ansehen.
Die Idee
EXPLAIN QUERY PLAN
In SQLite schreibst du EXPLAIN QUERY PLAN vor die Abfrage. Die Abfrage läuft nicht. Statt eines Ergebnisses bekommst du den Plan:
Die wichtige Spalte ist detail. id und parent ordnen die Schritte zu einem Baum, wenn es mehrere gibt, und notused wird nicht verwendet. Hier kannst du sie ignorieren. SCAN visits heißt: die ganze Tabelle Zeile für Zeile lesen.
In diesem Feld ist der Index auf customer_id schon angelegt:
SEARCH bedeutet eine Suche über einen Index. In der Klammer steht, wonach gesucht wird: customer_id=?, also nach einem Wert von customer_id. SCAN oder SEARCH ist das Erste, wonach du in einem Plan schaust.
Probier es aus
„Meine Bestellungen“
Die Seite „Meine Bestellungen“ sucht die Bestellungen eines Kunden nach customer_id. Sieh dir den Plan im Feld an und lege dann einen Index idx_orders_customer an, damit der Plan derselben Abfrage zu SEARCH wird.
Lösung
CREATE INDEX idx_orders_customer ON orders(customer_id);CREATE INDEX idx_orders_customer ON orders(customer_id); Die Prüfung führt nach deiner Antwort den Plan von SELECT * FROM orders WHERE customer_id = 5 aus und vergleicht ihn mit dem Plan nach der Lösung, deshalb muss der Indexname derselbe sein. Beachte, dass customer_id ein Fremdschlüssel ist und es dafür keinen Index gab. SQLite und PostgreSQL legen so einen Index nicht von allein an. MySQL (InnoDB) tut es.
Die Idee
Abdeckender Index
Vergleiche den Plan von SELECT * oben mit dem Plan von COUNT(*) bei derselben Bedingung:
Hier steht USING COVERING INDEX. Der Index speichert den Wert von customer_id und die Zeilennummer. Für COUNT(*) reicht das: Die Datenbank zählt die Einträge im Index und öffnet die Tabelle nicht. Das nennt man einen abdeckenden Index (covering index).
SELECT * braucht auch product_id und seen_at. Die stehen nicht im Index, also geht die Datenbank für jeden gefundenen Eintrag zusätzlich zur Tabelle. Die Zeilennummer (id) steckt in jedem Indexeintrag, deshalb ist SELECT id mit derselben Bedingung abgedeckt:
Sieh es dir an
Das versteckte Sortieren
Die Seite zeigt die Besuche des Kunden, beginnend mit dem neuesten. Führe den Plan aus:
Die zweite Zeile lautet USE TEMP B-TREE FOR ORDER BY. Der Index half beim Finden der Zeilen, aber sie sind nicht nach seen_at sortiert. Also sortiert die Datenbank sie selbst, in einer temporären Struktur. Bei 60 Zeilen ist das nichts. Bei 60 000 merkst du es. In der nächsten Lektion bauen wir einen Index, der die Zeilen schon sortiert liefert.
Eine Suche nach dem Primärschlüssel hat im Plan einen eigenen Eintrag:
Die Idee
Wie du herausfindest, warum es langsam ist
Führe den Plan der langsamen Abfrage aus und achte auf:
- SCAN einer großen Tabelle, obwohl die Bedingung wenige Zeilen trifft. Es fehlt ein Index, oder die Bedingung kann ihn nicht nutzen (das ist die letzte Lektion des Moduls).
- USE TEMP B-TREE. Die Datenbank sortiert oder gruppiert selbst. Ein Index in der richtigen Reihenfolge kann das sparen.
- SEARCH, aber trotzdem langsam. Die Bedingung trifft viele Zeilen, oder der Index deckt die Abfrage nicht ab und die Tabelle wird für jede Zeile gelesen.
Der Plan sagt, was passieren wird. Wie lange es dauert, siehst du, wenn du die Abfrage selbst ausführst.
Genauer, für Neugierige
In SQLite zeigt EXPLAIN ohne QUERY PLAN das Programm, in das SQLite die Abfrage übersetzt (Anweisungen für seine virtuelle Maschine). Das ist etwas für die Leute, die an SQLite selbst arbeiten.
In PostgreSQL zeigt EXPLAIN den Plan mit Schätzungen: wie viele Zeilen jeder Schritt erwartet und wie viel er „kosten“ wird. EXPLAIN ANALYZE führt die Abfrage wirklich aus und fügt die echte Zeit und die echte Zeilenzahl jedes Schritts hinzu. Weichen Schätzung und Wirklichkeit stark voneinander ab, liegt dort meist der Ursprung eines langsamen Plans. Ein vollständiges Lesen heißt dort Seq Scan, eine Suche über einen Index Index Scan und ein abdeckender Index Index Only Scan. Vorsicht: EXPLAIN ANALYZE bei einem UPDATE oder DELETE ändert die Daten wirklich, führe es also in einer Transaktion aus und mach danach ROLLBACK.
MySQL hat EXPLAIN, und seit Version 8.0.18 auch EXPLAIN ANALYZE.
Übung
Zwei Aufgaben
1. Der Oktoberbericht zählt die Besuche ab dem 1. Oktober 2026. Lege einen Index idx_visits_seen an, damit der Plan SEARCH ist.
Ein Index, der den Plan des Oktoberberichts zu SEARCH macht:
Lösung
CREATE INDEX idx_visits_seen ON visits(seen_at);CREATE INDEX idx_visits_seen ON visits(seen_at); Der Plan wird zu SEARCH visits USING COVERING INDEX idx_visits_seen (seen_at>?). Der Index ist sortiert, also findet die Datenbank bei >= das erste Datum und liest bis zum Ende weiter. Und er ist abdeckend, weil COUNT(*) nichts aus der Tabelle braucht.
2. Kunde 4321 bittet darum, seine Daten zu löschen. Zuerst braucht das Programm nur die Nummern seiner Besuche. Der Index auf customer_id ist schon angelegt. Schreibe die Abfrage so, dass die Datenbank die Tabelle nicht liest.
Die Nummern (id) der Besuche von Kunde 4321, nur aus dem Index:
Lösung
SELECT id FROM visits WHERE customer_id = 4321;SELECT id FROM visits WHERE customer_id = 4321; 60 Besuche. Prüfe es mit EXPLAIN QUERY PLAN vor der Abfrage: Es steht USING COVERING INDEX. Mit SELECT * wäre das Ergebnis anders, und die Datenbank würde auch jede der 60 Zeilen öffnen.
Test