Composite-Indizes richtig entwerfen: Spaltenreihenfolge zählt

1. Was ein Composite-Index wirklich ist

Ein Composite-Index ist ein einzelner B-Baum-Index über mehrere Spalten einer Tabelle, sortiert zuerst nach der ersten Spalte, dann nach der zweiten, dann nach der dritten und so weiter. Er ist keine Sammlung mehrerer Einzelindizes, sondern eine einzige, zusammenhängende Sortierstruktur. Genau diese Eigenschaft macht die Spaltenreihenfolge zur wichtigsten Entscheidung im gesamten Index-Design, denn sie bestimmt, welche Abfragen den Index überhaupt nutzen können.

In der Praxis sieht man häufig Composite-Indizes, die aus den richtigen Spalten bestehen, aber in einer Reihenfolge, die für die tatsächlichen Abfragemuster nutzlos ist. Ein Index über (status, created_at, customer_id) hilft einer Abfrage, die nach customer_id filtert und nach created_at sortiert, praktisch nicht, obwohl alle drei Spalten enthalten sind. Der B-Baum ist eben nach status zuerst sortiert, und der Optimizer kann die restliche Struktur nicht ohne einen vollständigen Scan der relevanten status-Gruppen nutzen.

Wer einen Composite-Index entwirft, muss deshalb immer von der Abfrage aus denken, nicht von der Tabelle aus. Die Frage lautet nicht "welche Spalten werden oft gefiltert", sondern "in welcher Reihenfolge werden sie gemeinsam in WHERE, JOIN und ORDER BY verwendet". Diese Denkweise unterscheidet einen funktionierenden Composite-Index von einem, der zwar existiert, aber vom Optimizer bei fast jeder Abfrage ignoriert wird.

2. Die Leftmost-Prefix-Regel verstehen

Die Leftmost-Prefix-Regel besagt, dass MySQL einen Composite-Index nur dann effizient nutzen kann, wenn die Abfrage die Spalten von links beginnend verwendet, ohne eine Spalte auszulassen. Bei einem Index über (a, b, c) kann der Optimizer Abfragen auf a, auf a, b oder auf a, b, c effizient bedienen. Eine Abfrage, die nur nach b oder nur nach c filtert, kann diesen Index dagegen nicht für einen Range-Scan nutzen, weil der B-Baum ohne bekannten Wert für a keinen Einstiegspunkt findet.

Diese Regel ist der Grund, warum ein einzelner gut geplanter Composite-Index oft mehrere Einzelindizes ersetzen kann, während ein schlecht geplanter Composite-Index selbst bei drei enthaltenen Spalten nur wie ein Index auf die erste Spalte wirkt. Der praktische Test ist einfach: Man schreibt die tatsächlich verwendeten WHERE-Bedingungen auf, sortiert sie nach der Häufigkeit von Equality-Filtern und prüft, ob die Spaltenreihenfolge des Composite-Index diesem Muster entspricht.

-- Composite index over three columns, ordered (a, b, c)
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status TINYINT NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_customer_status_created (customer_id, status, created_at)
) ENGINE=InnoDB;

-- Uses the leftmost prefix (customer_id) -- index range scan
EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;

-- Uses the full prefix (customer_id, status) -- index range scan
EXPLAIN SELECT * FROM orders WHERE customer_id = 4821 AND status = 2;

-- Cannot use the index for a range scan: status is not the leftmost column
EXPLAIN SELECT * FROM orders WHERE status = 2;
-- type: ALL, key: NULL -- full table scan, leftmost prefix rule violated

3. Spaltenreihenfolge: Equality vor Range

Die zweite zentrale Faustregel beim Composite-Index-Design: Spalten mit Equality-Filtern (=) gehören vor Spalten mit Range-Filtern (>, <, BETWEEN, LIKE 'prefix%'). Der Grund liegt in der Funktionsweise des B-Baums. Solange nur Equality-Bedingungen geprüft werden, bleibt der Baum an jeder Ebene auf einen exakten Wert eingeschränkt, und die nächste Spalte bleibt vollständig sortiert nutzbar. Sobald eine Range-Bedingung greift, wird der verbleibende Teilbaum zu einem Bereich, innerhalb dessen die nächste Spalte nicht mehr zusätzlich sortiert genutzt werden kann.

