Cardinality und Selektivität: warum manche Indizes nutzlos sind

1. Cardinality und Selektivität definiert

Cardinality bezeichnet die Anzahl unterschiedlicher Werte in einer Spalte oder einem Index. Eine email-Spalte in einer Kundentabelle mit hunderttausend Zeilen hat typischerweise eine Cardinality nahe hunderttausend, weil fast jeder Wert einzigartig ist. Eine status-Spalte mit nur fünf möglichen Zuständen hat dagegen eine Cardinality von fünf, unabhängig davon, wie viele Millionen Zeilen die Tabelle enthält.

Selektivität setzt die Cardinality ins Verhältnis zur Gesamtzeilenzahl: Cardinality geteilt durch Zeilenanzahl. Eine Selektivität nahe eins bedeutet, dass fast jeder Wert einzigartig ist, eine Selektivität nahe null bedeutet, dass sich viele Zeilen denselben Wert teilen. Diese Kennzahl ist entscheidend, weil sie direkt vorhersagt, wie viele Zeilen ein Index-Zugriff im Durchschnitt zurückliefert, wenn nach einem bestimmten Wert gefiltert wird.

Der Zusammenhang zwischen beiden Kennzahlen und der Indexnützlichkeit ist einfach, aber wird in der Praxis regelmäßig ignoriert: Ein Index ist nur dann wirksam, wenn er die Ergebnismenge deutlich einschränkt. Ein Index auf eine Spalte mit niedriger Selektivität schränkt kaum ein, verursacht aber trotzdem vollen Wartungsaufwand bei jeder Schreiboperation. Genau diese Fehleinschätzung führt zu Indizes, die zwar existieren, aber praktisch nie einen spürbaren Performancegewinn bringen.

2. Wie der Optimizer Cardinality-Statistiken nutzt

Der MySQL-Optimizer entscheidet bei jeder Abfrage anhand von Kostenschätzungen, ob ein Index genutzt wird oder ein Volltabellenscan günstiger ist. Diese Kostenschätzung basiert maßgeblich auf der geschätzten Cardinality der beteiligten Indizes, die in information_schema.statistics als Spalte CARDINALITY sichtbar ist. Eine hohe Cardinality signalisiert dem Optimizer, dass ein Index-Zugriff im Schnitt nur wenige Zeilen zurückliefert, was die geschätzten Kosten senkt und die Wahrscheinlichkeit erhöht, dass der Index gewählt wird.

Bei niedriger Cardinality schätzt der Optimizer, dass ein Index-Zugriff viele Zeilen pro Wert zurückliefert, oft so viele, dass der zusätzliche Aufwand für den Index-Zugriff plus die anschließenden Table-Lookups teurer wird als ein direkter Volltabellenscan. Genau das erklärt, warum MySQL manchmal einen offensichtlich vorhandenen Index ignoriert: Es ist keine Fehlentscheidung, sondern eine korrekte Kostenkalkulation basierend auf niedriger Selektivität.

SELECT
  index_name,
  column_name,
  cardinality
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

-- index_name          column_name    cardinality
-- PRIMARY              id             1048576
-- idx_status            status              5
-- idx_customer_id        customer_id    98211

3. Die Boolean-Spalten-Falle

Der klassische Fall eines nutzlosen Index ist ein Index auf eine Boolean- oder Flag-Spalte wie is_active, is_deleted oder gender. Bei nur zwei möglichen Werten liegt die Cardinality bei bestenfalls zwei, und selbst bei ungleichmäßiger Verteilung, etwa neunzig Prozent true und zehn Prozent false, liefert eine Abfrage nach dem selteneren Wert immer noch zehn Prozent aller Zeilen zurück. Bei einer Million Zeilen sind das hunderttausend Treffer, für die der Optimizer zurecht einen Volltabellenscan gegenüber dem Index bevorzugen kann.

Das bedeutet nicht, dass ein Index auf eine Boolean-Spalte grundsätzlich sinnlos ist. Als führende Spalte in einem Composite-Index kann eine solche Spalte durchaus sinnvoll sein, wenn die Verteilung stark asymmetrisch ist und die seltenere Ausprägung häufig abgefragt wird, etwa is_deleted = 1 in einer überwiegend aktiven Tabelle. Entscheidend ist immer die tatsächliche Datenverteilung, nicht die theoretische Anzahl möglicher Werte. Eine Spalte mit zwei Werten und einer Neunzig-zu-Zehn-Verteilung kann für die selteneren Zeilen eine brauchbare Selektivität bieten, während eine perfekte Fünfzig-zu-Fünfzig-Verteilung für beide Werte praktisch nutzlos bleibt.

