Covering-Indizes framework-unabhängig verstehen

1. Wie ein normaler Index arbeitet und warum ein Lookup nötig wird

Ein klassischer sekundärer Index speichert die indizierten Spaltenwerte zusammen mit einem Verweis auf die vollständige Zeile, entweder direkt als physische Zeilenadresse oder, bei einem geclusterten Primärschlüssel, als Wert dieses Primärschlüssels. Findet die Datenbank über den Index passende Einträge, enthält der Index selbst zunächst nur die indizierten Spalten und den Verweis, nicht aber die übrigen von der Abfrage angefragten Spalten.

Werden weitere Spalten benötigt, die nicht Teil des Index sind, muss die Datenbank für jeden gefundenen Eintrag zusätzlich die vollständige Zeile aus der Tabelle nachladen. Dieser zweite Zugriff heißt Key Lookup oder Bookmark Lookup und bedeutet bei einem Index, der auf einer anderen physischen Struktur liegt als die Tabelle selbst, einen weiteren, oft zufälligen I/O-Zugriff pro Treffer.

2. Warum sich Lookup-Kosten bei vielen Treffern deutlich summieren

Bei einer Abfrage mit wenigen Treffern fällt ein zusätzlicher Lookup pro Zeile kaum ins Gewicht, weil die absolute Anzahl zusätzlicher Zugriffe klein bleibt. Bei einer Abfrage, die Tausende oder Millionen Zeilen über den Index findet und für jede davon einen separaten Lookup ausführt, summieren sich diese vielen kleinen, oft zufällig über die Platte verteilten Zugriffe zu einer erheblichen Gesamtlast, die den theoretischen Vorteil des Index teilweise wieder aufzehrt.

Genau an diesem Punkt setzt der Optimizer eine Kostenschätzung an: Ab einer gewissen geschätzten Trefferzahl kann ein voller Tabellen-Scan trotz vorhandenem Index günstiger erscheinen als ein Index-Scan mit vielen Einzel-Lookups, weil ein sequentieller Scan I/O-technisch effizienter ist als viele verstreute Einzelzugriffe. Dieses Verhalten wird oft fälschlich als Beweis dafür gedeutet, dass der Index defekt oder ungeeignet ist, obwohl es eine rationale Kostenentscheidung ist.

3. Das Grundprinzip eines Covering-Index

Ein Covering-Index löst dieses Problem, indem er neben den eigentlichen Filter- und Sortierspalten zusätzlich alle Spalten enthält, die eine bestimmte Abfrage sonst noch benötigt. Findet die Datenbank passende Einträge im Index, stehen alle angefragten Werte bereits im Index selbst zur Verfügung, ein zusätzlicher Zugriff auf die Tabelle entfällt vollständig. Dieses Muster wird als Index-Only-Scan bezeichnet und ist meist deutlich schneller als ein Index-Scan mit anschließenden Einzel-Lookups.

Wichtig dabei ist die Unterscheidung zwischen Schlüsselspalten, die die Sortierreihenfolge des Index bestimmen und für Bereichsfilter oder Sortierung genutzt werden, und zusätzlichen, nicht-schlüsselartigen Spalten, die lediglich mitgeführt werden, um Lookups zu vermeiden, ohne selbst die Sortierreihenfolge zu beeinflussen. Viele Datenbanksysteme bieten dafür eine explizite INCLUDE-Klausel an.

-- Abfrage, die einen Lookup erfordert bei nur teilweise passendem Index
CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4711;
-- Index findet die Zeile, order_date/total_amount müssen
-- per Lookup aus der Tabelle nachgeladen werden

-- Covering-Index: enthält alle benötigten Spalten
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (order_date, total_amount);
-- Dieselbe Abfrage wird jetzt als Index-Only-Scan ausgeführt

4. Praktisches Beispiel: Index-Only-Scan im Ausführungsplan erkennen

Im Ausführungsplan zeigt sich der Unterschied deutlich: Ohne Covering-Index taucht neben dem Index-Zugriff ein separater Schritt für den Tabellenzugriff auf, oft explizit als Key Lookup oder Bookmark Lookup bezeichnet, verbunden über eine Nested-Loop-Verknüpfung mit dem Index-Zugriff. Mit einem passenden Covering-Index entfällt dieser zweite Schritt vollständig, und der Plan zeigt nur noch einen einzigen Zugriff, häufig explizit als Index-Only-Scan oder Covering-Index-Scan ausgewiesen.

Bei der Analyse eines bestehenden, langsamen Ausführungsplans lohnt sich deshalb ein gezielter Blick darauf, ob nach einem Index-Seek oder Index-Scan noch ein zusätzlicher Lookup-Schritt folgt. Ist das der Fall und macht dieser Lookup einen erheblichen Anteil der Gesamtkosten aus, ist die Erweiterung des Index um die fehlenden Spalten meist die naheliegendste und wirksamste Optimierung.

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4711;

-- Ohne Covering-Index (Auszug):
--   -> Index Lookup on orders using idx_orders_customer
--   -> Key Lookup on orders (fetches order_date, total_amount)

-- Mit Covering-Index (Auszug):
--   -> Covering Index Scan on idx_orders_customer_covering
--      (kein zusätzlicher Table-Access-Schritt mehr)

5. Der Trade-off: größere Indizes gegen weniger Lookups

