Covering-Index-Strategie: Index-Only-Scans gezielt erzwingen

1. Was ein Covering-Index eigentlich ist

Ein Covering-Index ist ein Index, der alle Spalten enthält, die eine bestimmte Abfrage benötigt, egal ob in der WHERE-Klausel, im SELECT-Teil oder in ORDER BY. Wenn ein Index alle diese Spalten abdeckt, muss der MySQL-Server nach dem Auffinden der passenden Indexeinträge nicht mehr zur eigentlichen Tabelle springen, um zusätzliche Spaltenwerte zu lesen. Diese Technik heißt Index-Only-Scan, weil das gesamte Ergebnis direkt aus der Indexstruktur beantwortet wird.

Der Unterschied zu einem normalen Index ist rein konzeptionell, keine syntaktische Besonderheit. Es gibt kein spezielles CREATE INDEX-Statement für einen Covering-Index, es ist einfach ein Composite-Index, dessen Spaltenauswahl bewusst so getroffen wurde, dass er die gesamte Abfrage abdeckt. Genau diese bewusste Spaltenwahl unterscheidet eine zufällige Indexdefinition von einer gezielten Covering-Index-Strategie.

Der Effekt ist besonders bei InnoDB spürbar, weil InnoDB Tabellen als clustered Index um den Primary Key organisiert. Ein Zugriff auf eine Zeile über einen Sekundärindex bedeutet dort immer einen zusätzlichen Lookup im Primärschlüssel-Baum, sofern nicht bereits alle benötigten Spalten im Sekundärindex selbst vorhanden sind. Ein Covering-Index eliminiert genau diesen zweiten Schritt.

2. Warum InnoDB Tabellenzugriffe teuer macht

InnoDB speichert Tabellendaten nicht in einer separaten Heap-Struktur, sondern direkt im B-Baum des Primary Keys, dem sogenannten clustered Index. Jeder Sekundärindex speichert deshalb nicht die vollständige Zeile, sondern nur die indizierten Spalten plus den Primary-Key-Wert. Wird über einen Sekundärindex eine Zeile gesucht, deren zusätzliche Spalten nicht im Index enthalten sind, muss InnoDB mit dem gefundenen Primary-Key-Wert einen zweiten Baum-Zugriff im clustered Index durchführen, um die restlichen Spalten zu lesen. Dieser zweite Zugriff heißt Bookmark Lookup oder Table Lookup.

Bei sequenziellem Lesen des Sekundärindex, aber wahllosem Zugriff auf die zugehörigen Zeilen im clustered Index, entsteht Random I/O, das auf klassischen Festplatten besonders teuer ist und selbst auf SSDs durch Cache-Misses spürbar bleibt. Bei tausend gefundenen Indexeinträgen bedeutet das im schlimmsten Fall tausend zusätzliche Zufallszugriffe auf den Buffer Pool oder die Festplatte. Ein Covering-Index reduziert das auf null zusätzliche Zugriffe, weil alle benötigten Daten bereits im sequenziell gelesenen Sekundärindex liegen.

-- InnoDB stores the full row inside the clustered index (PRIMARY KEY id)
-- A secondary index only stores its own columns plus the primary key value
SHOW CREATE TABLE orders\G
-- PRIMARY KEY (id)              -- clustered index, holds the full row
-- KEY idx_status (status)       -- secondary index, holds status + id only

-- Lookup via secondary index without covering columns:
-- step 1: find id via idx_status, step 2: bookmark lookup into PRIMARY
EXPLAIN SELECT total_amount FROM orders WHERE status = 2\G
-- key: idx_status, Extra: NULL -- total_amount forces a second tree access

3. Using index in EXPLAIN erkennen

Das eindeutige Signal für einen erfolgreichen Index-Only-Scan in MySQL ist der Wert Using index in der Extra-Spalte von EXPLAIN, ohne zusätzliches Using where mit separatem Table-Lookup. Wichtig ist die Unterscheidung: Using index condition bedeutet, dass eine Filterbedingung bereits im Index geprüft wird, aber danach trotzdem ein Table-Lookup für weitere Spalten stattfindet. Nur das reine Using index ohne diesen Zusatz bestätigt, dass der komplette Zugriff aus dem Index bedient wurde.

In der Praxis lohnt sich der Test mit einer konkreten Abfrage und einem gezielt entworfenen Composite-Index: Man fügt die SELECT-Spalten dem Index als zusätzliche, nicht filternde Spalten am Ende hinzu und prüft danach erneut mit EXPLAIN, ob Using index erscheint. Fehlt auch nur eine benötigte Spalte im Index, fällt der Optimizer auf den klassischen Table-Lookup zurück, und der Vorteil des Covering-Index geht komplett verloren.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- Non-covering index: id and total_amount must be looked up in the table
CREATE INDEX idx_customer_status ON orders (customer_id, status);