4. Cardinality mit SHOW INDEX auslesen

Der schnellste Weg, die aktuelle Cardinality-Schätzung eines Index zu prüfen, ist SHOW INDEX FROM tabelle. Die Spalte Cardinality zeigt die vom Optimizer verwendete Schätzung pro Index-Spalte. Bei einem Composite-Index steigt die kumulierte Cardinality typischerweise mit jeder zusätzlichen Spalte, weil mehr Spalten zusammen mehr unterschiedliche Kombinationen ergeben, es sei denn, die zusätzlichen Spalten korrelieren stark mit den vorherigen.

Ein praktischer Test für die Sinnhaftigkeit eines geplanten Index: Cardinality durch Gesamtzeilenzahl der Tabelle teilen ergibt die durchschnittliche Selektivität. Werte unter etwa fünf Prozent, also eine Selektivität kleiner als 0,05, sind ein Warnsignal, dass der Index im Schnitt zu viele Zeilen pro Wert zurückliefert, um gegenüber einem Volltabellenscan konsequent im Vorteil zu sein. Diese Faustregel ersetzt keine EXPLAIN-Analyse der konkreten Abfrage, gibt aber eine schnelle erste Einschätzung.

SHOW INDEX FROM orders WHERE Key_name IN ('idx_status', 'idx_customer_id');

-- Key_name        Column_name    Cardinality
-- idx_status       status                  5
-- idx_customer_id  customer_id         98211

-- Table has 1,048,576 rows total
-- Selectivity of idx_status:      5 / 1048576 = 0.0000048 -- essentially useless
-- Selectivity of idx_customer_id: 98211 / 1048576 = 0.0937 -- reasonably useful

5. ANALYZE TABLE: Statistiken aktualisieren

Die Cardinality-Werte in information_schema.statistics sind keine Live-Berechnung, sondern basieren auf Stichproben, die MySQL zu bestimmten Zeitpunkten erstellt. Nach großen Datenänderungen, etwa einem Bulk-Import, einer großflächigen Löschung oder einer Migration, können diese Werte veraltet sein und nicht mehr die tatsächliche Datenverteilung widerspiegeln. Der Befehl ANALYZE TABLE tabelle erzwingt eine Neuberechnung der Statistiken und sollte nach jeder signifikanten strukturellen oder mengenmäßigen Änderung ausgeführt werden.

ANALYZE TABLE ist eine relativ leichtgewichtige Operation, die eine kurze Metadata-Sperre benötigt, aber die Tabelle nicht blockierend umschreibt. Bei sehr großen Tabellen kann der Lesevorgang für die Stichprobe dennoch spürbar I/O erzeugen, weshalb sich die Ausführung außerhalb von Lastspitzen empfiehlt. In automatisierten Deployment-Pipelines lohnt sich ein ANALYZE TABLE-Schritt direkt nach größeren Datenmigrationen, um sicherzustellen, dass der Optimizer ab dem ersten produktiven Query mit aktuellen Zahlen arbeitet.

6. Wie InnoDB Cardinality schätzt: Stichproben statt Vollzählung

InnoDB berechnet Cardinality nicht durch Zählen aller Zeilen, sondern durch statistische Stichproben aus zufällig ausgewählten Index-Seiten, gesteuert über die Systemvariable innodb_stats_persistent_sample_pages, standardmäßig zwanzig Seiten. Diese Stichprobenmethode ist schnell, aber ungenau, besonders bei stark ungleichmäßig verteilten Daten oder bei Tabellen mit wenigen, aber sehr breiten Value-Clustern. Eine höhere Anzahl Stichprobenseiten liefert genauere Schätzungen auf Kosten längerer ANALYZE TABLE-Laufzeiten.

Für Tabellen, bei denen die Optimizer-Entscheidungen wiederholt falsch ausfallen, obwohl die Statistiken aktuell erscheinen, kann eine Erhöhung von innodb_stats_persistent_sample_pages auf hundert oder mehr die Genauigkeit spürbar verbessern. Dieser Parameter lässt sich pro Tabelle über STATS_SAMPLE_PAGES als Tabellenoption individuell setzen, statt global für den gesamten Server zu gelten.

-- Increase sampling accuracy for a specific table with skewed data
ALTER TABLE orders STATS_SAMPLE_PAGES = 100;
ANALYZE TABLE orders;

-- Check the effective sample size in use
SELECT @@innodb_stats_persistent_sample_pages;

-- Verify improved cardinality estimate afterwards
SHOW INDEX FROM orders WHERE Key_name = 'idx_customer_id';

7. Selektivität in Composite-Indizes richtig einordnen

