Datenbankindizes einem Junior-Backend-Entwickler beibringen

Datenbankindizes verstehen: Ein praktischer Leitfaden

Was ein Index ist

Ein Datenbankindex ist eine separate Datenstruktur, die die Datenbank neben einer Tabelle führt. Er speichert die Werte einer oder mehrerer Spalten in sortierter, durchsuchbarer Form, zusammen mit Zeigern zurück zu den tatsächlichen Zeilen. Die Tabelle selbst speichert Ihre Daten; der Index ist eine zusätzliche Struktur, deren einzige Aufgabe es ist, der Datenbank zu helfen, Zeilen schneller zu finden.

Hier ist eine Analogie, und sie ist die einzige, die Sie brauchen: Ein Index ist wie das Stichwortverzeichnis am Ende eines Lehrbuchs. Wenn Sie jede Seite wollen, auf der „Transaktionen“ erwähnt wird, lesen Sie nicht das ganze Buch Seite für Seite; Sie schlagen „Transaktionen“ im alphabetisch sortierten Index nach, erhalten eine kurze Liste von Seitenzahlen und springen direkt dorthin. Ohne diesen Index ist Ihre einzige Option, jede Seite zu durchsuchen. Eine Datenbank steht vor genau der gleichen Wahl: einen Index verwenden, um zu passenden Zeilen zu springen, oder die gesamte Tabelle durchsuchen.

Nun das tatsächliche Verhalten, ohne die Analogie. Wenn Sie eine Abfrage wie SELECT * FROM orders WHERE customer_id = 42 ausführen, hat die Datenbank zwei grundlegende Strategien. Ein vollständiger Tabellenscan liest jede Zeile und prüft die Bedingung, was Zeit proportional zur Größe der Tabelle kostet. Ein Index-Lookup durchsucht stattdessen die sortierte Indexstruktur nach customer_id = 42, findet schnell die passenden Einträge und folgt den gespeicherten Zeigern, um nur diese Zeilen abzurufen. Für eine große Tabelle, bei der nur wenige Zeilen übereinstimmen, kann der Indexpfad tausendmal günstiger sein.

Wie ein B-Tree-Index auf hoher Ebene funktioniert

Der gängigste Indextyp ist ein B-Tree. Es ist eine balancierte Baumstruktur, in der Schlüssel in sortierter Reihenfolge gehalten werden. Der oberste Knoten teilt den Schlüsselraum in Bereiche auf, jeder Kindknoten unterteilt weiter, und die unterste Ebene (die Blätter) enthält die tatsächlichen indizierten Werte mit Zeigern zu den Tabellenzeilen. Da der Baum balanciert ist und jeder Knoten viele Schlüssel enthält, sind selbst bei einer Tabelle mit Hunderten von Millionen von Zeilen normalerweise nur drei bis fünf Knotenlesevorgänge erforderlich, um einen bestimmten Wert zu finden.

Da ein B-Tree Werte in sortierter Reihenfolge hält, unterstützt er mehr als exakte Übereinstimmungen. Er verarbeitet effizient Bereichsbedingungen (WHERE created_at >= '2024-01-01'), Präfixübereinstimmungen bei Zeichenketten (WHERE email LIKE 'anna%') und kann Zeilen bereits sortiert zurückgeben, was der Datenbank ermöglicht, einen separaten Sortierschritt für passende ORDER BY-Klauseln zu überspringen.

Warum Indizes etwas kosten

Indizes sind nicht kostenlos, und das ist der Kompromiss, den Sie verinnerlichen müssen.

Schreibvorgänge werden langsamer. JEDES INSERT muss einen Eintrag zu jedem Index der Tabelle hinzufügen. JEDES DELETE muss Einträge entfernen. JEDES UPDATE, das eine indizierte Spalte ändert, muss die entsprechenden Indexeinträge aktualisieren. Eine Tabelle mit sechs Indizes führt effektiv bis zu sieben Schreibvorgänge für jede logische Zeileneinfügung durch. Bei schreibintensiven Tabellen beeinträchtigt unvorsichtige Indizierung den Durchsatz messbar.