EXPLAIN SELECT id, total_amount FROM orders
WHERE customer_id = 4821 AND status = 2\G
-- Extra: Using index condition -- table lookup still required

-- Covering index: total_amount added as a trailing, non-filtering column
CREATE INDEX idx_customer_status_covering
  ON orders (customer_id, status, total_amount);

EXPLAIN SELECT id, total_amount FROM orders
WHERE customer_id = 4821 AND status = 2\G
-- Extra: Using index -- pure index-only scan, no table lookup

4. Einen Covering-Index gezielt entwerfen

Der Entwurf eines Covering-Index folgt zunächst denselben Regeln wie jeder Composite-Index: Equality-Spalten zuerst, dann die eine Range- oder Sortierspalte, nach der Leftmost-Prefix-Regel geordnet. Zusätzlich hängt man am Ende alle weiteren Spalten an, die im SELECT-Teil der Abfrage benötigt werden, aber weder gefiltert noch sortiert werden. Diese angehängten Spalten tragen nicht zur Zugriffsgeschwindigkeit auf den Index selbst bei, sondern verhindern ausschließlich den nachgelagerten Table-Lookup.

Die Reihenfolge der angehängten, nicht filternden Spalten ist im Gegensatz zu den filternden Spalten meist unerheblich, weil sie im B-Baum nur als zusätzliche, unsortiert relevante Nutzlast mitgeführt werden. Wichtig ist nur, dass alle im SELECT verwendeten Spalten irgendwo im Index vorkommen, entweder als filternde Spalte am Anfang oder als angehängte Spalte am Ende.

Ein häufiges Anwendungsfeld ist die Paginierung großer Ergebnislisten: Eine Abfrage, die nur id und created_at für eine Übersichtsseite benötigt, profitiert enorm von einem Covering-Index über genau diese beiden Spalten plus die Filterspalte, während die vollständigen Zeilendaten erst für die angezeigten zwanzig Ergebnisse in einer zweiten, gezielten Abfrage über die IDs nachgeladen werden.

-- Covering index for a paginated customer order overview
CREATE INDEX idx_customer_covering
  ON orders (customer_id, status, created_at, id);

-- Step 1: index-only scan delivers just the ids for the current page
EXPLAIN SELECT id, created_at FROM orders
WHERE customer_id = 4821 AND status = 2
ORDER BY created_at DESC LIMIT 20\G
-- Extra: Using index -- no table lookup for the listing itself

-- Step 2: full row data loaded only for the 20 displayed ids
SELECT id, total_amount, customer_id, status, created_at
FROM orders WHERE id IN (9001, 9002, 9003 /* ... 20 ids */);

5. Der Primary Key als versteckter Bestandteil

Ein oft übersehener Aspekt: Bei InnoDB enthält jeder Sekundärindex implizit den Primary-Key-Wert, selbst wenn er nicht explizit in der Indexdefinition steht. Das bedeutet, eine Abfrage, die nur die Primary-Key-Spalte selbst benötigt, ist automatisch durch jeden Sekundärindex abgedeckt, der die Filterspalten enthält, ganz ohne zusätzliche Spalten anzuhängen. Dieser Umstand macht viele Covering-Index-Designs einfacher als zunächst gedacht.

Bei zusammengesetzten Primary Keys, wie sie in Verknüpfungstabellen häufig vorkommen, wird dieser implizite Bestandteil größer, weil alle Spalten des Primary Key mitgeführt werden. Das kann den Sekundärindex spürbar vergrößern und sollte bei der Wahl zwischen einem kompakten Surrogate Key und einem zusammengesetzten Primary Key mitbedacht werden, insbesondere bei Tabellen mit vielen Sekundärindizes.

-- customer_id is filtered, id is the implicit primary key payload:
-- no extra column needs to be appended to cover a plain id lookup
CREATE INDEX idx_customer_id_only ON orders (customer_id);

EXPLAIN SELECT id FROM orders WHERE customer_id = 4821\G
-- Extra: Using index -- id is already carried by every secondary index

-- Composite primary key in a link table carries both columns everywhere
CREATE TABLE order_items (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (order_id, product_id)
) ENGINE=InnoDB;
-- Every secondary index on order_items implicitly carries (order_id, product_id)

6. Warum SELECT * einen Covering-Index zunichtemacht