Bei Composite-Indizes ist die relevante Selektivität nicht die der einzelnen Spalte, sondern die kumulierte Selektivität des genutzten Prefix. Ein Composite-Index über (status, customer_id) hat für die erste Spalte allein eine niedrige Selektivität, aber die Kombination aus status und customer_id gemeinsam kann eine sehr hohe Selektivität erreichen, weil die Kombination beider Werte die Ergebnismenge stark einschränkt.

Das bedeutet, eine niedrige Selektivität der führenden Spalte allein disqualifiziert einen Composite-Index nicht automatisch, solange nachfolgende Spalten die Einschränkung deutlich verschärfen. Wichtig bleibt aber, dass die Leftmost-Prefix-Regel weiterhin gilt: Selbst eine exzellente kumulierte Selektivität nützt nichts, wenn die Abfrage die führende Spalte des Composite-Index gar nicht filtert.

-- status alone has low cardinality, but combined with customer_id
-- the composite index becomes highly selective
CREATE INDEX idx_status_customer ON orders (status, customer_id);

SELECT COUNT(DISTINCT status) AS status_cardinality,
       COUNT(DISTINCT CONCAT(status, '-', customer_id)) AS combined_cardinality,
       COUNT(*) AS total_rows
FROM orders;

-- status_cardinality: 5
-- combined_cardinality: 187402   -- cumulative prefix selectivity is high
-- total_rows: 1048576

8. Persistente Statistiken und automatische Aktualisierung

Seit MySQL 5.6 sind persistente Optimizer-Statistiken der Standard, gesteuert über innodb_stats_persistent. Im Gegensatz zu den früheren transienten Statistiken, die bei jedem Serverneustart neu berechnet wurden, bleiben persistente Statistiken über Neustarts hinweg erhalten und werden nur bei expliziten Ereignissen wie ANALYZE TABLE oder größeren automatischen Schwellenwertänderungen aktualisiert. Das sorgt für konsistentere Query-Pläne, erfordert aber eine bewusste Pflege der Statistiken nach großen Datenänderungen.

MySQL aktualisiert Statistiken automatisch, wenn sich mehr als zehn Prozent der Zeilen einer Tabelle seit der letzten Analyse geändert haben, gesteuert über innodb_stats_auto_recalc. Bei Tabellen mit sehr hoher Schreibfrequenz kann dieser automatische Mechanismus jedoch zu selten greifen oder unpassende Zeitpunkte wählen, weshalb explizite ANALYZE TABLE-Aufrufe nach kritischen Batch-Operationen weiterhin empfehlenswert bleiben.

-- Check whether persistent statistics and auto-recalc are enabled
SHOW VARIABLES LIKE 'innodb_stats_persistent%';
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

-- Inspect the last statistics update timestamp per table
SELECT table_name, stat_name, stat_value, last_update
FROM mysql.innodb_table_stats
WHERE database_name = 'shop' AND table_name = 'orders';

9. Selektivität verschiedener Spaltentypen im Vergleich

Die folgende Übersicht zeigt typische Selektivitätswerte für gängige Spaltentypen und ordnet ein, ob ein Index darauf sinnvoll ist.

Diese Tabelle ersetzt keine konkrete Messung mit SHOW INDEX für die eigene Datenbank, zeigt aber das grundsätzliche Muster: Je gleichmäßiger die Verteilung über wenige Werte, desto geringer die Selektivität, und desto unwahrscheinlicher ist ein spürbarer Nutzen aus einem eigenständigen Index auf genau diese Spalte.

10. Zusammenfassung

Cardinality beschreibt die Anzahl unterschiedlicher Werte in einer Spalte, Selektivität ihr Verhältnis zur Zeilenanzahl. Beide Kennzahlen bestimmen gemeinsam, wie der Optimizer die Kosten eines Index-Zugriffs im Vergleich zu einem Volltabellenscan bewertet. Niedrige Selektivität, wie sie bei Boolean-Spalten oder Statusfeldern mit wenigen Werten typisch ist, macht einen eigenständigen Index häufig wirkungslos, selbst bei Millionen Zeilen in der Tabelle.

SHOW INDEX zeigt die aktuelle Cardinality-Schätzung, ANALYZE TABLE aktualisiert veraltete Statistiken nach größeren Datenänderungen. Bei Composite-Indizes zählt die kumulierte Selektivität des genutzten Prefix, nicht die Selektivität einer einzelnen Spalte allein. Wer diese Kennzahlen regelmäßig prüft, vermeidet sowohl nutzlose Indizes mit unnötiger Schreiblast als auch fehlende Indizes an den Stellen, wo hohe Selektivität tatsächlich einen Unterschied macht.

11. FAQ: Cardinality und Selektivität in MySQL