MYSQL Performance Analyzer EXPLAIN Anwendungsbeispielanalyse

Dieser Artikel veranschaulicht anhand eines Beispiels die Verwendung des MYSQL-Performance-Analysators EXPLAIN. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Anwendung:

EXPLAIN SELECT * FROM Benutzer;

Umgebung und Datenaufbereitung

-- Überprüfen Sie die MySQL-Version SELECT VERSION();
 
-- Welche Speicher-Engines bietet MySQL? ENGINES ANZEIGEN;
 
-- Zeigen Sie die Standardspeicher-Engine an. SHOW VARIABLES LIKE '%storage_engine%';

Ausgabe:

id: Die Ausgabe ist eine Ganzzahl, die zur Identifizierung der Ausführungsreihenfolge des gesamten SQL verwendet wird. Wenn die ID gleich ist, werden die Zeilen mit unterschiedlichen IDs von oben nach unten ausgeführt. Je größer der ID-Wert ist, desto höher ist die Ausführungspriorität und die Zeile wird zuerst ausgeführt. Wenn die Zeile auf das Vereinigungsergebnis anderer Zeilen verweist, kann der Wert NULL sein.

select_type:[Abfragetyp]

SIMPLE: Einfache SELECT-Abfrage, keine UNION- oder Unterabfrage, einschließlich Einzeltabellenabfrage oder Mehrtabellen-JOIN-Abfrage

PRIMARY: Die äußerste Auswahlabfrage, die häufig in Unterabfragen oder UNION-Abfragen vorkommt. Die äußerste Abfrage wird als PRIMARY gekennzeichnet.

UNION: Das zweite oder nachfolgende SELECT in einer UNION-Operation hängt nicht vom Ergebnissatz der äußeren Abfrage ab (die äußere Abfrage bezieht sich auf das SELECT, das dem PRIMARY entspricht).

DEPENDENT UNION: Das zweite oder nachfolgende SELECT in einer UNION-Operation hängt vom Ergebnissatz der äußeren Abfrage ab.

UNION RESULT: Das Ergebnis von UNION (kein Ergebnis, wenn es UNION ALL ist)

SUBQUERY: Die erste SELECT-Abfrage in einer Unterabfrage, die nicht vom Ergebnissatz der äußeren Abfrage abhängt.

ABHÄNGIGE UNTERABFRAGE: Die erste Auswahlabfrage in einer Unterabfrage hängt vom Ergebnis der äußeren Abfrage ab.

DERIVED: Abgeleitete Tabelle (temporäre Tabelle), häufig anzutreffen, wenn in der FROM-Klausel eine Unterabfrage vorhanden ist

Hinweis : MySQL 5.7 verfügt über eine neue Funktion für abgeleitete Tabellen, mit der die untergeordnete Tabelle in der abgeleiteten Tabelle, die die Bedingungen erfüllt, direkt mit der übergeordneten Abfragetabelle verknüpft werden kann, wodurch der Ausführungsplan vereinfacht und die Ausführungseffizienz verbessert wird. Standardmäßig ist diese Funktion in MySQL 5.7 aktiviert, daher sollte der Ausführungsplan des obigen SQL standardmäßig wie folgt aussehen

MATERIALIZED: Materialisierte Unterabfrage, ein neuer Auswahltyp, der in MySQL 5.6 eingeführt wurde und hauptsächlich zur Optimierung von Unterabfragen in FROM- oder IN-Klauseln verwendet wird. Weitere Einzelheiten finden Sie unter: Optimieren von Unterabfragen mit Materialisierung

NICHT ZWISCHENSPEICHERBARE UNTERABFRAGE: Für die äußere Haupttabelle kann die Unterabfrage nicht zwischengespeichert werden und muss jedes Mal neu berechnet werden.

UNCACHEABLE UNION: Ähnlich wie UNCACHEABLE SUBQUERY, erscheint aber in UNION-Operationen

SIMPLLE, PRIMARY, SUBQUERY, DERIVED Diese vier werden in der Praxis häufiger vorkommen. Sie müssen diese vier verstehen. Was die anderen betrifft, suchen Sie einfach nach den Informationen, wenn Sie auf sie stoßen.