Ein Composite-Index über (customer_id, created_at, status) nutzt bei der Abfrage customer_id = 4821 AND created_at > '2026-01-01' AND status = 2 nur die ersten beiden Spalten effizient als Index-Zugriff. Der Filter auf status muss danach für jede Zeile im gefundenen Bereich einzeln geprüft werden, weil der Bereich auf created_at die Sortierung nach status aufhebt. Tauscht man die Reihenfolge zu (customer_id, status, created_at), wird sowohl customer_id als auch status als Equality-Zugriff genutzt und nur created_at bleibt als Range übrig, was in der Regel deutlich weniger Zeilen zum Nachprüfen liefert.

Diese Regel gilt nicht absolut. Wenn eine Range-Spalte extrem selektiv ist und die nachfolgende Equality-Spalte kaum Werte unterscheidet, kann die Reihenfolge in Einzelfällen umgekehrt sinnvoll sein. Als Ausgangspunkt für neunzig Prozent der Fälle bleibt "Equality vor Range" aber die verlässlichste Regel für einen funktionierenden Composite-Index.

-- Range column placed before the equality column: only the first column
-- of the index is used for narrowing, status is checked row by row
CREATE INDEX idx_created_status ON orders (created_at, status);

EXPLAIN SELECT id FROM orders
WHERE created_at > '2026-01-01' AND status = 2\G
-- key_len: 5 (only created_at contributes to the index range)
-- rows: 48213 -- most rows still have to be filtered after the range scan

-- Equality column first, range column last: both conditions narrow the scan
CREATE INDEX idx_status_created ON orders (status, created_at);

EXPLAIN SELECT id FROM orders
WHERE created_at > '2026-01-01' AND status = 2\G
-- key_len: 6 (status and created_at both contribute)
-- rows: 1840 -- status already narrows the range before created_at applies

4. Selektivität bei der Spaltenwahl berücksichtigen

Neben der Reihenfolge nach Equality und Range spielt auch die Selektivität der einzelnen Spalten eine Rolle. Eine Spalte mit hoher Selektivität, also vielen unterschiedlichen Werten im Verhältnis zur Zeilenanzahl, grenzt bei gleicher Position im Composite-Index den Suchraum stärker ein als eine Spalte mit wenigen unterschiedlichen Werten. Eine customer_id-Spalte mit zehntausenden unterschiedlichen Werten ist selektiver als eine status-Spalte mit fünf möglichen Zuständen, selbst wenn beide als Equality-Filter verwendet werden.

Bei zwei gleichwertigen Equality-Spalten sollte deshalb tendenziell die selektivere Spalte weiter links im Composite-Index stehen, weil sie den B-Baum-Bereich schneller auf wenige Zeilen reduziert. Diese Faustregel ist jedoch der Leftmost-Prefix-Regel und der Equality-vor-Range-Regel untergeordnet: Erst muss der Composite-Index zum tatsächlichen Abfragemuster passen, dann optimiert man innerhalb der Equality-Spalten nach Selektivität. Ein detaillierter Blick auf Kardinalität und ANALYZE TABLE folgt in einem eigenen Artikel zu diesem Thema.

5. Composite-Index versus mehrere Einzelindizes

Ein häufiger Irrglaube ist, dass mehrere Einzelindizes auf einzelnen Spalten dieselbe Wirkung erzielen wie ein gut geplanter Composite-Index. MySQL kann zwar mit der Index-Merge-Optimierung mehrere Einzelindizes für eine Abfrage kombinieren, aber diese Strategie ist fast immer langsamer als ein einziger passender Composite-Index, weil zwei separate Baum-Traversierungen und eine anschließende Schnittmengenbildung im Speicher notwendig sind. Ein Composite-Index liefert das Ergebnis dagegen direkt aus einer einzigen sortierten Struktur.

