Erklären, wie die SQL-Effizienz analysiert wird

Der Befehl „Explain“ ist der erste empfohlene Befehl zum Lösen von Datenbankleistungsproblemen. Die meisten Leistungsprobleme lassen sich mit diesem Befehl problemlos lösen. Mit „Explain“ können Sie die Ausführungseffekte von SQL-Anweisungen anzeigen, was dabei helfen kann, bessere Indizes auszuwählen, Abfrageanweisungen zu optimieren und besser optimierte Anweisungen zu schreiben.

Syntax erklären:

EXPLAIN tbl_name oder: EXPLAIN [EXTENDED] SELECT select_options

Ersteres kann die Feldstruktur einer Tabelle usw. ableiten, während letzteres hauptsächlich einige zugehörige Indexinformationen bereitstellt. Heute werden wir uns auf Letzteres konzentrieren.

Beispiel:

ERKLÄREN 
  SELECT Summe(Betrag) 
VON Kunde a, Zahlung b 
  WO1 = 1 
UND a.customer_id = b.customer_id 
UND a.email = '[email protected]'; 

Ausführungsergebnis:

Schauen wir uns die einzelnen Attribute genauer an:

1. id: Dies ist die Abfragesequenznummer von SELECT

2. select_type: select_type ist der Auswahltyp und kann wie folgt lauten:

SIMPLE: Einfaches SELECT (verwendet keine UNION oder Unterabfragen usw.)

PRIMARY: das äußerste SELECT

UNION: Die zweite oder nachfolgende SELECT-Anweisung in einer UNION

DEPENDENT UNION: Die zweite oder nachfolgende SELECT-Anweisung in einer UNION hängt von der äußeren Abfrage ab

UNION-ERGEBNIS: Das Ergebnis von UNION.

SUBQUERY: Die erste SELECT-Anweisung in einer Unterabfrage

ABHÄNGIGE UNTERABFRAGE: Die erste SELECT-Anweisung in einer Unterabfrage, die von der äußeren Abfrage abhängt.

DERIVED: SELECT der exportierten Tabelle (Unterabfrage in der FROM-Klausel)

3. Tabelle: zeigt den tatsächlichen Tabellennamen (z. B. „select * from customer;“) oder den Tabellenalias (z. B. „select * from customer a“), auf den sich die Daten in dieser Zeile beziehen;

4. Typ: Diese Spalte ist die wichtigste. Sie zeigt, welcher Verbindungstyp verwendet wird und ob ein Index verwendet wird. Sie ist eines der wichtigsten Elemente für die Analyse von Leistungsengpässen mit dem Befehl „Explain“.

Die Ergebnisse vom besten bis zum schlechtesten sind:

System > const > eq_ref > ref > Volltext > ref_or_null > Index_Merge > eindeutige Unterabfrage > Index_Unterabfrage > Bereich > Index > ALLE

Generell muss darauf geachtet werden, dass die Abfrage mindestens die Bereichsebene, besser noch die Referenzebene erreicht, da es sonst zu Performanceproblemen kommen kann.

all : bedeutet, die gesamte Tabelle zeilenweise zu scannen, beginnend mit der ersten Zeile der Tabelle. Wenn Sie Pech haben, scannen Sie möglicherweise die letzte Zeile.

Index : etwas bessere Leistung als alle anderen.
Einfach ausgedrückt: all durchsucht alle Datenzeilen, entspricht data_all index durchsucht alle Indexknoten, entspricht index_all

Hinweis: Alles wird entlang der Festplatte gescannt, der Index wird entlang des Index gescannt

Bereich : bedeutet, dass Sie bei der Abfrage den Bereich basierend auf dem Index scannen können

Erläutern Sie „Select * from customer“, wobei „customer_id“ > 4 ist.

index_subquery : Scannen Sie in einer Unterabfrage basierend auf einem anderen Index als einem eindeutigen Index.

unique_subquery scannt basierend auf einem eindeutigen Index in einer Unterabfrage, ähnlich wie EQ_REF;

index_merge mehrere Bereichsscans. Bei der Verbindung der beiden Tabellen befindet sich im Verbindungsfeld jeder Tabelle ein Index. Die Indizes sind in der richtigen Reihenfolge und die Ergebnisse werden zusammengeführt. Anwendbar auf Vereinigungs- und Schnittmengenoperationen.

ref_or_null ist ähnlich wie REF, außer dass die Suchbedingung den Fall einschließt, in dem der Wert des Verbindungsfelds NULL sein kann, z. B. wenn col = 2 oder col null ist

Volltext Volltextindex

