Detaillierte Analyse des Explain-Ausführungsplans in MySQL

Vorwort

Das Schreiben effizienter SQL-Anweisungen ist untrennbar mit der Analyse des Ausführungsplans verbunden. Was der Ausführungsplan ist und wie man effizientes SQL schreibt, wird in diesem Artikel nacheinander erläutert.

Ausführungsplan

Der Ausführungsplan ist eine Abfragelösung, die von der Datenbank auf Grundlage der SQL-Anweisung und der statistischen Informationen der zugehörigen Tabellen erstellt wird. Diese Lösung wird automatisch vom Abfrageoptimierer analysiert und generiert.

Mit dem Schlüsselwort „explain“ können Sie die Ausführung von SQL-Abfrageanweisungen durch den Optimierer simulieren, sodass Sie verstehen, wie MySQL Ihre SQL-Anweisungen verarbeitet, die Leistungsengpässe Ihrer Select-Anweisungen oder Tabellenstrukturen analysieren, uns die Gründe für die geringe Effizienz von Select mitteilen und so unsere Abfragen verbessern können.

Das Ergebnis von Explain ist wie folgt:

Nachfolgend finden Sie eine detaillierte Einführung in jede Spalte. Die wichtigsten sind ID, Typ, Schlüssel, Zeilen und Extra.

Ausweis

• Die Zahl in der ID-Spalte ist die Sequenznummer der Auswahl, die auch als Kennung der SQL-Ausführungsreihenfolge verstanden werden kann. Es gibt so viele IDs wie Auswahlen.
  • Unterschiedliche ID-Werte: Wenn es sich nur um eine Abfrage handelt, erhöht sich die ID-Nummer. Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.
  • Die ID-Werte sind dieselben: von oben nach unten ausführen;
  • Die ID-Spalte ist null: Dies bedeutet, dass es sich um einen Ergebnisset handelt und dieser nicht für Abfragen verwendet werden muss.

Wählen Sie Typ

Der Abfragetyp wird hauptsächlich verwendet, um zwischen allgemeinen Abfragen, gemeinsamen Abfragen, Unterabfragen und anderen komplexen Abfragen zu unterscheiden.

einfach: Gibt an, dass die Abfrage keine Vereinigungsoperationen oder Unterabfragen enthält. Der select_type der äußersten Abfrage ist einfach und es gibt nur einen;

 Erläutern Sie „Select * from t3 where id=3952602“;

primär: erfordert eine Vereinigungsoperation oder eine Auswahl mit Unterabfragen. Der Auswahltyp der äußersten Abfrage ist primär, und es gibt nur einen;

erkläre select * from (select * from t3 where id=3952602) a;

Abgeleitet: Unterabfragen, die in der „Von“-Liste erscheinen, werden auch abgeleitete Tabellen genannt; MySQL führt diese Unterabfragen rekursiv aus und platziert die Ergebnisse in einer temporären Tabelle.

 erkläre select * from (select * from t3 where id=3952602) a;

Unterabfrage: Mit Ausnahme der in der From-Klausel enthaltenen Unterabfrage kann jede Unterabfrage, die an anderer Stelle erscheint, eine Unterabfrage sein.

erläutern Sie „select * from t3 where id = (select id from t3 whereid=3952602);“

Union: Wenn die zweite Auswahl nach der Union erscheint, wird sie als Union markiert. Wenn die Union in einer Unterabfrage in der From-Klausel enthalten ist, wird die äußere Auswahl als abgeleitet markiert.

Erklären Sie „Select * from t3“, wobei id=3952602 und „Union all“ „Select * from t3“ lautet.

Union-Ergebnis: Die Auswahl, die das Ergebnis aus der Union-Tabelle erhält. Da es nicht an der Abfrage teilnehmen muss, ist das ID-Feld null.

 Erklären Sie „Select * from t3“, wobei id=3952602 und „Union all“ „Select * from t3“ lautet.

Abhängige Union: Erscheint wie Union in einer Union- oder Union-All-Anweisung, aber diese Abfrage wird von der äußeren Abfrage beeinflusst;

Abhängige Unterabfrage: Ähnlich wie bei der abhängigen Vereinigung wird die erste SELECT-Anweisung in der Unterabfrage von der Abfrage der äußeren Tabelle beeinflusst.

Tisch

Gibt an, auf welche Tabelle eine EXPLAIN-Zeile zugreift.

