Indizes

Kurz gesagt: Ein Index ist eine eigene, sortierte Struktur auf einer Spalte, mit der die Datenbank die gesuchten Zeilen findet, ohne die ganze Tabelle zu lesen.

Problem

Zuletzt angesehen

Der Shop ist gewachsen. Jedes Mal, wenn jemand eine Produktseite öffnet, kommt eine Zeile in die Tabelle visits: welcher Kunde (customer_id), welches Produkt (product_id) und wann (seen_at). Diese Tabelle gibt es nur in diesem Modul: Sie hat 5 000 Kunden und 300 000 Besuche.

SQLDatenbank: Shop

SELECT * FROM visits LIMIT 5;

SELECT * FROM visits LIMIT 5;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

SQLDatenbank: Shop

SELECT COUNT(*) FROM visits;

SELECT COUNT(*) FROM visits;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Seite „Zuletzt angesehen“ fragt die Datenbank nach den Besuchen eines Kunden. Führe die Abfrage aus und sieh dir die Zeit unter dem Ergebnis an:

SQLDatenbank: Shop

SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Ein paar Millisekunden wirken wenig. Aber die Seite wird von Tausenden Menschen geöffnet, und die Tabelle wächst jeden Tag.

Die Idee

Die Datenbank liest alles

Die Zeilen in der Tabelle sind nicht nach Kunden sortiert. Um die 60 Besuche von Kunde 1234 zu finden, liest die Datenbank alle 300 000 Zeilen und prüft jede einzelne.

Stell dir vor, du suchst in einem Lehrbuch ohne Register das Wort „Index“. Du müsstest jede Seite umblättern. Darum hat ein Buch hinten ein alphabetisches Register: Die Wörter sind sortiert, und neben jedem stehen die Seiten, auf denen es vorkommt.

Ein Index auf einer Tabelle ist dasselbe. Er ist eine eigene, sortierte Kopie einer Spalte, in der bei jedem Wert vermerkt ist, wo seine Zeile liegt. Weil er sortiert ist, sucht die Datenbank durch Halbieren: Sie schaut in die Mitte, findet heraus, in welcher Hälfte 1234 liegt, schaut dann in die Mitte dieser Hälfte, und so weiter. Für 300 000 Werte reichen etwa 19 Schritte.

Hier siehst du es an einer kleinen Tabelle mit sechs Besuchen, erst ohne Index, dann mit einem:

SELECT * FROM visits WHERE customer_id = 3;
CREATE INDEX idx_visits_customer ON visits(customer_id);
SELECT * FROM visits WHERE customer_id = 3;

Schritt für Schritt, in Worten

1. 6 Besuche. Die Zeilen sind nicht nach customer_id sortiert: 5, 3, 8, 3, 1, 6.
2. Ohne Index macht die Datenbank einen SCAN: Sie liest Zeile für Zeile und vergleicht customer_id mit 3.
3. 6 von 6 Zeilen wurden gelesen, und 2 passen: die Zeilen mit id 2 und 4.
4. CREATE INDEX legt eine sortierte Kopie von customer_id an: 1, 3, 3, 5, 6, 8. Neben jedem Wert steht, welche Zeile es ist.
5. Mit dem Index ist es ein SEARCH. In der Mitte steht 5, mehr als 3: 3 liegt also in der Hälfte mit den kleineren Werten, oberhalb von 5.
6. In der Mitte dieser Hälfte steht 3. Darüber steht 1, das ist weniger: Das ist das erste 3.
7. Neben 3 steht Zeile 2, neben dem nächsten 3 Zeile 4. Dann 5: fertig. Nur 2 Zeilen werden aus der Tabelle gelesen.

Einen Index legst du mit CREATE INDEX name ON tabelle(spalte) an. Führe das Feld zweimal aus und vergleiche die Zeiten:

SQLDatenbank: Shop

CREATE INDEX IF NOT EXISTS idx_visits_customer ON visits(customer_id);
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

CREATE INDEX IF NOT EXISTS idx_visits_customer ON visits(customer_id); SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Beim ersten Lauf wird der Index gebaut, deshalb ist er langsam. Beim zweiten Lauf gibt es den Index schon (IF NOT EXISTS überspringt das Anlegen, wie bei CREATE TABLE), und nur die Suche bleibt übrig. Vergleiche das mit der Zeit der Abfrage ohne Index in der vorigen Karte: Der Unterschied beträgt das Hundertfache.

Genauer, für Neugierige