Tabelle: Zeigt an, auf welche Tabelle die entsprechende Zeile zugreift (der Alias ​​wird angezeigt, falls vorhanden), und es gibt ähnliche Werte wie <union2,3>, <subquery2> und <derived2> (wobei sich 2,3, 2, 2 auf die Werte der ID-Spalte beziehen).

Partitionen: Die Partitionen, die mit der Abfrage übereinstimmen. Für nicht partitionierte Tabellen ist dieser Wert NULL. In den meisten Fällen werden Partitionen nicht verwendet, daher müssen wir dieser Spalte keine Beachtung schenken.

Typ:

Der Join-Typ oder Zugriffstyp gibt an, wie MySQL entscheidet, welche Zeilen die Bedingungen in der Tabelle erfüllen. Dies ist eine wichtige Grundlage für uns, um zu beurteilen, ob die Abfrage effizient ist. Eine vollständige Einführung finden Sie unter: Explain-Join-Types

system: Diese Tabelle hat nur eine Zeile (= Systemtabelle), die ein Sonderfall des Typs const ist

const: Wenn festgestellt wird, dass nur eine übereinstimmende Zeile vorhanden ist, liest der MySQL-Optimierer diese vor der Abfrage und liest sie nur einmal, was sehr schnell ist. Wird für Vergleiche konstanter Werte in Primärschlüsseln oder eindeutigen Indizes verwendet

eq_ref: Für jede Zeile aus der vorherigen Tabelle wird höchstens ein qualifizierender Datensatz aus dieser Tabelle zurückgegeben. Dies ist sehr effizient, wenn der von der Verbindung verwendete Index ein PRIMARY KEY- oder UNIQUE NOT NULL-Index ist.

ref: Der Indexzugriff, auch als Indexsuche bekannt, gibt alle Zeilen zurück, die einem einzelnen Wert entsprechen. Dieser Typ erscheint normalerweise in JOIN-Abfragen mit mehreren Tabellen, für nicht-UNIQUE oder nicht-PRIMARY KEY oder Abfragen, die den am weitesten links stehenden Präfixregelindex verwenden. Mit anderen Worten: Wenn der JOIN keine einzelne Zeile basierend auf dem Schlüsselwort auswählen kann, verwenden Sie ref

Volltext: Dies wird verwendet, wenn Volltextindizierung verwendet wird. Dieser Indextyp wird im Allgemeinen nicht verwendet und wird durch einen dedizierten Suchdienst (Solr, Elasticsearch usw.) ersetzt.

ref_or_null: Ähnlich wie ref, fügt aber eine Zeile hinzu, die gezielt nach NULL suchen kann

Voraussetzung hierfür ist, dass die Spalte „Waffe“ einen Index besitzt und die Spalte „Waffe“ NULL enthält.

index_merge: Dieser Zugriffstyp verwendet die Index-Merge-Optimierungsmethode

Dies ist ebenfalls bedingt. Sowohl die ID-Spalte als auch die Waffenspalte haben einspaltige Indizes. Wenn „index_merge“ auftritt und dieser SQL-Typ später häufig verwendet wird, können Sie den einspaltigen Index durch einen zusammengesetzten Index ersetzen, was effizienter ist.

unique_subquery: Ähnlich wie die eq_ref-Zugriffsmethode der gesteuerten Tabelle in einem Join mit zwei Tabellen wird unique_subquery in einigen Abfrageanweisungen verwendet, die IN-Unterabfragen enthalten. Wenn der Abfrageoptimierer beschließt, die IN-Unterabfrage in eine EXISTS-Unterabfrage umzuwandeln, und die Unterabfrage den Primärschlüssel oder den eindeutigen Index für die Übereinstimmung gleicher Werte verwenden kann, wird unique_subquery verwendet.

index_subquery: index_subquery ist ähnlich zu unique_subquery, außer dass ein normaler Index verwendet wird, um in der Unterabfrage auf die Tabelle zuzugreifen.

