Indizes richtig bauen: Selectivity, Cardinality, Composite Indexes

1. Index Selectivity: Was sie bedeutet und wie man sie misst

Die Index Selectivity ist der fundamentale Maßstab dafür, ob ein MySQL Index für eine bestimmte Spalte sinnvoll ist. Die Formel ist einfach: Selectivity = DISTINCT_WERTE / GESAMT_ZEILEN. Ein Wert nahe 1,0 bedeutet hohe Selectivity – fast jeder Wert ist einzigartig, der Index filtert sehr stark. Ein Wert nahe 0 bedeutet niedrige Selectivity – der Index ist für die meisten Abfragen wertlos, weil MySQL trotzdem fast alle Zeilen lesen muss.

Typische Beispiele: Eine Spalte email hat fast immer eine Selectivity nahe 1 (ideal für Indizes). Eine Spalte status mit drei möglichen Werten auf einer Million Zeilen hat eine Selectivity von 0,000003 – ein alleiniger Index darauf ist fast nutzlos. Der MySQL Query Optimizer berücksichtigt diese Selectivity bei der Planungsentscheidung.

So berechnet man die Selectivity manuell in SQL:

-- Selectivity manuell berechnen
SELECT
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT customer_group_id) / COUNT(*) AS group_selectivity
FROM customer_entity;

-- Erwartete Ausgabe (Beispiel):
-- email_selectivity: 0.9998  (sehr hoch -> guter Index-Kandidat)
-- status_selectivity: 0.0000 (sehr niedrig -> schlechter Index allein)
-- group_selectivity: 0.0000  (sehr niedrig -> nur in Kombination sinnvoll)
    

2. Cardinality: SHOW INDEX und was der Optimizer daraus macht

Die Cardinality ist die geschätzte Anzahl eindeutiger Werte in einem Index. MySQL speichert diesen Schätzwert im Data Dictionary und zeigt ihn mit SHOW INDEX FROM an. Je höher die Cardinality relativ zur Tabellenbreite, desto lieber nutzt der Optimizer diesen Index. Der Wert ist ein Schätzwert, der durch InnoDB-Sampling entstanden ist und mit ANALYZE TABLE aktualisiert werden kann.

-- Alle Indizes mit Cardinality anzeigen
SHOW INDEX FROM catalog_product_entity;

-- Relevante Spalten der Ausgabe:
-- Table        | Key_name               | Seq_in_index | Column_name      | Cardinality | Non_unique
-- cpe          | PRIMARY                | 1            | entity_id        | 85432       | 0
-- cpe          | CATALOG_PRODUCT_ENTITY_SKU | 1       | sku              | 85432       | 0
-- cpe          | idx_type_attribute_set | 1            | type_id          | 6           | 1
-- cpe          | idx_type_attribute_set | 2            | attribute_set_id | 142         | 1

-- Cardinality aktualisieren (nach größeren Datenänderungen)
ANALYZE TABLE catalog_product_entity;
    

Wichtig: Der Optimizer entscheidet sich nicht immer für den Index mit der höchsten Cardinality. Er kalkuliert die geschätzten Kosten jedes Plans. Bei kleinen Tabellen (wenige hundert Zeilen) ist oft ein Full Table Scan günstiger als ein Index Scan. Bei sehr selektiven Queries (WHERE email = ?) ist ein Index mit hoher Cardinality fast immer die richtige Wahl.

3. Composite Indexes: Spaltenreihenfolge und Left-Prefix-Regel

Ein Composite Index (mehrspaltig) ist mächtiger als mehrere Einzel-Indizes. Die Reihenfolge der Spalten im Index ist entscheidend und folgt einer klaren Hierarchie: Zuerst kommen Equality-Spalten (Filterung mit =), dann Range-Spalten (Filterung mit >, <, BETWEEN, LIKE 'prefix%'), dann Spalten, die lediglich als Covering-Columns benötigt werden.

Die Left-Prefix-Regel besagt: Ein Composite Index (a, b, c) kann für Abfragen auf (a), (a, b) oder (a, b, c) genutzt werden, aber nicht für (b), (c) oder (b, c) allein. MySQL kann den Index von links nach rechts verwenden, aber nicht in der Mitte beginnen.

-- Index mit optimaler Spaltenreihenfolge für typische Magento-Sales-Query
-- Query: WHERE state = 'processing' AND created_at >= '2025-01-01' AND created_at < '2025-02-01'
-- Reihenfolge: Equality (state) zuerst, dann Range (created_at)

CREATE INDEX idx_order_state_created
    ON sales_order (state, created_at);

-- EXPLAIN zeigt: type=range, key=idx_order_state_created, Extra=Using index condition
EXPLAIN SELECT entity_id, increment_id, grand_total
FROM sales_order
WHERE state = 'processing'
  AND created_at >= '2025-01-01'
  AND created_at < '2025-02-01';

