Detaillierte Erklärung der Rolle von Explain in MySQL

1. MySQL-Index

Index: Eine Datenstruktur, die MySQL dabei hilft, Daten effizient abzurufen. Wird verwendet, um die Sucheffizienz zu verbessern und kann mit einem Wörterbuch verglichen werden. Es kann einfach als sortierte, schnell durchsuchbare Datenstruktur verstanden werden.

Die Rolle des Indexes: Abfragen und Sortieren zu erleichtern (das Hinzufügen eines Indexes wirkt sich also auf die Where-Anweisung und die Order-by-Sortieranweisung aus).

Zusätzlich zu den Daten enthält die Datenbank auch Datenstrukturen, die bestimmten Suchalgorithmen genügen und in irgendeiner Weise auf die Daten verweisen. Dadurch können erweiterte Suchalgorithmen auf diesen Datenstrukturen implementiert werden. Diese Datenstrukturen sind Indizes.

Der Index selbst ist außerdem sehr groß und es ist unmöglich, ihn vollständig im Speicher zu speichern. Daher wird der Index häufig in Form von Indexdateien auf der Festplatte gespeichert.

Wenn wir von Indizes sprechen, handelt es sich, sofern nicht anders angegeben, normalerweise um B-Baum-Indizes. (Clusterindizes, zusammengesetzte Indizes, Präfixindizes und eindeutige Indizes sind standardmäßig alle B+-Baumindizes.) Neben B-Baumindizes gibt es auch Hash-Indizes.

Vorteil:

A. Verbessern Sie die Effizienz des Datenabrufs und senken Sie die Datenbank-E/A-Kosten
B. Das Sortieren der Daten nach Indexspalten reduziert den Aufwand für das Sortieren der Daten und die CPU-Auslastung.

Mangel:

A. Der Index ist auch eine Tabelle, die den Primärschlüssel und die Indexfelder speichert und auf die Datensätze der Entitätstabelle verweist, sodass der Index auch Speicherplatz einnimmt.
B. Wenn Sie INSERT-, UPDATE- oder DELETE-Vorgänge an einer Tabelle ausführen, aktualisiert MySQL nicht nur die Daten, sondern speichert bei jeder Aktualisierung auch die entsprechenden Informationen der Indexspaltenfelder, die der Indexdatei hinzugefügt werden.

In der tatsächlichen Produktionsumgebung müssen wir Schritt für Schritt analysieren, optimieren, den besten Index erstellen und unsere Abfragebedingungen optimieren.

Indexklassifizierung:

1. Einzelwertindex: Ein Index enthält nur ein Feld und eine Tabelle kann mehrere einspaltige Indizes haben.
2. Der Wert der eindeutigen Indexspalte muss eindeutig sein, aber Nullwerte sind zulässig.
3. Zusammengesetzter Index: Ein Index enthält mehrere Spalten

Es wird empfohlen, nicht mehr als 5 Indizes für eine Tabelle zu erstellen.

Grammatik:

1. CREATE [UNIQUE] INDEX indexName ON myTable (Spaltenname(Länge));
2. ALTER myTable Add [UNIQUE] INDEX [indexName] ON (spaltenName(Länge));

Löschen: DROP INDEX [indexName] ON myTable;

Ansicht: SHOW INDEX FROM table_name\G;

2. Die Rolle von EXPLAIN

ERKLÄREN: Simulieren Sie, wie der MySQL-Optimierer SQL-Abfrageanweisungen ausführt, damit Sie erfahren, wie MySQL Ihre SQL-Anweisungen verarbeitet. Analysieren Sie den Leistungsengpass Ihrer Abfrageanweisung oder Tabellenstruktur.

mysql> erklären Sie select * from tb_user;
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+
| 1 | EINFACH | tb_user | ALLE | NULL | NULL | NULL | NULL | 1 | NULL |
+----+----------+---------+------+---------------+-----------+---------+---------+------+------+------+

(a) ID-Spalte:

(1) Die Ausführungsreihenfolge derselben ID ist von oben nach unten

mysql> erklären 
  -> WÄHLEN SIE * AUS tb_order tb1
  -> LINKS JOIN tb_Produkt tb2 ON tb1.tb_Produkt_id = tb2.id
  -> LINKS JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+----------+----------+--------+---------------+---------+---------+---------+---------------------------+---------+---------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+--------+---------------+---------+---------+---------+---------------------------+---------+---------+
| 1 | EINFACH | tb1 | ALLE | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | EINFACH | tb2 | eq_ref | PRIMÄR | PRIMÄR | 4 | product.tb1.tb_product_id | 1 | NULL |
| 1 | EINFACH | tb3 | eq_ref | PRIMÄR | PRIMÄR | 4 | product.tb1.tb_user_id | 1 | NULL |
+----+----------+----------+--------+---------------+---------+---------+---------+---------------------------+---------+---------+

