Detaillierte Erläuterung der Verwendung von MySQL Explain (Analyseindex)

EXPLAIN zeigt, wie MySQL Indizes verwendet, um Auswahlanweisungen zu verarbeiten und Tabellen zu verknüpfen. Es kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben.

Um es zu verwenden, fügen Sie einfach „explain“ vor der Select-Anweisung hinzu, zum Beispiel:

Erläutern Sie „Select * from statuses_status where id=11“;

Erklärung der Spalte „Explain“

Tabelle: Zeigt an, um welche Tabelle es sich bei den Daten in dieser Zeile handelt

Typ: Dies ist die wichtige Spalte und zeigt, welche Art von Verbindung verwendet wird. Die Join-Typen vom besten zum schlechtesten sind const, eq_reg, ref, range, indexhe und alle

Possible_keys: Zeigt die Indizes an, die auf diese Tabelle angewendet werden können. Wenn leer, ist kein Index möglich. Sie können aus der Where-Klausel für die jeweilige Domäne eine entsprechende Anweisung auswählen

Schlüssel: Der tatsächlich verwendete Index. Wenn null, wird kein Index verwendet. In seltenen Fällen kann es sein, dass MySQL einen Index wählt, der nicht optimal genug ist. In diesem Fall können Sie „use index (indexname)“ in der Select-Anweisung verwenden, um die Verwendung eines Indexes zu erzwingen, oder „ignore index (indexname)“ verwenden, um MySQL zu zwingen, den Index zu ignorieren.

key_len: Die Länge des verwendeten Indexes. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

ref: zeigt an, welche Spalte des Index verwendet wird, wenn möglich eine Konstante

Zeilen: Die Anzahl der Zeilen, die MySQL als notwendig erachtet, um die angeforderten Daten zurückzugeben

extra: Zusätzliche Informationen darüber, wie MySQL die Abfrage analysiert. Dies wird in Tabelle 4.3 erläutert. Die hier gezeigten schlechten Beispiele beziehen sich jedoch auf die Verwendung von temporären Methoden und von Filesort. Dies bedeutet, dass MySQL den Index überhaupt nicht verwenden kann. Das Abrufen wird daher sehr langsam sein.

Die Bedeutung der Beschreibung, die in der zusätzlichen Spalte zurückgegeben wird

Eindeutig: Sobald MySQL eine Zeile findet, die mit der Zeile im Join übereinstimmt, wird die Suche beendet.

nicht vorhanden: MySQL optimiert den Left Join. Sobald es eine Zeile findet, die den Left Join-Kriterien entspricht, wird nicht mehr gesucht.

