SQL: Indizes und Performance

Kurz: Ein Index ist wie das Stichwortverzeichnis eines Buchs: Statt jede Zeile zu lesen (Full Table Scan), springt die Datenbank direkt zu den Treffern. Das macht Lesen schneller, Schreiben aber etwas langsamer und braucht Speicher.

Teil des Kurses SQL

Kapitel 11 von 15 im Kurs SQL (Abschnitt „Fortgeschritten“). Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

Was ist ein Index?

Ein Index ist wie das Stichwortverzeichnis eines Buchs: Statt jede Zeile zu lesen (Full Table Scan), springt die Datenbank direkt zu den Treffern. Das macht Lesen schneller, Schreiben aber etwas langsamer und braucht Speicher.

CREATE TABLE messungen (id INTEGER PRIMARY KEY, sensor INTEGER, wert REAL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000)
INSERT INTO messungen (sensor, wert) SELECT i % 50, i * 1.5 FROM n;
 
CREATE INDEX idx_messungen_sensor ON messungen (sensor);
CREATE UNIQUE INDEX idx_eindeutig ON messungen (id, sensor);
 
SELECT count(*) AS treffer FROM messungen WHERE sensor = 7;
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' ORDER BY name;

Ausgabe:

treffer
-------
20
name                  tbl_name
--------------------  ---------
idx_eindeutig         messungen
idx_messungen_sensor  messungen

Primärschlüssel und UNIQUE-Spalten bekommen automatisch einen Index.

Den Abfrageplan lesen

Mit EXPLAIN QUERY PLAN (PostgreSQL: EXPLAIN ANALYZE) siehst du, wie die Datenbank vorgeht:

EXPLAIN QUERY PLAN SELECT * FROM messungen WHERE sensor = 7;
-- ohne Index:  SCAN messungen
-- mit Index:   SEARCH messungen USING INDEX idx_messungen_sensor (sensor=?)

SCAN liest alles, SEARCH ... USING INDEX springt gezielt.

Wann lohnt sich ein Index?

Index sinnvoll	Index weniger sinnvoll
Spalten in WHERE, JOIN ... ON, ORDER BY	sehr kleine Tabellen
Fremdschlüssel	Spalten mit wenigen verschiedenen Werten (z. B. Geschlecht)
Spalten mit vielen verschiedenen Werten	Tabellen mit sehr vielen Schreibzugriffen

Zusammengesetzte Indizes

Ein Index auf (a, b) hilft bei Filtern auf a oder a und b, nicht bei b allein (Leftmost-Prefix-Regel).

Index wird nicht genutzt

Typische Stolperfallen:

WHERE lower(name) = 'mia'      -- Funktion auf der Spalte verhindert den Index
WHERE name LIKE '%ia'          -- führender Platzhalter
WHERE punkte + 1 = 100         -- Rechnung auf der Spalte (besser: punkte = 99)
WHERE preis = '10'             -- Typ passt nicht

Weitere Tipps

• Nur die nötigen Spalten abfragen (SELECT * vermeiden)
• LIMIT setzen, wenn du nicht alles brauchst
• Mehrere Einzelabfragen in einer Schleife (N+1-Problem) durch einen JOIN ersetzen
• Statistiken aktuell halten (ANALYZE)
• Große Änderungen in Transaktionen bündeln

Merke

• Indizes beschleunigen Lesen, bremsen Schreiben und kosten Speicher
• Indiziere Spalten aus WHERE, JOIN und ORDER BY
• EXPLAIN zeigt, ob der Index wirklich genutzt wird
• Funktionen auf indizierten Spalten verhindern oft die Indexnutzung
• Zusammengesetzte Indizes beachten die Reihenfolge der Spalten

Übungsaufgabe

Lege eine Tabelle mit 1000 Zeilen an, erstelle einen Index auf eine Filterspalte und prüfe mit EXPLAIN QUERY PLAN den Unterschied.

Quiz zur Selbstkontrolle

Was ist ein Nachteil von Indizes?

• Sie verlangsamen Schreibzugriffe und brauchen Speicher (richtig)
• Sie verlangsamen Lesezugriffe
• Sie löschen Daten
• Sie verhindern Joins

Wofür steht ein Full Table Scan?

• Alle Zeilen der Tabelle werden gelesen (richtig)
• Nur der Index wird gelesen
• Die Tabelle wird gesichert
• Die Tabelle wird gelöscht

Welche Bedingung kann einen Index auf name oft nicht nutzen?

• WHERE lower(name) = ‘mia’ (richtig)
• WHERE name = ‘Mia’
• WHERE name > ‘M’
• WHERE name IN (‘Mia’, ‘Tom’)

Weiter im Kurs

Zurück: Fensterfunktionen

Weiter: Transaktionen, Views und Trigger

Alle Kapitel: SQL im Überblick