MySQL 8: Absteigende Indizes und unsichtbare Indizes richtig einsetzen

1. Warum ORDER BY DESC vor MySQL 8 teuer werden konnte

InnoDB legt die Einträge eines B-Baum-Index physisch in aufsteigender Schlüsselreihenfolge ab. Fragt eine Query eine absteigende Sortierung an, konnte der Optimizer vor MySQL 8 diesen Baum nur rückwärts durchlaufen, also von der letzten zur ersten Seite. Für eine einzelne Sortierspalte funktionierte das gut genug, weil ein Rückwärts-Scan über einen aufsteigenden Index dieselbe Reihenfolge liefert wie ein Vorwärts-Scan über einen gedachten absteigenden Index. Der Optimizer musste dafür lediglich die Leserichtung umkehren, was intern kaum zusätzlichen Aufwand verursachte.

Kritisch wurde es erst, sobald eine Query mehrere Spalten mit unterschiedlicher Sortierrichtung verlangte, etwa Status aufsteigend und Erstellungsdatum absteigend zugleich. Ein einzelner B-Baum kann pro Definition nur eine konsistente physische Reihenfolge abbilden, sodass ein Rückwärts-Scan in diesem Fall nicht mehr ausreichte, um beide Anforderungen gleichzeitig zu erfüllen. MySQL musste dann auf ein zusätzliches Filesort zurückgreifen, also die bereits über den Index gefundenen Zeilen im Anschluss noch einmal explizit sortieren, was bei großen Ergebnismengen spürbar Zeit und Arbeitsspeicher kostet.

2. Wie ein echter DESC-Index intern funktioniert

Seit MySQL 8 lässt sich bei der Indexdefinition pro Spalte explizit ASC oder DESC angeben, und diese Angabe ist keine reine Syntax-Kosmetik mehr, sondern verändert tatsächlich die physische Ablage der Schlüssel im B-Baum. Eine als DESC deklarierte Spalte wird intern so gespeichert, dass ein einfacher Vorwärts-Scan bereits die gewünschte absteigende Reihenfolge liefert. Dadurch entfällt sowohl der Rückwärts-Scan als auch, im entscheidenden Fall gemischter Richtungen, das nachgelagerte Filesort vollständig.

Der Unterschied zeigt sich am deutlichsten bei zusammengesetzten Indizes mit gemischter Richtung. Ein Index über zwei Spalten, von denen die erste aufsteigend und die zweite absteigend sortiert werden soll, lässt sich als echter Kombi-Index anlegen, sodass beide Sortierkriterien durch einen einzigen sequenziellen Scan bedient werden. Vor dieser Möglichkeit blieb nur die Wahl zwischen einem teuren Filesort oder einer künstlichen Umformulierung der Query, die in der Praxis selten sauber funktionierte.

-- Klassischer Index: beide Spalten implizit aufsteigend
CREATE INDEX idx_status_created_asc
    ON sales_order (status, created_at);

-- Echter Kombi-Index mit gemischter Richtung (MySQL 8+)
CREATE INDEX idx_status_created_mixed
    ON sales_order (status ASC, created_at DESC);

3. Praxisbeispiel: Bestellliste nach Status und Datum sortieren

Ein typisches Muster in Magento-nahen Auswertungen ist eine Bestellliste, die zunächst nach Status gruppiert und innerhalb jedes Status nach dem neuesten Datum zuerst sortiert werden soll, etwa um offene Bestellungen chronologisch absteigend im Support-Dashboard anzuzeigen. Ohne passenden Index greift MySQL entweder auf einen Index-Scan mit anschließendem Filesort zurück oder verwirft den Index für die Sortierung ganz und liest die komplette Tabelle.

