So verwenden Sie den EXPLAIN-Befehl in SQL

Bei unserer täglichen Arbeit führen wir manchmal langsame Abfragen aus, um einige SQL-Anweisungen aufzuzeichnen, deren Ausführung lange dauert. Das Auffinden dieser SQL-Anweisungen bedeutet nicht, dass die Arbeit erledigt ist. Manchmal verwenden wir häufig den Befehl „explain“, um den Ausführungsplan dieser SQL-Anweisungen anzuzeigen und festzustellen, ob die SQL-Anweisung einen Index verwendet und ob ein vollständiger Tabellenscan durchgeführt wird. Dies kann über den Befehl „explain“ angezeigt werden. Wir verfügen also über ein tiefes Verständnis des kostenbasierten Optimierers von MySQL und können auch viele Einzelheiten zu den Zugriffsstrategien erhalten, die vom Optimierer in Betracht gezogen werden können, und dazu, welche Strategie vom Optimierer voraussichtlich beim Ausführen von SQL-Anweisungen übernommen wird.

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:

wie:

Erklären Sie, dass Sie Nachname und Vorname in der Form a,b auswählen, wobei a.id=b.id 

1. ERKLÄREN Sie tbl_name

EXPLAIN tbl_name ist ein Synonym für DESCRIBE tbl_name oder SHOW COLUMNS FROM tbl_name.

2. ERKLÄREN [ERWEITERT] SELECT select_options

Wenn Sie vor einer SELECT-Anweisung das Schlüsselwort EXPLAIN platzieren, erläutert MySQL die Verarbeitung der SELECT-Anweisung und gibt Aufschluss darüber, wie und in welcher Reihenfolge die Tabellen verknüpft werden.

Mithilfe von EXPLAIN können Sie erkennen, wann Sie der Tabelle einen Index hinzufügen müssen, um ein schnelleres SELECT zu erhalten, das den Index zum Suchen von Datensätzen verwendet.

Sie können außerdem erkennen, ob der Optimierer die Tabellen in optimaler Reihenfolge verknüpft hat. Um den Optimierer zu zwingen, eine SELECT-Anweisung in der Reihenfolge zu verknüpfen, in der die Tabellen benannt sind, sollte die Anweisung mit STRAIGHT_JOIN und nicht nur mit SELECT beginnen.

EXPLAIN gibt eine Zeile für jede in einer SELECT-Anweisung verwendete Tabelle zurück. Die Tabellen werden in der Reihenfolge aufgelistet, in der sie von MySQL bei der Verarbeitung der Abfrage gelesen werden. MySQL löst alle Verknüpfungen mithilfe eines Single-Sweep-Multi-Join-Ansatzes auf. Dies bedeutet, dass MySQL eine Zeile aus der ersten Tabelle liest, dann eine passende Zeile in der zweiten Tabelle findet, dann in der dritten Tabelle und so weiter. Wenn alle Tabellen verarbeitet wurden, gibt es die ausgewählten Spalten aus und kehrt zur Tabellenliste zurück, bis es eine Tabelle mit weiteren übereinstimmenden Zeilen findet. Lesen Sie die nächste Zeile aus der Tabelle und fahren Sie mit der Verarbeitung der nächsten Tabelle fort.

Wenn das Schlüsselwort EXTENDED verwendet wird, erzeugt EXPLAIN zusätzliche Informationen, die mit SHOW WARNINGS angezeigt werden können. Diese Informationen zeigen, was der Optimierer tut, nachdem er die Tabellen- und Spaltennamen in der SELECT-Anweisung qualifiziert und die Optimierungsregeln neu geschrieben und ausgeführt hat, und können weitere Kommentare zum Optimierungsprozess enthalten.

Jede Ausgabezeile von EXPLAIN enthält Informationen zu einer Tabelle und besteht aus den folgenden Spalten:

id: SELECT-Kennung. Dies ist die Abfragesequenznummer für die SELECT-Anweisung.

select_type: SELECT-Typ.

1. SIMPLE: Einfaches SELECT (ohne UNION oder Unterabfrage)
2. PRIMARY: Das äußerste SELECT
3. UNION: Die zweite oder nachfolgende SELECT-Anweisung in einer UNION
4. DEPENDENT UNION: Die zweite oder nachfolgende SELECT-Anweisung in einer UNION hängt von der äußeren Abfrage ab
5. UNION-ERGEBNIS: Das Ergebnis von UNION
6. SUBQUERY: Die erste SELECT-Anweisung in einer Unterabfrage
7. ABHÄNGIGE UNTERABFRAGE: Die erste SELECT-Anweisung in einer Unterabfrage, die von der äußeren Abfrage abhängt.
8. DERIVED: SELECT der exportierten Tabelle (Unterabfrage in der FROM-Klausel)
   

Tabelle: Tabellenname

Typ: Verbindungstyp