Der häufigste Grund, warum ein sorgfältig geplanter Covering-Index in der Praxis nicht wirkt, ist die Verwendung von SELECT * statt einer expliziten Spaltenliste. Sobald auch nur eine Spalte der Tabelle benötigt wird, die nicht im Index enthalten ist, muss der Optimizer auf den vollständigen Table-Lookup zurückgreifen, unabhängig davon, wie viele der übrigen Spalten bereits im Index verfügbar wären. Für die Covering-Index-Strategie ist eine explizite, bewusst kurz gehaltene Spaltenliste deshalb keine Stilfrage, sondern funktional notwendig.

In ORM-lastigen Anwendungen, die standardmäßig alle Spalten eines Modells laden, muss die Anwendung explizit angepasst werden, um von einem Covering-Index zu profitieren, etwa durch projizierte Abfragen für Listenansichten, die nur die tatsächlich angezeigten Spalten laden, während Detailansichten weiterhin die vollständige Zeile über den Primary Key nachladen.

7. Speicherkosten und Schreiblast gegen Nutzen abwägen

Ein Covering-Index ist grundsätzlich größer als ein minimaler Index über nur die Filterspalten, weil zusätzliche Spalten mitgeführt werden. Dieser zusätzliche Speicherplatz muss bei jedem INSERT und UPDATE auf den betroffenen Spalten mitgepflegt werden, was die Schreiblast gegenüber einem schlankeren Index leicht erhöht. Bei Tabellen mit sehr hoher Schreibfrequenz und selten ausgeführten Leseabfragen kann dieser Trade-off gegen einen Covering-Index sprechen.

Für lesehäufige Tabellen, insbesondere solche, die in Übersichtsseiten, APIs oder Reports wiederholt mit denselben Spaltenkombinationen abgefragt werden, überwiegt der Lesevorteil fast immer den moderaten Mehraufwand beim Schreiben. Die Faustregel lautet: Je höher das Verhältnis von Lesezugriffen zu Schreibzugriffen auf eine Tabelle, desto eher lohnt sich ein bewusst breiter angelegter Covering-Index.

-- Compare index size before and after adding covering columns
SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size';

-- idx_customer_status              4.10 MB  (minimal filter index)
-- idx_customer_status_covering    11.80 MB  (with total_amount appended)
-- Roughly 3x larger, but table lookups for this query drop to zero

8. Grenzen der Covering-Index-Strategie

Nicht jede Abfrage lässt sich sinnvoll mit einem Covering-Index abdecken. Bei Abfragen, die viele unterschiedliche, selten wiederkehrende Spaltenkombinationen im SELECT-Teil verwenden, würde ein Covering-Index für jede Variante entweder sehr breit oder es wären mehrere spezialisierte Indizes nötig, was die Schreiblast unverhältnismäßig erhöht. In solchen Fällen ist ein normaler, schlanker Index in Kombination mit einem akzeptierten Table-Lookup oft die pragmatischere Lösung.

Auch bei Textspalten mit TEXT oder BLOB-Typen ist ein Covering-Index meist keine Option, da diese Spaltentypen nur mit Präfixlänge indizierbar sind und selten sinnvoll als angehängte Spalte in einem Index geführt werden können. Für solche Fälle sind andere Strategien wie separate Volltext-Indizes oder ausgelagerte Suchsysteme oft die bessere Wahl.

9. Covering-Index im Vergleich zu anderen Ansätzen

Die folgende Tabelle stellt die Covering-Index-Strategie den gängigen Alternativen gegenüber und zeigt, wann welcher Ansatz sinnvoll ist.

Ein Covering-Index und ein Caching-Layer schließen sich nicht gegenseitig aus. In der Praxis reduziert ein guter Covering-Index bereits die Datenbanklast so stark, dass ein zusätzlicher Cache erst bei noch höherem Traffic notwendig wird, was den Betrieb insgesamt einfacher hält.

10. Zusammenfassung

Ein Covering-Index enthält alle Spalten, die eine Abfrage benötigt, und erlaubt MySQL, das Ergebnis vollständig aus dem Index zu beantworten, ohne die eigentliche Tabelle anzufassen. Bei InnoDB, wo jeder Sekundärindex-Treffer sonst einen zusätzlichen Zugriff auf den clustered Index erfordert, spart diese Strategie besonders viel Random I/O. Das Signal in EXPLAIN heißt Using index, während Using index condition weiterhin einen Table-Lookup bedeutet.

Der Entwurf folgt den Regeln für Composite-Indizes, ergänzt um angehängte, nicht filternde Spalten für alle im SELECT benötigten Werte. SELECT * zunichte macht diese Strategie fast immer, weshalb explizite Spaltenlisten Pflicht sind. Bei lesehäufigen Tabellen mit wiederkehrenden Abfragemustern überwiegt der Lesegewinn fast immer den moderaten Mehraufwand bei Schreiboperationen und zusätzlichem Speicherplatz.

11. FAQ: Covering-Index in MySQL