Detaillierte Erläuterung des Ausführungsplans, Beispiel für einen Befehl in MySQL

Vorwort

Der Befehl „Explain“ ist die primäre Möglichkeit, um zu sehen, wie der Abfrageoptimierer entschieden hat, eine Abfrage auszuführen.

Diese Funktion weist Einschränkungen auf und liefert nicht immer die Wahrheit, doch die Ausgabe enthält die besten verfügbaren Informationen und es lohnt sich, sich die Zeit zu nehmen, sie zu verstehen, da Sie erfahren, wie die Abfrage ausgeführt wird.

Aufruf von EXPLAIN

Wenn Sie vor der SELECT-Anweisung EXPLAIN hinzufügen, setzt MySQL ein Flag für die Abfrage, das bei der Ausführung des Abfrageplans dazu führt, dass Informationen zu jedem Schritt im Ausführungsplan zurückgegeben werden, anstatt ihn auszuführen.

Es gibt eine oder mehrere Informationszeilen zurück, die jeden Teil des Ausführungsplans und die Reihenfolge anzeigen, in der sie ausgeführt werden.

Hier ist eine einfache Erklärung der Wirkung:

Für jede Tabelle in der Abfrage gibt es in der Ausgabe nur eine Zeile. Wenn es sich bei der Abfrage um eine Verknüpfung zweier Tabellen handelt, gibt es in der Ausgabe zwei Zeilen.

Das Aliasformular zählt als eine Tabelle. Wenn Sie also eine Tabelle mit sich selbst verknüpfen, enthält die Ausgabe zwei Zeilen.

Die Bedeutung von „Tabelle“ ist hier recht weit gefasst; es kann sich um eine Unterabfrage, ein Vereinigungsergebnis usw. handeln.

Es gibt zwei Varianten von „erklären“ gleichzeitig.

EXPLAIN EXTENDED weist den Server an, den Ausführungsplan in eine Select-Anweisung „rückzukompilieren“.

Sie können diese generierte Anweisung sehen, indem Sie unmittelbar danach „show warnings“ ausführen. Diese Anweisung stammt direkt aus dem Ausführungsplan und nicht aus der ursprünglichen SQL-Anweisung, die an dieser Stelle zu einer Datenstruktur geworden ist.

In den meisten Fällen weicht sie von der ursprünglichen Anweisung ab. Sie können überprüfen, wie der Abfrageoptimierer die Anweisung konvertiert.

EXPLAIN EXTENDED ist in MySQL 5.0 und höher verfügbar und in 5.1 wurde eine gefilterte Spalte hinzugefügt.

EXPLAIN PARTITIONS zeigt die Partitionen an, auf die die Abfrage zugreifen würde, wenn die Abfrage sich auf eine partitionierte Tabelle beziehen würde.

Es existiert in MySQL 5.1 und höher.

EXPLAIN-Einschränkungen:

Explain wird Ihnen überhaupt nicht sagen, wie Trigger, gespeicherte Prozeduren oder UDFs Abfragen beeinflussen

Gespeicherte Prozeduren werden nicht unterstützt. Es ist jedoch möglich, Abfragen manuell zu extrahieren und einzeln zu erläutern.

Es informiert Sie nicht über spezifische Optimierungen, die MySQL in seinem Ausführungsplan vorgenommen hat.

Es werden nicht alle Informationen zum Ausführungsplan der Abfrage angezeigt

Es wird nicht zwischen Dingen mit demselben Namen unterschieden. Beispielsweise wird „Filesort“ sowohl für Sortierungen im Arbeitsspeicher als auch für temporäre Dateien verwendet und für temporäre Tabellen sowohl auf der Festplatte als auch im Arbeitsspeicher wird „Temporär verwenden“ angezeigt.

Kann irreführend sein. Beispielsweise kann es einen vollständigen Indexscan für eine Abfrage mit einem kleinen Limit anzeigen (MySQL 5.1 Explain zeigt genauere Informationen über die Anzahl der geprüften Zeilen an, frühere Versionen berücksichtigen das Limit jedoch nicht).

Umschreiben von Nicht-SELECT-Abfragen