1. System: Die Tabelle hat nur eine Zeile (= Systemtabelle). Dies ist ein Sonderfall des const-Jointyps.
2. const: Die Tabelle hat höchstens eine passende Zeile, die beim Starten der Abfrage gelesen wird. Da nur eine Zeile vorhanden ist, können die Spaltenwerte in dieser Zeile vom Rest des Optimierers als Konstanten betrachtet werden. const wird verwendet, wenn alle Teile eines PRIMARY KEY- oder UNIQUE-Index mit konstanten Werten verglichen werden.
3. eq_ref: Lesen Sie für jede Zeilenkombination aus der vorherigen Tabelle eine Zeile aus dieser Tabelle. Dies ist wahrscheinlich der beste Join-Typ, mit Ausnahme von Konstantentypen. Es wird verwendet, wenn alle Teile eines Index vom Join verwendet werden und der Index UNIQUE oder PRIMARY KEY ist. eq_ref kann mit indizierten Spalten verwendet werden, die mit dem Operator „=“ verglichen werden. Der Vergleichswert kann eine Konstante oder ein Ausdruck unter Verwendung einer zuvor gelesenen Spalte einer Tabelle sein.
4. ref: Für jede Kombination von Zeilen aus den vorherigen Tabellen werden alle Zeilen mit übereinstimmenden Indexwerten aus dieser Tabelle gelesen. Wenn der Join nur ein ganz linkes Präfix des Schlüssels verwendet oder wenn der Schlüssel weder UNIQUE noch PRIMARY KEY ist (mit anderen Worten, wenn der Join keine einzelne Zeile basierend auf dem Schlüssel auswählen kann), verwenden Sie „ref“. Dieser Verknüpfungstyp ist gut, wenn die verwendeten Schlüssel nur mit einer kleinen Anzahl von Zeilen übereinstimmen. ref kann mit indizierten Spalten unter Verwendung der Operatoren = oder <=> verwendet werden.
5. ref_or_null: Dieser Join-Typ ist wie ref, bietet aber zusätzlich die Möglichkeit, dass MySQL gezielt nach Zeilen mit NULL-Werten suchen kann. Diese Join-Typ-Optimierung wird häufig zum Auflösen von Unterabfragen verwendet.
6. index_merge: Dieser Verknüpfungstyp gibt an, dass die Indexzusammenführungsoptimierungsmethode verwendet wird. In diesem Fall enthält die Spalte „key“ die Liste der verwendeten Indizes und „key_len“ das längste Schlüsselelement der verwendeten Indizes.
7. unique_subquery: Dieser Typ ersetzt den Verweis auf die IN-Unterabfrage der folgenden Form: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery ist eine Index-Lookup-Funktion, die die Unterabfrage vollständig ersetzen kann und effizienter ist.
8. index_subquery: Dieser Verknüpfungstyp ähnelt unique_subquery. Kann IN-Unterabfragen ersetzen, aber nur für nicht eindeutige Indizes in Unterabfragen der folgenden Form: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. Bereich: Rufen Sie nur die Zeilen in einem bestimmten Bereich ab und verwenden Sie einen Index zum Auswählen der Zeilen. In der Schlüsselspalte ist zu sehen, welcher Index verwendet wurde. key_len enthält das längste Schlüsselelement des verwendeten Index. Die Ref-Spalte ist in diesem Typ NULL. Sie können den Bereich verwenden, wenn Sie die Operatoren =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN oder IN nutzen, um Schlüsselwortspalten mit Konstanten zu vergleichen.
10. Index: Dieser Verknüpfungstyp ist derselbe wie ALL, außer dass nur der Indexbaum gescannt wird. Dies ist normalerweise schneller als ALL, da Indexdateien normalerweise kleiner als Datendateien sind.
11. alle: Für jede Zeilenkombination aus den vorherigen Tabellen wird ein vollständiger Tabellenscan durchgeführt. Dies ist normalerweise schlecht, wenn die Tabelle die erste Tabelle ist, die nicht als „const“ markiert ist, und ist in anderen Fällen normalerweise sehr schlecht. Oft können Sie statt ALL weitere Indizes hinzufügen, sodass Zeilen basierend auf konstanten Werten oder Spaltenwerten in der vorherigen Tabelle abgerufen werden können.
    

Possible_keys: Die Spalte Possible_keys gibt an, welchen Index MySQL verwenden kann, um Zeilen in der Tabelle zu finden. Beachten Sie, dass diese Spalte völlig unabhängig von der Reihenfolge der in der EXPLAIN-Ausgabe angezeigten Tabellen ist. Dies bedeutet, dass einige der Schlüssel in „possible_keys“ in der Reihenfolge, in der die Tabelle generiert wird, nicht verwendet werden können.

Schlüssel: In der Spalte „Schlüssel“ wird der Schlüssel (Index) angezeigt, für dessen Verwendung sich MySQL entschieden hat. Wenn kein Index ausgewählt wurde, ist der Schlüssel NULL. Um MySQL zu zwingen, den Index der Spalte „possible_keys“ zu verwenden oder zu ignorieren, verwenden Sie in Ihrer Abfrage FORCE INDEX, USE INDEX oder IGNORE INDEX.