Der Speicherplatz wächst. Jeder Index ist eine vollständige Kopie der indizierten Spaltenwerte plus Zeiger und Baumstruktur. Indizes auf großen Tabellen können die Größe der Tabelle selbst erreichen oder übertreffen, was sich auch auf Backups und den Arbeitsspeicher-Cache auswirkt.

Das Leitprinzip lautet also: Indizes tauschen Schreibkosten und Speicherplatz gegen Lesegeschwindigkeit. Sie fügen sie hinzu, wo Lesevorgänge deutlich profitieren, nicht überall.

Selektivität: das Schlüsselkonzept für die Entscheidung über den Wert

Selektivität beschreibt, wie gut eine Bedingung Zeilen eingrenzt. Eine hochselektive Spalte hat viele verschiedene Werte im Verhältnis zur Zeilenanzahl. Eine E-Mail- oder Bestell-ID ist hochselektiv: Das Filtern danach gibt eine oder eine Handvoll Zeilen von Millionen zurück, und ein Index glänzt. Eine Spalte wie „status“ mit drei Werten („pending“, „shipped“, „cancelled“) oder ein boolesches „is_active“-Flag hat eine geringe Selektivität: Das Filtern kann immer noch 40 % der Tabelle abgleichen.

Warum ist das wichtig? Wenn eine Bedingung einen großen Teil der Tabelle abgleicht, ist das Hin- und Herspringen zwischen Index und Tabelle für Millionen von Zeilen oft langsamer als das einfache sequentielle Durchsuchen der Tabelle. Abfrageplaner wissen das und ignorieren einen Index, wenn der geschätzte Übereinstimmungsanteil zu hoch ist. Als grobe Intuition: Wenn eine typische Abfrage, die den Index verwendet, mehr als ein paar Prozent der Zeilen zurückgeben würde, wird der Index möglicherweise gar nicht verwendet und ist reiner Overhead.

Zusammengesetzte Indizes und die Regel für das linkeste Präfix

Ein Index kann mehrere Spalten in einer bestimmten Reihenfolge abdecken. Zum Beispiel:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Stellen Sie sich das wie eine Sortierung von Einträgen vor, zuerst nach customer_id, dann nach created_at innerhalb jedes Kunden, wie ein Telefonbuch, sortiert nach Nachnamen, dann Vornamen.

Die Idee des linkesten Präfixes folgt direkt aus dieser Sortierreihenfolge. Dieser Index kann effizient bedienen:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND created_at >= '2024-01-01'

Aber er kann WHERE created_at >= '2024-01-01' allein nicht effizient bedienen, da Einträge für einen bestimmten Datumsbereich über alle Kunden verstreut sind; Sie können kein nach Nachnamen sortiertes Telefonbuch verwenden, um alle namens „Anna“ zu finden. Der Index ist nur nutzbar, wenn Ihre Bedingungen ein Präfix seiner Spaltenliste einschränken, beginnend mit der linken Spalte. Das bedeutet, dass (customer_id, created_at) und (created_at, customer_id) unterschiedliche Indizes sind, die unterschiedliche Abfragen bedienen, und die Spaltenreihenfolge sollte Ihren wichtigsten Abfragemustern folgen. Eine gängige Faustregel: Gleichheitsgefilterte Spalten zuerst, dann die Bereichs- oder Sortierspalte.

Wenn ein Index nicht hilft