Bereich: Verwenden Sie einen Index, um Zeilen in einem bestimmten Bereich abzurufen. Wenn Sie die Operatoren =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN oder IN verwenden und Schlüsselwortspalten mit Konstanten vergleichen, wird der Bereich verwendet. Die Voraussetzung ist, dass es auf einem Index basieren muss, d. h. es muss einen Index für die ID geben.

Index: Wenn wir die Indexabdeckung nutzen können, aber alle Indexdatensätze scannen müssen, verwenden wir den Index. Dies ist bei der Erstellung von Statistiken sehr üblich.

ALL: Der bekannte vollständige Tabellenscan

Possible_keys: zeigt, welche Indizes in diesem SQL verwendet werden können, aber nicht unbedingt während der Abfrage verwendet werden. Wenn es leer ist, bedeutet dies, dass kein Index vorhanden ist, der verwendet werden kann. In diesem Fall können Sie die WHERE-Anweisung überprüfen, um festzustellen, ob Sie auf bestimmte Spalten verweisen oder einen neuen Index erstellen können, um die Leistung zu verbessern.

Schlüssel: Zeigt den Index an, der tatsächlich von diesem SQL verwendet wird. Wenn kein Index ausgewählt ist, ist diese Spalte null. Um MySQL zu zwingen, den Index in der Spalte „possible_keys“ zu verwenden oder zu ignorieren, verwenden Sie in der Abfrage FORCE INDEX, USE INDEX oder I GNORE INDEX.

key_len: Zeigt die von MySQL zu verwendende Schlüssellänge (in Bytes). Wenn der Schlüssel NULL ist, ist die Länge NULL. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

ref: zeigt, was der Indexspalte entspricht, z. B. einer Konstanten oder einer Spalte. Es zeigt den Namen der Spalte (oder Konstante) an, der häufig Null ist.

Zeilen: Zeigt die Anzahl der Zeilen an, die nach Ansicht des MySQL-Parsers beim Ausführen dieses SQL gescannt werden müssen. Bei diesem Wert handelt es sich um eine Schätzung und nicht um einen konkreten Wert, der in der Regel kleiner ist als der tatsächliche Wert.

Gefiltert: zeigt das Verhältnis der Anzahl der zurückgegebenen Zeilen zur Anzahl der zu lesenden Zeilen (den Zeilenwert). Natürlich gilt: je kleiner, desto besser.

Extra:

Kennzeichnet Zusatzinformationen, die in den anderen Spalten nicht enthalten sind, aber dennoch wichtig sind. Es gibt viele mögliche Werte. Sehen wir uns einige häufige an.

Index verwenden: Gibt an, dass SQL einen überdeckenden Index verwendet, anstatt zum Abfragen der Daten zur Tabelle zurückzukehren, was zu einer sehr guten Leistung führt.

Using where: gibt an, dass die Speicher-Engine nach der Suche nach Datensätzen eine Nachfilterung durchführt. Wenn die Abfrage den Index nicht verwendet, erinnert uns using where nur daran, dass MySQL die Where-Bedingung verwenden sollte, um den Ergebnissatz zu filtern.

Temporär verwenden: bedeutet, dass MySQL eine temporäre Tabelle zum Speichern des Ergebnissatzes verwenden muss, was beim Sortieren und Gruppieren von Abfragen üblich ist

Dateisortierung verwenden: Gibt an, dass MySQL den Index nicht direkt zum Sortieren verwenden kann (das sortierte Feld ist kein Indexfeld) und Pufferspeicher (Speicher oder Festplatte) zum Sortieren verwendet wird. Im Allgemeinen gibt dieser Wert an, dass SQL optimiert werden muss, was viel CPU verbraucht.

impossible where: Diese Zusatzinformation wird angezeigt, wenn die WHERE-Klausel der Abfrageanweisung immer FALSE ist

Natürlich gibt es noch andere, die nicht üblich sind. Bitte schauen Sie nach ihnen, wenn Sie auf sie stoßen!!!

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“, „Zusammenfassung der Kenntnisse zu MySQL-Datenbanksperren“ und „Zusammenfassung der allgemeinen MySQL-Funktionen“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.