mysql Explain kann nur ausgewählte Abfragen erklären, nicht jedoch Aufrufe gespeicherter Prozeduren und Einfüge-, Aktualisierungs-, Lösch- oder andere Anweisungen. Sie können jedoch bestimmte Nicht-Select-Abfragen umschreiben, um die Vorteile von „Explain“ zu nutzen. Um dies zu erreichen, konvertieren Sie die Anweisung einfach in eine entsprechende Auswahl, die auf alle gleichen Spalten zugreift. Alle zusätzlichen Spalten müssen in der Auswahlliste, der Join-Klausel oder der Where-Klausel enthalten sein.

Angenommen, Sie möchten die folgende Update-Anweisung so umschreiben, dass sie „explain“ verwenden kann.

AKTUALISIEREN sakila.actor
INNER JOIN sakila.film_actor USING (Schauspieler-ID)
SET Schauspieler.letztes_Update = Filmschauspieler.letztes_Update;

Die folgende EXPLAIN-Anweisung ist nicht mit dem obigen Update identisch, da der Server die Spalte „last_update“ nicht aus einer Tabelle abrufen muss.

Diese Unterscheidung ist sehr wichtig. Beispielsweise zeigt die Ausgabe, dass MySQL den überdeckenden Index verwendet. Beim Abrufen und Aktualisieren der Spalte last_updated kann der überdeckende Index jedoch nicht verwendet werden. Die folgende Umformulierung kommt der ursprünglichen Anweisung näher:

Das Umschreiben der Abfrage auf diese Weise ist nicht besonders wissenschaftlich, reicht jedoch aus, um zu verstehen, was die Abfrage bewirkt.
(MySQL 5.6 ermöglicht die Erklärung von Nicht-SELECT-Abfragen)

Beim Anzeigen von Abfrageplänen ist es wichtig zu verstehen, dass es für eine Schreibabfrage keine „äquivalente“ Leseabfrage gibt. Eine SELECT-Abfrage muss nur eine Kopie der Daten finden und zurückgeben. Jede Abfrage, die Daten ändert, muss alle Kopien der Daten in allen Indizes suchen und ändern, was oft viel aufwändiger ist als eine scheinbar gleichwertige SELECT-Abfrage.

Spalten in EXPLAIN

Die Bedeutung der einzelnen Spalten im Erläuterungsergebnis wird im nächsten Abschnitt gezeigt.

Die Zeilen in der Ausgabe erscheinen in der Reihenfolge, in der MySQL die Teile der Abfrage tatsächlich ausgeführt hat. Dies ist nicht immer die gleiche Reihenfolge wie im ursprünglichen SQL.

[ID-Spalte]

Diese Spalte enthält immer eine Nummer, die die Zeile identifiziert, zu der die Auswahl gehört. Wenn die Anweisung keine Unterabfragen oder Verknüpfungen enthält, gibt es nur eine Auswahl, sodass in jeder Zeile in dieser Spalte eine 1 angezeigt wird. Andernfalls werden die inneren Auswahlanweisungen im Allgemeinen fortlaufend nummeriert, entsprechend ihrer Position in der ursprünglichen Anweisung.

MySQL unterteilt Select-Abfragen in einfache und komplexe Typen. Komplexe Typen lassen sich in drei Kategorien unterteilen: einfache Unterabfragen, sogenannte abgeleitete Tabellen (Unterabfragen in der From-Klausel) und Union-Abfragen.

Hier ist eine einfache Unterabfrage:

Unterabfragen und Verknüpfungen in der From-Klausel erhöhen die Komplexität der ID-Spalte noch weiter.

Hier ist eine einfache Unterabfrage in einer From-Klausel:

Wie Sie wissen, wird diese Abfrage mit einer anonymen temporären Tabelle ausgeführt. MySQL referenziert diese temporäre Tabelle intern in der äußeren Abfrage über einen Alias ​​(der). In komplexeren Abfragen können Sie die Spalte ref sehen.

Abschließend noch eine Union-Abfrage:

Beachten Sie die dritte zusätzliche Zeile. Das Ergebnis der Vereinigung wird immer in einer anonymen temporären Tabelle abgelegt. MySQL liest dann das Ergebnis aus der temporären Tabelle. Die temporäre Tabelle erscheint nicht im ursprünglichen SQL, daher ist ihre ID-Spalte null.

Im Vergleich zum vorherigen Beispiel (das die From-Klausel einer Unterabfrage demonstrierte) wird die aus dieser Abfrage resultierende temporäre Tabelle als letzte Zeile im Ergebnis angezeigt und nicht als erste.

