Analysieren für die Statistiken

Auch wenn der Titel etwas sehr nach Matheunterricht oder Studium klingt, birgt sich dahinter eine wichtige, praxisrelevante Vorgehensweise, um den Zugriff auf MySQL-Tabellen zu beschleunigen. Fakt ist, dass MySQL nur einen Index benutzen wird, wenn die Anzahl der zurückgegebenen Datensätze kleiner als etwa 10 % der Gesamtanzahl der Datensätze der Tabelle ist. Ist das nicht der Fall ist ein Full Table Scan oft schneller. Der Full Table Scan ist recht langsam. Er kann aber vermieden werden, wenn man dem DBMS Auskunft darüber gibt, wie die Daten verteilt sind.

Das Problem, vor dem das DBMS steht, ist zu entscheiden, ob ein Index oder ein Full Table Scan schneller sein wird. Es muss sozusagen in die Zukunft schauen. Um das nicht zum Glücksspiel verkommen zu lassen, wird die Abfrage analysiert und darauf basierend eine Entscheidung getroffen. Das kann allerdings nur richtig funktionieren, wenn MySQL genug Statistiken über die Tabelle vorliegen.

Ich möchte es am Beispiel erklären. Wir haben eine Tabelle mit einem Feld vom Typ ENUM, das zwei Werte aufnehmen kann: ENUM(‚ja‘,’nein‘). In unserer Tabelle befinden sich 10.000 Datensätze, davon 100 mit ‚ja‘ und 9900 mit ’nein‘. Wir wollen nun alle Datensätze haben, die ‚ja‘ als Wert eingetragen haben:

SELECT * FROM tabelle WHERE enumspalte='ja'

Wenn MySQL nichts weiter über die Tabellenspalte weiß, außer dass sie 2 mögliche Werte hat, wird eine Gleichverteilung der Werte angenommen (50% ja, 50% nein). Und genau da liegt das Problem: Nun „denkt“ MySQL, dass mehr als die besagten ca. 10 % (der Wert kann etwas abweichen) der Datensätze zurückgeliefert werden sollen, und zwar 50% von 10.000 = 5000. Deshalb wird es den Full Table Scan nutzen. Selbst wenn ein Index auf der Spalte liegt, wird dieser nicht genutzt.

Das Geheimnis liegt darin, MySQL mitzuteilen, wie die Werte verteilt sind. Das geschieht über

ANALYZE TABLE tabelle

Durch diesen Befehl „merkt“ sich MySQL, dass nur 1 % der Datensätze den Wert ‚ja‘ haben und wird daraus folgend für die Abfrage den Index benutzen.

Es sollte also regelmäßig ein Analysieren der Tabellen erfolgen. Und das vor allem mit Realdaten, denn es hilft nichts, wenn man zu Beginn mal eben die Tabelle analysiert, wo 10 Einträge drin sind. Dann kann nämlich eine völlig falsche Verteilung vorliegen, die erst später mit einer Vielzahl von neuen Datensätzen angeglichen wird. Wenn MySQL dann nichts von der neuen Verteilung weiß, kann es passieren, dass der Index sogar dann benutzt würde, wenn der Full Table Scan wesentlich schneller wäre.

Jan hat 152 Beiträge geschrieben

5 Kommentare zu “Analysieren für die Statistiken

  1. rtauchnitz sagt:

    Wann wäre eine solche Analyse sinnvoll? Sicherlich nicht vor jedem Zugriff. Wie also kann man so etwas sinnvoll realisieren? In einem bestimmten Zeitintervall (per Cron jede Nacht um drei) oder beispielsweise alle 100 Zugriffe?

  2. admin sagt:

    Nein, vor jedem zugriff ist nicht sinnvoll.
    Man sollte immer dann eine Analyse durchführen, wenn sich die Werteverteilungen grundlegend ändern. Als Anhaltspunkt empfehle ich eine Periode von ca. 1 Monat, aber das ist auch daten- und anwendungsabhängig.

  3. GhostGambler sagt:

    Da das ganze selbst bei riesigen Tabellen weniger als 5 Minuten dauert (selbst mit normalen live-Betrieb), kann man das durchaus als Cronjob jede Nacht laufen lassen – wenn man Spaß hat – andererseits hatte ich persönlich bisher noch nie den Fall, dass ANALYZE überhaupt irgendetwas geändert hätte an der Ausführung der Queries, würde das also wenn eher von Hand machen, sobald ich über einen offensichtlich falsch ausführenden Query stolpern würde~

  4. Marco sagt:

    Also könnte man einfach eine Datei erstellen, die den folgenden Inhalt hat und diese per Cronjob ein Mal pro Tag aufrufen lassen, oder?
    —-
    mysql_query(„ANALYZE TABLE meinetabelle“);
    —-
    Habe ich das so richtig verstanden? Es schadet also auch nicht, wenn man das jeden Tag macht!?

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>