Standardzusammenfassung zur Analyse der Leistung einer SQL-Anweisung
|
In diesem Artikel wird die Verwendung von „Explain“ zur Analyse einer SQL-Anweisung vorgestellt. Es gibt tatsächlich viele Artikel im Internet, die die Verwendung von „explain“ ausführlich erläutern. Dieser Artikel kombiniert Beispiele und Prinzipien, um Ihnen ein besseres Verständnis zu vermitteln. Glauben Sie mir, Sie sollten etwas Besonderes daraus lernen, wenn Sie ihn sorgfältig lesen. Explain bedeutet Erklärung, was in MySQL als Ausführungsplan bezeichnet wird. Das heißt, Sie können diesen Befehl verwenden, um zu sehen, wie MySQL entscheidet, das SQL auszuführen, nachdem der Optimierer es analysiert hat. Apropos Optimierer: Lassen Sie mich hinzufügen, dass MySQL über einen integrierten leistungsstarken Optimierer verfügt. Die Hauptaufgabe des Optimierers besteht darin, das von Ihnen geschriebene SQL zu optimieren und es mit den geringstmöglichen Kosten auszuführen, z. B. indem weniger Zeilen gescannt werden, das Sortieren vermieden wird usw. Was passiert beim Ausführen einer SQL-Anweisung? Den Optimierer habe ich im vorherigen Artikel vorgestellt. Sie fragen sich vielleicht, wann wir Explain im Allgemeinen verwenden müssen? In den meisten Fällen verwenden wir Explain, um einige SQL-Anweisungen mit langsamer Abfrageeffizienz aus dem MySQL-Protokoll für langsame Abfragen zu analysieren. Manchmal verwenden wir Explain, um zu analysieren, ob der hinzugefügte Index bei der Optimierung von MySQL erreicht werden kann, z. B. beim Hinzufügen eines Index. Manchmal müssen Sie bei der Geschäftsentwicklung Explain verwenden, um eine effizientere SQL-Anweisung auszuwählen, die den Anforderungen entspricht. Wie verwendet man also „explain“? Das ist ganz einfach. Fügen Sie einfach „explain“ vor „sql“ ein, wie unten gezeigt. mysql> erklären Sie select * from t; +----+-----------+------+---------+---------------+------+---------+---------+---------+---------+---------+-----------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+-----------+------+---------+---------------+------+---------+---------+---------+---------+---------+-----------+ | 1 | EINFACH | t | ALLE | NULL | NULL | NULL | NULL | 100332 | NULL | +----+-----------+------+---------+---------------+------+---------+---------+---------+---------+---------+-----------+ 1 Zeile im Satz (0,04 Sek.) Wie Sie sehen, gibt Explain etwa 10 Felder zurück. Die von verschiedenen Versionen zurückgegebenen Felder unterscheiden sich geringfügig. Jedes Feld hat eine bestimmte Bedeutung. In diesem Artikel werde ich nicht jedes Feld im Detail vorstellen. Es gibt zu viele Dinge und ich fürchte, es ist nicht einfach für Sie, sich diese zu merken. Es ist besser, zunächst einige wichtige Felder zu verstehen. Unter ihnen sind meiner Meinung nach Typ, Schlüssel, Zeilen und zusätzliche Felder wichtiger. Lassen Sie uns anhand konkreter Beispiele die Bedeutung dieser Felder besser verstehen. Zunächst ist es notwendig, kurz die wörtliche Bedeutung dieser Felder vorzustellen. Typ gibt an, wie MySQL auf Daten zugreift. Gängige Typen sind vollständiger Tabellenscan (alle), Durchlaufindex (Index), Intervallabfrage (Bereich), Konstante oder gleiche Abfrage (Ref, Eq_Ref), Primärschlüssel-gleiche Abfrage (const), wenn in der Tabelle nur ein Datensatz vorhanden ist (System). Nachfolgend finden Sie eine Rangfolge von der besten bis zur schlechtesten Effizienz. System > const > eq_ref > ref > Bereich > Index > alle Schlüssel gibt den Indexnamen an, der tatsächlich im Abfrageprozess verwendet wird. Zeilen gibt die Anzahl der Zeilen an, die während des Abfragevorgangs möglicherweise gescannt werden müssen. Diese Daten sind möglicherweise nicht genau und stellen eine Stichprobenstatistik von MySQL dar. Extra gibt einige Zusatzinformationen an, die normalerweise zeigen, ob ein Index verwendet wird, ob eine Sortierung erforderlich ist, ob eine temporäre Tabelle verwendet wird usw. Okay, beginnen wir mit der Fallanalyse. Lassen Sie uns eine Testtabelle mit der im vorherigen Artikel erstellten Speicher-Engine erstellen. Wir werden 100.000 Testdatenelemente in die Tabelle einfügen. Die Tabellenstruktur ist wie folgt: TABELLE ERSTELLEN `t` ( `id` int(11) NICHT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMÄRSCHLÜSSEL (`id`) )ENGINE=InnoDB; Sehen Sie sich dann die folgende Abfrageanweisung an. Beachten Sie, dass diese Tabelle derzeit nur einen Primärschlüsselindex hat und kein normaler Index erstellt wurde. mysql> Tabelle ändern t Index hinzufügen a_index(a); Abfrage OK, 0 Zeilen betroffen (0,19 Sek.) Datensätze: 0 Duplikate: 0 Warnungen: 0 mysql> Tabelle ändern t Index hinzufügen b_index(b); Abfrage OK, 0 Zeilen betroffen (0,20 Sek.) Datensätze: 0 Duplikate: 0 Warnungen: 0 mysql> Index von t anzeigen; +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ | Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar | +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ | t | 0 | PRIMARY | 1 | ID | A | 100332 | NULL | NULL | | BTREE | | | | t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | JA | BTREE | | | | t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | JA | BTREE | | | +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ 3 Zeilen im Satz (0,00 Sek.) Der Typwert ist ALL, was bedeutet, dass die gesamte Tabelle gescannt wird. Beachten Sie, dass das Zeilenfeld 100.332 Datensätze anzeigt. Tatsächlich haben wir insgesamt nur 100.000 Datensätze, daher ist dieses Feld nur eine Schätzung von MySQL und möglicherweise nicht genau. Dieser vollständige Tabellenscan ist sehr ineffizient und muss optimiert werden. Als Nächstes fügen wir den Feldern a und b jeweils normale Indizes hinzu und sehen uns dann die SQL-Anweisungen nach dem Hinzufügen der Indizes an. mysql> Tabelle ändern t Index hinzufügen a_index(a); Abfrage OK, 0 Zeilen betroffen (0,19 Sek.) Datensätze: 0 Duplikate: 0 Warnungen: 0 mysql> Tabelle ändern t Index hinzufügen b_index(b); Abfrage OK, 0 Zeilen betroffen (0,20 Sek.) Datensätze: 0 Duplikate: 0 Warnungen: 0 mysql> Index von t anzeigen; +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ | Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar | +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ | t | 0 | PRIMARY | 1 | ID | A | 100332 | NULL | NULL | | BTREE | | | | t | 1 | a_index | 1 | a | A | 100332 | NULL | NULL | JA | BTREE | | | | t | 1 | b_index | 1 | b | A | 100332 | NULL | NULL | JA | BTREE | | | +-------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+ 3 Zeilen im Satz (0,00 Sek.) mysql> erläutern Sie „select * from t“, wobei a > 1000; +----+----------+-------+---------+---------------+------+---------+---------+---------+---------+-------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+----------+-------+---------+---------------+------+---------+---------+---------+---------+-------------+ | 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Verwenden von „where“ | +----+----------+-------+---------+---------------+------+---------+---------+---------+---------+-------------+ 1 Zeile im Satz (0,00 Sek.) Sieht das obige SQL ein wenig verwirrend aus? Type zeigt tatsächlich an, dass gerade ein Index zum Feld a hinzugefügt wurde, und Possible_Keys zeigt auch, dass a_Index verfügbar ist, aber Key zeigt null an, was darauf hinweist, dass MySQL den a-Index nicht wirklich verwenden wird. Warum ist das so? Dies liegt daran, dass Sie bei Verwendung von select * zum Primärschlüsselindex zurückkehren müssen, um nach Feld b zu suchen. Dieser Vorgang wird als Tabellenrückgabe bezeichnet. Diese Anweisung filtert 90.000 Daten heraus, die die Bedingungen erfüllen. Das heißt, diese 90.000 Daten müssen an die Tabelle zurückgegeben werden, und ein vollständiger Tabellenscan umfasst nur 100.000 Daten. Aus Sicht des MySQL-Optimierers ist es daher besser, die gesamte Tabelle direkt zu scannen, da zumindest der Tabellenrückgabeprozess vermieden wird. Natürlich bedeutet dies nicht, dass der Index nicht erreicht wird, solange eine Tabellenrückgabeoperation vorliegt. Der Schlüssel zur Verwendung des Index liegt darin, welche Abfragekosten MySQL als niedriger ansieht. Lassen Sie uns die Where-Bedingung im obigen SQL leicht ändern. mysql> erläutern Sie „select * from t“, wobei a > 99000; +----+-----------+----------+-----------+---------------+---------+---------+---------+------+------+---------------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+-----------+----------+-----------+---------------+---------+---------+---------+------+------+---------------------------------+ | 1 | SIMPLE | t | Bereich | a_index | a_index | 5 | NULL | 999 | Indexbedingung verwenden | +----+-----------+----------+-----------+---------------+---------+---------+---------+------+------+---------------------------------+ 1 Zeile im Satz (0,00 Sek.) Diesmal ist der Typwert range und der Schlüssel a_index, was bedeutet, dass Index a gefunden wurde. Dies ist eine gute Wahl, da nur 1.000 Datensätze diese SQL-Bedingung erfüllen. MySQL geht davon aus, dass selbst wenn 1.000 Datensätze an die Tabelle zurückgegeben werden, die Kosten geringer sind als beim Scannen der gesamten Tabelle. MySQL ist also tatsächlich ein sehr schlauer Kerl. Wir können auch sehen, dass der Wert im Feld „Extra“ „Indexbedingung verwenden“ lautet, was bedeutet, dass der Index verwendet wird, aber eine Tabellenrückgabe erforderlich ist. Sehen wir uns die folgende Anweisung an. mysql> erklären Sie „select a from t“, wobei a > 99000; +----+-----------+----------+-----------+---------------+---------+---------+---------+------+---------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+-----------+----------+-----------+---------------+---------+---------+---------+------+---------+--------------------------+ | 1 | SIMPLE | t | Bereich | a_index | a_index | 5 | NULL | 999 | Verwenden von where; Verwenden von index | +----+-----------+----------+-----------+---------------+---------+---------+---------+------+---------+--------------------------+ 1 Zeile im Satz (0,00 Sek.) Der Wert in diesem Extra lautet Using where; Using index, was bedeutet, dass die Abfrage den Index verwendet und die abzufragenden Felder im Index abgerufen werden können, ohne zur Tabelle zurückzukehren. Diese Effizienz ist offensichtlich höher als die oben genannte, schreiben Sie also nicht einfach select *. Fragen Sie nur die Felder ab, die das Unternehmen benötigt, um eine Rückkehr zur Tabelle so weit wie möglich zu vermeiden. Schauen wir uns noch ein weiteres Problem an, das geklärt werden muss. mysql> erklären Sie „Wählen Sie a aus t, wobei a > 99000, sortieren Sie nach b“; +----+-----------+-------+-----------+---------------+---------+---------+---------+------+------+---------------------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+-----------+-------+-----------+---------------+---------+---------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t | Bereich | a_index | a_index | 5 | NULL | 999 | Indexbedingung wird verwendet; Dateisortierung wird verwendet | +----+-----------+----------+-----------+---------------+---------+---------+---------+------+------+---------------------------------------+ 1 Zeile im Satz (0,00 Sek.) Dieses Extra gibt ein Using Filesort zurück, was bedeutet, dass eine Sortierung erforderlich ist. Dies muss optimiert werden. Das heißt, nachdem die Daten gefunden wurden, muss MySQL sie im Speicher sortieren. Sie müssen wissen, dass der Index selbst geordnet ist. Daher sollten Sie im Allgemeinen versuchen, die Ordnung des Index so weit wie möglich zu nutzen, z. B. indem Sie ihn wie folgt schreiben. mysql> erklären Sie „Wählen Sie a aus t, wobei a > 99990, sortieren Sie nach a;“ +----+--------------+-------+-------+------------------+---------+---------+------+------+----------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+--------------+-------+-------+------------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | Bereich | a_index,ab_index | a_index | 5 | NULL | 10 | Verwenden von where; Verwenden von index | +----+--------------+-------+-------+------------------+---------+---------+------+------+----------+--------------------------+ 1 Zeile im Satz (0,00 Sek.) Lassen Sie uns einen weiteren zusammengesetzten Index erstellen und sehen. mysql> Tabelle ändern t Index hinzufügen ab_index(a,b); Abfrage OK, 0 Zeilen betroffen (0,19 Sek.) Datensätze: 0 Duplikate: 0 Warnungen: 0 mysql> erläutern Sie „select * from t“, wobei a > 1000; +----+--------------+-------+-------+------------------+----------+----------+----------+------+----------+--------------------------+ | ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra | +----+--------------+-------+-------+------------------+----------+----------+----------+------+----------+--------------------------+ | 1 | SIMPLE | t | Bereich | a_index,ab_index | ab_index | 5 | NULL | 50166 | Verwenden von „where“; Verwenden von „index“ | +----+--------------+-------+-------+------------------+----------+----------+----------+------+----------+--------------------------+ 1 Zeile im Satz (0,00 Sek.) Dieses SQL wurde oben erwähnt. Wenn kein zusammengesetzter Index erstellt wird, wird ein vollständiger Tabellenscan durchgeführt. Jetzt wird ein abdeckender Index verwendet, der auch den Tabellenrückgabeprozess vermeidet. Das heißt, das abzufragende Feld befindet sich im Index (ab_index). Dieser Artikel stellt anhand mehrerer Beispiele vor, wie Sie mit „explain“ den Ausführungsplan einer SQL-Anweisung analysieren können. Außerdem werden einige gängige Indexoptimierungen erwähnt. Tatsächlich gibt es noch mehr Möglichkeiten. Sie können auch selbst eine SQL-Anweisung schreiben und diese dann mit „explain“ analysieren, um zu sehen, welche optimiert werden können. Das könnte Sie auch interessieren:
|
<<: Nginx leitet dynamisch an Upstream weiter, entsprechend dem Pfad in der URL
>>: WeChat-Applet implementiert Taschenrechnerfunktion
Artikel empfehlen
Webdesign: Implementierungstechniken für Webmusik
<br />Wenn Sie Musik in eine Webseite einfüg...
MySQL- und SQLYog-Installationstutorial mit Bildern und Text
1. MySQL 1.1 MySQL-Installation mysql-5.5.27-winx...
Detaillierte Erläuterung der MySQL-Optimierung für Schreibprobleme im großen Maßstab
Zusammenfassung: Wenn über die Leistungsoptimieru...
MySQL-Experiment: Verwenden von Explain zur Analyse des Indextrends
Überblick Die Indizierung ist eine Fähigkeit, die...
Detaillierte Schritte zum Erstellen, Ausführen, Veröffentlichen und erstmaligen Abrufen eines Docker-Images
1. Einleitung Wenn Sie früher mit dem Schreiben v...
Verwenden von CSS zur Implementierung von Bildrahmenanimationen und Kurvenbewegungen
Das Grundprinzip aller Animationen besteht darin,...
Erläuterung des TypeScript-Namespace
Inhaltsverzeichnis 1. Definition und Verwendung 1...
Klasse in Front-End-JavaScript
Inhaltsverzeichnis 1. Klasse 1.1 Konstruktor() 1....
Detaillierte Erklärung des JavaScript-Prototyps und Beispiele
Inhaltsverzeichnis Die Beziehung zwischen der Kon...
Beispielanalyse der drei Verbindungsmethoden für virtuelle VMware-Maschinen
NAT Auf diese Weise wird die Netzwerkkarte der vi...
Zwei Möglichkeiten, die CSS-Priorität zu verstehen
Methode 1: Werte hinzufügen Gehen wir zu MDN, um ...
Vue verwendet dynamische Komponenten, um einen TAB-Umschalteffekt zu erzielen
Inhaltsverzeichnis Problembeschreibung Was ist di...
Detaillierte Erklärung des JavaScript ES6-Moduls
Inhaltsverzeichnis 0. Was ist ein Modul 1.Modul l...
Ist MySQL eine relationale Datenbank?
MySQL ist ein relationales Datenbankverwaltungssy...
Probleme mit der NodeJS-, Koa- und Typescript-Integration und dem automatischen Neustart
Inhaltsverzeichnis Versionshinweise Erstellen ein...