Detaillierte Erklärung des MySQL-Ausführungsplans

Die EXPLAIN-Anweisung liefert Informationen darüber, wie MySQL eine Anweisung ausführt. EXPLAIN wird mit den Anweisungen SELECT, DELETE, INSERT, REPLACE und UPDATE verwendet.

EXPLAIN gibt eine Zeile für jede in der SELECT-Anweisung verwendete Tabelle zurück. Es listet die Tabellen in der Ausgabe in der Reihenfolge auf, in der MySQL sie beim Verarbeiten der Anweisung liest. MySQL löst alle Verknüpfungen mithilfe der Nested-Loop-Join-Methode auf. Dies bedeutet, dass MySQL eine Zeile aus der ersten Tabelle liest und dann eine passende Zeile in der zweiten Tabelle, der dritten Tabelle usw. findet. Nachdem alle Tabellen verarbeitet wurden, gibt MySQL die ausgewählten Spalten aus und durchsucht die Tabellenliste zurück, bis es eine Tabelle mit weiteren übereinstimmenden Zeilen findet. Lesen Sie die nächste Zeile aus dieser Tabelle und fahren Sie mit der Verarbeitung der nächsten Tabelle fort.

1. EXPLAIN-Ausgabespalten

Hier sind einige wichtige Spalten:

• Typ: Verbindungstyp
• mögliche Schlüssel: optionale Indizes
• Schlüssel: der während der tatsächlichen Ausführung verwendete Index
• ref: Die Spalte „ref“ zeigt, welche Spalte oder Konstante mit dem benannten Index in der vorherigen Schlüsselspalte verglichen wird, um Zeilen aus der Tabelle auszuwählen.
• Zeilen: Die Spalte „Zeilen“ gibt die Anzahl der Zeilen an, die nach Ansicht von MySQL untersucht werden müssen, um die Abfrage auszuführen.
  

2. Verbindungstyp

Die Verbindungstypen sind in der Reihenfolge vom Besten zum Schlechtesten:

System

Die Tabelle hat nur eine Zeile. Dies ist ein Sonderfall des const-Jointyps.

Konstante

Die Tabelle hat höchstens eine übereinstimmende Zeile, die zu Beginn der Abfrage gelesen wird. Da nur eine Zeile vorhanden ist, können die Werte der Spalten in dieser Zeile vom Rest des Optimierers als Konstanten behandelt werden. Const-Tabellen sind sehr schnell, da sie nur einmal gelesen werden.

const wird verwendet, wenn Sie alle Teile eines PRIMARY KEY- oder UNIQUE-Index mit einem konstanten Wert vergleichen.

Beispielsweise kann die folgende Tabelle tbl_name als Konstantentabelle behandelt werden:

SELECT * FROM tbl_name WHERE primärer_schlüssel=1;
SELECT * FROM tbl_name WHERE primärer_schlüssel_teil1=1 AND primärer_schlüssel_teil2=2;

eq_ref

Lesen Sie für jede Zeilenkombination in der vorherigen Tabelle eine Zeile aus dieser Tabelle. Abgesehen von den System- und Konstantentypen ist dies der bestmögliche Join-Typ. Verwenden Sie es, wenn alle Teile eines Indexes von Verknüpfungen verwendet werden und der Index ein PRIMARY KEY- oder UNIQUE NOT NULL-Index 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 von Spalten aus einer zuvor gelesenen Tabelle sein.

Im folgenden Beispiel kann MySQL beispielsweise den eq_ref-Join verwenden, um ref_table zu verarbeiten:

SELECT * FROM Referenztabelle, andere Tabelle
 WO ref_table.key_column=other_table.column;

SELECT * FROM Referenztabelle, andere Tabelle
 WO ref_table.key_column_part1=other_table.column UND ref_table.key_column_part2=1;

Referenz