Für jeden Datensatz wird der Bereich geprüft (Indexzuordnung: #): Es wurde kein idealer Index gefunden, daher prüft MySQL für jede Zeilenkombination aus den vorherigen Tabellen, welcher Index verwendet werden soll, und verwendet ihn, um Zeilen aus der Tabelle zurückzugeben. Dies ist eine der langsamsten Verbindungen über den Index

using filesort: Wenn Sie dies sehen, muss die Abfrage optimiert werden. MySQL muss einen zusätzlichen Schritt ausführen, um herauszufinden, wie die zurückgegebenen Zeilen sortiert werden sollen. Es sortiert alle Zeilen basierend auf dem Verknüpfungstyp und speichert den Sortierschlüsselwert und die Zeilenzeiger für alle Zeilen, die der Bedingung entsprechen.

mit Index: Spaltendaten werden aus der Tabelle zurückgegeben, indem nur die Informationen im Index verwendet werden, ohne dass diese tatsächlich gelesen werden. Dies geschieht, wenn alle angeforderten Spalten für die Tabelle Teil desselben Index sind.

Wenn Sie dies sehen, muss die Abfrage optimiert werden. Hier muss MySQL eine temporäre Tabelle zum Speichern der Ergebnisse erstellen. Dies geschieht normalerweise, wenn Sie eine Sortierung nach auf einen anderen Spaltensatz anwenden, statt einer Gruppierung nach.

where used (wo verwendet) Die Where-Klausel wird verwendet, um einzuschränken, welche Zeilen mit der nächsten Tabelle abgeglichen oder an den Benutzer zurückgegeben werden. Dies kann passieren, wenn Sie nicht alle Zeilen in der Tabelle zurückgeben möchten und der Verknüpfungstyp „alle“ oder „Index“ ist oder wenn ein Problem mit der Abfrage vorliegt. Erklärung der verschiedenen Verknüpfungstypen (sortiert nach Effizienz)

Die Systemtabelle hat nur eine Zeile: Systemtabelle. Dies ist ein Sonderfall des const-Verbindungstyps

const: Der Maximalwert eines Datensatzes in der Tabelle, der dieser Abfrage entsprechen kann (der Index kann ein Primärschlüssel oder ein eindeutiger Index sein). Da nur eine Zeile vorhanden ist, ist dieser Wert tatsächlich eine Konstante, da MySQL den Wert zuerst liest und ihn dann als Konstante behandelt.

eq_ref: Bei einer Verbindung liest MySQL für jeden Datensatz in der Abfrage einen Datensatz aus der vorherigen Tabelle. Wird verwendet, wenn die Abfrage einen Index für den Primärschlüssel oder eindeutigen Schlüssel verwendet.

ref: Dieser Verknüpfungstyp tritt nur auf, wenn die Abfrage einen Schlüssel verwendet, der kein eindeutiger oder Primärschlüssel ist oder Teil eines dieser Typen ist (z. B. bei Verwendung eines ganz linken Präfixes). Bei jedem Zeilenjoin mit den vorherigen Tabellen werden alle Datensätze aus der Tabelle gelesen. Dieser Typ hängt stark davon ab, wie viele Datensätze mit dem Index abgeglichen werden – je weniger, desto besser.

Bereich: Dieser Verknüpfungstyp verwendet einen Index, um einen Zeilenbereich zurückzugeben, z. B. wenn Sie > oder < verwenden, um etwas zu finden.

Index: Dieser Join-Typ führt einen vollständigen Scan aller Datensätze in der vorherigen Tabelle durch (besser als alle, da der Index im Allgemeinen kleiner als die Tabellendaten ist).

all: Dieser Verknüpfungstyp führt einen vollständigen Scan aller vorherigen Datensätze durch. Dies ist im Allgemeinen schlecht und sollte vermieden werden.

Analyse Indexanalyse

Hier analysieren wir jedes Erläuterungsfeld im Detail, um Ihnen bei der Analyse zu helfen, ob das von Ihnen geschriebene SQL die Indizes optimal nutzt.

Zuerst kommt select_type: Auswahlabfragen werden in einfache und komplexe Typen unterteilt

Komplexe Typen werden in Unterabfragen (Subquery) und aus Listen mit Unterabfragen (DrivenD) unterteilt.

einfach:

angetrieben:

Eine detaillierte Einführung in den Typ:

System, const, eq_ref, ref, Bereich, Index, alle
all : vollständiger Tabellenscan
index : Scannen Sie in Indexreihenfolge. Lesen Sie zuerst den Index und dann die eigentliche Zeile. Das Ergebnis ist immer noch ein vollständiger Tabellenscan. Der Hauptvorteil besteht darin, dass das Sortieren vermieden wird. Weil der Index sortiert ist.
range : Scan in Form eines Bereichs.

Erklären Sie select * from a where a_id > 1\G

ref : nicht eindeutiger Indexzugriff (nur normaler Index)

Tabelle erstellen a(a_id int ungleich null, Schlüssel(a_id));
in Werte einfügen(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> erklären select * from a where a_id=1\G

eq_ref : Eindeutige Indexsuche verwenden (Primärschlüssel oder eindeutiger Index)
const : Konstante Abfrage

Während des gesamten Abfragevorgangs enthält diese Tabelle höchstens eine übereinstimmende Zeile. Wenn beispielsweise die Primärschlüssel-ID = 1 ist, gibt es definitiv nur eine Zeile. Die erforderlichen Ergebnisse können erzielt werden, indem die Tabellendaten nur einmal gelesen werden. Die Tabellendaten werden gelesen, wenn der Ausführungsplan zerlegt wird.

Wenn das Ergebnis nicht eins ist, wird es zu anderen Typen wie Index oder Bereich

system : Systemabfrage

null : Das Ergebnis wurde während des Optimierungsprozesses erzielt und auf die Tabelle oder den Index wird nicht zugegriffen.

possible_keys : mögliche zu verwendende Indizes

key : der tatsächlich verwendete Index

key_line : Die maximal mögliche Länge des Indexfeldes

ref :

Gibt die Suchmethode für den für die Schlüsselspalte ausgewählten Index an. Zu den üblichen Werten gehören const, func, NULL und bestimmte Feldnamen. Wenn die Schlüsselspalte NULL ist, das heißt wenn der Index nicht verwendet wird, ist dieser Wert entsprechend auch NULL.

rows : Geschätzte Anzahl der zu scannenden Zeilen

Extra : Zeigt zusätzliche Informationen neben den oben genannten Informationen an

Verwenden des Index

Diese Abfrage verwendet einen überdeckenden Index, was bedeutet, dass die Ergebnisse über den Index zurückgegeben werden können, ohne auf die Tabelle zuzugreifen.

Wenn „Index verwenden“ nicht angezeigt wird, bedeutet dies, dass die Tabellendaten gelesen wurden.

Verwenden von „where“

Gibt an, dass der MySQL-Server nach dem Empfang der Zeile von der Speicher-Engine eine „Nachfilterung“ durchführt. Die sogenannte „Nachfilterung“ besteht darin, zuerst die gesamte Datenzeile zu lesen und dann zu prüfen, ob diese Zeile die Bedingungen der Where-Klausel erfüllt. Wenn sie die Bedingungen erfüllt, wird sie beibehalten, und wenn sie die Bedingungen nicht erfüllt, wird sie verworfen. Da die Prüfung nach dem Lesen der Zeile erfolgt, wird sie als Nachfilterung bezeichnet.

Mit temporären

Verwenden temporärer Tabellen

Erstellen Sie eine Tabelle und fügen Sie Daten ein:

Tabelle erstellen a(a_id int, b_id int);
in Werte einfügen(1,1),(1,1),(2,1),(2,2),(3,1);
mysql> erklären select distinct a_id from a\G

Extra : Verwendung von temporären

MySQL verwendet temporäre Tabellen, um unterschiedliche Operationen zu implementieren.

Verwenden von Filesort

Wenn die von der Abfrage geforderte Reihenfolge mit der Reihenfolge des verwendeten Index übereinstimmt, werden die Ergebnisse gelesen und in der Reihenfolge des Index zurückgegeben, da der Index sortiert ist. Andernfalls müssen die Ergebnisse nach dem Abrufen in der von der Abfrage geforderten Reihenfolge sortiert werden. Zu diesem Zeitpunkt wird „Dateisortierung verwenden“ angezeigt.

Wählen Sie * aus einer Reihenfolge nach ID;

Für Spalten ohne Indexe wird Filesort angezeigt, wenn Order by ausgeführt wird.