Mit dem oben gezeigten gemischten Kombi-Index kann derselbe Query-Plan die Bedingung im WHERE-Teil, die Gruppierung nach Status und die absteigende Datumsordnung in einem einzigen Indexdurchlauf abdecken. Gerade bei Bestelltabellen mit mehreren Millionen Zeilen, wie sie in gewachsenen Magento-Shops keine Seltenheit sind, macht dieser Unterschied den Unterschied zwischen einer Antwortzeit im Millisekundenbereich und einer spürbaren Verzögerung im Admin-Grid aus.

SELECT entity_id, increment_id, status, created_at
FROM sales_order
WHERE store_id = 1
ORDER BY status ASC, created_at DESC
LIMIT 25;

4. Mit EXPLAIN ANALYZE den Effekt sichtbar machen

Der zuverlässigste Weg, den Effekt eines DESC-Index zu belegen, ist ein direkter Vorher-Nachher-Vergleich mit EXPLAIN ANALYZE. Ohne passenden Kombi-Index zeigt der Ausführungsplan typischerweise einen Hinweis auf ein zusätzliches Sortieren der Zwischenergebnisse, erkennbar an einer eigenen Sort-Stufe im Plan sowie an einer messbar höheren tatsächlichen Laufzeit gegenüber der reinen Zeilenanzahl.

Mit dem neuen Kombi-Index verschwindet diese zusätzliche Sortierstufe vollständig aus dem Plan, und die Zeilen werden bereits in der finalen Reihenfolge aus dem Index gelesen. Bei wiederkehrenden Reports oder stark frequentierten Admin-Grids lohnt es sich, diesen Vergleich fest im Rahmen der Query-Optimierung zu dokumentieren, damit spätere Schema-Änderungen nicht versehentlich wieder ein Filesort einführen, ohne dass jemand es bemerkt.

EXPLAIN ANALYZE
SELECT entity_id, status, created_at
FROM sales_order
WHERE store_id = 1
ORDER BY status ASC, created_at DESC
LIMIT 25;

5. Invisible Indexes: Grundprinzip und Syntax

Ein unsichtbarer Index bleibt vollständig gepflegt, wird also bei jedem INSERT, UPDATE und DELETE weiterhin aktualisiert, fließt aber nicht mehr in die Entscheidungen des Optimizers ein. Für den Ausführungsplan verhält sich die Tabelle so, als existiere dieser Index gar nicht, während er im Hintergrund unverändert weiter Speicherplatz und Schreibaufwand verursacht. Diese Eigenschaft macht ihn zum idealen Zwischenschritt vor einem endgültigen Löschen.

Der Wechsel zwischen sichtbar und unsichtbar erfolgt über eine einfache ALTER-TABLE-Anweisung und ist sofort wirksam, ohne dass eine teure Tabellen-Kopie oder ein langwieriger Rebuild nötig wird. Das unterscheidet den Vorgang deutlich vom eigentlichen Löschen oder Neuanlegen eines Index, das bei großen InnoDB-Tabellen durchaus mehrere Minuten oder Stunden Sperrzeit beziehungsweise Online-DDL-Aufwand bedeuten kann.

-- Index für den Optimizer unsichtbar machen
ALTER TABLE sales_order
    ALTER INDEX idx_legacy_customer_email INVISIBLE;

-- Rückgängig machen, falls doch noch benötigt
ALTER TABLE sales_order
    ALTER INDEX idx_legacy_customer_email VISIBLE;

6. Praktischer Workflow für risikofreies Index-Aufräumen

In der Praxis bewährt sich ein mehrstufiges Vorgehen: Zuerst wird über information_schema.statistics und sys.schema_unused_indexes geprüft, welche Indizes seit dem letzten Neustart der Datenbank überhaupt nicht gelesen wurden. Kandidaten aus dieser Liste werden anschließend nicht sofort gelöscht, sondern für einen definierten Zeitraum, etwa zwei volle Geschäftszyklen inklusive Monatsabschluss und Reporting, auf INVISIBLE gesetzt.

