So optimieren Sie die MySQL-Indexfunktion basierend auf dem Schlüsselwort „Explain“

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. Vereinfacht ausgedrückt besteht seine Funktion darin, die Abfrageleistung zu analysieren.

Die Verwendung des Schlüsselworts „explain“ ist sehr einfach: Setzen Sie es einfach vor die Select-Abfrageanweisung.

Um zu überprüfen, ob MySQL einen Index verwendet, schauen Sie sich einfach den Typ an. Wenn es „alle“ ist, bedeutet dies, dass diese Abfrageanweisung alle Zeilen durchläuft und den Index nicht verwendet.

Beispiel: Erklären Sie „select * from company_info where cname like '%小%'“

Erklären Sie „select * from company_info“, wobei Cname wie „小%“ aussieht.

die folgende

1) Je größer die Zahl in der ID-Spalte, desto früher wird sie ausgeführt. Wenn die Zahlen gleich sind, werden sie von oben nach unten ausgeführt. Wenn die ID-Spalte null ist, bedeutet dies, dass es sich um einen Ergebnissatz handelt und dieser nicht für die Abfrage verwendet werden muss.

2) Zu den allgemeinen select_type-Spalten gehören:

• A: einfach: Gibt eine einfache Auswahlabfrage an, die keine Vereinigungsoperation erfordert oder keine Unterabfrage enthält. Bei einer Join-Abfrage ist die äußere Abfrage einfach und es gibt nur eine
• B: primär: Eine Auswahl, die eine Union-Operation erfordert oder eine Unterabfrage enthält. Der Auswahltyp der äußersten Unit-Abfrage ist primär. Und nur einer
• C: Union: Zwei Auswahlabfragen, die durch Union verbunden sind. Die erste Abfrage ist eine abgeleitete Tabelle. Mit Ausnahme der ersten Tabelle ist der Auswahltyp der zweiten und der folgenden Tabellen Union.
• D: Abhängige Union: Wie Union erscheint es in einer Union- oder Union-All-Anweisung, aber diese Abfrage wird von der externen Abfrage beeinflusst
• E: Union-Ergebnis: enthält den Ergebnissatz der Union. In den Anweisungen „Union“ und „Union All“ ist das ID-Feld null, da es nicht an der Abfrage teilnehmen muss.
• F: Unterabfrage: Mit Ausnahme der in der From-Klausel enthaltenen Unterabfrage kann jede Unterabfrage, die an anderer Stelle erscheint, eine Unterabfrage sein.
• G: Abhängige Unterabfrage: Ähnlich wie bei der abhängigen Vereinigung bedeutet dies, dass die Abfrage dieser Unterabfrage von der Abfrage der externen Tabelle beeinflusst wird.
• H: abgeleitet: Die Unterabfrage, die in der From-Klausel erscheint, wird auch als abgeleitete Tabelle bezeichnet. Andere Datenbanken nennen sie möglicherweise Inline-Ansicht oder verschachtelte Auswahl.
  

3) Tabelle

Der Name der Abfragetabelle wird angezeigt. Wenn die Abfrage einen Alias ​​verwendet, wird dieser hier angezeigt. Wenn es sich nicht um Operationen an der Datentabelle handelt, wird er als null angezeigt. Wenn er als <derived N> in spitzen Klammern angezeigt wird, bedeutet dies, dass es sich um eine temporäre Tabelle handelt. Das N dahinter ist die ID im Ausführungsplan und zeigt an, dass das Ergebnis aus dieser Abfrage stammt. Wenn es sich um <union M,N> in spitzen Klammern handelt, ähnelt es <derived N> 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.

4) Typ

Vom Besten zum Schlechtesten: system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL. Mit Ausnahme von „all“ können alle anderen Typen Indizes verwenden. Mit Ausnahme von „index_merge“ können andere Typen nur einen Index verwenden.

