Indizes über mehrere Spalten

Kurz gesagt: Ein zusammengesetzter Index ist ein Index auf mehreren Spalten, der hilft, wenn die Abfrage seine Spalten von links nach rechts verwendet.

Problem

Die letzten zehn

Die Seite „Zuletzt angesehen“ zeigt die letzten 10 Besuche des Kunden, beginnend mit dem neuesten. Der Index auf customer_id ist schon angelegt. Sieh dir den Plan an:

SQLDatenbank: Shop

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

EXPLAIN QUERY PLAN SELECT seen_at, product_id FROM visits WHERE customer_id = 1234 ORDER BY seen_at DESC LIMIT 10;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Index findet die 60 Besuche, liefert sie aber nicht nach Datum sortiert. Die Datenbank sortiert sie selbst (USE TEMP B-TREE). Bei einem Kunden mit Tausenden Besuchen ist das der langsamste Teil, und gebraucht werden nur 10 davon.

Die Idee

Zwei Spalten in einem Index

Ein Index kann mehrere Spalten umfassen: CREATE INDEX name ON tabelle(spalte1, spalte2). Das ist ein zusammengesetzter Index. Er ist sortiert wie ein Telefonbuch: zuerst nach Nachnamen, und bei gleichem Nachnamen nach Vornamen.

Der Index auf (customer_id, seen_at) ist zuerst nach Kunden sortiert, und die Besuche eines Kunden sind nach Datum sortiert. Die Datenbank findet den Kunden und liest seine Daten vom Ende rückwärts. Sortieren ist nicht nötig:

SQLDatenbank: Shop

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

EXPLAIN QUERY PLAN SELECT seen_at, product_id FROM visits WHERE customer_id = 1234 ORDER BY seen_at DESC LIMIT 10;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Derselbe Index hilft auch, wenn die Bedingung beide Spalten betrifft. Die Datenbank geht zum Kunden und sucht in dessen Bereich ab dem Datum:

SQLDatenbank: Shop

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM visits WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Und bei der ersten Spalte allein, als wäre es ein Index nur auf customer_id:

SQLDatenbank: Shop

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

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

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Probier es aus

Besuche in einem Zeitraum

Der Kundendienst fragt oft, wie oft ein Kunde ab einem bestimmten Datum Produkte angesehen hat. Lege einen Index genau auf diese beiden Spalten an, damit die Abfrage sowohl beim Kunden als auch beim Datum ein SEARCH ist.

SQLDatenbank: Shop

SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

SELECT COUNT(*) FROM visits WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

Prüfen▶ Ausführen

Sieh es dir an

Von links nach rechts

Mit demselben Index fragt der Bericht „pro Tag“ nur nach dem Datum:

SQLDatenbank: Shop

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

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

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Plan ist ein SCAN. Er sagt USING COVERING INDEX, weil COUNT(*) aus dem Index berechnet wird, aber die Datenbank liest trotzdem alle 300 000 Einträge darin. Führe die Abfrage selbst aus und sieh dir die Zeit an:

SQLDatenbank: Shop

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

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

▶ AusführenAbfrage zurücksetzenNeue Datenbank

In einem Telefonbuch findest du nicht alle, die „Maria“ heißen, ohne das ganze Buch zu lesen. Die Vornamen sind nur innerhalb jedes Nachnamens sortiert.

Das ist die Regel von links nach rechts: Ein Index auf (a, b) hilft bei Bedingungen auf a, auf a und b und bei ORDER BY b, wenn a mit = vorgegeben ist. Bei einer Bedingung nur auf b hilft er nicht. Wenn du unsicher bist, prüfe es mit EXPLAIN QUERY PLAN.

Die Idee

Was ein Index kostet

Indizes sind nicht umsonst. Jede neue Zeile muss in jedem Index der Tabelle an ihren Platz geschrieben werden. Die beiden Felder unten fügen je 100 000 Besuche hinzu. Das erste hat keine Indizes, das zweite vier. Führe sie aus und vergleiche die Zeiten:

SQLDatenbank: Shop

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

INSERT INTO visits (customer_id, product_id, seen_at) SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000; SELECT COUNT(*) FROM visits;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

SQLDatenbank: Shop

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

INSERT INTO visits (customer_id, product_id, seen_at) SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000; SELECT COUNT(*) FROM visits;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Dasselbe gilt für ein UPDATE einer indizierten Spalte und für DELETE.

Indizes belegen auch Platz. SQLite speichert die Datenbank in Seiten zu 4 096 Byte, und pragma_page_count() sagt, wie viele Seiten es gibt. Hier ist die Datenbank ohne Indizes und mit den vier:

SQLDatenbank: Shop

SELECT page_count FROM pragma_page_count();

SELECT page_count FROM pragma_page_count();

▶ AusführenAbfrage zurücksetzenNeue Datenbank

SQLDatenbank: Shop

SELECT page_count FROM pragma_page_count();

SELECT page_count FROM pragma_page_count();

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Mit den vier Indizes ist die Datenbank mehr als dreimal so groß. Darum legst du Indizes für die Abfragen an, die du wirklich hast, und nicht für jede Spalte „zur Sicherheit“.

Genauer, für Neugierige

Der zweite der vier Indizes, (customer_id, seen_at), macht den ersten, (customer_id), überflüssig: Nach der Regel von links nach rechts erledigt er auch dessen Aufgabe. Solche Paare sind häufig und fallen selten auf. PostgreSQL zählt mit, wie oft jeder Index benutzt wurde (pg_stat_user_indexes), sodass sich die ungenutzten finden lassen.

Übung

Zwei Aufgaben

1. Die Datenbank hat einen Index auf (customer_id) und einen auf (customer_id, seen_at). Entferne den überflüssigen.

Entferne den Index, dessen Aufgabe der andere miterledigt:

SQLDatenbank: Shop

SELECT name FROM sqlite_master WHERE type = 'index';

SELECT name FROM sqlite_master WHERE type = 'index';

Prüfen▶ Ausführen

2. Der Index auf (customer_id, seen_at) ist angelegt. Zeige die Daten der letzten 3 Besuche von Kunde 1234, beginnend mit dem neuesten.

Die letzten 3 Besuchsdaten von Kunde 1234:

SQLDatenbank: Shop

SELECT seen_at FROM visits
WHERE customer_id = 1234;

SELECT seen_at FROM visits WHERE customer_id = 1234;

Prüfen▶ Ausführen

Test

Teste dich

1. Es gibt einen Index auf (customer_id, seen_at). Welche Bedingung kann ihn NICHT für eine Suche nutzen?

WHERE customer_id = 7WHERE customer_id = 7 AND seen_at >= '2026-01-01'WHERE seen_at = '2026-03-01'

2. Es gibt einen Index auf (customer_id, seen_at). Brauchst du zusätzlich einen eigenen Index nur auf customer_id?

Nein: Eine Suche nach der ersten Spalte nutzt den Index auf beidenJa, sonst ist die Suche nach Kunden langsamNur wenn die Tabelle groß ist

3. Warum bremsen Indizes INSERT?

Weil jede neue Zeile in jedem Index der Tabelle an ihren Platz geschrieben wirdWeil die Datenbank ihre Syntax prüftSie bremsen nicht

4. Wann ist ein Index auf einer Spalte eine gute Idee?

Wenn Abfragen oft danach suchen, verbinden oder sortierenNur für den PrimärschlüsselImmer, für jede Spalte

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Aggregatfunktionen: COUNT, SUM, AVG · INSERT: Zeilen einfügen · Indizes · EXPLAIN: der Abfrageplan
• Geht weiter in: Langsame Abfragen
• Aufgaben zum Thema: Indizes und Geschwindigkeit