Indizes in SQL

Wenn du eine neue Zeile in eine Tabelle einfügst, legt das DBMS die Daten nicht in einer optimalen Reihenfolge ab. Wenn du zum Beispiel eine Zeile in die Tabelle Users einfügst, sortiert das DBMS die Zeilen weder nach den Werten in der Spalte id noch alphabetisch nach last_name. Stattdessen legt es die Daten einfach an die nächste freie Stelle in der Datei (das DBMS führt für jede Tabelle eine Liste freier Plätze).

Für eine Abfrage wie:

MySQL 8.1
SELECT email FROM Users WHERE email LIKE 'l%';

muss der Datenbankserver also jede Zeile der Tabelle prüfen, um die Treffer zu finden. Bei kleinen Tabellen ist das in Ordnung, aber sobald die Datenmenge wächst, wird es zu langsam.

Zum Vergleich, wie eine Suche nach email mit und ohne Index abschneidet:

Indizes funktionieren wie das Stichwortverzeichnis in einem Buch 📖 — du findest die gewünschte Information schnell, ohne den ganzen Text lesen zu müssen. Es sind spezielle Tabellen, deren Zeilen anders als bei normalen Datentabellen in einer streng definierten Reihenfolge stehen. Statt aber alle Daten eines Datensatzes zu enthalten, speichert ein Index nur die Spalte (oder Spalten), nach der gesucht wird, plus eine Information, wo die zugehörige Zeile physisch liegt. Indizes helfen also, eine Teilmenge von Zeilen und Spalten zu finden, ohne jede Zeile der Tabelle scannen zu müssen.

Index erstellen ✨

Zurück zur Tabelle Users: du kannst einen Index auf der Spalte email anlegen, um alle Abfragen zu beschleunigen, die mit dieser Spalte arbeiten.

MySQL 8.1
CREATE INDEX idx_email
    ON Users (email);

MySQL 8.1
SHOW INDEX FROM Users;

MySQL 8.1
CREATE INDEX idx_email
    ON Users (email);

MySQL 8.1
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

Index löschen

MySQL 8.1
DROP INDEX idx_email ON Users;

MySQL 8.1
DROP INDEX idx_email;

UNIQUE-Indizes

Beim Design einer Datenbank ist es wichtig zu entscheiden, in welchen Spalten Werte mehrfach vorkommen dürfen und in welchen nicht.

In der Tabelle Users kann es zum Beispiel mehrere Nutzer mit demselben Namen geben, aber ihre IDs und E-Mail-Adressen müssen unterschiedlich sein, damit sie unterscheidbar bleiben.

Eindeutigkeit lässt sich garantieren, indem du einen UNIQUE-Index auf die Spalte Users.email legst. Ein UNIQUE-Index erfüllt zwei Aufgaben:

• er bietet alle Vorteile eines normalen Index
• er verhindert doppelte Werte in der indizierten Spalte

Das DBMS prüft den UNIQUE-Index bei jedem Insert oder Update der indizierten Spalte und stellt sicher, dass der neue Wert noch nicht in der Tabelle existiert.

Einen UNIQUE-Index für die Spalte Users.email legst du so an:

MySQL 8.1
CREATE UNIQUE INDEX idx_email
    ON Users (email);

MySQL 8.1
Error(1062) 23000: "Duplicate entry 'duplicate@gmail.com' for key 'users.idx_email'"

MySQL 8.1
ERROR: duplicate key value violates unique constraint "idx_email"
DETAIL: Key (email)=(duplicate@gmail.com) already exists.

UNIQUE-Indizes für die Primärschlüsselspalte(n) anzulegen ist überflüssig, da das DBMS die Eindeutigkeit von Primärschlüsselwerten automatisch erzwingt. Mehrere UNIQUE-Indizes in einer Tabelle sind aber erlaubt und können sinnvoll sein, wenn du den Bedarf siehst.

Mehrspaltige Indizes

Neben einspaltigen Indizes gibt es auch die Möglichkeit, Indizes über mehrere Spalten zu legen. Um zum Beispiel Studierende nach Vor- und Nachname zu suchen, kannst du einen gemeinsamen Index über diese beiden Felder anlegen:

MySQL 8.1
CREATE INDEX idx_full_name
    ON Student (last_name, first_name);

So ein Index hilft Abfragen, bei denen sowohl Vor- als auch Nachname vorkommen — oder nur der Nachname. Bei Abfragen, die nur den Vornamen filtern, bringt er dagegen nichts. Das ist wie die Suche nach einer Telefonnummer im Telefonbuch: kennst du Vor- und Nachname, hilft dir die Sortierung (erst nach Nachname, dann nach Vorname). Kennst du nur den Vornamen, musst du alle Einträge durchgehen.

Bei mehrspaltigen Indizes ist die Reihenfolge der Spalten wichtig für die Effizienz. Du kannst aber jederzeit zusätzlich weitere Indizes über dieselben Spalten in anderer Reihenfolge anlegen, falls bestimmte Abfragen das brauchen.

Wie Indizes verwendet werden

Indizes werden vom DBMS verwendet, um die gesuchten Zeilen schnell zu finden — und dann ggf. weitere Daten aus verknüpften Tabellen nachzuholen. Nehmen wir als Beispiel diese Abfrage:

MySQL 8.1
SELECT id, first_name, last_name
  FROM Student
  WHERE first_name LIKE 'A%' AND last_name LIKE 'L%'

Auf so eine Abfrage hat das DBMS mehrere Optionen:

• die ganze Tabelle Zeile für Zeile scannen.
• den Index auf last_name nutzen, um Studierende mit Nachnamen, die mit „L“ anfangen, zu finden, und dann jede dieser Zeilen prüfen, ob der Vorname mit „A“ beginnt.
• den zusammengesetzten Index auf last_name und first_name nutzen, um direkt die Studierenden zu finden, die beide Kriterien erfüllen.

Die letzte Variante ist am effizientesten: in einem Durchgang alle passenden Zeilen finden, ohne nochmal zur Tabelle gehen zu müssen.

MySQL 8.1
EXPLAIN
  SELECT id, first_name, last_name
  FROM Student
  WHERE first_name LIKE 'A%'
  AND last_name LIKE 'L%';

MySQL 8.1
EXPLAIN
  SELECT id, first_name, last_name
  FROM Student
  WHERE first_name LIKE 'A%'
  AND last_name LIKE 'L%';

MySQL 8.1
EXPLAIN ANALYZE
  SELECT id, first_name, last_name
  FROM Student
  WHERE first_name LIKE 'A%'
  AND last_name LIKE 'L%';

Die Kehrseite von Indizes

Wenn Indizes so effektiv sind — warum nicht einfach alles indizieren? 🧐

Die Antwort: jeder Index ist selbst eine Tabelle (eine spezielle, aber doch eine Tabelle). Jedes Mal, wenn eine Zeile in die Tabelle eingefügt oder daraus gelöscht wird, müssen alle Indizes dieser Tabelle aktualisiert werden. Bei einem Update werden alle Indizes auf den betroffenen Spalten ebenfalls angepasst. Je mehr Indizes du hast, desto mehr Arbeit muss das DBMS leisten, um alle Schemaobjekte konsistent zu halten — und desto langsamer wird die Schreibarbeit.

Außerdem belegen Indizes zusätzlichen Plattenplatz und brauchen Aufmerksamkeit von der DBA-Seite. Die Faustregel: leg einen Index nur dann an, wenn du ihn wirklich brauchst. Wird er nur temporär benötigt — etwa für einen Monatsreport — leg ihn vor dem Job an und lösche ihn danach wieder.

Am Ende geht es um das Gleichgewicht: genug Indizes, damit alles schnell läuft, aber nicht so viele, dass es die Performance frisst. Wenn du dir bei der richtigen Anzahl unsicher bist, fang mit wenigen an und ergänze nach Bedarf.

Lass uns prüfen, ob du das Thema verstanden hast:

Welche Aussage erklärt am besten, warum du nicht jede Spalte in einer Datenbanktabelle indizieren solltest?