Der Vorteil von Einzelindizes liegt in der Flexibilität: Sie unterstützen jede Abfrage, die genau diese eine Spalte filtert, unabhängig von anderen Bedingungen. Ein Composite-Index ist dagegen zielgerichteter, aber durch die Leftmost-Prefix-Regel auch enger gefasst. In der Praxis überwiegt für Kernabfragen mit stabilen Filterkombinationen fast immer der Composite-Index, während seltene, unvorhersehbare Ad-hoc-Filter eher für Einzelindizes sprechen.

-- Two single-column indexes: requires an index merge
CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_status ON orders (status);

EXPLAIN SELECT id FROM orders WHERE customer_id = 4821 AND status = 2\G
-- type: index_merge
-- Extra: Using intersect(idx_customer_id,idx_status); Using where

-- One composite index: single sorted structure, no intersection needed
DROP INDEX idx_customer_id ON orders;
DROP INDEX idx_status ON orders;
CREATE INDEX idx_customer_status ON orders (customer_id, status);

EXPLAIN SELECT id FROM orders WHERE customer_id = 4821 AND status = 2\G
-- type: ref
-- Extra: Using index -- direct, single index access, no merge step

6. Mit EXPLAIN die Indexnutzung prüfen

Kein Composite-Index-Design ist vollständig, ohne die tatsächliche Nutzung mit EXPLAIN zu verifizieren. Die Spalte key zeigt, welcher Index tatsächlich gewählt wurde, key_len zeigt, wie viele Bytes des Index tatsächlich für den Zugriff verwendet wurden. Ein kürzerer key_len als die volle Indexlänge deutet oft darauf hin, dass nicht alle Spalten des Composite-Index tatsächlich zum Einsatz kamen, meist weil eine Range-Bedingung frühzeitig die Nutzung der nächsten Spalte beendet hat.

Die Spalte ref zeigt, ob ein konstanter Wert, eine andere Spalte oder ein Funktionsergebnis für den Zugriff verwendet wurde, während rows die geschätzte Anzahl untersuchter Zeilen angibt. Ein Composite-Index, der die Zeilenzahl von Millionen auf wenige hundert reduziert, ist wirksam. Bleibt rows trotz Composite-Index hoch, stimmt entweder die Spaltenreihenfolge nicht mit dem Abfragemuster überein, oder die Selektivität der führenden Spalten reicht nicht aus.

EXPLAIN SELECT id, total_amount FROM orders
WHERE customer_id = 4821 AND status = 2
ORDER BY created_at DESC LIMIT 20\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ref
possible_keys: idx_customer_status_created
          key: idx_customer_status_created
      key_len: 7
          ref: const,const
         rows: 34
        Extra: Using index condition; Backward index scan

7. Praktischer Entwurf: ein Composite-Index Schritt für Schritt

Der praktische Entwurf eines Composite-Index folgt einem festen Ablauf. Zuerst sammelt man die tatsächlich ausgeführten Abfragen aus dem Slow Query Log oder Performance Schema für die betroffene Tabelle. Danach identifiziert man die Equality-Filter, die Range-Filter und die ORDER BY Spalte pro Abfrage. Equality-Spalten kommen zuerst in den Index, in absteigender Selektivität sortiert, danach die einzige Range- oder Sortierspalte.

Im dritten Schritt prüft man, ob dieselbe Spaltenkombination von mehreren häufigen Abfragen geteilt wird, denn ein Composite-Index deckt automatisch auch alle Leftmost-Prefixe ab. Ein Index über (customer_id, status, created_at) bedient dann sowohl die Abfrage mit allen drei Bedingungen als auch die einfachere Abfrage mit nur customer_id, ohne einen zweiten Index zu benötigen. Abschließend wird der Composite-Index mit EXPLAIN gegen die realen Abfragen getestet und mit ANALYZE TABLE sichergestellt, dass die Statistiken aktuell sind.