Bisher war dies ziemlich unkompliziert, aber das Mischen dieser drei Anweisungstypen kann die Ausgabe ziemlich kompliziert machen, wie wir gleich sehen werden.

[Spalte select_type]

In dieser Spalte wird angezeigt, ob es sich bei der entsprechenden Zeile um eine einfache oder komplexe Auswahl handelt (und wenn letzteres der Fall ist, um welchen der drei Komplexitätstypen). Der einfache Wert bedeutet, dass die Abfrage keine Unterabfragen oder Unions enthält. Wenn die Abfrage verantwortliche Unterteile hat, wird der äußerste Teil als primär markiert und die anderen Teile werden wie folgt markiert:

UNTERABFRAGE

Eine Auswahl, die in einer Unterabfrage enthalten ist, die sich in der Auswahlliste befindet (also nicht in der From-Klausel), wird als SUBQUERY gekennzeichnet.

ABGELEITET

Der DERIVED-Wert wird verwendet, um anzugeben, dass die Auswahl in der in der FROM-Klausel enthaltenen Unterabfrage von MySQL rekursiv ausgeführt wird und die Ergebnisse in einer temporären Tabelle abgelegt werden. Der Server bezeichnet dies intern als „abgeleitete Tabelle“, da die temporäre Tabelle aus der Unterabfrage abgeleitet wird.

UNION

Die zweite und alle nachfolgenden Auswahlen in einer UNION werden als „Unoin“ markiert und die erste Auswahl wird so markiert, als ob sie als Teil der äußeren Abfrage ausgeführt würde. Aus diesem Grund wird die erste Auswahl in der Vereinigung im vorherigen Beispiel als primär angezeigt. Wenn in einer Unterabfrage in der From-Klausel eine Union enthalten ist, wird ihre erste Auswahl als abgeleitet markiert.

UNION-ERGEBNIS

Die zum Abrufen der Ergebnisse aus der anonymen temporären Tabelle der Union verwendete Auswahl wird als UNION RESULT gekennzeichnet.

Zusätzlich zu diesen Werten können SUBQUERY und UNION auch als DEPENDENT und UNCACHEABLE markiert werden.

DEPENDENT bedeutet, dass die Auswahl von den in der äußeren Abfrage gefundenen Daten abhängt.

UNCACHEABLE bedeutet, dass einige Eigenschaften der Auswahl verhindern, dass die Ergebnisse in einem Item_cache zwischengespeichert werden.

(Item_cache ist nicht dokumentiert und ist nicht dasselbe wie der Abfragecache, obwohl es durch einige Konstrukte desselben Typs, wie etwa die Funktion RAND(), negiert werden kann.)

【Tabellenspalte】

Diese Spalte zeigt, auf welche Tabelle die entsprechende Zeile zugreift, und im Normalfall ist dies ganz einfach: Es ist die Tabelle oder eine Spalte dieser Tabelle (wenn im SQL ein Alias ​​definiert ist).

In dieser Spalte können Sie von oben nach unten die Join-Reihenfolge beobachten, die MySQLs Join-Optimierer für die Abfrage auswählt. Sie können beispielsweise sehen, dass in der folgenden Abfrage die von MySQL gewählte Join-Reihenfolge von der in der Anweisung angegebenen Reihenfolge abweicht:

Der Ausführungsplan von MySQL ist immer ein linksseitiger Tiefensuche-Baum. Wenn Sie diesen Plan umdrehen, können Sie die Blattknoten der Reihe nach lesen, was direkt den Zeilen in Explain entspricht. Der vorherige Abfrageplan sieht wie in der folgenden Abbildung aus:

Abgeleitete Tabellen und Vereinigungen

Wenn die From-Klausel eine Unterabfrage oder eine Union enthält, wird die Tabellenspalte viel komplizierter. In diesen Szenarien gibt es eigentlich keine „Tabelle“, auf die verwiesen werden kann, da die von MySQL erstellte anonyme temporäre Tabelle nur während der Ausführung der Abfrage existiert.

Wenn die From-Klausel eine Unterabfrage enthält, hat die Tabellenspalte die Form <derivedN>, wobei N die ID der Unterabfrage ist. Dies bezieht sich immer „vorwärts“, d. h. N zeigt auf die nächste Zeile in der EXPLAIN-Ausgabe.