Für jede Kombination von Zeilen aus der vorherigen Tabelle werden alle Zeilen mit passenden Indexwerten aus dieser Tabelle gelesen. Wenn der Join nur ein ganz links stehendes Schlüsselpräfix verwendet oder wenn der Schlüssel kein PRIMARY KEY oder UNIQUE-Index ist (mit anderen Worten, wenn der Join keine einzelne Zeile basierend auf dem Schlüsselwert auswählen kann), wird „ref“ verwendet. Dies ist ein guter Verknüpfungstyp, wenn der verwendete Schlüssel nur mit wenigen Zeilen übereinstimmt.

ref kann auf indizierte Spalten angewendet werden, die mit den Operatoren = oder <=> verglichen werden.

Im folgenden Beispiel kann MySQL beispielsweise Ref-Verbindungen verwenden, um ref_table zu verarbeiten:

SELECT * FROM ref_table WHERE Schlüsselspalte=Ausdruck;

SELECT * FROM Referenztabelle, andere Tabelle
 WO ref_table.key_column=other_table.column;

SELECT * FROM Referenztabelle, andere Tabelle
 WO ref_table.key_column_part1=other_table.column
 UND ref_table.key_column_part2=1;

Volltext

Ausführen eines Joins mithilfe eines FULLTEXT-Index

ref_or_null

Dieser Join-Typ ähnelt „ref“, aber MySQL sucht zusätzlich nach Zeilen, die NULL-Werte enthalten. Diese Join-Typ-Optimierung wird am häufigsten zum Auflösen von Unterabfragen verwendet.

Im folgenden Beispiel kann MySQL beispielsweise ref_or_null verwenden, um ref_table zu verarbeiten:

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

Dieser Verknüpfungstyp gibt an, dass die Index Merge-Optimierung verwendet wird. In diesem Fall enthält die Spalte „key“ in der Ausgabezeile die Liste der verwendeten Indizes und „key_len“ die längste Liste der Schlüsselteile der verwendeten Indizes.

eindeutige Unterabfrage

Dieser Typ ersetzt eq_ref durch eine IN-Unterabfrage der Form:

Wert IN (SELECT Primärschlüssel FROM Einzeltabelle WHERE some_expr)

Index-Unterabfrage

Ähnlich wie unique_subquery ersetzt es eine IN-Unterabfrage, funktioniert aber mit nicht eindeutigen Indizes in Unterabfragen der folgenden Form:

Wert IN (SELECT Schlüsselspalte FROM Einzeltabelle WHERE some_expr)

Reichweite

Rufen Sie nur die Zeilen in einem bestimmten Bereich ab und verwenden Sie den Index zum Auswählen der Zeilen. Die Schlüsselspalte in der Ausgabezeile gibt an, welcher Index verwendet wurde. key_len enthält den längsten verwendeten Schlüsselteil. Für diesen Typ ist die Ref-Spalte NULL.

Sie können den Bereich verwenden, wenn Sie eine Schlüsselspalte mithilfe der Operatoren =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE oder IN() mit einer Konstanten vergleichen:

SELECT * FROM tbl_name WHERE key_column = 10;

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name WHERE Schlüsselteil1 = 10 AND Schlüsselteil2 IN (10,20,30);

Index

Der Indexjointyp ist derselbe wie bei allen anderen. Der Unterschied besteht darin, dass der Indexjointyp den Indexbaum durchsucht. Normalerweise passiert dies nur in den folgenden zwei Situationen:

• Wenn der Index ein überdeckender Index für die Abfrage ist und verwendet werden kann, um alle in der Tabelle erforderlichen Daten abzudecken, wird nur der Indexbaum gescannt. In diesem Fall wird in der Spalte „Extra“ „Verwendeter Index“ angezeigt. Ein Nur-Index-Scan ist normalerweise schneller als ein ALL-Scan, da die Größe des Indexes normalerweise kleiner als die Tabellendaten ist.
• Führen Sie mit „Daten aus Index lesen“ einen vollständigen Tabellenscan durch, um die Datenzeilen in Indexreihenfolge zu finden. „Verwendet Index“ erscheint nicht in der Spalte „Extra“.

ALLE