(2) Wenn es sich um eine Unterabfrage handelt, wird die ID-Nummer automatisch erhöht. Je größer der ID-Wert, desto höher die Priorität und desto früher wird sie ausgeführt.

mysql> ERKLÄREN
  -> wähle * aus tb_product tb1, wobei tb1.id = (wähle tb_product_id aus tb_order tb2, wobei id = tb2.id =1);
+----+-----------+----------+-----------+---------------+---------+---------+---------+-----------+---------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+---------+---------+---------+-----------+---------+-------------+
| 1 | PRIMARY | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | UNTERABFRAGE | tb2 | ALLE | NULL | NULL | NULL | NULL | 1 | Verwenden von „where“ |
+----+-----------+----------+-----------+---------------+---------+---------+---------+-----------+---------+-------------+

(3) Die IDs sind gleich und verschieden und existieren gleichzeitig

mysql> ERKLÄREN 
  -> wähle * aus (wähle * aus tb_order tb1, wobei tb1.id = 1) s1, tb_user tb2, wobei s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+------------+---------+---------+---------+---------+-------+-------+---------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-------------+------------+--------+------------+---------+---------+---------+---------+-------+-------+---------+
| 1 | PRIMARY | <abgeleitet2> | System | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | PRIMARY | tb2 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | ABGELEITET | tb1 | const | PRIMÄR | PRIMÄR | 4 | const | 1 | NULL |
+----+-------------+------------+--------+------------+---------+---------+---------+---------+-------+-------+---------+

Derived2: Abgeleitete Tabelle 2 bedeutet, dass die abgeleitete Tabelle die Tabelle tb1 mit der ID=2 ist.

(ii) Spalte select_type: der Operationstyp der Datenleseoperation

1. EINFACH: Einfache Auswahlabfrage, SQL enthält keine Unterabfragen oder UNION.
2. PRIMARY: Die Abfrage enthält komplexe Unterabfragen, und die äußerste Abfrage ist als PRIMARY gekennzeichnet.
3. UNTERABFRAGE: Die Unterabfrage ist in der Auswahl- oder WHERE-Liste enthalten. 4. ABGELEITET: Die in der FROM-Liste enthaltene Unterabfrage wird als ABGELEITET (abgeleitete Tabelle) markiert. MYSQL führt diese Unterabfragen rekursiv aus und fügt den Ergebnissatz in die temporäre Tabelle ein.
5. UNION: Wenn das zweite SELECT nach UNION erscheint, wird es als UNION gekennzeichnet; wenn UNION in einer Unterabfrage in der FROM-Klausel enthalten ist, wird das äußere SELECT als DERIVED gekennzeichnet
6. UNION RESULT: Wählen Sie diese Option, um Ergebnisse aus der UNION-Tabelle zu erhalten.

(III) Tabellenspalte: Auf welche Tabelle bezieht sich die Datenzeile?

4. Spalte „Typ“: Zugriffstyp, vom besten zum schlechtesten: system > const > eq_ref > ref > range > index > ALL

1. System: Die Tabelle hat nur einen Datensatz (entspricht der Systemtabelle). Dies ist ein Sonderfall des Typs const und tritt im Normalbetrieb nicht auf.
2. const: Daten einmal über den Index finden. Dieser Typ wird hauptsächlich zum Vergleichen von Primärschlüsseln oder eindeutigen Indizes verwendet. Da er nur mit einer Datenzeile übereinstimmt, ist er sehr schnell. Wenn der Primärschlüssel nach der WHERE-Anweisung platziert wird, kann Mysql die Abfrage in eine Konstante umwandeln.
3. eq_ref: Eindeutiger Index-Scan. Für jeden Indexschlüssel gibt es in der Tabelle nur einen Datensatz, der damit übereinstimmt. Häufig bei Primärschlüssel- oder eindeutigen Indexscans.
4. ref: Nicht eindeutiger Index-Scan, gibt alle Zeilen zurück, die einem einzelnen Wert entsprechen. Es handelt sich im Wesentlichen um einen Indexzugriff, der alle Zeilen zurückgibt, die einem einzelnen Wert entsprechen. Dies bedeutet, dass möglicherweise mehrere qualifizierte Daten gefunden werden. Es handelt sich also um eine Mischung aus Suche und Scan.
5. Bereich: Ruft nur Zeilen in einem bestimmten Bereich ab und verwendet einen Index zum Auswählen der Zeilen. In der Schlüsselspalte ist zu sehen, welcher Index verwendet wurde. Im Allgemeinen erscheinen Abfragen wie „between“, „<“, „>“ und „in“ in Ihrer WHERE-Anweisung. Dieser Bereichsscan ist besser als ein vollständiger Tabellenscan. Da er nur an einer Stelle im Index beginnen und an einer anderen Stelle enden muss, ist es nicht erforderlich, den gesamten Index zu scannen.
6. Index: Der vollständige Indexscan scannt und durchläuft den Indexbaum (scannt den Index der gesamten Tabelle und ruft Daten aus dem Index ab).
7. ALLE vollständigen Tabellenscans holen Millionen von Daten von der Festplatte. ALLE Datentypen werden so weit wie möglich optimiert.