• Geringe Selektivität: Filtern von WHERE is_active = true in einer Tabelle, in der 90 % der Zeilen aktiv sind. Der Planer wird stattdessen scannen.
• Funktionen oder Ausdrücke in der Spalte: WHERE LOWER(email) = 'x@y.com' kann keinen einfachen Index auf E-Mail verwenden, da der Index Rohwerte und keine transformierten Werte speichert. (Einige Datenbanken unterstützen Ausdrucksindizes, aber der einfache Index wird nicht verwendet.)
• Führende Wildcards: WHERE name LIKE '%son' kann keinen B-Tree verwenden, da die sortierte Reihenfolge nur hilft, wenn das Präfix bekannt ist.
• Überspringen der linken Spalte eines zusammengesetzten Index, wie oben beschrieben.
• Winzige Tabellen: Bei einigen hundert Zeilen ist ein Scan bereits schnell; der Index fügt Schreibkosten ohne Nutzen hinzu.
• Typ-Fehlüberschreibungen oder implizite Typumwandlungen in der indizierten Spalte können ebenfalls die Indexnutzung verhindern.

Zwei kleine Beispiele

Ein nützlicher Index. Angenommen, Ihre Anwendung führt ständig aus:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Dann passt dieser Index perfekt:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Die Datenbank springt zu den Einträgen von Kunde 42, die bereits nach created_at sortiert sind, liest die neuesten 20 und stoppt. Das ist bei Tabellen jeder Größe schnell und bedient dank des linken Präfixes auch einfache Lookups nach customer_id.

Ein problematischer Index. Angenommen, Sie erstellen stattdessen:

CREATE INDEX idx_orders_status ON orders (status);

wo status drei mögliche Werte hat und die meisten Zeilen 'shipped' sind. Eine Abfrage wie SELECT * FROM orders WHERE status = 'shipped' passt auf den größten Teil der Tabelle, sodass der Planer sowieso einen Tabellenscan durchführt. Der Index wird selten oder nie verwendet, aber jeder Insert und jedes Status-Update zahlt dafür, ihn zu pflegen. Das ist ein Nettoverlust. (Eine erwähnenswerte Ausnahme: Das Indizieren einer Spalte mit niedriger Kardinalität kann sich auszahlen, wenn ein Wert selten und stark abgefragt wird, wie die kleine Menge an 'pending'-Bestellungen, aber die generische Version oben ist ein klassischer Fehler.)

Praktische Checkliste vor dem Hinzufügen eines Index

1. Identifizieren Sie zuerst die tatsächlich langsame Abfrage; indizieren Sie nicht spekulativ. Betrachten Sie reale Abfragemuster und verwenden Sie EXPLAIN, um den aktuellen Plan zu sehen.
2. Prüfen Sie die Selektivität: Geben typische Abfragen mit diesem Index einen kleinen Bruchteil der Tabelle zurück? Wenn nicht, überdenken Sie es.
3. Entwerfen Sie für Mehrspaltenfilter und -sortierungen einen einzigen zusammengesetzten Index mit der richtigen Spaltenreihenfolge (Gleichheitsspalten zuerst, dann Bereichs-/Sortierspalten) anstelle mehrerer Einspaltenindizes.
4. Bestätigen Sie die Regel für das linkeste Präfix: Schränkt Ihre häufigste Abfrage die erste Spalte des Index ein?
5. Stellen Sie sicher, dass die Abfrage den Index nicht durch Funktionen, führende Wildcards oder Typumwandlungen in der indizierten Spalte umgeht.
6. Wägen Sie den Schreibverkehr ab: Bei stark geschriebenen Tabellen hat jeder zusätzliche Index erhebliche Kosten. Entfernen Sie Indizes, die sich mit anderen überschneiden oder Präfixe davon sind.
7. Prüfen Sie, ob ein vorhandener Index die Abfrage bereits abdeckt, bevor Sie einen neuen erstellen.
8. Verifizieren Sie nach dem Erstellen des Index mit EXPLAIN, dass der Planer ihn tatsächlich verwendet, und messen Sie die Abfragezeit vor und nachher.
9. Überprüfen und löschen Sie regelmäßig ungenutzte Indizes; sie kosten für immer Schreibvorgänge und Speicherplatz.

Das zentrale mentale Modell, das Sie beibehalten sollten: Ein Index ist eine sortierte Nachschlage-Datenstruktur, für die Sie bei jedem Schreibvorgang bezahlen, damit bestimmte, selektive Lesevorgänge günstig werden. Fügen Sie einen hinzu, wenn Sie die Abfrage benennen können, die er bedient, und zeigen können, dass er hilft.

