Antwort

Indizes sind spezielle Datenstrukturen (meist basierend auf B-Bäumen), die zur schnellen Suche von Daten nach einer bestimmten Spalte oder einer Gruppe von Spalten in einer Tabelle dienen. Indizes beschleunigen die Abfrage, Sortierung und Filterung, indem sie die Anzahl der durchsuchten Zeilen reduzieren.

Typen von Indizes:

• Übliche (B-tree, hash)
• Zusammengesetzte (mehrspaltige)
• Einzigartige (sichern die Eindeutigkeit der Werte)
• Abdeckende (covering)
• Volltext (full-text)

Indizes beschleunigen:

• WHERE ... = ...
• JOIN nach der indizierten Spalte
• ORDER BY und GROUP BY nach der indizierten Spalte

Können verlangsamen:

• Einfügen, Aktualisieren, Löschen — Indizes erfordern zusätzliche Operationen zur Aufrechterhaltung ihrer Struktur.
• Wenn Abfragen häufig Spalten verwenden, für die es keine Indizes gibt, sind die Indizes nutzlos, und bei massiven Einfügungen können sie sogar schädlich sein.

Beispiel zur Erstellung eines Index:

CREATE INDEX idx_user_email ON users (email);

Beispiel, wo der Index nicht hilft:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- Wenn der Index auf email gebaut ist, aber die Abfrage die Funktion lower(email) verwendet, wird der Index nicht verwendet!

Fangfrage

Wenn man einen Index auf allen Spalten der Tabelle hinzufügt, beschleunigt das immer die Ausführung aller SELECT-Abfragen?

Antwort:
Nein. Indizes beschleunigen nur die Abfragen, bei denen die Filterung oder Sortierung strikt nach der indizierten Spalte ohne Anwendung von Funktionen oder Operationen erfolgt, die die Nutzung des Indexes verhindern. Eine übermäßige Anzahl von Indizes verlangsamt nicht nur INSERT/UPDATE/DELETE, sondern nimmt auch viel Platz ein, und einige komplexe Abfragen können die Indizes sogar ganz umgehen (zum Beispiel bei Bereichsscans mit einem Ausdruck).

Beispiel:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- Wenn der Index nur auf order_date ist, funktioniert der Index nicht wegen der Funktion year().

---

Geschichte

In einem E-Mail-System wurde ein Trigger eingerichtet, der für jedes häufig verwendete Feld einen Index erzeugte. Nach sechs Monaten sank die Leistung des Systems — für jede Einfügung oder Änderung einer Zeile wurde 4-5 Mal mehr Zeit benötigt. Nach einem Audit wurde die Anzahl der Indizes reduziert, und das System wurde schneller.

---

Geschichte

In einer Werbeplattform gab es häufig SELECT-Abfragen mit einem Filter für substring(url, 1, 10). Trotz des Indexes auf URL wurde der Index aufgrund der Funktion substring nicht verwendet. Die Lösung bestand darin, ein separates Feld für diese Abfrage und einen Index darauf einzuführen.

---

Geschichte

In einem Loyalitätsprogramm wurde ein zusammengesetzter Index auf die Felder (customer_id, shop_id) erstellt. Bei Abfragen nur nach shop_id wurde der Index nicht angewendet, und es fand ein vollständiger Tabellenscan (full scan) statt. Dies führte zu einem Verlust des Vorteils des Indexes bei der Berechnung von Boni. Eine Optimierung half: ein separater Index auf shop_id.