Wia Datenbank-Indizes wirklich funktionieren

"Füg einfach an Index hinzu" is der häufigste Datenbank-Rat, und a der am häufigsten falsch angwendete - auf langsame Queries gworfn, ohne zu vastehn, wos der Index tatsächlich mocht, weshalb er genauso oft gor nix ändert. A Index is a zwoate, separat sortierte Kopie von ana Scheibn deiner Daten, synchron zur Tabelle gholten, die Schreibkosten und Speicherplatz gegn Lesegschwindigkeit für bestimmte Zugriffsmuster tauscht.

Ohne Index: Der Full Scan

A Tabelle ohne Index auf ana Spoitn is für a Query, de auf diese Spoitn filtert, a ungeordneter Haufn Zeiln. Um die Zeiln zu finden, wo email = 'user@example.com', hot die Datenbank ka Wahl, ois jede einzelne Zeiln zu prüfn.

SELECT * FROM users WHERE email = 'user@example.com';
-- Seq Scan on users (cost=0.00..18334.00 rows=1 width=120)
--   Filter: (email = 'user@example.com'::text)

Bei ana Tabelle mit a poar hundert Zeiln is des sofort. Bei ana Tabelle mit zehn Millionen Zeiln sand's zehn Millionen Vergleiche für jeden einzelnen Lookup - und es wird linear schlimmer, wenn die Tabelle wachst.

Wos a Index tatsächlich is

De meisten Indizes (Standard in Postgres, MySQLs InnoDB, SQL Server) sand B-Bäume: a balancierte Baumstruktur, wo jeder Knoten sortierte Schlüsselwerte und Zeiger hoit, entweder zu Kindknoten oda, auf Blattebene, zur eigentlichen Zeiln.

CREATE INDEX idx_users_email ON users (email);

Des baut an Baum auf email, sortiert. 'user@example.com' zu finden heißt jetzt, den Baum runterzugehn - mit den Werten von am Knoten vergleichen, dem Zeiger zum Ast folgen, der deinen Schlüssel enthoitn kunnt, wiederholn - statt die ganze Tabelle zu scannen. A B-Baum über a Million Zeiln braucht typischerweise in etwa 20 Vergleiche, um an Treffer zu finden, ned a Million.

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=120)
--   Index Cond: (email = 'user@example.com'::text)

Dass die cost-Schätzung in da EXPLAIN-Ausgabe von 18334 auf 8.44 fällt, is der ganze Pitch fürs Indizieren in oaner Zeiln.

Der Index hüft nur den Spoitn, de er abdeckt

A Index auf email bringt nix für a Query, de auf created_at filtert. Er hört außerdem, entscheidend, auf zu hüfn, sobois die linkeste Spoitn von am mehrspaltigen Index ned Teil von deim Filter is.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- uses the index: user_id is the leftmost column
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- does NOT use this index: status alone isn't the leftmost column
SELECT * FROM orders WHERE status = 'shipped';

Denk an an mehrspaltigen Index wia a Telefonbuch, sortiert nach Nachnama, dann Vorname. Du kannst direkt zu "Smith" springa, und von dort zu "Smith, John" - aber du kannst es ned nutzen, um olle Leit namens "John" zu finden, ohne jeden Nachnama durchzugehn. Die Spoitnreihenfolge in am zusammengsetzten Index is ned kosmetisch; sie bestimmt, wöche Queries der Index tatsächlich bedienen kann.

Indizes sand ned gratis

Jeder Index muass bei jedem INSERT, UPDATE oda DELETE, der a indizierte Spoitn berührt, aktualisiert wern - des is mehr Orbeit pro Schreibvorgang und mehr Speicher. A Tabelle mit acht Indizes zoit diese Kosten achtmoi bei jedem Insert. Drum is blindes Indizieren jeder Spoitn ein eigener Fehler: es verlangsamt Schreibvorgänge und blähte die Datenbank für Indizes auf, de Queries nie tatsächlich nutzen.

Such nach Indizes, de nix nutzt:

-- Postgres: indexes with zero scans since the last stats reset
SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

A ungenutzter Index is reine Kosten - koa Nutzen, aber er zoit immer no die Schreib- und Speichersteuer.

LIKE und warum es manchmoi deinen Index ignoriert

SELECT * FROM users WHERE email LIKE '%example.com';  -- can't use a standard B-tree index
SELECT * FROM users WHERE email LIKE 'user@%';         -- can

A B-Baum-Index is sortiert, also kann er effizient alles finden, wos mit am bestimmten Präfix anfangt ('user@%' - an dera Stelle in da Sortierung springa und vorwärts lesn), aber ned alles, wos mit am bestimmten Suffix endet ('%example.com' - des kann irgendwo in da Sortierreihenfolge sei, drum fallt's auf an Full Scan zruck). Wenn Suffix- oda Teilstring-Suche a echte Anforderung is, dafür gibt's Trigram-Indizes (Postgres' pg_trgm) oda a dedizierte Suchmaschine - a B-Baum is do ned das richtige Werkzeug.

EXPLAIN vorm Indizieren, ned danach

Der Instinkt "füg einfach an Index hinzu" überspringt den Schritt, der dir tatsächlich sogt, ob's hüft: erst den Query-Plan anschaun.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

EXPLAIN ANALYZE führt die Query aus und berichtet, wos tatsächlich passiert is - Seq Scan versus Index Scan, wie vül Zeiln untersucht versus zruckgeben wurden, und echte Zeiten. Wenn a Query scho an Index nutzt und trotzdem langsam is, kann die Lösung a ondrer Index sei (der mehr Spoitn von da Query abdeckt), a gscheitere Query (an Funktionsaufruf auf der indizierten Spoitn vermeiden, der den Index ebenfalls aushebelt), oda die Einsicht, dass monche Queries - Reporting über a ganzes Jahr an Daten - von Natur aus vül Zeiln scannen werdn.

Fazit

A Index macht aus "prüf jede Zeiln" ein "geh an sortierten Baum entlang", aber nur für die Spoitn, de er abdeckt, und die Zugriffsmuster, de er unterstützt - er is ka pauschaler Performance-Schalter. Leg Indizes für die Queries o, die du tatsächlich ausführst, in der Spoitnreihenfolge, nach der diese Queries tatsächlich filtern, und check mit EXPLAIN, dass die Datenbank nutzt, wos du gbaut host, statt es anzunehmen.