Lassen Sie uns über Datenbankindizes sprechen. Sie haben SQL-Abfragen geschrieben, und diese funktionieren wahrscheinlich hervorragend auf Ihrem Entwicklungsrechner mit ein paar hundert Zeilen. Aber wenn eine Tabelle Millionen von Zeilen erreicht, können einige dieser Abfragen unglaublich langsam werden. Ein Index ist das gebräuchlichste Werkzeug, das wir zur Lösung dieses Problems verwenden.

Was ist ein Datenbankindex? Eine Analogie

Stellen Sie sich einen Index wie den Index am Ende eines Lehrbuchs vor. Wenn Sie jede Seite finden möchten, auf der „B-Trees“ erwähnt wird, lesen Sie nicht das gesamte Buch von vorne bis hinten. Stattdessen gehen Sie zum Index, suchen den Eintrag für „B-Trees“ und sehen eine sortierte Liste von Seitenzahlen (z. B. 112, 254, 256). Das ist viel schneller.

Ein Datenbankindex funktioniert genauso. Es ist eine separate Datenstruktur, die die Datenbank pflegt. Sie enthält eine Kopie der Daten aus einer oder mehreren Spalten in sortierter Reihenfolge, zusammen mit einem Zeiger zurück zur ursprünglichen Zeile in der Tabelle. Wenn Sie Daten in dieser Spalte abfragen, kann die Datenbank diesen sortierten Index verwenden, um schnell die Zeiger zu den benötigten Zeilen zu finden, anstatt die gesamte Tabelle Zeile für Zeile zu durchsuchen (ein „Full Table Scan“).

Wie es funktioniert: Der B-Tree

Die meisten relationalen Datenbanken verwenden einen B-Tree für ihre Standardindizes. Sie müssen nicht die tiefen Informatikdetails kennen, aber auf einer hohen Ebene ist ein B-Tree eine selbstbalancierende Baumstruktur. Stellen Sie sich eine verzweigte Hierarchie vor. Um einen Wert zu finden, beginnt die Datenbank am Wurzelknoten und folgt einem Pfad nach unten durch die Zweige. Jeder Schritt grenzt die Suche drastisch ein. Dies ermöglicht es der Datenbank, jeden Wert in einem riesigen Datensatz mit sehr wenigen Schritten zu finden, was Suchen unglaublich effizient macht (logarithmische Zeitkomplexität oder O(log n)) im Vergleich zu einem Full Table Scan (lineare Zeitkomplexität oder O(n)).

Die Kompromisse: Schreibgeschwindigkeit und Speicherplatz

Indizes sind nicht kostenlos. Sie haben zwei Hauptkosten:

1. Speicherplatz: Ein Index ist eine physische Datenstruktur, die auf der Festplatte gespeichert wird. Er dupliziert Daten aus Ihrer Tabelle und verbraucht daher zusätzlichen Speicherplatz.
2. Langsamere Schreibvorgänge: Dies ist der wichtigste Kompromiss. Wenn Sie eine Zeile INSERT, UPDATE oder DELETEn, ändert die Datenbank nicht nur die Tabelle. Sie muss auch jeden einzelnen Index aktualisieren, der die betroffenen Spalten enthält, um ihn synchron und sortiert zu halten. Wenn Sie eine Tabelle mit fünf Indizes haben, führt eine INSERT-Anweisung tatsächlich zu sechs Schreibvorgängen: einer für die Tabelle und einer für jeden Index. Dies kann schreibintensive Anwendungen erheblich verlangsamen.

Schlüsselkonzepte für die praktische Indizierung

1. Selektivität
Selektivität bezieht sich darauf, wie viele eindeutige Werte in einer Spalte im Verhältnis zur Gesamtzahl der Zeilen vorhanden sind.

