Detaillierte Analyse der MySQL-Indizes

Vorwort

Wir wissen, dass die Indexauswahl Aufgabe der Optimierungsphase ist, doch der Optimierer ist nicht allmächtig und kann unter Umständen den falschen Index auswählen. Im Allgemeinen berücksichtigt der Optimierer bei der Auswahl eines Indexes die folgenden Faktoren: die Anzahl der zu scannenden Zeilen, ob sortiert werden soll und ob eine temporäre Tabelle verwendet werden soll.

Verwenden Sie „explain“, um SQL zu analysieren

„explain“ ist ein guter Selbsttestbefehl. Die häufige Verwendung von „explain“ kann uns helfen, sinnvollere SQL-Anweisungen zu schreiben und sinnvollere Indizes zu erstellen:

mysql> erklären Sie „select * from t“, wobei (a zwischen 1 und 1000) und (b zwischen 50000 und 100000) sortieren nach b, Limit 1;
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+----------+----------+----------+------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+----------+----------+----------+------------------------------------+
| 1 | SIMPLE | t | NULL | Bereich | a,b | b | 5 | NULL | 50223 | 1,00 | Indexbedingung verwenden; Where verwenden |
+----+----------+----------+---------+-------+---------------+-----------+---------+---------+------+---------+----------+----------+----------+------------------------------------+
1 Zeile im Satz, 1 Warnung (0,01 Sek.)

In:

Tabellenfeld: gibt an, um welche Tabelle es sich handelt;
Typfeld: System, const, eq_reg, ref, Bereich, Index, alle. Im Allgemeinen sollte es über dem Bereichsniveau liegen.

System, const: Die abgefragte Variable kann in eine Konstante umgewandelt werden, z. B. id = 1; id ist der Primärschlüssel oder eindeutige Schlüssel;
eq_ref: greift auf den Index zu und gibt die Daten einer einzelnen Zeile zurück. Erscheint normalerweise beim Joinen. Der für die Abfrage verwendete Index ist der Primärschlüssel oder eindeutige Schlüssel.
ref: greift auf den Index zu und gibt einen bestimmten Wert (möglicherweise mehrere Zeilen) zurück, tritt normalerweise auf, wenn = verwendet wird;
Bereich: Verwenden Sie einen Index, um Zeileninformationen innerhalb eines Bereichs zurückzugeben, z. B. mit >, <, zwischen
Index: Scannen Sie die gesamte Tabelle in der Reihenfolge des Index. Obwohl ein Index vorhanden ist und keine Sortierung erforderlich ist, muss die gesamte Tabelle gescannt werden.
alle: vollständiger Tabellenscan

Schlüsselfeld: der tatsächlich verwendete Index;

Feld key_len: die Länge des verwendeten Indexes (je kürzer, desto besser, ohne an Genauigkeit zu verlieren);

Ref-Feld: zeigt, welche Spalte des Index verwendet wird;

Zeilenfeld: die Anzahl der Datenzeilen, die MySQL zum Abrufen als notwendig erachtet;

Zusatzfeld: Zusatzinformationen zur Abfrage, im Wesentlichen Folgendes:

using index: Verwenden eines Indexes
using where: wobei die Bedingung verwendet wird
using tmporary: Verwenden Sie eine temporäre Tabelle, um die aktuelle Abfrage zu verarbeiten
Verwenden von Filesort: Verwenden Sie zusätzliche Sortierung, z. B. hat das Bestellfeld keinen Index
Bereich für jeden Datensatz geprüft (Indexzuordnung: N): Kein Index verfügbar
Durch die Verwendung des Indexes für group-by: table name können alle für die Gruppierung benötigten Daten im Index gefunden werden, ohne die eigentliche Tabelle abzufragen

Wenn Sie auf die Verwendung temporärer Dateien und die Verwendung von Filesort stoßen, müssen Sie im Allgemeinen nach Möglichkeiten zur Optimierung suchen, da der Index nicht verwendet wird.

Wie berechnet MySQL die Anzahl der abzurufenden Zeilen?

In der Praxis ist die von MySQL gezählte Anzahl der gescannten Zeilen kein exakter Wert und kann manchmal weit davon abweichen. Die Anzahl der gescannten Zeilen wird basierend auf der Kardinalität des Index berechnet.

In MySQL wird die Indexkardinalität durch Stichprobenstatistiken ermittelt: Das System wählt standardmäßig N Datenseiten aus, berechnet den Durchschnitt verschiedener Werte auf der Datenseite und multipliziert ihn dann mit der Anzahl der Indexseiten, um die Kardinalität zu erhalten. Darüber hinaus löst MySQL den Vorgang zum Wiederherstellen der Indexstatistik aus, wenn die Anzahl der geänderten Datenzeilen 1/M überschreitet.

In MySQL gibt es zwei Möglichkeiten zum Speichern von Indexstatistiken, die durch Festlegen des Parameters innodb_stats_persistent ausgewählt werden können:

Wenn diese Option aktiviert ist, werden die Statistiken dauerhaft gespeichert. Derzeit beträgt der Standardwert N 20 und M 10.

Wenn die Option deaktiviert ist, werden Statistiken nur im Speicher gespeichert. Derzeit ist der Standardwert für N 8 und für M 16.

Im Allgemeinen gibt es keinen großen Unterschied zwischen den aus der Kardinalitätsstatistik erhaltenen Daten und der tatsächlichen Zeilenanzahl. Bei Datentabellen, in denen häufig Daten gelöscht und hinzugefügt werden, kann es jedoch vorkommen, dass die Datentabelle 100.000 Datensätze enthält, die Kardinalitätsstatistik jedoch 200.000 anzeigt. Dies kann durch MVCC verursacht werden. Da die InnoDB-Transaktionsunterstützung von MySQL mehrere Datenversionen verwalten muss, ist es möglich, dass einige Transaktionen noch nicht beendet sind und immer noch Daten verwenden, die vor langer Zeit gelöscht wurden, was dazu führt, dass der gelöschte Datenspeicherplatz nicht freigegeben werden kann, während die neu hinzugefügten Daten neuen Speicherplatz freigegeben haben. In diesem Fall ist die Anzahl der Datenseiten in der Kardinalitätsstatistik möglicherweise falsch, was zu einem großen Fehler führt.

Eine gute Möglichkeit, dies zu korrigieren, besteht darin, den Befehl „Analyze Table Table Name“ auszuführen, der zum Neuberechnen der Indexinformationen verwendet wird.

Was sollen wir tun, wenn wir den falschen Index wählen?

Wenn wir die erforderlichen Indizes richtig erstellen, wählt der Optimierer in den meisten Fällen nicht den falschen Index. Was sollten wir tun, wenn wir auf die Situation stoßen, dass der Index falsch gewählt wurde?

1. Verwenden Sie „Force Index“, um die Verwendung eines bestimmten Indexes zu erzwingen.

2. Ändern Sie Ihre Denkweise und optimieren Sie die SQL-Anweisung, sodass Sie den Index verwenden können, den Sie benötigen.

3. Erstellen Sie einen geeigneteren Index oder löschen Sie einen ungeeigneten Index, der versehentlich verwendet wurde. (Manchmal ist dieser Index möglicherweise wirklich redundant und nicht optimal und der Optimierer verwendet ihn zufällig).

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.