• Wenn die Abfrage einen Alias ​​verwendet, wird der Alias ​​hier angezeigt.
• Wenn keine Operation an der Datentabelle erforderlich ist, wird dies als Null angezeigt.
• Wenn es in spitze Klammern eingeschlossen ist, bedeutet dies, dass es sich um eine temporäre Tabelle handelt, und das N dahinter ist die ID im Ausführungsplan, die angibt, dass das Ergebnis von dieser Abfrage stammt.
• Wenn es in spitzen Klammern steht, ähnelt es und ist ebenfalls eine temporäre Tabelle, was darauf hinweist, dass dieses Ergebnis aus dem Ergebnissatz der Union-Abfrage mit der ID M,N stammt.

Typ

Anhand des Zugriffstyps entscheidet MySQL, wie Zeilen in der Tabelle gesucht werden.

Vom Besten zum Schlechtesten: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL. Außer all können andere Typen Indizes verwenden. Außer index_merge können andere Typen nur einen Index verwenden. Generell muss darauf geachtet werden, dass die Abfrage mindestens die Bereichsebene, besser noch die Referenzebene erreicht.

1. system: Die Tabelle enthält nur eine Datenzeile (entspricht der Systemtabelle). Dies ist ein Sonderfall des Typs const und tritt normalerweise nicht auf, daher kann er ignoriert werden.
2. const: Verwenden Sie einen eindeutigen Index oder Primärschlüssel, was bedeutet, dass er durch einmaliges Verwenden des Index gefunden werden kann. const wird zum Vergleichen von Primärschlüsseln oder eindeutigen Indizes verwendet. Da nur eine Datenzeile abgeglichen werden muss, ist dies sehr schnell. Wenn Sie den Primärschlüssel in die Where-Liste einfügen, kann MySQL die Abfrage in eine Konstante konvertieren.
3. eq_ref: Eindeutiger Indexscan. Für jeden Indexschlüssel gibt es in der Tabelle nur eine Datenzeile, die damit übereinstimmt. Häufig bei Primärschlüssel- oder eindeutigen Indexscans.
4. ref: Ein nicht eindeutiger Indexscan, der alle Zeilen zurückgibt, die einem einzelnen Wert entsprechen. Essence ist auch ein Index.
5. Volltext: Abrufen des Volltextindex. Der Volltextindex hat eine hohe Priorität. Wenn der Volltextindex und der normale Index gleichzeitig vorhanden sind, gibt MySQL dem Volltextindex unabhängig von den Kosten Vorrang.
6. ref_or_null: Ähnlich der Ref-Methode, jedoch mit zusätzlichem Vergleich auf Nullwerte.
7. index_merge: Gibt an, dass die Abfrage mehr als zwei Indizes verwendet. Es handelt sich um eine Optimierungsmethode für die Indexzusammenführung, die schließlich die Schnittmenge oder Vereinigung ergibt. Gemeinsame und/oder Bedingungen verwenden unterschiedliche Indizes.
8. unique_subquery: wird für Unterabfragen im Formular verwendet, wobei die Unterabfrage eindeutige Werte ohne Duplikate zurückgibt;
9. index_subquery: Wird für Unterabfragen im Formular verwendet, die Hilfsindizes oder Konstantenlisten verwenden. Unterabfragen können doppelte Werte zurückgeben, und Indizes können verwendet werden, um Unterabfragen zu deduplizieren.
10. Bereich: Indexbereichsscan, wird häufig in Abfragen mit Operatoren wie >, <, zwischen, in und ähnlich verwendet.
11. Index: Vollständiger Tabellenscan des Index, Scannen des Indexbaums von Anfang bis Ende;
12. all: durchläuft die gesamte Tabelle, um übereinstimmende Zeilen zu finden (obwohl sowohl Index als auch ALL die gesamte Tabelle lesen, liest Index aus dem Index, während ALL von der Festplatte liest)
13. NULL: MySQL zerlegt die Anweisung während der Optimierung und führt sie aus, ohne auf die Tabelle oder den Index zuzugreifen.

mögliche Schlüssel

Zeigt die Indizes an, die von der Abfrage verwendet werden können.

Schlüssel

Zeigt an, welchen Index die Abfrage tatsächlich verwendet hat, um den Zugriff auf die Tabelle zu optimieren;

Wenn select_type index_merge ist, können hier mehr als zwei Indizes erscheinen, und bei anderen select_types kann hier nur ein Index erscheinen.

Schlüssellänge