(V) Spalte „possible_keys“: zeigt einen oder mehrere Indizes an, die auf diese Tabelle angewendet werden können. Wenn für ein in der Abfrage enthaltenes Feld ein Index vorhanden ist, wird dieser zwar aufgelistet, aber möglicherweise nicht tatsächlich von der Abfrage verwendet.

(6) Spalte „Schlüssel“: der tatsächlich verwendete Index. Wenn NULL, wird kein Index verwendet. Wenn in einer Abfrage ein überdeckender Index verwendet wird, erscheint der Index nur in der Schlüsselliste. Abdeckungsindex: Die Felder nach der Auswahl entsprechen der Anzahl der von uns indizierten Felder.

(VII) Spalte ken_len: gibt die Anzahl der im Index verwendeten Bytes an. Mit dieser Spalte kann die in der Abfrage verwendete Indexlänge berechnet werden. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren. 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.

(8) ref column: gibt an, welche Spalte des Index verwendet wird, wenn möglich eine Konstante. Welche Spalten oder Konstanten werden zum Nachschlagen von Werten in der Indexspalte verwendet?

9. Die Zeilenspalte (wie viele Zeilen jeder Tabelle vom Optimierer abgefragt werden): Basierend auf den Tabellenstatistiken und der Indexauswahl wird grob die Anzahl der Zeilen geschätzt, die gelesen werden müssen, um die erforderlichen Datensätze zu finden.

(10) Zusatzspalte: erweiterte Attribute, aber auch sehr wichtige Informationen.

1. Verwenden von Filesort: MySQL kann die Tabelle nicht in der Reihenfolge der angegebenen Indizes lesen.

 mysql> erläutern Sie „select order_number from tb_order order by order_money“;
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Filesort verwenden |
+----+----------+----------+------+---------------+-----------+---------+---------+------+------+----------------+
1 Zeile im Satz (0,00 Sek.)

Hinweis: „order_number“ ist eine eindeutige Indexspalte in der Tabelle, aber „order by“ verwendet diese Indexspalte nicht zum Sortieren, sodass MySQL zum Sortieren eine andere Spalte verwenden muss.

2. Temporäre Tabellen verwenden: MySQL verwendet temporäre Tabellen, um Zwischenergebnisse zu speichern, was häufig bei Order-By- und Group-By-Abfragen verwendet wird.

mysql> erläutern Sie „select order_number“ aus der tb_order-Gruppe nach order_money;
+----+----------+----------+------+---------------+------+------+------+------+------+---------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+------+---------------+------+------+------+------+------+---------------------------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Wird temporär verwendet; Wird Filesort verwendet |
+----+----------+----------+------+---------------+------+------+------+------+------+---------------------------------+
1 Zeile im Satz (0,00 Sek.)

3. Die Verwendung eines Index bedeutet, dass der entsprechende Auswahlvorgang einen überdeckenden Index verwendet, wodurch der Zugriff auf die Datenzeilen der Tabelle vermieden wird und eine gute Effizienz aufweist.

Wenn gleichzeitig „Using where“ erscheint, bedeutet dies, dass der Index für die Suche nach dem Indexschlüsselwert verwendet wird.

Wenn „using where“ nicht vorhanden ist, wird der Index zum Lesen der Daten verwendet, nicht zum Durchführen einer Suche.

mysql> erläutern Sie „select order_number“ aus der tb_order-Gruppe nach order_number;
+----+----------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE | tb_order | index | index_order_number | index_order_number | 99 | NULL | 1 | Index wird verwendet |
+----+----------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 Zeile im Satz (0,00 Sek.)

4. Verwenden von „Wo suchen“

5. Join-Puffer verwenden: Zeigt an, dass das aktuelle SQL den Verbindungscache verwendet.

6. Unmögliches Where: Die Where-Klausel ist immer falsch und MySQL kann die Datenzeile nicht abrufen.

7. Select-Tabellen wegoptimiert:

8. deutlich:

Zusammenfassen

Dies ist der gesamte Inhalt dieses Artikels zur detaillierten Erläuterung der Rolle von Explain in MySQL. Ich hoffe, dass er für alle hilfreich sein wird. Interessierte Freunde können sich auf Folgendes beziehen: Beispielanalyse der Optimierung von MySQL-Unterabfragen und verschachtelten Abfragen, mehrere wichtige MySQL-Variablen, Analyse technischer Punkte zur Optimierung von Oracle-SQL-Anweisungen usw. Wenn es Mängel gibt, hinterlassen Sie bitte eine Nachricht und der Herausgeber wird Ihnen antworten und rechtzeitig Korrekturen vornehmen. Vielen Dank, Freunde, für die Unterstützung dieser Site!