Ein letzter praktischer Schritt betrifft überflüssige Indizes: Nach dem Anlegen eines neuen Composite-Index sollte man mit sys.schema_redundant_indexes prüfen, ob bestehende Einzelindizes durch den neuen Composite-Index redundant geworden sind, und diese entfernen, um Schreiblast und Speicherverbrauch zu reduzieren.

-- Step 1: inspect existing redundant indexes after adding a composite index
SELECT table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema = 'shop';

-- Step 2: drop an index made redundant by the new composite index
ALTER TABLE orders DROP INDEX idx_customer_id;

-- Step 3: refresh optimizer statistics after structural changes
ANALYZE TABLE orders;

8. Häufige Fehler beim Composite-Index-Design

Der häufigste Fehler ist, einen Composite-Index nach der Reihenfolge im CREATE TABLE-Statement anzulegen statt nach dem Abfragemuster. Die physische Spaltenreihenfolge der Tabelle hat keinerlei Einfluss auf die sinnvolle Indexreihenfolge. Ein zweiter häufiger Fehler ist das Anlegen von zu vielen, sich überlappenden Composite-Indizes "auf Verdacht", die zwar keine Abfrage beschleunigen, aber jede INSERT- und UPDATE-Operation zusätzlich verlangsamen, weil jeder Index bei jeder Schreiboperation mitgepflegt werden muss.

Ein dritter Fehler betrifft Funktionen auf indizierten Spalten: WHERE DATE(created_at) = '2026-07-23' verhindert die Nutzung eines Composite-Index über created_at, weil der gespeicherte Wert erst berechnet werden muss, bevor der Vergleich stattfindet. Die Lösung ist ein Bereichsvergleich created_at >= '2026-07-23' AND created_at < '2026-07-24', der den Index unverändert nutzen kann, oder ab MySQL 5.7 ein Functional Index auf den berechneten Ausdruck.

9. Composite-Index-Strategien im Vergleich

Die folgende Übersicht fasst zusammen, wie unterschiedliche Ansätze beim Composite-Index-Design abschneiden, gemessen an Trefferquote im Optimizer und Wartungsaufwand für Schreiboperationen.

Der Vergleich zeigt, dass es keine universell "richtige" Spaltenmenge gibt, solange die Reihenfolge nicht stimmt. Zwei Composite-Indizes mit identischen Spalten, aber unterschiedlicher Reihenfolge, verhalten sich für den Optimizer wie zwei völlig verschiedene Indizes. Deshalb lohnt sich vor jedem CREATE INDEX ein kurzer Blick auf die realen WHERE-Klauseln der wichtigsten Abfragen.

10. Zusammenfassung

Ein Composite-Index ist ein einziger, zusammenhängender B-Baum über mehrere Spalten, und seine Nützlichkeit hängt fast ausschließlich von der Spaltenreihenfolge ab. Die Leftmost-Prefix-Regel bestimmt, welche Teilmenge der Spalten überhaupt für einen Index-Zugriff genutzt werden kann. Equality-Filter gehören vor Range-Filter, weil sie den B-Baum-Bereich für nachfolgende Spalten nicht aufweiten. Bei mehreren Equality-Spalten gewinnt in der Regel die selektivste Spalte den vorderen Platz.

Der praktische Entwurf beginnt immer bei den tatsächlichen Abfragen, nicht bei der Tabellenstruktur, und wird mit EXPLAIN anhand von key_len und rows verifiziert. Ein gut geplanter Composite-Index ersetzt oft mehrere Einzelindizes und deckt dank der Leftmost-Prefix-Eigenschaft automatisch auch einfachere Abfragen ab. Wer diese Regeln konsequent anwendet, bekommt aus jedem neuen Composite-Index messbar mehr Wirkung pro Byte Speicherplatz.

11. FAQ: Composite-Indizes in MySQL