• Die Länge des Indexes, der zur Verarbeitung der Abfrage verwendet wird, gibt die Anzahl der im Index verwendeten Bytes an. Mit diesem Wert lässt sich ermitteln, welcher Teil eines mehrspaltigen Indexes tatsächlich verwendet wird.
• Hinweis: Der von key_len angezeigte Wert ist die maximal mögliche Länge des Indexfelds, nicht die tatsächlich verwendete Länge. Das heißt, key_len wird basierend auf der Tabellendefinition berechnet und nicht aus der Tabelle abgerufen. Darüber hinaus berechnet key_len nur die Länge des in der Where-Bedingung verwendeten Indexes, und selbst wenn der Index zum Sortieren und Gruppieren verwendet wird, wird er in key_len nicht berechnet.

Referenz

Zeigt an, welches Feld oder welche Konstante mit dem Schlüssel verwendet wird.

• Wenn Sie eine Abfrage mit konstantem gleichen Wert verwenden, wird hier „const“ angezeigt.
• Wenn es sich um eine Join-Abfrage handelt, zeigt der Ausführungsplan der gesteuerten Tabelle die zugehörigen Felder der gesteuerten Tabelle an.
• Wenn die Bedingung einen Ausdruck oder eine Funktion verwendet oder eine interne implizite Konvertierung in der Bedingungsspalte erfolgt, wird sie möglicherweise als „func“ angezeigt.

Reihen

Gibt an, dass MySQL die Anzahl der Zeilen, die gelesen werden müssen, um den erforderlichen Zieldatensatz zu finden, anhand der Tabellenstatistiken und der Indexauswahl grob schätzt. Dies ist kein exakter Wert.

Extra

Zusätzliche wichtige Informationen, die nicht in eine andere Spalte passen.

In dieser Spalte können zahlreiche Informationen angezeigt werden. Es gibt Dutzende von Typen. Die am häufigsten verwendeten sind:

Filtern nach

• Diese Spalte wird angezeigt, wenn „explain extended“ verwendet wird. Versionen nach 5.7 haben dieses Feld standardmäßig, daher besteht keine Notwendigkeit, „explain extended“ zu verwenden.
• Dieses Feld gibt das Verhältnis der Anzahl der Datensätze an, die die Abfrage erfüllen, nachdem die vom Speichermodul zurückgegebenen Daten auf Serverebene gefiltert wurden. Beachten Sie, dass es sich um einen Prozentsatz und nicht um eine bestimmte Anzahl von Datensätzen handelt.

Einschränkungen von MySQL-Ausführungsplänen

1. EXPLAIN informiert Sie nicht über Trigger, gespeicherte Prozeduren oder die Auswirkungen benutzerdefinierter Funktionen auf die Abfrage.
2. EXPLAIN berücksichtigt nicht verschiedene Caches;
3. EXPLAIN zeigt nicht die von MySQL bei der Ausführung der Abfrage durchgeführte Optimierungsarbeit an;
4. Bei manchen Statistiken handelt es sich um Schätzungen und nicht um genaue Werte.
5. EXPALIN kann nur SELECT-Operationen erklären. Andere Operationen müssen als SELECT neu geschrieben und dann angezeigt werden.

Fallstudie zur Abfrageplanung

Ausführungsreihenfolge

1. (id = 4): [Select-ID, Name aus t2]: select_type ist Union, was bedeutet, dass die Auswahl mit der ID = 4 die zweite Auswahl in der Union ist.
2. (id = 3): [select id, name from t1 where address = '11']: Da es sich um eine in der from-Anweisung enthaltene Unterabfrage handelt, wird sie als ABGELEHNT markiert. Wobei address = '11' über den zusammengesetzten Index idx_name_email_address abgerufen werden kann, also ist der Typ Index.
3. (id = 2): [Select-ID aus t3]: Da es sich um eine in der Auswahl enthaltene Unterabfrage handelt, wird sie als UNTERABFRAGE gekennzeichnet.
4. (id = 1): [select d1.name, … d2 from … d1]: select_type ist PRIMARY, was darauf hinweist, dass die Abfrage die äußerste Abfrage ist, und die Tabellenspalte ist als „derived3“ markiert, was darauf hinweist, dass die Abfrageergebnisse aus einer abgeleiteten Tabelle stammen (das Auswahlergebnis von id = 3).
5. (id = NULL): [ … union … ]: stellt die Phase des Lesens von Zeilen aus der temporären Union-Tabelle dar. Die „Union 1, 4“ in der Tabellenspalte zeigt die Union-Operation unter Verwendung der Auswahlergebnisse von id=1 und id=4 an.

Zusammenfassen

Dies ist das Ende dieses Artikels über die eingehende Untersuchung des MySQL Explain-Ausführungsplans. Weitere relevante Inhalte zum MySQL Explain-Ausführungsplan finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!