Für jede Zeilenkombination aus der vorherigen Tabelle wird ein vollständiger Tabellenscan durchgeführt. Dies ist im Allgemeinen schlecht, wenn die Tabelle die erste Tabelle ist, die nicht als „const“ gekennzeichnet ist, und im Allgemeinen in allen anderen Fällen sehr schlecht. Oftmals lässt sich ALL durch das Hinzufügen von Indizes vermeiden, die das Abrufen von Zeilen aus einer Tabelle basierend auf konstanten Werten oder Spaltenwerten aus früheren Tabellen ermöglichen.

3. Zusätzliche Spalte

Im Folgenden sind einige häufige Ausgaben der Spalte „Extra“ aufgeführt:

Verwenden von Filesort

MySQL muss einen zusätzlichen Vorgang ausführen, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden. Die Sortierung erfolgt durch Iteration über alle Zeilen entsprechend dem Verknüpfungstyp und Speichern des Sortierschlüssels und eines Zeigers auf die Zeile für alle Zeilen, die der WHERE-Klausel entsprechen. Anschließend werden die Schlüssel sortiert und die Zeilen in sortierter Reihenfolge abgerufen.

Verwenden des Index

Spalteninformationen werden aus der Tabelle nur unter Verwendung der Informationen im Indexbaum abgerufen, ohne dass zusätzliche Suchvorgänge zum Lesen der tatsächlichen Zeile durchgeführt werden. Diese Strategie kann verwendet werden, wenn die Abfrage nur Spalten verwendet, die zu einem einzigen Index gehören.

Mit temporären

Um die Abfrage zu analysieren, muss MySQL eine temporäre Tabelle erstellen, in der die Ergebnisse gespeichert werden. Dies geschieht normalerweise, wenn die Abfrage GROUP BY- und ORDER BY-Klauseln enthält, die Spalten unterschiedlich darstellen.

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 beabsichtigen, alle Zeilen aus einer Tabelle abzurufen oder zu untersuchen, können in Ihrer Abfrage Fehler auftreten, wenn die zusätzlichen Werte nicht mit „where“ verwendet werden und der Verknüpfungstyp der Tabelle „all“ oder „index“ ist.

4. ORDER BY optimieren

In einigen Fällen verwendet MySQL möglicherweise einen Index, um die ORDER BY-Klausel zu erfüllen und so die zusätzliche Sortierung zu vermeiden, die bei der Durchführung eines Dateisortiervorgangs erforderlich ist.

Vorausgesetzt, es gibt einen Index für (Schlüsselteil1, Schlüsselteil2), kann die folgende Abfrage den Index verwenden, um den ORDER BY-Teil aufzulösen. Ob der Optimierer dies tatsächlich tut, hängt davon ab, ob das Lesen des Indexes effizienter ist als ein Tabellenscan, wenn auch außerhalb des Indexes gelesen werden muss.

AUSWÄHLEN * AUS t1 ORDER BY Schlüsselteil1, Schlüsselteil2;

In der obigen Anweisung verwendet die Abfrage SELECT *, wodurch möglicherweise mehr Spalten als key_part1 und key_part2 ausgewählt werden. In diesem Fall ist das Durchsuchen des gesamten Indexes und das Nachschlagen der Tabellenzeilen nach Spalten, die nicht im Index enthalten sind, möglicherweise aufwändiger als das Durchsuchen der Tabelle und Sortieren der Ergebnisse. Wenn dies der Fall ist, ist es weniger wahrscheinlich, dass der Optimierer den Index verwendet. Wenn mit SELECT * nur indizierte Spalten ausgewählt werden, wird der Index verwendet und eine Sortierung vermieden.

In der folgenden Abfrage ist key_part1 konstant, sodass alle über den Index abgerufenen Zeilen in der Reihenfolge key_part2 vorliegen und ein Index auf (key_part1, key_part2) eine Sortierung vermeiden kann, wenn die WHERE-Klausel selektiv genug ist, um einen Indexbereichsscan kostengünstiger zu machen als einen Tabellenscan:

SELECT * FROM t1 WHERE key_part1 = konstant ORDER BY key_part2;

Oben finden Sie den detaillierten Inhalt des MySQL-Ausführungsplans. Weitere Informationen zum MySQL-Ausführungsplan finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!