• A: System: Die Tabelle enthält nur eine Datenzeile oder ist eine leere Tabelle und kann nur für MyISAM- und Memory-Tabellen verwendet werden. Wenn es sich um eine Innodb-Engine-Tabelle handelt, ist die Typspalte in diesem Fall normalerweise all oder index
• B: const: Wenn ein eindeutiger Index oder Primärschlüssel verwendet wird und die zurückgegebenen Datensätze einer Datensatzzeile in der Where-Bedingung entsprechen müssen, ist der Typ normalerweise const. Andere Datenbanken nennen es auch Unique Index Scan
• C: eq_ref: Erscheint im Abfrageplan, um eine Verbindung zu zwei Tabellen herzustellen. Die treibende Tabelle gibt nur eine Datenzeile zurück, und diese Datenzeile ist der Primärschlüssel oder eindeutige Index der zweiten Tabelle und darf nicht null sein. Wenn der eindeutige Index und der Primärschlüssel mehrere Spalten sind, wird eq_ref nur angezeigt, wenn alle Spalten zum Vergleich verwendet werden.
• D: ref: Im Gegensatz zu eq_ref ist keine Verbindungsreihenfolge erforderlich und es ist kein Primärschlüssel oder eindeutiger Index erforderlich. Es kann bei der Suche mit gleichen Bedingungen erscheinen und wird häufig für gleichwertige Suchen mit Hilfsindizes verwendet. Oder in mehrspaltigen Primärschlüsseln und eindeutigen Indizes können auch andere Spalten als die erste Spalte als gleichwertige Suchvorgänge verwendet werden. Kurz gesagt können gleichwertige Suchvorgänge auftreten, die nicht eindeutige Daten zurückgeben.
• E: Volltext: Abrufen des Volltextindex. Bitte beachten Sie, dass der Volltextindex eine hohe Priorität hat. Wenn der Volltextindex und der normale Index gleichzeitig vorhanden sind, gibt MySQL dem Volltextindex unabhängig von den Kosten Vorrang.
• F: ref_or_null: Ähnlich der ref-Methode, außer dass ein Vergleich von Nullwerten hinzugefügt wird. Eigentlich nicht viel verwendet.
• G: unique_subquery: wird für In-Form-Unterabfragen verwendet, wobei die Unterabfrage eindeutige Werte ohne Duplizierung zurückgibt
• H: index_subquery: wird für Unterabfragen im Formular verwendet, die Hilfsindizes oder Konstantenlisten verwenden. Die Unterabfrage kann doppelte Werte zurückgeben, und der Index kann zum Entfernen von Duplikaten der Unterabfrage verwendet werden.
• I: Bereich: Indexbereichsscan, wird häufig in Abfragen mit Operatoren wie >, <, ist null, zwischen, in, wie usw. verwendet.
• J: index_merge: Gibt an, dass die Abfrage mehr als zwei Indizes verwendet und schließlich die Schnittmenge oder Vereinigungsmenge annimmt. Gemeinsame und oder Bedingungen verwenden unterschiedliche Indizes. Die offizielle Rangfolge liegt nach ref_or_null, aber da tatsächlich alle Indizes gelesen werden müssen, ist die Leistung die meiste Zeit möglicherweise nicht so gut wie bei range.
• K: Index: Vollständiger Tabellenscan des Index, der Index wird von Anfang bis Ende gescannt. Es ist üblich, Indexspalten zu verwenden, um Abfragen zu verarbeiten, die keine Datendateien lesen müssen, und Abfragen, die Indexsortierung oder -gruppierung verwenden können.
• L: alle: Hiermit wird die Datendatei in der gesamten Tabelle gescannt und dann auf Serverebene gefiltert, um die Datensätze zurückzugeben, die die Anforderungen erfüllen.
  

5), mögliche Schlüssel

Alle Indizes, die von der Abfrage verwendet werden können, sind hier aufgelistet

6) Schlüssel

Abfrage der tatsächlich verwendeten Indizes. Wenn select_type index_merge ist, können hier mehr als zwei Indizes erscheinen. Bei anderen select_types wird hier nur ein Index angezeigt.

7) Schlüssellänge

Die Länge des Indexes, der zur Verarbeitung der Abfrage verwendet wird. Handelt es sich um einen einspaltigen Index, wird die gesamte Indexlänge berücksichtigt. Handelt es sich um einen mehrspaltigen Index, werden möglicherweise nicht alle Spalten verwendet. Die konkrete Anzahl der verwendeten Spalten wird hier berechnet. Nicht verwendete Spalten werden hier nicht berechnet. Achten Sie auf den Wert dieser Spalte und berechnen Sie die Gesamtlänge Ihres mehrspaltigen Indexes, um zu sehen, ob alle Spalten verwendet werden. Beachten Sie, dass die von der ICP-Funktion von MySQL verwendeten Indizes nicht gezählt werden. 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.

8) Referenz

Wenn die Abfrage eine Abfrage mit konstantem Wert ist, wird hier const angezeigt. Wenn es sich um eine Join-Abfrage handelt, zeigt der Ausführungsplan der getriebenen Tabelle die zugehörigen Felder der treibenden Tabelle an. Wenn die Bedingung einen Ausdruck oder eine Funktion verwendet oder die Bedingungsspalte eine interne implizite Konvertierung aufweist, wird sie möglicherweise als func angezeigt.

9) Reihen

Hier ist die geschätzte Anzahl der Scanzeilen im Ausführungsplan, nicht der genaue Wert

10) Extra

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