Wenn eine Vereinigung vorliegt, enthält die Tabellenspalte des Vereinigungsergebnisses eine Liste der an der Vereinigung beteiligten IDs. Dabei handelt es sich immer um eine „Rückwärtsreferenz“, da das Vereinigungsergebnis nach allen an der Vereinigung beteiligten Zeilen erscheint und die Tabellenspalte bei mehr als 20 IDs in der Liste gekürzt wird, um zu verhindern, dass sie zu lang wird und somit nicht mehr alle Werte angezeigt werden. Glücklicherweise lässt sich dennoch ableiten, welche Zeilen eingeschlossen sind, da Sie die ID der ersten Zeile sehen können und alles, was zwischen dieser Zeile und dem Vereinigungsergebnis erscheint, in irgendeiner Weise eingeschlossen wird.

Ein Beispiel für einen komplexen Auswahltyp

Hier ist eine nichtssagende Abfrage, die wir hier als kompaktes Beispiel für eine bestimmte Art komplexer Auswahl verwenden:

Die Limit-Klausel dient lediglich der Einfachheit halber für den Fall, dass Sie das Programm ausführen möchten, ohne die Ergebnisse zu erklären.

Im Folgenden sind einige der Ergebnisse von Explain aufgeführt:

Wir haben absichtlich dafür gesorgt, dass jeder Abfrageteil auf eine andere Tabelle zugreift, damit wir das Problem herausfinden konnten. Dennoch ist es schwer zu lösen, wenn man von oben anfängt:

Zeile 1 verweist vorwärts auf der_1. Diese Abfrage ist als <derived3> gekennzeichnet, was Zeile 2 im ursprünglichen SQL ist. Um zu erfahren, welche Zeilen in der Ausgabe sich auf die Select-Anweisung in <derived3> beziehen, lesen Sie weiter.

Die 2. Zeile hat die ID 3, da sie Teil der 3. Auswahl in der Abfrage ist. Sie wird als abgeleitet klassifiziert, da sie in einer Unterabfrage in der From-Klausel verschachtelt ist. Im ursprünglichen SQL ist dies die 4. Zeile.

Die ID der dritten Zeile ist 2, also die dritte Zeile im ursprünglichen SQL. Beachten Sie, dass sie nach der Zeile mit der höheren ID steht, was darauf hindeutet, dass sie später ausgeführt wird, was sinnvoll ist. Sie wird als ABHÄNGIGE UNTERABFRAGE klassifiziert, was bedeutet, dass ihre Ergebnisse von der äußeren Abfrage (d. h. einer korrelierten Unterabfrage) abhängen. Die äußere Abfrage in diesem Beispiel beginnt in Zeile 2 und wählt Daten aus der_1 aus.

Die vierte Zeile wird als Union klassifiziert, was bedeutet, dass sie die zweite oder nachfolgende Auswahl in der Union ist und ihre Tabelle <abgeleitet6> ist, was bedeutet, dass sie Daten aus der Unterabfrage der From-Klausel abruft und sie an die temporäre Tabelle der Union anhängt. Um die EXPLAIN-Zeile zu finden, die den Abfrageplan für diese Unterabfrage anzeigt, scrollen Sie wie zuvor nach unten.

Zeile 5 ist die der_2-Unterabfrage in Zeile 8 des ursprünglichen SQL und wird von Explain <derived6> genannt.

Zeile 6 ist eine normale Unterabfrage in der Auswahlliste von <derived6> und ihre ID ist 7, was sehr wichtig ist ...

...weil es größer als 5 ist, was der ID von Zeile 7 entspricht. Warum ist es wichtig? weil es die Grenzen der Unterabfrage <derived6> zeigt. Wenn Explain eine Zeile mit dem ausgewählten abgeleiteten Typ ausgibt, zeigt dies den Anfang eines „verschachtelten Bereichs“ an. Wenn die nachfolgende Zeile eine kleinere ID hat (in diesem Fall ist 5 kleiner als 6), bedeutet dies, dass der verschachtelte Bereich geschlossen wurde. Dadurch wissen wir, dass Zeile 7 Teil der Auswahlliste ist, die Daten aus <derived6> abruft – beispielsweise der erste Teil der Auswahlliste in Zeile 4 (Zeile 7 im ursprünglichen SQL). Dieses Beispiel ist relativ einfach zu verstehen, auch wenn man die Bedeutung und Regeln verschachtelter Bereiche nicht kennen muss, aber manchmal ist es natürlich nicht so einfach. Bei dieser Zeile in der Ausgabe ist außerdem zu beachten, dass sie aufgrund der Benutzervariablen als UNCACHEABLE SUBQUERY aufgeführt wird.