Während dieses Zeitraums lässt sich über Monitoring, Slow-Query-Log und Anwendungs-Metriken beobachten, ob irgendeine Abfrage messbar langsamer wird oder ein zuvor unauffälliger Query-Plan plötzlich ein Filesort oder einen vollständigen Tabellen-Scan verwendet. Bleibt die Performance über den gesamten Beobachtungszeitraum stabil, ist das Löschen des Index ein risikoarmer, gut begründeter letzter Schritt, statt einer Bauchentscheidung auf Basis einer einzelnen Momentaufnahme.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'magento2db';

7. Fallstricke: Unique-Constraints und Foreign Keys

Eine wichtige Ausnahme betrifft eindeutige Indizes: Auch ein auf INVISIBLE gesetzter UNIQUE-Index prüft weiterhin bei jedem INSERT und UPDATE, ob der Wert bereits existiert, und verhindert Duplikate genau wie zuvor. Für den Optimizer verschwindet er zwar als mögliche Zugriffsstruktur für Lesezugriffe, die Integritätsprüfung selbst bleibt jedoch vollständig aktiv, ein Detail, das leicht übersehen wird, wenn man INVISIBLE fälschlich mit vollständig deaktiviert gleichsetzt.

Zusätzlich lässt sich ein Index nicht unsichtbar machen, wenn er die einzige verfügbare Struktur ist, die einen bestehenden Foreign-Key-Constraint absichert. MySQL verweigert diesen ALTER-Befehl dann mit einer expliziten Fehlermeldung, weil sonst referenzielle Integritätsprüfungen ohne effizienten Index-Zugriff auskommen müssten. In solchen Fällen muss zuerst ein alternativer, weiterhin sichtbarer Index angelegt werden, bevor der ursprüngliche testweise verschwinden darf.

8. Beide Funktionen kombiniert in einer Migrationsstrategie

Bei einer geplanten Umstellung von einem klassischen, nur aufsteigenden Index auf einen gemischten DESC-Index lassen sich beide Funktionen sinnvoll kombinieren. Zunächst wird der neue, gemischt sortierte Index parallel zum alten angelegt, sodass beide Indizes gleichzeitig existieren und der Optimizer bereits den neuen wählen kann, sobald er den bestehenden in der Kostenschätzung schlägt.

Erst wenn Monitoring über einen ausreichenden Zeitraum bestätigt, dass tatsächlich durchgängig der neue Index genutzt wird, wird der alte Index auf INVISIBLE gesetzt statt sofort gelöscht. Diese Kombination aus paralleler Bereitstellung und schrittweisem Unsichtbarmachen reduziert das Risiko einer Regression auf nahezu null, weil jederzeit ein einzeiliger ALTER-Befehl genügt, um den alten Zustand vollständig wiederherzustellen, ohne einen Index neu aufbauen zu müssen.

9. Wann sich der Aufwand wirklich lohnt

Ein echter DESC-Index lohnt sich vor allem dann, wenn eine Query regelmäßig und mit spürbarer Häufigkeit mehrere Spalten mit unterschiedlicher Sortierrichtung gleichzeitig verlangt, etwa in Admin-Grids, Reporting-Views oder API-Endpunkten mit Paginierung über große Ergebnismengen. Bei einer reinen Einzelspalten-Sortierung bringt die explizite DESC-Deklaration dagegen kaum messbaren Zusatznutzen gegenüber dem klassischen Rückwärts-Scan.

Invisible Indexes wiederum sollten fester Bestandteil jedes Index-Aufräumprozesses werden, gerade in gewachsenen Magento-Installationen mit vielen historisch angelegten, teils redundanten Indizes. Die geringen Kosten eines testweisen Unsichtbarmachens stehen in keinem Verhältnis zum Risiko eines vorschnellen, endgültigen Löschens, das im schlimmsten Fall erst Tage später bei einem selten ausgeführten Monatsreport auffällt.

10. Zusammenfassung

11. FAQ: DESC- und Invisible-Indizes: Das Wichtigste auf einen Blick