• A: distinct: Das Schlüsselwort distinct wird im Auswahlteil verwendet
• B: keine Tabellen verwendet: Abfrage ohne From-Klausel oder aus dualer Abfrage
• C: Eine Join-Abfrage mit einer Unterabfrage der Form „not in()“ oder dem Operator „not exists“ wird als Anti-Join bezeichnet. Das heißt, eine allgemeine Join-Abfrage fragt zuerst die innere Tabelle und dann die äußere Tabelle ab, während ein Anti-Join zuerst die äußere Tabelle und dann die innere Tabelle abfragt.
• D: using filesort: Dies tritt auf, wenn der Index beim Sortieren nicht verwendet werden kann. Häufig verwendet in Order by- und Group by-Anweisungen
• E: Index verwenden: Bei der Abfrage muss nicht zur Abfragetabelle zurückgekehrt werden. Die abgefragten Daten können direkt über den Index abgerufen werden.
• F: Verwendung des Join-Puffers (Block-Nested-Loop), Verwendung des Join-Puffers (Batch-Schlüsselzugriff): Versionen nach 5.6.x optimieren die BNL- und BKA-Funktionen zugehöriger Abfragen. Der Hauptzweck besteht darin, die Anzahl der Schleifen in der inneren Tabelle zu reduzieren und sequentielle Scan-Abfragen zu vergleichen.
• G: mit sort_union, mit_union, mit intersect, mit sort_intersection:
• using intersect: Gibt die Bedingungen für die Verwendung jedes Index von und an. Diese Information gibt an, dass die Schnittmenge aus den Verarbeitungsergebnissen erhalten wird
• Vereinigung verwenden: Gibt an, dass bei Verwendung oder zum Verbinden von Bedingungen, die den Index verwenden, diese Information angibt, dass die Vereinigung aus den Verarbeitungsergebnissen erhalten wird.
• using sort_union und using sort_intersection: Ähnlich wie die beiden vorherigen, außer dass sie erscheinen, wenn und und oder verwendet werden, um eine große Menge an Informationen abzufragen. Zuerst wird der Primärschlüssel abgefragt, und dann werden das Sortieren und Zusammenführen durchgeführt, bevor die Datensätze gelesen und zurückgegeben werden können.
• H: using temporary: Gibt an, dass eine temporäre Tabelle zum Speichern von Zwischenergebnissen verwendet wird. Temporäre Tabellen können temporäre Speichertabellen und temporäre Festplattentabellen sein. Sie sind im Ausführungsplan nicht sichtbar und können nur durch Überprüfen der Statusvariablen used_tmp_table und used_tmp_disk_table angezeigt werden.
• I: using where: gibt an, dass nicht alle vom Speicher-Engine zurückgegebenen Datensätze die Abfragebedingungen erfüllen und auf Serverebene gefiltert werden müssen. Die Abfragebedingungen sind in Einschränkungsbedingungen und Prüfbedingungen unterteilt. Vor 5.6 konnte die Speicher-Engine Daten nur basierend auf den Einschränkungsbedingungen scannen und zurückgeben. Anschließend filterte die Serverebene basierend auf den Prüfbedingungen und gab die Daten zurück, die die Abfrage tatsächlich erfüllten. 5.6.x und spätere Versionen unterstützen die ICP-Funktion, die die Prüfbedingungen auf die Speicher-Engine-Ebene verlagern kann. Daten, die die Prüfbedingungen und Einschränkungsbedingungen nicht erfüllen, werden nicht direkt gelesen, was die Anzahl der von der Speicher-Engine gescannten Datensätze erheblich reduziert. Die zusätzliche Spalte zeigt die verwendete Indexbedingung an.
• J: firstmatch(tb_name): Eine der neuen Funktionen zur Optimierung von Unterabfragen, die in 5.6.x eingeführt wurden und häufig in Where-Klauseln zu sehen sind, die Unterabfragen vom Typ in() enthalten. Wenn die Datenmenge in der internen Tabelle groß ist, kann dies passieren
• K: loosescan(m..n): Eine der neuen Funktionen zur Optimierung von Unterabfragen, die nach 5.6.x eingeführt wurden. Dies kann auftreten, wenn die Unterabfrage in einer Unterabfrage vom Typ in() doppelte Datensätze zurückgibt.
  

Darüber hinaus gibt es viele Abfragedatenwörterbuchbibliotheken, und während des Ausführungsplans werden einige Eingabeaufforderungsmeldungen gefunden, dass es unmöglich ist, Ergebnisse zu erhalten.

11), gefiltert

Diese Spalte wird angezeigt, wenn „explain extended“ verwendet wird. Versionen nach 5.7 haben dieses Feld standardmäßig, daher wird „explain extended“ nicht mehr benötigt. 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.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.