Ein langsames SELECT, das früher in 20 Millisekunden erledigt war, hängt plötzlich 12 Sekunden lang. Die Datenbank ist von 50.000 auf 5 Millionen Zeilen angewachsen, und jede Abfrage wird zur Lotterie. Kommt Ihnen das bekannt vor?

Von Indizes hat jeder schon gehört, aber nur wenige konfigurieren sie bewusst. Sie haben ein paar hinzugefügt, die Abfragen wurden gefühlt schneller, und Sie sind zur Tagesordnung übergegangen. Ein halbes Jahr später ist INSERT langsamer als ein SELECT ohne Index, weil jedes Insert fünf überflüssige B-Bäume neu aufbaut.

Sehen wir uns an, wie Indizes tatsächlich funktionieren, welche Typen es gibt und vor allem, an welche Regeln Sie sich beim Aufbau halten sollten, damit Sie in der Produktion kein Chaos anrichten.

💡 Schneller Überblick:

• Verstehen Sie die Funktionsweise von B-Baum-, Hash- und zusammengesetzten Indizes, denn ohne dieses Wissen ist die Wahl des richtigen Typs unmöglich
• Beherrschen Sie 7 zentrale Praktiken: von der Indizierung von Fremdschlüsseln bis zum Löschen ungenutzter Indizes
• Lernen Sie, EXPLAIN zu lesen und einen nützlichen von einem nutzlosen Index zu unterscheiden

Was ist ein Datenbankindex

Ein Datenbankindex ist eine separate Struktur, die sortierte Werte einer oder mehrerer Tabellenspalten zusammen mit Zeigern auf die Zeilen speichert. Wenn Sie SELECT ... WHERE category_id = 42 ausführen, durchsucht der Server ohne Index die Tabelle Zeile für Zeile (Full Table Scan). Mit einem Index findet er die benötigten Datensätze in O(log n), ähnlich wie wenn Sie einen Namen im Telefonbuch nachschlagen.

Ein Index wird mit CREATE INDEX angelegt:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Der Preis für schnellere Lesevorgänge sind jedoch langsamere Schreibvorgänge. Jedes INSERT, UPDATE und DELETE muss nicht nur die Tabelle, sondern auch alle zugehörigen Indizes aktualisieren. Drei Indizes auf einer Tabelle mit einer Million Zeilen, und Massen-Inserts werden um eine Größenordnung langsamer. Die Balance zwischen Lese- und Schreibgeschwindigkeit ist die zentrale Frage des Index-Designs.

Eine detaillierte Aufbereitung des Themas von Hussein Nasser: 497 Tausend Abonnenten, ein technischer Ansatz ohne überflüssiges Beiwerk. Am Beispiel von PostgreSQL zeigt er die interne Mechanik von Indizes, warum ein CREATE INDEX eine Abfrage um das Hundertfache beschleunigt, während ein anderer nichts bewirkt.

Indextypen: Wann Sie welchen einsetzen

Die Wahl des Indextyps bestimmt, wie effizient die Datenbank Ihre Abfrage verarbeitet. Verschiedene DBMS setzen sie unterschiedlich um, aber die Prinzipien sind universell.

B-Baum (Balanced Tree)

Der Standardtyp in den meisten relationalen DBMS. PostgreSQL, MySQL, Oracle und SQL Server verwenden alle den B-Baum als Standardindex. Er speichert Schlüssel in sortierter Reihenfolge, unterstützt Vergleichsoperationen, Bereiche (BETWEEN), Präfixsuche (LIKE 'prefix%') und Sortierung. Für die allermeisten Fälle ist er die beste Wahl.

Hash-Index

Funktioniert nur für exakte Gleichheitsvergleiche =. Blitzschnell bei punktuellen Abfragen, aber unbrauchbar für Bereiche und Sortierung. In PostgreSQL sind Hash-Indizes seit Version 10 produktionsreif; in MySQL sind sie in InnoDB nicht verfügbar (nur in MEMORY).

Clustered Index

Definiert die physische Reihenfolge der Zeilen auf der Festplatte. In MySQL InnoDB ist der Primärschlüssel immer geclustert: Zeilen werden in der Reihenfolge des PRIMARY KEY gespeichert. In SQL Server gibt es einen Clustered Index pro Tabelle. Die Wahl des richtigen Clustered Key (monoton steigend: BIGSERIAL, AUTO_INCREMENT oder UNIQUEIDENTIFIER mit NEWSEQUENTIALID()) bringt Vorteile bei Bereichsabfragen und verhindert Seitenfragmentierung.

Zusammengesetzter Index

Ein Index über zwei oder mehr Spalten. Entscheidend für Abfragen, die nach mehreren Spalten filtern und sortieren:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Die Spaltenreihenfolge ist wichtig: Setzen Sie die Spalte mit der höchsten Selektivität, die in WHERE verwendet wird, an die erste Stelle. Das Prinzip der linken Präfixe: Ein Index (A, B, C) funktioniert für WHERE A und WHERE A AND B, aber nicht für WHERE B AND C.