key_len: Die Spalte key_len zeigt die von MySQL zu verwendende Schlüssellänge. Wenn der Schlüssel NULL ist, ist die Länge NULL. Beachten Sie, dass wir mit dem key_len-Wert bestimmen können, wie viele Teile eines mehrteiligen Schlüsselworts MySQL tatsächlich verwendet.

ref: Die Spalte „ref“ zeigt, welche Spalte oder Konstante zusammen mit dem Schlüssel verwendet wird, um Zeilen aus der Tabelle auszuwählen.

Zeilen: Die Spalte „Zeilen“ zeigt die Anzahl der Zeilen, die MySQL seiner Meinung nach untersuchen muss, um die Abfrage auszuführen.

Extra: Diese Spalte enthält detaillierte Informationen darüber, wie MySQL die Abfrage gelöst hat.

1. Distinct: Nachdem MySQL die erste passende Zeile gefunden hat, wird die Suche nach weiteren Zeilen für die aktuelle Zeilenkombination beendet.
2. Nicht vorhanden: MySQL kann eine LEFT JOIN-Optimierung für die Abfrage durchführen. Nachdem eine Zeile gefunden wurde, die den LEFT JOIN-Kriterien entspricht, werden keine weiteren Zeilen in der Tabelle auf die vorherige Zeilenkombination geprüft.
3. Für jeden Datensatz wird der Bereich geprüft (Indexzuordnung: #): MySQL hat keinen geeigneten Index zur Verwendung gefunden, hat jedoch festgestellt, dass ein Teilindex verwendet werden kann, wenn die Spaltenwerte aus der vorherigen Tabelle bekannt sind. Für jede Kombination von Zeilen aus den vorhergehenden Tabellen prüft MySQL, ob es die Zeile mit der Zugriffsmethode „Range“ oder „index_merge“ abrufen kann.
4. Verwenden von Filesort: MySQL benötigt einen zusätzlichen Durchlauf, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden. Die Sortierung erfolgt, indem alle Zeilen entsprechend dem Verknüpfungstyp durchgegangen werden und der Sortierschlüssel und die Zeilenzeiger für alle Zeilen gespeichert werden, die der WHERE-Klausel entsprechen. Anschließend werden die Schlüssel sortiert und die Zeilen in sortierter Reihenfolge abgerufen.
5. Verwenden des Index: Rufen Sie Spalteninformationen aus einer Tabelle ab, indem Sie nur die Informationen im Indexbaum verwenden, ohne weitere Suche durchzuführen, um die tatsächlichen Zeilen zu lesen. Diese Strategie kann verwendet werden, wenn die Abfrage nur Spalten verwendet, die Teil eines einzelnen Indexes sind.
6. Temporäre Tabelle verwenden: Um die Abfrage aufzulösen, muss MySQL eine temporäre Tabelle erstellen, um die Ergebnisse zu speichern. Ein typischer Fall ist, wenn die Abfrage GROUP BY- und ORDER BY-Klauseln enthält, die Spalten in unterschiedlichen Fällen auflisten.
7. Verwenden von „where“: Mit der WHERE-Klausel wird eingeschränkt, welche Zeilen der nächsten Tabelle zugeordnet oder an den Client gesendet werden. Sofern Sie nicht ausdrücklich alle Zeilen aus der Tabelle anfordern oder prüfen, kann Ihre Abfrage Fehler enthalten, wenn der zusätzliche Wert nicht „Using where“ lautet und der Verknüpfungstyp der Tabelle „ALL“ oder „Index“ ist.
8. Verwenden von sort_union(...), Verwenden von union(...), Verwenden von intersect(...): Diese Funktionen veranschaulichen, wie Index-Scans für den Join-Typ index_merge zusammengeführt werden.
9. Index für Group-By verwenden: Ähnlich wie die Methode „Index verwenden“ für den Zugriff auf eine Tabelle bedeutet „Index verwenden für Group-By“, dass MySQL einen Index gefunden hat, mit dem alle Spalten einer GROUP BY- oder DISTINCT-Abfrage abgefragt werden können, ohne dass zusätzlich die Festplatte nach der eigentlichen Tabelle durchsucht werden muss. Außerdem wird der Index auf die effizienteste Weise genutzt, sodass für jede Gruppe nur eine kleine Anzahl von Indexeinträgen gelesen wird.
   

Durch Multiplikation aller Werte in der Zeilenspalte der EXPLAIN-Ausgabe können Sie einen Hinweis darauf erhalten, wie ein Join funktionieren wird. Dies sollte Ihnen ungefähr sagen, wie viele Zeilen MySQL untersuchen musste, um die Abfrage auszuführen. Dieses Produkt wird auch verwendet, um zu bestimmen, welche SELECT-Anweisung für mehrere Tabellen ausgeführt werden soll, wenn Sie die Variable max_join_size verwenden, um die Abfrage einzuschränken.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Verwendung des EXPLAIN-Befehls in SQL. Weitere Informationen zur Verwendung des SQL EXPLAIN-Befehls finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!