EXPLAIN und Abfragepläne: lies, was die Datenbank wirklich tut

Entschlüssle mit EXPLAIN QUERY PLAN den Plan, den der SQLite-Planer wirklich für deine Abfragen wählt.

10 Min. · 3 Fragen

SQL ist deklarativ: Du beschreibst das Ergebnis, nicht den Weg dorthin. Es ist der Abfrageplaner, der entscheidet, wie die Daten gelesen werden. Um ihm bei der Arbeit zuzusehen, bietet SQLite zwei Befehle: EXPLAIN QUERY PLAN liefert einen lesbaren Plan auf hoher Ebene, während EXPLAIN allein den Bytecode der virtuellen VDBE-Maschine ausgibt. Installiere das Kommandozeilen-Tool sqlite3 oder öffne sqliteonline.com in deinem Browser und spiele jedes folgende Beispiel selbst durch.

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);

Stelle EXPLAIN QUERY PLAN vor jede beliebige SELECT-Abfrage. Jede Zeile des Ergebnisses beschreibt, wie eine Tabelle gelesen wird. Zwei Schlüsselwörter dominieren: SCAN bedeutet, dass die Tabelle vollständig Zeile für Zeile durchsucht wird, während SEARCH einen gezielten, von einem Index geführten Zugriff anzeigt. Ohne einen passenden Index löst schon ein einfacher Gleichheitsvergleich einen vollständigen Scan aus.

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

Lege einen Index auf der gefilterten Spalte an und führe dann exakt dieselbe Abfrage erneut aus: Der Plan wechselt von SCAN zu SEARCH ... USING INDEX. Auch andere Hinweise sind wertvoll: USE TEMP B-TREE verrät eine im Speicher materialisierte Sortierung oder Gruppierung (oft ein Zeichen dafür, dass ein Index für die Sortierung fehlt), und USING COVERING INDEX signalisiert, dass die Tabelle nicht einmal geöffnet wurde.

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

Wissenscheck

Stelle sicher, dass du die wichtigsten Punkte dieser Lektion behalten hast.

1. Was gibt EXPLAIN QUERY PLAN in SQLite im Vergleich zu EXPLAIN allein zurück?
   • Den VDBE-Bytecode, Anweisung für Anweisung
   • Einen Plan auf hoher Ebene (SCAN, SEARCH, temporäre Sortierung)
   • Die tatsächlich gemessene Ausführungszeit der Abfrage
   • Die Liste der vorhandenen Indizes auf der Tabelle
2. Was bedeutet in einem SQLite-Plan die Zeile SCAN employees?
   • Die Tabelle wird vollständig Zeile für Zeile durchsucht
   • Ein abdeckender Index wird verwendet
   • Eine gezielte Suche wird über einen Index durchgeführt
   • Die Tabelle wird in den Speicher-Cache geladen
3. Wofür wird der Befehl ANALYZE verwendet?
   • Er baut alle Indizes der Datenbank neu auf
   • Er sammelt Statistiken in sqlite_stat1, um den Planer zu führen
   • Er führt die Abfrage aus und misst ihre Dauer
   • Er sperrt die Tabelle während der Analyse