MySQL 8: Histogramm-Statistiken gegen schiefe Datenverteilungen

1. Warum klassische Cardinality-Schätzungen an schiefen Spalten scheitern

Für Nicht-Index-Spalten und für die Bewertung von Bereichsbedingungen stützt sich der Optimizer traditionell auf einen einzigen Cardinality-Wert je Spalte, also eine grobe Schätzung der Anzahl unterschiedlicher Werte im Verhältnis zur Gesamtzeilenzahl. Aus diesem einen Wert wird für jede Bedingung eine durchschnittliche Trefferquote abgeleitet, unabhängig davon, nach welchem konkreten Wert tatsächlich gefiltert wird.

Dieses Verfahren funktioniert zuverlässig, solange sich die Werte einer Spalte einigermaßen gleichmäßig verteilen. Sobald aber wenige Werte extrem häufig und der große Rest selten vorkommen, etwa ein Bestellstatus, bei dem neunzig Prozent aller Zeilen auf 'complete' entfallen und der Rest sich auf ein Dutzend seltener Zustände verteilt, führt eine reine Durchschnittsschätzung systematisch in die Irre. Eine Bedingung auf einen seltenen Status wird dann massiv überschätzt, eine Bedingung auf den häufigen Status massiv unterschätzt.

2. Welche Ausführungsplan-Fehler daraus konkret entstehen

Überschätzt der Optimizer die Trefferzahl einer eigentlich seltenen Bedingung, entscheidet er sich unter Umständen gegen einen vorhandenen Index und liest stattdessen die gesamte Tabelle, weil ein vollständiger Scan bei vermeintlich vielen Treffern günstiger erscheint als ein Index-Zugriff mit anschließendem Nachschlagen der Zeilen. Bei tatsächlich wenigen Treffern ist dieser Plan spürbar langsamer als der eigentlich passende Index-Zugriff.

Umgekehrt kann eine Unterschätzung bei einer Join-Reihenfolge mit mehreren Tabellen dazu führen, dass MySQL die vermeintlich kleinere Zwischenmenge zuerst verarbeitet, obwohl sie in Wirklichkeit die größte ist, was die gesamte Join-Strategie ungünstig verschiebt. Solche Fehleinschätzungen zeigen sich in der Praxis oft erst bei wachsenden Datenmengen, weil kleine Testdatenbanken die Schiefe der Verteilung häufig gar nicht abbilden.

3. Wie ein Histogramm die Datengrundlage verbessert

Ein Histogramm unterteilt die beobachteten Werte einer Spalte in mehrere Buckets und speichert für jeden Bucket eine eigene, differenzierte Häufigkeitsangabe, statt einen einzigen Durchschnittswert für die gesamte Spalte zu verwenden. MySQL unterstützt dafür zwei Varianten: Equi-Height-Histogramme, bei denen jeder Bucket ungefähr denselben Anteil der Gesamtzeilen abdeckt, und Singleton-Histogramme, bei denen jeder häufige Einzelwert einen eigenen Bucket erhält.

Bei einer Spalte mit wenigen, dafür sehr dominanten Werten wählt MySQL automatisch die Singleton-Variante, weil sie für genau diesen Fall die präziseste Information liefert. Eine Bedingung auf den häufigen Wert 'complete' erhält dann eine realistische, hohe Trefferschätzung, während eine Bedingung auf den seltenen Wert 'fraud_suspected' eine entsprechend niedrige Schätzung bekommt, exakt die Differenzierung, die eine einfache Cardinality-Angabe nicht leisten kann.

4. Histogramme praktisch mit ANALYZE TABLE erstellen

Die Erstellung eines Histogramms erfolgt über die bereits bekannte ANALYZE-TABLE-Anweisung, erweitert um die Klausel UPDATE HISTOGRAM ON, gefolgt von der betroffenen Spalte. Optional lässt sich die Anzahl der Buckets festlegen, standardmäßig verwendet MySQL 100 Buckets, was für die meisten Anwendungsfälle bereits ausreichend fein auflöst.

Der Vorgang liest die Tabelle einmalig, um die Verteilung zu ermitteln, verursacht dabei aber keine dauerhafte Sperre und beeinträchtigt laufende Schreibzugriffe nur minimal. Das erzeugte Histogramm wird als JSON-Dokument im Data Dictionary abgelegt und bleibt bestehen, bis es explizit aktualisiert oder gelöscht wird, es unterliegt also keiner automatischen Verfallszeit.

-- Histogramm mit 100 Buckets für die Statusspalte erstellen
ANALYZE TABLE sales_order
    UPDATE HISTOGRAM ON status;

-- Mit expliziter Bucket-Anzahl für eine feinere Aufloesung
ANALYZE TABLE sales_order
    UPDATE HISTOGRAM ON status WITH 64 BUCKETS;

-- Bestehendes Histogramm wieder entfernen
ANALYZE TABLE sales_order
    DROP HISTOGRAM ON status;

5. Ein bestehendes Histogramm einsehen und bewerten

Die tatsächlichen Bucket-Daten eines erstellten Histogramms lassen sich direkt über information_schema.column_statistics abfragen, wo sie als strukturiertes JSON-Dokument abgelegt sind. Darin enthalten sind unter anderem der Histogramm-Typ, die Anzahl der Buckets sowie für jeden Bucket der abgedeckte Wertebereich und der kumulierte Anteil an der Gesamtzeilenzahl.