Covering Index

Enthält alle Spalten, die die Abfrage benötigt, sowohl zum Filtern als auch für die Ausgabe. Die Datenbank bezieht alles aus dem Index, ohne auf die Tabelle zugreifen zu müssen. In PostgreSQL geschieht dies mit INCLUDE-Spalten; in MySQL InnoDB erfolgt ein implizites Covering über den Clustered Index.

7 Zentrale Praktiken zur Indizierung

1. Indizieren Sie in WHERE verwendete Spalten

Die erste Regel: Jede Spalte, die regelmäßig in WHERE, JOIN ... ON und HAVING verwendet wird, sollte indiziert sein. Dies sind die Operationen, die am meisten von Indizes profitieren.

Prüfen Sie vor dem Anlegen eines Index die Selektivität der Spalte. Wenn die Spalte status nur drei Werte hat ('neu', 'in Bearbeitung', 'erledigt') und es 2 Millionen Zeilen gibt, ist ein Index auf status nahezu nutzlos: Der Planer wird einen Full Table Scan als günstigere Option wählen. Ein Index ist dann sinnvoll, wenn die Anzahl der eindeutigen Werte im Verhältnis zur Tabellengröße ausreichend groß ist.

2. Indizieren Sie für die Sortierung verwendete Spalten

ORDER BY ohne Index bedeutet Filesort (MySQL) oder explizite Sortierung (PostgreSQL): Der Server sammelt alle Zeilen ein und sortiert sie im Arbeitsspeicher (oder auf der Festplatte, wenn work_mem/sort_buffer_size zu klein ist). Ein Index auf denselben Spalten wie ORDER BY ist kostenlos, da die Daten im B-Baum bereits sortiert vorliegen.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indizieren Sie in GROUP BY und Aggregationen verwendete Spalten

Gruppierung ohne Index erfordert einen vollständigen Scan und den Aufbau einer Hashtabelle. Ein Index auf den GROUP BY-Spalten verwandelt die Operation in eine Streaming-Aggregation: Die Zeilen sind bereits in Schlüsselreihenfolge gruppiert.

4. Indizieren Sie alle Fremdschlüssel

Ein nicht indizierter Fremdschlüssel ist eine tickende Zeitbombe. DELETE FROM users WHERE id = 5 mit einem FOREIGN KEY (user_id) REFERENCES users(id) auf der Tabelle orders, aber ohne Index auf user_id, bedeutet einen Full Table Scan von orders bei jeder Löschung. Alle gängigen DBMS verlangen einen Index auf dem Fremdschlüssel oder legen ihn implizit an (MySQL InnoDB macht das automatisch, PostgreSQL nicht).

5. Indizieren Sie eindeutige Spalten und Primärschlüssel

Der Primärschlüssel wird automatisch indiziert (oft als Clustered Index). Ein expliziter UNIQUE INDEX schützt vor Duplikaten und beschleunigt zudem Suchvorgänge. Jede Spalte mit einer fachlichen Eindeutigkeitsbedingung (zum Beispiel email, slug oder external_id) sollte einen Unique Index erhalten, sowohl aus Integritätsgründen als auch für die Performance.

6. Setzen Sie den Clustered Index bewusst ein

Bei großen Tabellen (zig Millionen Zeilen) ist der richtige Clustered Key entscheidend. Eine gute Wahl ist ein monoton steigender Wert: AUTO_INCREMENT, BIGSERIAL oder UUID v7. Eine zufällige UUID als Clustered Key verursacht Seitenfragmentierung: Jedes Insert landet an einer zufälligen Stelle im B-Baum und teilt volle Seiten in zwei halbgefüllte auf.

7. Löschen Sie ungenutzte Indizes

Ein Index, den keine Abfrage nutzt, ist ein reiner Verlust. Er verlangsamt Schreibvorgänge, belegt Festplattenspeicher und Buffer-Pool-Speicher und führt den Abfrageplaner in die Irre. In PostgreSQL liefert die Systemtabelle pg_stat_user_indexes eine Liste ungenutzter Indizes:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

In MySQL sind ähnliche Informationen aus sys.schema_unused_indexes verfügbar (ab Version 5.7). Planen Sie eine monatliche Prüfung ein und löschen Sie Indizes, die im Berichtszeitraum kein einziges Mal genutzt wurden.

So überprüfen Sie, ob Ihre Indizes funktionieren

Wenn Sie einen Index angelegt haben, verifizieren Sie, dass er tatsächlich genutzt wird. Der Befehl EXPLAIN (oder EXPLAIN ANALYZE) zeigt den Ausführungsplan der Abfrage und die tatsächliche Indexnutzung:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

