Übersicht über MySQL-Statistiken

MySQL führt SQL durch den Prozess der SQL-Analyse und Abfrageoptimierung aus. Der Parser zerlegt das SQL in Datenstrukturen und übergibt sie an die nachfolgenden Schritte. Der Abfrageoptimierer findet die beste Lösung für die Ausführung der SQL-Abfrage und generiert einen Ausführungsplan. Der Abfrageoptimierer bestimmt, wie SQL ausgeführt wird, was von den Datenbankstatistiken abhängt. Im Folgenden stellen wir den relevanten Inhalt der InnoDB-Statistiken in MySQL 5.7 vor.

Es gibt zwei Speichertypen für MySQL-Statistiken: nicht persistente und persistente Statistiken.

1. Nicht persistente Statistiken

Nicht persistente Statistiken werden im Speicher abgelegt und gehen bei einem Neustart der Datenbank verloren. Es gibt zwei Möglichkeiten, nicht persistente Statistiken festzulegen:

Nicht persistente Statistiken werden in den folgenden Situationen automatisch aktualisiert:

Die Nachteile nicht persistenter statistischer Informationen liegen auf der Hand. Wenn nach dem Neustart der Datenbank eine große Anzahl von Tabellen mit der Aktualisierung statistischer Informationen beginnt, hat dies große Auswirkungen auf die Instanz. Daher werden derzeit persistente statistische Informationen verwendet.

2. Dauerhafte Statistiken

Ab 5.6.6 verwendet MySQL standardmäßig persistente Statistiken, d. h. INNODB_STATS_PERSISTENT=ON, und persistente Statistiken werden in den Tabellen mysql.innodb_table_stats und mysql.innodb_index_stats gespeichert.

Permanente Statistiken werden in den folgenden Situationen automatisch aktualisiert:

innodb_table_stats sind die statistischen Informationen der Tabelle und innodb_index_stats sind die statistischen Informationen des Index. Die Bedeutung jedes Felds ist wie folgt:

Um innodb_index_stats besser zu verstehen, erstellen Sie zur Veranschaulichung eine Testtabelle:

Tabelle erstellen t1 (
 a INT, b INT, c INT, d INT, e INT, f INT,
 PRIMÄRSCHLÜSSEL (a, b), SCHLÜSSEL i1 (c, d), EINZIGARTIGER SCHLÜSSEL i2uniq (e, f)
)ENGINE=INNODB;

Die geschriebenen Daten lauten wie folgt:

Um die Statistiken der T1-Tabelle anzuzeigen, konzentrieren Sie sich auf die Felder stat_name und stat_value.

Wenn tat_name=size: stat_value gibt die Anzahl der indexierten Seiten an

Wenn stat_name=n_leaf_pages: stat_value gibt die Anzahl der Blattknoten an

Wenn stat_name=n_diff_pfxNN: stat_value die Anzahl der eindeutigen Werte im Indexfeld angibt. Hier ist eine detaillierte Beschreibung:

1. n_diff_pfx01 gibt die Zahl nach „distinct“ in der ersten Spalte des Index an. Beispielsweise hat Spalte a von PRIMARY nur einen Wert 1. Wenn also index_name='PRIMARY' und stat_name='n_diff_pfx01' ist, ist stat_value=1.

2. n_diff_pfx02 gibt die Anzahl der unterschiedlichen Werte in den ersten beiden Spalten des Index an. Beispielsweise haben die Spalten e und f von i2uniq 4 Werte. Wenn also index_name = "i2uniq" und stat_name = "n_diff_pfx02", ist stat_value = 4.

3. Bei nicht eindeutigen Indizes wird der Primärschlüsselindex nach der ursprünglichen Spalte hinzugefügt. Wenn beispielsweise index_name='i1' und stat_name='n_diff_pfx03' ist, wird die Primärschlüsselspalte a nach den ursprünglichen Indexspalten c und d hinzugefügt. Das eindeutige Ergebnis von (c, d, a) ist 2.

Das Verständnis der spezifischen Bedeutungen von stat_name und stat_value kann uns dabei helfen, den Grund zu ermitteln, warum der entsprechende Index während der SQL-Ausführung nicht verwendet wird. Wenn beispielsweise der stat_value eines Index n_diff_pfxNN viel kleiner als der tatsächliche Wert ist, könnte der Abfrageoptimierer annehmen, dass der Index eine schlechte Selektivität aufweist, was zur Verwendung des falschen Index führen kann.

3. Umgang mit ungenauen statistischen Informationen

Wir haben den Ausführungsplan überprüft und festgestellt, dass der richtige Index nicht verwendet wurde. Wenn dies durch einen großen Unterschied in den statistischen Informationen in innodb_index_stats verursacht wird, können Sie wie folgt damit umgehen:

1. Aktualisieren Sie die Statistiken manuell. Beachten Sie, dass während der Ausführung eine Lesesperre hinzugefügt wird:

ANALYSIERBARER TABELLENNAME;

2. Wenn die Statistiken nach der Aktualisierung immer noch ungenau sind, sollten Sie die Anzahl der aus der Tabelle entnommenen Datenseiten erhöhen. Es gibt zwei Möglichkeiten, dies zu ändern:

a) Globale Variable INNODB_STATS_PERSISTENT_SAMPLE_PAGES, Standardwert ist 20;

b) Eine einzelne Tabelle kann die Stichprobenentnahme für diese Tabelle festlegen:

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40;

Nach dem Testen beträgt der Maximalwert von STATS_SAMPLE_PAGES hier 65535. Wenn dieser Wert überschritten wird, wird ein Fehler gemeldet.

Derzeit bietet MySQL keine Histogrammfunktion. In manchen Fällen (z. B. bei ungleichmäßiger Datenverteilung) führt das einfache Aktualisieren statistischer Informationen nicht unbedingt zu einem genauen Ausführungsplan. Die einzige Möglichkeit besteht darin, den Index über einen Indexhinweis anzugeben. Die neue Version 8.0 wird die Histogrammfunktion hinzufügen. Wir freuen uns darauf, dass MySQL immer leistungsfähiger wird!