Die letzte Zeile, das Union-Ergebnis, stellt die Phase des Lesens der Zeilen aus der temporären Tabelle der Union dar. Wenn Sie möchten, können Sie von dieser Zeile aus rückwärts arbeiten. Es werden die Ergebnisse für die Zeilen mit den IDs 1 und 4 zurückgegeben, die jeweils auf <derived3> und <derived6> verweisen.

Wie Sie sehen, können diese komplexen Kombinationen von Auswahltypen die Erklärungsausgabe ziemlich schwer verständlich machen. Das Verstehen der Regeln macht es einfacher, nimmt aber trotzdem mehr Zeit in Anspruch.

Das Lesen der EXPLAIN-Ausgabe erfordert häufig das Hin- und Herspringen in der Liste. Wenn Sie sich beispielsweise die erste Ausgabezeile noch einmal ansehen, werden Sie allein durch Anstarren nicht erkennen, dass es sich um einen Teil einer Union handelt. Sie werden es erst verstehen, wenn Sie die letzte Zeile sehen.

[Typspalte]

Im MySQL-Benutzerhandbuch steht, dass diese Spalte den „Assoziationstyp“ anzeigt. Wir meinen jedoch, dass es genauer ist, von Zugriffstyp zu sprechen – mit anderen Worten, wie MySQL die Zeilen in der Tabelle findet. Hier sind die wichtigsten Zugriffsmethoden in der Reihenfolge vom schlechtesten zum besten:

ALLE:

Dies wird als vollständiger Tabellenscan bezeichnet. Dies bedeutet, dass MySQL die gesamte Tabelle von Anfang bis Ende scannen muss, um die erforderliche Zeile zu finden. (Es gibt Ausnahmen, z. B. wenn in der Abfrage ein Limit verwendet wird oder in der zusätzlichen Spalte „Verwendung von distinct/not exists“ angezeigt wird.

Index:

Dies ist dasselbe wie ein vollständiger Tabellenscan, mit dem Unterschied, dass MySQL die Tabelle in Indexreihenfolge statt in Zeilenreihenfolge scannt. Der Hauptvorteil besteht darin, dass das Sortieren vermieden wird; der größte Nachteil ist der Mehraufwand, der durch das Lesen der gesamten Tabelle in Indexreihenfolge entsteht. Dies bedeutet normalerweise, dass der Zugriff auf Zeilen in zufälliger Reihenfolge sehr aufwändig ist.

Wenn in der zusätzlichen Spalte „Index verwenden“ steht, bedeutet das, dass MySQL Index Covering verwendet. Es werden nur die Indexdaten gescannt und nicht jede Zeile in der Indexreihenfolge. Das ist viel kostengünstiger, als die gesamte Tabelle in der Indexreihenfolge zu scannen.

Reichweite:

Ein Bereichsscan ist ein eingeschränkter Indexscan, der an einem bestimmten Punkt im Index beginnt und Zeilen zurückgibt, die diesem Wertebereich entsprechen. Dies ist besser als ein vollständiger Indexscan, da nicht der gesamte Index durchlaufen werden muss. Der offensichtliche Scan ist eine Abfrage mit „between“ oder „>“ in der Where-Klausel.

Wenn MySQL einen Index verwendet, um einen Wertebereich zu finden, wie z. B. in() und/oder Listen, erscheint dies ebenfalls als Bereichsscan. Tatsächlich handelt es sich jedoch um zwei recht unterschiedliche Zugriffstypen mit wichtigen Leistungsunterschieden.

Die Kosten für diesen Scantyp sind mit denen eines indizierten Scans vergleichbar.

Verweis:

Dies ist ein Indexzugriff (manchmal auch als Indexsuche bezeichnet), der alle Zeilen zurückgibt, die einem einzelnen Wert entsprechen. Es können jedoch mehrere qualifizierte Zeilen gefunden werden, sodass es sich um eine Mischung aus Suche und Scan handelt. Diese Art von Indexzugriff tritt nur auf, wenn ein nicht eindeutiger Index oder ein nicht eindeutiges Präfix eines eindeutigen Index verwendet wird. Der Name „Ref“ kommt daher, dass der Index mit einem Referenzwert verglichen wird. Dieser Referenzwert ist entweder eine Konstante oder der Ergebniswert aus der vorherigen Tabelle bei einer Abfrage mit mehreren Tabellen.

ref_or_null ist eine Variation von ref, die bedeutet, dass MySQL eine zweite Suche durchführen muss, um Nulleinträge in den Ergebnissen der ersten Suche zu finden.

Gleichungsreferenz:

Mithilfe dieser Indexsuche weiß MySQL, dass es höchstens einen Datensatz zurückgeben muss, der die Kriterien erfüllt. Diese Zugriffsmethode ist zu sehen, wenn MySQL eine Primärschlüssel- oder eindeutige Indexsuche verwendet und diese mit einem Referenzwert vergleicht. MySQL optimiert diese Art des Zugriffs sehr gut, da es weiß, dass es den Bereich der übereinstimmenden Zeilen nicht schätzen oder die Suche fortsetzen muss, nachdem es eine übereinstimmende Zeile gefunden hat.

Konstante, System:

MySQL verwendet diese Zugriffstypen, wenn es einen Teil einer Abfrage optimieren und in eine Konstante umwandeln kann. Wenn Sie beispielsweise den Primärschlüssel einer Zeile auswählen, indem Sie ihn in die Where-Klausel einfügen, kann MySQL die Abfrage in eine Konstante umwandeln und die Tabelle effektiv aus dem Join entfernen.

null:

Diese Zugriffsmethode bedeutet, dass MySQL die Abfrageanweisung während der Optimierungsphase zerlegen kann und während der Ausführungsphase nicht einmal auf die Tabelle oder den Index zugreifen muss. Beispielsweise kann der Minimalwert aus einer indizierten Spalte einfach durch Nachschlagen des Index ausgewählt werden, ohne dass zur Ausführungszeit auf die Tabelle zugegriffen werden muss.

[Spalte „possible_keys“]

In dieser Spalte wird angezeigt, welche Indizes die Abfrage verwenden kann, basierend auf den von der Abfrage aufgerufenen Spalten und den verwendeten Vergleichsoperatoren. Diese Liste wird früh im Optimierungsprozess erstellt, daher sind einige der aufgelisteten Indizes für nachfolgende Optimierungsprozesse möglicherweise nicht nützlich.

[Schlüsselspalte]

Diese Spalte zeigt, welchen Index MySQL verwendet, um den Zugriff auf die Tabelle zu optimieren. Wenn der Index nicht in der Spalte „possible_keys“ erscheint, hat MySQL ihn aus einem anderen Grund gewählt. Beispielsweise könnte es auch ohne eine Where-Klausel einen überdeckenden Index gewählt haben.

Mit anderen Worten: Possible_keys zeigt an, welcher Index dabei helfen kann, die Suche effizient durchzuführen, während Key zeigt, welchen Index der Optimierer verwenden kann, um die Abfragekosten zu minimieren. Hier ist ein Beispiel:

[key_len-Spalte]

Diese Spalte zeigt die Anzahl der von MySQL im Index verwendeten Bytes. Wenn MySQL nur einige Spalten im Index verwendet, können Sie diesen Wert verwenden, um herauszufinden, welche Spalten verwendet werden. Denken Sie daran, dass MySQL 5.5 und frühere Versionen nur das am weitesten links stehende Präfix des Index verwenden können. Beispielsweise besteht der Primärschlüssel von film_actor aus zwei Smallint-Spalten, und jede Smallint-Spalte besteht aus zwei Bytes, sodass jedes Element im Index 4 Bytes groß ist. Das Folgende ist eine Beispielabfrage:

Anhand der Spalte key_len in den Ergebnissen können wir schließen, dass die Abfrage nur die erste Spalte, die Spalte actor_id, verwendet, um die Indexsuche durchzuführen. Bei der Berechnung der Spaltennutzung müssen wir die Zeichensatzseite in der Zeichenspalte berücksichtigen.

Zeigen Sie den Ausführungsplan an:

Die durchschnittliche Länge dieser Abfrage beträgt 13 Bytes, was der Gesamtlänge der Spalten a und b entspricht. Spalte a besteht aus 3 Zeichen, von denen jedes in UTF-8 höchstens 3 Bytes groß ist, und Spalte b ist eine 4-Byte-Ganzzahl.

MySQL zeigt nicht immer an, wie viel von einem Index tatsächlich verwendet wird. Wenn Sie beispielsweise eine LIKE-Abfrage für eine Präfixmusterübereinstimmung ausführen, wird angezeigt, dass die volle Breite der Spalte verwendet wird.

Die Spalte key_len zeigt die maximal mögliche Länge im Indexfeld und nicht die tatsächliche Anzahl der von den Daten in der Tabelle belegten Bytes. Im vorherigen Beispiel zeigt MySQL immer 13 Bytes an, selbst wenn Spalte a zufällig nur ein Zeichen lang ist. Mit anderen Worten: key_len wird durch Nachschlagen der Tabellendefinition berechnet, nicht durch Nachschlagen der Daten in der Tabelle.

[Ref-Spalte]

Diese Spalte zeigt die Spalte oder Konstante, die von der vorherigen Tabelle verwendet wurde, um den Wert im Index des Schlüsselspaltendatensatzes zu finden. Das folgende Beispiel zeigt die Kombination von Join-Bedingungen und Aliasnamen. Beachten Sie, dass die Spalte „ref“ widerspiegelt, wie die Filmtabelle im Abfragetext als „f“ aliasiert wird:

[Zeilenspalte]

Diese Spalte gibt die Anzahl der Zeilen an, die MySQL voraussichtlich lesen muss, um die erforderliche Zeile zu finden. Diese Zahl ist die Anzahl der Schleifen im Inline-Loop-Joinplan. Das heißt, es handelt sich nicht um die Anzahl der Zeilen, die MySQL letztendlich aus der Tabelle lesen muss, sondern um die durchschnittliche Anzahl der Zeilen, die MySQL lesen muss, um an jedem Punkt der Abfrage die Zeilen zu finden, die die Kriterien erfüllen. (Dieses Kriterium umfasst die im SQL angegebenen Bedingungen sowie die aktuellen Spalten aus der vorherigen Tabelle in der Join-Reihenfolge.)

Abhängig von den Tabellenstatistiken und der Indexauswahl kann diese Schätzung sehr ungenau sein. In MySQL 5.0 und früheren Versionen spiegelt sie die Limit-Klausel nicht wider. Die folgende Abfrage prüft beispielsweise nicht wirklich 1057 Zeilen.

Durch Multiplikation der Werte aller Zeilenspalten können Sie die Anzahl der Zeilen, die die gesamte Abfrage untersuchen wird, grob abschätzen. Die folgende Abfrage untersucht beispielsweise ungefähr 2600 Zeilen.

Es ist wichtig, sich daran zu erinnern, dass diese Zahl die Anzahl der Zeilen ist, die MySQL zu untersuchen glaubt, und nicht die Anzahl der Zeilen im Ergebnissatz. Bedenken Sie auch, dass viele Optimierungen, wie z. B. assoziative Puffer und Caches, die angezeigte Zeilenanzahl nicht beeinflussen können. MySQL liest möglicherweise nicht alle Zeilen, die es schätzt, und es weiß nichts über Betriebssystem- oder Hardware-Caches.

[Zusätzliche Spalte]

Diese Spalte enthält zusätzliche Informationen, die nicht in die anderen Spalten passen. Die meisten Werte, die hier vorkommen können, sind im MySQL-Benutzerhandbuch dokumentiert.

Die häufigsten und wichtigsten Werte sind folgende.

„Index verwenden“

Dieser Wert gibt an, dass MySQL einen überdeckenden Index verwendet, um den Zugriff auf die Tabelle zu vermeiden. Verwechseln Sie abdeckende Indizes nicht mit Indexzugriffstypen.

„Wo verwenden“

Dies bedeutet, dass der MySQL-Server Zeilen filtert, nachdem die Speicher-Engine sie abgerufen hat. Viele Where-Bedingungen betreffen Spalten im Index und können von der Speicher-Engine überprüft werden, wenn (und falls) sie den Index liest. Daher wird nicht bei allen Abfragen mit einer Where-Klausel „Using where“ angezeigt. Manchmal ist das Vorhandensein von „Using where“ ein Hinweis darauf, dass die Abfrage von einem anderen Index profitieren könnte.

„Temporäre Nutzung“

Dies bedeutet, dass MySQL beim Sortieren der Abfrageergebnisse eine temporäre Tabelle verwendet.

„Dateisortierung verwenden“

Dies bedeutet, dass MySQL zum Sortieren der Ergebnisse einen externen Index verwendet, anstatt die Zeilen in Indexreihenfolge aus der Tabelle zu lesen. MySQL verfügt über zwei Dateisortierungsalgorithmen, die beide im Speicher oder auf der Festplatte durchgeführt werden können. Explain sagt Ihnen weder, welche Dateisortierung MySQL verwenden wird, noch, ob die Sortierung im Speicher oder auf der Festplatte durchgeführt wird.

„Für jeden Datensatz geprüfter Bereich (Indexkarte: N)“

Dies bedeutet, dass kein geeigneter Index vorhanden ist und der neue Index in jeder Zeile des Joins neu ausgewertet wird. N ist eine Bitmap des in der Spalte „possible_keys“ angezeigten Indexes und ist redundant.

Ausgabe im Baumformat

MySQL-Benutzer bevorzugen es häufig, die Ausgabe von Explain in einer Baumstruktur zu formatieren, um den Ausführungsplan genauer anzuzeigen. Tatsächlich ist die Art und Weise, wie der Ausführungsplan in Explain angezeigt wird, etwas umständlich und die Baumstruktur ist für die tabellarische Ausgabe nicht geeignet. Wenn die zusätzliche Spalte eine große Anzahl von Werten enthält, sind die Nachteile offensichtlicher. Dasselbe gilt für die Verwendung von Union. Union unterscheidet sich von anderen Join-Typen, die MySQL ausführen kann, und ist für Explain nicht geeignet.

Wenn Sie die Regeln und Merkmale von Explain vollständig verstanden haben, ist auch die Verwendung eines Ausführungsplans mit Baumstruktur möglich. Dies ist jedoch etwas mühsam und sollte am besten automatisierten Tools überlassen werden. Percona Toolkit enthält pt-visual-explain, ein solches Tool.

Verbesserungen in MySQL 5.6

MySQL 5.6 wird eine wichtige Verbesserung zur Erklärung enthalten: die Möglichkeit, Abfragen wie Update, Insert usw. zu erklären. Obwohl DML-Anweisungen in äquivalente „Select“-Abfragen umgewandelt und erklärt werden können, spiegeln die Ergebnisse nicht vollständig wider, wie die Anweisungen ausgeführt werden, sodass dies immer noch sehr hilfreich ist. Wir haben versucht, diese Technik bei der Entwicklung von Elementen wie pt-upgrade im Percona Toolkit zu verwenden und haben dabei mehr als einmal festgestellt, dass der Optimierer beim Konvertieren von Abfragen in Auswahlen den erwarteten Codepfad nicht ausführen kann. Daher ist es für uns sehr hilfreich, eine Abfrage zu erklären, ohne sie in eine Auswahl umzuwandeln, um zu verstehen, was während des Ausführungsprozesses geschieht.

MySQL 5.6 wird auch eine Reihe von Änderungen an der Abfrageoptimierungs- und -ausführungs-Engine enthalten, die es ermöglichen, anonyme temporäre Tabellen so spät wie möglich zu materialisieren, anstatt sie immer dann zu erstellen und zu füllen, wenn der Teil der Abfrage optimiert und ausgeführt wird, der diese temporäre Tabelle verwendet. Dadurch kann MySQL Abfrageanweisungen mit Unterabfragen direkt interpretieren, ohne die Unterabfragen zuerst tatsächlich auszuführen.

Schließlich wird MySQL 5.6 den Optimierer verbessern, indem dem Server Optimierungsverfolgungsfunktionen hinzugefügt werden. Dadurch können Benutzer die vom Optimierer getroffenen Entscheidungen sowie die Eingaben (beispielsweise die Kardinalität eines Indexes) und die Gründe für die Entscheidungen anzeigen. Dies ist nicht nur zum Verständnis des vom Server gewählten Ausführungsplans sehr hilfreich, sondern auch, um die Gründe für die Wahl dieses Plans zu verstehen.

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. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.