-- Covering Index: Alle SELECT-Spalten im Index -> kein Table Lookup nötig
CREATE INDEX idx_order_state_created_covering
    ON sales_order (state, created_at, entity_id, increment_id, grand_total);
-- EXPLAIN Extra: "Using index" (kein Zugriff auf Datenseiten)
    

Die Reihenfolge falsch zu setzen ist ein klassischer Fehler. Ein Index (created_at, state) für die obige Query wäre suboptimal: MySQL könnte zwar den Range-Teil auf created_at nutzen, würde aber state nicht mehr als Range-Präfix nutzen können – was fast immer zu einem größeren Scan-Bereich führt.

4. Prefix-Indizes für lange VARCHAR-Spalten

Bei sehr langen VARCHAR-Spalten (URLs, Beschreibungen, E-Mails) ist ein vollständiger Index manchmal nicht möglich oder ineffizient. InnoDB erlaubt standardmäßig maximal 767 Bytes pro Indexspalte (bei utf8mb4 entspricht das 191 Zeichen). Ein Prefix-Index indiziert nur die ersten N Zeichen einer Spalte, was den Index kleiner und schneller macht.

-- Prefix-Index: nur erste 40 Zeichen der E-Mail indizieren
CREATE INDEX idx_customer_email_prefix
    ON customer_entity (email(40));

-- Selectivity des Prefix prüfen (sollte nahe an der vollen Selectivity sein)
SELECT
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT LEFT(email, 40)) / COUNT(*) AS sel_40,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM customer_entity;

-- Magento: url_key auf Kategorieseiten mit Prefix-Index
CREATE INDEX idx_url_key_prefix
    ON catalog_category_entity_varchar (value(100));
    

Wichtiger Nachteil von Prefix-Indizes: Sie können nicht als Covering Index fungieren. MySQL muss weiterhin die Datenseite laden, um den vollständigen Wert zu lesen. Für exakte Gleichheitssuche (WHERE email = ?) reichen sie aber oft aus, um den Index zu nutzen und die Ergebnismenge massiv einzuschränken.

5. Index Merge als Warnsignal

Erscheint in der type-Spalte von EXPLAIN der Wert index_merge, bedeutet das: MySQL kann keinen einzelnen optimalen Index finden und kombiniert daher zwei oder mehr Einzel-Indizes. Das ist fast immer ein Zeichen, dass ein besserer Composite Index fehlt.

-- EXPLAIN zeigt index_merge -> Warnsignal
EXPLAIN SELECT entity_id, status, created_at
FROM sales_order
WHERE status = 'pending' OR customer_id = 42;
-- type: index_merge, Extra: Using union(idx_status, idx_customer_id)

-- Lösung: Composite Index wenn Queries häufig beide Spalten kombinieren
-- (Bei OR-Verknüpfung ist index_merge normal; bei AND -> Composite Index)
EXPLAIN SELECT entity_id, status, created_at
FROM sales_order
WHERE status = 'processing'
  AND customer_id = 42;
-- Ohne Composite Index: index_merge
-- Mit Composite Index (customer_id, status): type=ref -> deutlich schneller

CREATE INDEX idx_order_customer_status
    ON sales_order (customer_id, status);
    

6. Invisible Indexes für sicheres Testen (MySQL 8)

MySQL 8.0 führte Invisible Indexes ein – ein sehr praktisches Feature für sicheres Index-Tuning in Produktion. Ein Invisible Index wird weiterhin gepflegt (bei INSERT/UPDATE/DELETE aktualisiert), ist aber für den Query Optimizer unsichtbar. So kann man testen, ob eine Query ohne bestimmten Index langsamer wird, ohne den Index dauerhaft zu löschen.

-- Index unsichtbar machen (Optimizer ignoriert ihn)
ALTER TABLE sales_order ALTER INDEX idx_order_state_created INVISIBLE;

-- Prüfen: EXPLAIN jetzt ohne diesen Index
EXPLAIN SELECT entity_id FROM sales_order WHERE state = 'processing';
-- Wenn die Query nun deutlich teurer wird -> Index ist wichtig -> wieder sichtbar machen

-- Index wieder sichtbar machen
ALTER TABLE sales_order ALTER INDEX idx_order_state_created VISIBLE;

-- Alle Invisible Indexes im Schema anzeigen
SELECT TABLE_NAME, INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND IS_VISIBLE = 'NO'
GROUP BY TABLE_NAME, INDEX_NAME;
    

7. Redundante Indizes erkennen mit sys.schema_redundant_indexes

Überflüssige Indizes erhöhen den Schreiboverhead, verbrauchen Speicher und verwirren den Optimizer. Das MySQL sys-Schema enthält die View schema_redundant_indexes, die Indizes auflistet, die vollständig durch einen anderen abgedeckt werden.

