Indizes und Performance

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;

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?

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