Jede zusätzliche Spalte in einem Covering-Index vergrößert diesen Index physisch, was mehr Speicherplatz auf der Platte und mehr Speicher im Buffer Pool oder Page Cache bindet. Ein größerer Index bedeutet außerdem mehr Daten, die bei jedem Insert, Update oder Delete auf die indizierten oder mitgeführten Spalten zusätzlich geschrieben und im Index-Baum sortiert eingefügt werden müssen, was Schreiblast erhöht.

Dieser Trade-off ist kein pauschales Argument gegen Covering-Indizes, sondern ein Abwägungsproblem, das pro Abfragemuster einzeln bewertet werden sollte: Bei einer sehr häufig ausgeführten, leseintensiven Abfrage mit vielen Treffern rechtfertigt die Lookup-Ersparnis meist den zusätzlichen Speicher- und Schreibaufwand deutlich. Bei einer seltenen Abfrage mit wenigen Treffern lohnt sich der zusätzliche Speicherplatz dagegen oft nicht.

6. INCLUDE-Spalten vs. zusätzliche Schlüsselspalten

Eine mitgeführte Spalte über INCLUDE unterscheidet sich technisch von einer zusätzlichen Schlüsselspalte, weil sie nicht Teil der sortierten Baumstruktur des Index ist, sondern nur an den Blattknoten mitgespeichert wird. Das reduziert den Overhead gegenüber einer vollwertigen zusätzlichen Schlüsselspalte, weil weder die Baumhöhe noch die Sortierlogik durch die zusätzliche Spalte beeinflusst werden, gleichzeitig kann eine INCLUDE-Spalte aber nicht für Bereichsfilter oder Sortierung genutzt werden.

Systeme, die keine explizite INCLUDE-Klausel kennen, erreichen denselben Effekt, indem die benötigten Spalten einfach als zusätzliche, nachrangige Schlüsselspalten in den Index aufgenommen werden. Der Effekt für den Index-Only-Scan ist funktional ähnlich, der Overhead beim Schreiben fällt aber tendenziell etwas höher aus, weil diese Spalten dann auch die Sortierreihenfolge und damit die Struktur des Baums mitbestimmen.

7. Warum SELECT * einen Covering-Index fast immer verhindert

Ein Covering-Index kann nur greifen, wenn die Menge der angefragten Spalten tatsächlich bekannt und begrenzt ist. Eine Abfrage mit SELECT * fordert implizit jede Spalte der Tabelle an, wodurch ein Covering-Index alle Spalten der Tabelle enthalten müsste, um wirksam zu sein, was den ursprünglichen Vorteil gegenüber der Tabelle selbst zunichtemacht. In der Praxis bedeutet SELECT * deshalb fast immer, dass der Optimizer auf einen klassischen Lookup oder gleich auf einen vollständigen Tabellen-Scan ausweicht.

Explizites Auflisten der tatsächlich benötigten Spalten ist deshalb nicht nur allgemein guter Stil, sondern eine direkte Voraussetzung dafür, dass ein Covering-Index überhaupt wirksam werden kann. Diese Disziplin bei der Spaltenauswahl ist gerade bei häufig ausgeführten, performance-kritischen Abfragen ein einfacher, aber oft unterschätzter Hebel.

8. Pflegeaufwand: Covering-Indizes an sich ändernde Abfragen anpassen

Ein Covering-Index ist eng an ein konkretes Abfragemuster gebunden. Ändert sich die Anwendung und fragt eine bislang häufig genutzte Abfrage plötzlich eine zusätzliche Spalte ab, die nicht im Index enthalten ist, fällt der Index-Only-Scan wieder auf einen Lookup zurück, ohne dass dies sofort auffällt, wenn nicht regelmäßig Ausführungspläne überprüft werden. Covering-Indizes brauchen deshalb mehr laufende Pflege als generische Indizes auf einzelnen Filterspalten.

In der Praxis bewährt sich, Covering-Indizes gezielt für eine kleine Zahl besonders häufiger, performance-kritischer Abfragen anzulegen und regelmäßig, etwa nach größeren Anwendungsänderungen, zu überprüfen, ob sie noch greifen. Für seltener genutzte oder sich ständig ändernde Abfragen lohnt sich der Pflegeaufwand eines maßgeschneiderten Covering-Index dagegen selten.

9. Eine praktische Entscheidungsregel für den Einsatz

Ein Covering-Index lohnt sich am ehesten für Abfragen, die häufig ausgeführt werden, eine überschaubare, stabile Menge an Spalten benötigen, und im Ausführungsplan aktuell einen spürbaren Lookup-Anteil an den Gesamtkosten zeigen. Fehlt einer dieser drei Faktoren, insbesondere eine hohe Ausführungshäufigkeit, ist der zusätzliche Speicher- und Pflegeaufwand meist nicht gerechtfertigt.

Vor jeder Entscheidung lohnt sich ein Blick in den aktuellen Ausführungsplan der betroffenen Abfrage mit realen Produktionsdaten, weil erst dort sichtbar wird, wie groß der Lookup-Anteil tatsächlich ist. Eine Optimierung ohne diesen Beleg bleibt spekulativ und kann im schlimmsten Fall Schreiblast erhöhen, ohne den Lesepfad messbar zu beschleunigen.

10. Zusammenfassung

11. FAQ: Covering-Indizes: Das Wichtigste auf einen Blick