Für die praktische Bewertung reicht es oft aus, die Singleton-Werte mit den tatsächlich bekannten Top-Werten der Spalte abzugleichen, etwa über eine einfache GROUP BY-Abfrage mit COUNT. Weichen die im Histogramm hinterlegten Anteile deutlich von der aktuellen Realität ab, etwa weil sich die Statusverteilung nach einer Rabattaktion stark verschoben hat, ist das ein klares Signal, das Histogramm zeitnah neu zu erstellen.

SELECT schema_name, table_name, column_name,
       histogram->'$."data-type"' AS data_type,
       histogram->'$."number-of-buckets-specified"' AS buckets
FROM information_schema.column_statistics
WHERE table_name = 'sales_order' AND column_name = 'status';

6. Wann Histogramme tatsächlich bessere Pläne erzeugen

Histogramme entfalten ihren Nutzen vor allem bei Spalten, die nicht indiziert werden sollen oder können, etwa weil sie nur selten als Filterbedingung dienen und ein dauerhafter Index reinen Schreibaufwand ohne entsprechenden Lesenutzen bedeuten würde. Für solche Spalten war der Optimizer vorher komplett auf grobe Schätzungen angewiesen, ein Histogramm liefert hier den größten relativen Gewinn.

Ebenso hilfreich sind Histogramme bei Bereichsbedingungen auf schief verteilten numerischen Spalten, etwa Bestellsummen mit wenigen sehr hohen Ausreißern, oder bei Fremdschlüsselspalten mit stark ungleicher Verteilung, wie einer store_id, bei der ein einzelner Store neunzig Prozent des Datenvolumens ausmacht. Bei gleichmäßig verteilten oder bereits gut indizierten Spalten mit hoher Selektivität bringt ein zusätzliches Histogramm dagegen meist keinen messbaren Vorteil mehr.

7. Praxisbeispiel: Histogramm auf der Bestellstatus-Spalte

In einer gewachsenen Magento-Installation mit mehreren Millionen Bestellungen sammelt sich typischerweise eine stark schiefe Verteilung im Statusfeld an: Der Großteil der Bestellungen ist längst abgeschlossen, während Zustände wie 'payment_review' oder 'holded' nur einen kleinen Bruchteil ausmachen, aber gerade im operativen Tagesgeschäft häufig gezielt abgefragt werden, etwa im Support-Dashboard oder in automatisierten Eskalations-Reports.

Ein Histogramm auf dieser Spalte sorgt dafür, dass eine gezielte Abfrage auf den seltenen Status 'payment_review' korrekt als hochselektiv erkannt wird und der Optimizer bevorzugt einen vorhandenen Index nutzt, während eine Abfrage auf 'complete' weiterhin realistisch als wenig selektiv eingestuft wird und gegebenenfalls bewusst auf einen Tabellen-Scan zurückgreift, wenn das insgesamt günstiger ist.

8. Pflege und Aktualisierungsstrategie für Histogramme

Anders als reguläre Index-Statistiken werden Histogramme nicht automatisch bei jedem größeren Änderungsvolumen neu berechnet, sondern bleiben bis zur expliziten Aktualisierung unverändert bestehen. Für Spalten mit stabiler, sich kaum verändernder Verteilung ist das unproblematisch, für Spalten mit saisonalen oder ereignisgetriebenen Verschiebungen, etwa nach großen Verkaufsaktionen, sollte die Aktualisierung fest in den regulären Wartungsplan aufgenommen werden.

In der Praxis hat sich bewährt, die Histogramm-Aktualisierung an denselben Rhythmus wie andere Wartungsaufgaben zu koppeln, etwa im Rahmen eines wöchentlichen Wartungsfensters gemeinsam mit ANALYZE TABLE für die Index-Statistiken. Da der Vorgang selbst ressourcenschonend abläuft und keine langwierige Sperre erzeugt, lässt er sich problemlos auch außerhalb klassischer Wartungsfenster ausführen, wenn eine kurzfristige Verschiebung der Datenverteilung bekannt wird.

9. Grenzen: Was Histogramme nicht lösen

Histogramme ersetzen keinen fehlenden Index für tatsächlich häufig genutzte, hochselektive Filterbedingungen. Sie verbessern lediglich die Schätzgrundlage des Optimizers, ändern aber nichts an der physischen Zugriffsgeschwindigkeit, wenn ohne Index ein vollständiger Tabellen-Scan die einzig verfügbare Zugriffsmethode bleibt. Wer ein Performance-Problem allein durch Histogramme lösen möchte, ohne die zugrunde liegende Indexstrategie zu prüfen, wird meist enttäuscht.

Zudem berücksichtigt ein Histogramm nur die Verteilung innerhalb einer einzelnen Spalte und erfasst keine Korrelationen zwischen mehreren Spalten. Zwei Spalten, die in der Praxis stark zusammenhängen, etwa store_id und Versandland, werden vom Optimizer weiterhin unabhängig voneinander bewertet, was in solchen Fällen selbst mit gepflegten Histogrammen zu suboptimalen Plänen führen kann und im Zweifel eine funktionale Indexspalte oder eine umformulierte Query erfordert.

10. Zusammenfassung

11. FAQ: Histogramm-Statistiken: Das Wichtigste auf einen Blick