-- Redundante Indizes finden
SELECT
    table_schema,
    table_name,
    redundant_index_name,
    redundant_index_columns,
    dominant_index_name,
    dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE()
ORDER BY table_name;

-- Beispiel: Index (customer_id) ist redundant wenn (customer_id, status) existiert
-- Der Left-Prefix (customer_id) wird bereits durch den Composite Index abgedeckt

-- Duplikate (identische Indizes) finden
SELECT
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
GROUP BY table_name, index_name
HAVING COUNT(*) > 0
ORDER BY table_name, columns;
    

8. Magento: Index-Muster auf catalog_product_entity

Magento 2 hat durchdachte Indizes auf den zentralen Katalog-Tabellen. Wer sie kennt, kann Queries gezielt optimieren und unnötige Full-Table-Scans vermeiden. Die catalog_product_entity hat neben dem PRIMARY KEY auf entity_id einen UNIQUE Index auf sku, weil Produktimporte häufig per SKU suchen.

-- Bestehende Indizes auf zentralen Magento-Tabellen prüfen
SHOW INDEX FROM catalog_product_entity;
SHOW INDEX FROM catalog_product_entity_varchar;
SHOW INDEX FROM catalog_category_product;

-- EAV-Attributtabellen: der entscheidende Composite Index
-- catalog_product_entity_varchar hat Index auf (entity_id, attribute_id, store_id)
-- Das erlaubt effiziente Queries wie:
SELECT cpev.value AS product_name
FROM catalog_product_entity cpe
JOIN catalog_product_entity_varchar cpev
    ON cpev.entity_id = cpe.entity_id
    AND cpev.attribute_id = 73  -- name-Attribut
    AND cpev.store_id IN (0, 1)  -- global + Store 1
WHERE cpe.sku = 'PROD-001';

-- Fehlender Index kann bei großen Katalogen fatale Folgen haben:
-- Bei 100k Produkten und 50 Attributen: 5 Mio. EAV-Zeilen pro Tabelle
-- Ohne Index auf (attribute_id, store_id): Full Table Scan bei jedem Produkt-Load
    

9. Reale EXPLAIN-Ausgaben lesen und bewerten

Die wichtigsten Felder in EXPLAIN für Index-Diagnose sind: type (Zugriffstyp – von const über ref bis ALL), key (genutzter Index), rows (geschätzte gescannte Zeilen) und Extra (zusätzliche Informationen wie "Using index", "Using filesort", "Using temporary").

-- EXPLAIN Ausgabe bewerten: von gut nach schlecht
-- type=const    -> Primärschlüssel oder Unique-Index, exakt eine Zeile
-- type=eq_ref   -> JOIN mit Unique-Index, eine Zeile pro Join-Partner
-- type=ref      -> Nicht-Unique-Index, wenige Zeilen
-- type=range    -> Index-Range-Scan (BETWEEN, >, <, IN)
-- type=index    -> Full Index Scan (besser als ALL, aber alle Index-Seiten)
-- type=ALL      -> Full Table Scan (schlecht bei großen Tabellen)

-- Praxisbeispiel: Query ohne und mit Index vergleichen
-- Ohne passenden Index:
EXPLAIN SELECT entity_id, increment_id, grand_total
FROM sales_order
WHERE state = 'processing'
  AND customer_email = 'kunde@example.com';
-- type=ALL, rows=500000, Extra=Using where  (sehr teuer)

-- Nach Anlegen des Composite Index:
CREATE INDEX idx_order_state_email ON sales_order (customer_email, state);
EXPLAIN SELECT entity_id, increment_id, grand_total
FROM sales_order
WHERE state = 'processing'
  AND customer_email = 'kunde@example.com';
-- type=ref, key=idx_order_state_email, rows=2, Extra=Using index condition
    

Das Feld Extra: Using filesort bedeutet, dass MySQL eine zusätzliche Sortierperation außerhalb des Index durchführen muss – bei großen Ergebnismengen ein erheblicher Performance-Killer. Kann ORDER BY durch den Index abgedeckt werden, verschwindet "Using filesort".

10. Zusammenfassung

Ein MySQL Index ist kein universelles Allheilmittel, sondern ein gezielt einzusetzendes Werkzeug. Die Entscheidung, ob und wie ein Index angelegt wird, hängt von Selectivity, Abfragemustern und Schreib-/Lese-Verhältnis ab. Composite Indexes sind mächtiger als Einzel-Indizes, aber nur wenn die Spaltenreihenfolge stimmt. Invisible Indexes erlauben risikofreies Testen in Produktion. Index-Merge-Signale im EXPLAIN deuten fast immer auf fehlende Composite Indexes hin.

11. FAQ: MySQL Index – Selectivity, Cardinality und Composite Indexes