Indizes in SQLite, PostgreSQL, MySQL und SQL Server sind standardmäßig B-Bäume. Das ist keine Liste, die halbiert wird, sondern ein Baum aus Seiten: Jede Seite enthält Hunderte sortierter Werte und zeigt auf die nächste Ebene. Deshalb genügen auch bei Millionen Zeilen drei oder vier Seitenzugriffe. Die Idee ist dieselbe: sortiert, und jeder Schritt wirft fast alles weg, was nicht gebraucht wird.

Probier es aus

Wie oft ein Produkt angesehen wird

Jede Produktseite soll zeigen, wie oft sie angesehen wurde. Lege einen Index an, der die Suche nach Produkt beschleunigt, und zähle dann die Besuche von Produkt 7.

SQLDatenbank: Shop

SELECT COUNT(*) FROM visits WHERE product_id = 7;

SELECT COUNT(*) FROM visits WHERE product_id = 7;

Prüfen▶ Ausführen

Die Idee

Indizes, die du schon hast

Die Suche nach dem Primärschlüssel ist auch ohne CREATE INDEX schnell:

SQLDatenbank: Shop

SELECT * FROM visits WHERE id = 150000;

SELECT * FROM visits WHERE id = 150000;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

In SQLite ist eine Spalte INTEGER PRIMARY KEY die Reihenfolge der Tabelle selbst: Die Zeilen sind nach id gespeichert. Einen eigenen Index braucht es also nicht.

Für UNIQUE legt die Datenbank von allein einen Index an. Ohne ihn müsste sie bei jeder neuen E-Mail alle Kunden lesen, um zu prüfen, ob es sie schon gibt. SQLite hält die Beschreibung der Datenbank in der Tabelle sqlite_master. Sieh nach, welche Indizes der Shop hat:

SQLDatenbank: Shop

SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der erste gehört zu email in customers. Der zweite zum Primärschlüssel von order_items, der zwei Spalten hat (order_id, product_id) und kein INTEGER PRIMARY KEY ist.

Sieh es dir an

DROP INDEX

Einen Index entfernst du mit DROP INDEX name. Die Daten ändern sich nicht: Nur der Index und die schnelle Suche verschwinden. In diesem Feld ist der Index auf den Kunden schon angelegt. Führe die Abfrage aus und sieh dir die Zeit an:

SQLDatenbank: Shop

DROP INDEX idx_visits_customer;
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

DROP INDEX idx_visits_customer; SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Zeit ist wieder so wie ohne Index. Wenn du das Feld ein zweites Mal ausführst, sagt SQLite, dass es diesen Index nicht gibt. Die Schaltfläche „Neue Datenbank“ bringt den Ausgangszustand zurück.

Übung

Zwei Aufgaben

1. Der Bericht „Besuche pro Tag“ läuft jeden Morgen. Lege einen Index an, der ihn beschleunigt, und zähle damit die Besuche am 1. März 2026.

Ein Index für die Suche nach Datum, und die Zahl der Besuche am 2026-03-01:

SQLDatenbank: Shop

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

Prüfen▶ Ausführen

2. Die Seite mit der Aufrufzahl eines Produkts wurde abgeschaltet. Die Datenbank hat zwei Indizes: idx_visits_customer und idx_visits_product. Entferne den, der nicht mehr gebraucht wird.

Entferne den Index, den niemand nutzt:

SQLDatenbank: Shop

SELECT name FROM sqlite_master WHERE type = 'index';

SELECT name FROM sqlite_master WHERE type = 'index';

Prüfen▶ Ausführen

Test

Teste dich

1. Die Tabelle hat keinen Index auf customer_id. Was tut die Datenbank bei WHERE customer_id = 1234?

Sie liest jede Zeile und prüft jede einzelnSie gibt einen Fehler zurückSie geht direkt zu den richtigen Zeilen

2. Warum ist die Suche in einem Index schnell?

Weil er kleiner ist als die TabelleWeil er im Arbeitsspeicher liegtWeil die Werte sortiert sind und jeder Schritt den Rest halbiert

3. Die Tabelle customers hat email TEXT NOT NULL UNIQUE und id INTEGER PRIMARY KEY. Welche Suche ist ohne neuen Index schnell?

Nur nach idNach id und nach emailNur nach email

4. Was passiert mit den Daten, wenn du DROP INDEX ausführst?

Die Tabelle wird geleertNichts: Nur der Index und die schnelle Suche verschwindenDie Zeilen, auf die der Index zeigt, werden gelöscht

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Primärschlüssel und Fremdschlüssel · CREATE TABLE: eine Tabelle anlegen · Constraints: Regeln für die Daten · ALTER TABLE: eine Tabelle ändern
• Geht weiter in: EXPLAIN: der Abfrageplan · Indizes über mehrere Spalten · Langsame Abfragen · OLTP und OLAP
• Aufgaben zum Thema: Indizes und Geschwindigkeit