Achten Sie in der Ausgabe auf Index Scan oder Index Only Scan (PostgreSQL) / Using index (MySQL). Wenn Sie Seq Scan (PostgreSQL) oder Using where; Using filesort (MySQL) sehen, wird der Index nicht genutzt. Mögliche Gründe: geringe Selektivität, falscher Indextyp, nicht passende Spaltenreihenfolge in einem zusammengesetzten Index oder veraltete Statistiken (ANALYZE table_name;).

Überwachen Sie regelmäßig Metriken: pg_stat_user_indexes.idx_scan in PostgreSQL, sys.schema_index_statistics in MySQL. Ein Index mit null Scans über einen Monat hinweg ist ein Kandidat für die Entfernung.

⁉️🤔 Häufig gestellte Fragen

Wie viele Indizes sollte eine Tabelle haben?

Idealerweise 2 bis 6 Indizes pro aktiv genutzter Tabelle. Weniger als zwei bedeuten fast sicher, dass einige Abfragen suboptimal sind. Bei mehr als sechs sollten Sie sorgfältig prüfen, ob wirklich alle benötigt werden: Jeder zusätzliche Index verlangsamt Schreibvorgänge. Für Lookup-Tabellen (seltene Schreibvorgänge, häufige Lesevorgänge) sind mehr Indizes gerechtfertigt. Bei operativen Tabellen mit hohem Durchsatz (viele INSERT/UPDATE) halten Sie die Anzahl minimal.

Wieso ist ein zusammengesetzter Index besser als mehrere Einzelspalten-Indizes?

Ein zusammengesetzter Index (A, B) ist EINE Struktur. Der Server durchläuft sie einmal. Drei separate Indizes (A), (B) und (C) für WHERE A=1 AND B=2 zwingen den Server, entweder einen Index auszuwählen (und den Rest zu filtern) oder einen Bitmap Index Scan durchzuführen (Zusammenführung von Bitmaps). Ein zusammengesetzter Index ist fast immer effizienter, vorausgesetzt, die Spaltenreihenfolge passt zu Ihren Abfragen.

Wann schadet ein Index mehr, als dass er nützt?

Drei typische Szenarien. Erstens: Die Tabelle ist klein (bis zu einigen tausend Zeilen), und ein Full Table Scan ist schneller als das Lesen des Index plus das Abrufen der Zeilen. Zweitens: Ein Index auf einer Spalte mit geringer Selektivität (is_deleted, status mit drei Werten). Drittens: Massen-Inserts während ETL/Import, bei denen Indizes bei jedem Stapel neu aufgebaut werden. Löschen Sie sie vor dem Laden und legen Sie sie danach neu an.

Sollte man in JOINs verwendete Spalten indizieren?

Unbedingt. Jeder JOIN ohne Index auf der Join-Spalte der äußeren Tabelle ist eine Nested Loop mit einem Full Scan. Für LEFT JOIN orders ON users.id = orders.user_id verwandelt ein Index auf orders.user_id die Nested Loop in einen Index Lookup. Indizieren Sie immer die Spalten, über die Sie verknüpfen.

B-Baum oder Hash: Welchen für exakte Suchvorgänge wählen?

Für = ist Hash schneller: Ein Hash-Lookup benötigt konstante Zeit, während der B-Baum den Baum in einer logarithmischen Anzahl von Schritten durchläuft. Aber Hash unterstützt keine Bereiche, Sortierung oder UNIQUE. In der Praxis deckt der B-Baum die allermeisten Szenarien ab; Hash ist ein Nischenwerkzeug für punktuelle Schlüsselabfragen in hochbelasteten Systemen (Sessions, Caches). In PostgreSQL sind Hash-Indizes seit Version 10 produktionsreif und benötigen weniger Platz als B-Bäume.

Sollte man Indizes "nur für alle Fälle" anlegen?

Nein. Jeder Index ist ein Kompromiss. Er beschleunigt Lesevorgänge auf Kosten langsamerer Schreibvorgänge und zusätzlichen Festplattenspeichers. Legen Sie Indizes nicht "nur für alle Fälle" an; indizieren Sie für konkrete Abfragen, die in Ihrer Anwendung tatsächlich ausgeführt werden. Profilen Sie langsame Abfragen (pg_stat_statements, slow_query_log), fügen Sie Indizes chirurgisch hinzu und prüfen Sie EXPLAIN vorher und nachher.

Die wichtigste Erkenntnis ist einfach: Indizes sind ein Werkzeug, kein Selbstzweck. Ein gut gestalteter zusammengesetzter Index kann drei Einzelspalten-Indizes ersetzen und Gigabytes an Festplattenspeicher sparen. Und ein ungenutzter Index auf einer schreibintensiven Tabelle kann die gesamte Anwendung verlangsamen.

Wenn Sie tiefer einsteigen möchten, beginnen Sie mit dem offiziellen SQL Server-Leitfaden zum Index-Design und der PostgreSQL-Dokumentation zu Indextypen. Und wenn Sie auf eine Abfrage stoßen, die Indizes nicht beheben können, liegt das Problem möglicherweise im Datenmodell selbst.