• Hohe Selektivität: Eine Spalte, in der die meisten Werte eindeutig sind, wie user_id oder email_address. Indizes auf diesen Spalten sind sehr effektiv, da sie eine Suche sehr schnell auf nur eine oder wenige Zeilen eingrenzen können.
• Geringe Selektivität: Eine Spalte mit sehr wenigen eindeutigen Werten, wie ein boolesches Flag is_active (wahr/falsch) oder eine status-Spalte („pending“, „active“, „inactive“). Ein Index auf einer Spalte mit geringer Selektivität ist oft nutzlos. Wenn Sie nach allen aktiven Benutzern suchen, sind das möglicherweise 80 % Ihrer Tabelle. Es ist für die Datenbank schneller, die gesamte Tabelle zu lesen, als zwischen einem Index und den Tabellendaten hin und her zu springen.

2. Zusammengesetzte Indizes und die Leftmost-Prefix-Regel
Ein zusammengesetzter Index ist ein Index über zwei oder mehr Spalten, wie CREATE INDEX idx_name ON users (last_name, first_name);.

Die Reihenfolge der Spalten ist entscheidend. Dies ist die Leftmost-Prefix-Regel. Ein Index auf (A, B, C) kann von der Datenbank verwendet werden, um Abfragen zu beschleunigen, die filtern nach:

• A
• A und B
• A, B und C

Er kann jedoch nicht für Abfragen verwendet werden, die nur nach B, C oder B und C filtern. Die Datenbank benötigt die erste(n) Spalte(n) in der Indexdefinition, um ihre Suche zu starten.

SQL-Beispiele

Nehmen wir eine products-Tabelle mit Millionen von Zeilen an.

Gutes Index-Beispiel:
Viele Benutzer suchen nach Produkten anhand eines eindeutigen Produktcodes.

-- Die Abfrage
SELECT name, price FROM products WHERE product_code = 'ABC-12345';

-- Die Lösung: Ein Index auf einer Spalte mit hoher Selektivität
CREATE INDEX idx_products_product_code ON products (product_code);

Dieser Index ist sehr effektiv, da product_code eindeutig ist (hohe Selektivität) und der Index es der Datenbank ermöglicht, die genaue Produktzeile fast sofort zu finden.

Weniger nützliches Index-Beispiel:
Wir möchten alle Produkte finden, die derzeit zum Verkauf angeboten werden.

-- Die Abfrage
SELECT * FROM products WHERE is_available = TRUE;

-- Der problematische Index
CREATE INDEX idx_products_is_available ON products (is_available);

Dieser Index ist nicht sehr nützlich. Die Spalte is_available hat eine geringe Selektivität (nur zwei Werte: wahr und falsch). Wenn 90 % der Produkte verfügbar sind, wird die Datenbank diesen Index wahrscheinlich ignorieren und einen Full Table Scan durchführen, da dies effizienter ist.

Checkliste: Wann ein Index hinzugefügt werden sollte

Bevor Sie einen Index hinzufügen, stellen Sie sich diese Fragen:

1. Ist die Tabelle groß? (Indizes auf kleinen Tabellen haben wenig bis gar keinen Nutzen).
2. Läuft eine bestimmte Abfrage langsam? (Fügen Sie keine Indizes blind hinzu; fügen Sie sie hinzu, um ein bekanntes Leistungsproblem zu beheben).
3. Filtere ich häufig auf dieser Spalte in einer WHERE-Klausel oder verwende ich sie in einem JOIN? (Dies sind die primären Kandidaten für die Indizierung).
4. Hat die Spalte eine hohe Selektivität? (Wird der Index die Anzahl der zu prüfenden Zeilen erheblich reduzieren?).
5. **Verwendet meine Abfrage bei einem zusammengesetzten Index die linkeste(n) Spalte(n) des Indexes?
6. Ist dies eine Lese-intensive Tabelle? (Wenn die Tabelle sehr häufige Schreibvorgänge, aber wenige Lesevorgänge hat, können die Kosten für die Wartung des Indexes die Lese-Vorteile überwiegen).