ref Dies ist ebenfalls ein Indexzugriff, der alle Zeilen zurückgibt, die einem einzelnen Wert entsprechen. Es können jedoch mehrere qualifizierte Zeilen gefunden werden, daher sollte es sich um eine Mischung aus Suche und Scan handeln (ebenfalls ein Bereichsintervall, aber genauer als Bereich).

Erläutern Sie „Select * from payment“, wobei customer_id = 4 ist.

eq_ref bedeutet, dass eine Datenzeile (genau auf eine Datenzeile) direkt über die Indexspalte referenziert wird. Dies wird häufig in Join-Abfragen verwendet.

const, system, null Wenn MySQL einen Teil der Abfrage optimieren und in eine Konstante umwandeln kann, wird dieser Zugriffstyp verwendet. Wenn Sie beispielsweise den Primärschlüssel einer Zeile als Where-Bedingung festlegen, kann MySQL ihn in eine Konstante umwandeln und dann abfragen.

5. Possible_keys: Die Spalte gibt an, welchen Index MySQL verwenden kann, um Zeilen in der Tabelle zu finden

6. Schlüssel: Zeigt den Schlüssel (Index), den MySQL tatsächlich verwenden wollte. Wenn kein Index ausgewählt ist, ist der Schlüssel NULL

7. key_len: Zeigt die von MySQL zu verwendende Schlüssellänge an. Wenn der Schlüssel NULL ist, ist die Länge NULL. Die Länge des zu verwendenden Indexes. Je kürzer die Länge, desto besser, ohne an Genauigkeit zu verlieren.

8. ref: Zeigt, welche Spalte oder Konstante zusammen mit dem Schlüssel verwendet wird, um Zeilen aus der Tabelle auszuwählen.

9. Zeilen: Zeigt die Anzahl der Zeilen, die MySQL seiner Meinung nach untersuchen muss, um die Abfrage auszuführen.

10. Extra: Enthält detaillierte Informationen zur Lösung von Abfragen durch MySQL und ist zugleich eines der wichtigsten Nachschlagewerke.

using index: Dies zeigt an, dass MySQL einen überdeckenden Index verwendet, um den Zugriff auf die Datenzeilen der Tabelle zu vermeiden, was ziemlich effizient ist! 
using where: Dies gibt an, dass der Server die Zeilen filtert, nachdem er sie von der Speicher-Engine erhalten hat. Einige Where-Bedingungen können Spalten enthalten, die zum Index gehören. Wenn der Index gelesen wird, wird er gefiltert. Daher haben einige Where-Anweisungen in der zusätzlichen Spalte nicht die Beschreibung „using where“. 
using temporary: Dies bedeutet, dass MySQL beim Sortieren der Abfrageergebnisse eine temporäre Tabelle verwendet. 
using filesort: Das bedeutet, dass MySQL zum Sortieren der Daten einen externen Index verwendet, anstatt sie in der Reihenfolge des Index in der Tabelle zu lesen. 

Darüber hinaus kann die erweiterte Erweiterung von Explain einige zusätzliche Informationen zur Abfrageoptimierung basierend auf dem ursprünglichen Explain bereitstellen. Diese Informationen können über den Befehl mysql show warnings abgerufen werden. Hier ist ein einfaches Beispiel.

ERWEITERT ERKLÄREN
SELECT Summe(Betrag)
VON Kunde a, Zahlung b
WO 1 = 1
UND a.customer_id = b.customer_id
UND a.email = '[email protected]';

Führen Sie als nächstes Show Warnings aus.

mysql> Warnungen anzeigen;
+---------+------+----------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Ebene | Code | Nachricht

|
+---------+------+----------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warnung | 1681 | „EXTENDED“ ist veraltet und wird in einer zukünftigen Version entfernt
ase.

|
| Hinweis | 1003 | /* Auswahl Nr. 1 */ Auswahl Summe(`sakila`.`b`.`Betrag`) AS `Summe(Betrag
nt)` von `sakila`.`Kunde` `a` verbinden `sakila`.`Zahlung` `b` wobei ((`sakila`.`
b`.`customer_id` = `sakila`.`a`.`customer_id`) und (`sakila`.`a`.`email` = 'JANE
[email protected]')) |
+---------+------+----------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
2 Zeilen im Satz (0,00 Sek.)

Sie können sehen, dass der Optimierer automatisch die Bedingung entfernt, dass 1=1 immer wahr ist.

MySQL 5.1 begann, die Partitionierungsfunktion zu unterstützen, und der Befehl „Explain“ fügte auch Unterstützung für die Partitionierung hinzu. Mit dem Befehl „explain partitions“ können Sie die Partitionen anzeigen, auf die SQL zugreift.

Die oben erläuterte Methode zur Analyse der SQL-Effizienz ist der gesamte Inhalt, den der Editor mit Ihnen teilt. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.