Detaillierte Untersuchung des MySQL-Befehls EXPLAIN
黄舟
Freigeben： 2017-02-17 13:26:19
Original
1547 Leute haben es durchsucht
Detaillierte Erläuterung des MySQL-Befehls EXPLAIN
Der Befehl EXPLAIN von MySQL wird für den Abfrageausführungsplan (Query Execution Plan, QEP) von SQL-Anweisungen verwendet. Die Ausgabe dieses Befehls ermöglicht es uns zu verstehen, wie der MySQL-Optimierer die SQL-Anweisung ausführt. Dieser Befehl liefert keine Optimierungsvorschläge, kann jedoch wichtige Informationen liefern, die Ihnen bei der Entscheidungsfindung bei der Optimierung helfen.
1 Syntax Die EXPLAIN-Syntax von MySQL kann auf einer SELECT-Anweisung oder einer bestimmten Tabelle ausgeführt werden. Bei der Bearbeitung einer Tabelle entspricht dieser Befehl dem Tabellenbefehl DESC. UPDATE - und DELETE-Befehle erfordern ebenfalls Leistungsverbesserungen. Wenn diese Befehle nicht direkt auf dem Hauptschlüssel der Tabelle ausgeführt werden, müssen sie in SELECT-Anweisungen geändert werden (damit sie den EXPLAIN-Befehl ausführen). ). Sehen Sie sich bitte das folgende Beispiel an:
UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';
Nach dem Login kopieren
Diese UPDATE-Anweisung kann wie folgt in eine SELECT-Anweisung umgeschrieben werden:
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';
Nach dem Login kopieren
In Version 5.6.10 können Sie EXPLAIN-Analyseoperationen direkt für DML-Anweisungen durchführen.Der MySQL-Optimierer arbeitet kostenbasiert und stellt keine QEP-Position bereit. Das bedeutet, dass QEP dynamisch berechnet wird, wenn jede SQL-Anweisung ausgeführt wird. SQL-Anweisungen in gespeicherten MySQL-Prozeduren berechnen bei jeder Ausführung auch QEP. Der Cache für gespeicherte Prozeduren analysiert nur den Abfragebaum.
2 Detaillierte Erklärung jeder Spalte
Der MySQL-Befehl EXPLAIN kann die folgenden Informationen für jede Tabelle in der SQL-Anweisung generieren:
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: inventory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 787338
Extra: Using where
Nach dem Login kopieren
Dieser QEP zeigt, dass keine Indizes verwendet werden (d. h. ein vollständiger Tabellenscan) und eine große Anzahl von Zeilen verarbeitet werden, um die Abfrage zu erfüllen . Für dieselbe SELECT-Anweisung lautet ein optimiertes QEP wie folgt:
In diesem QEP sehen wir, dass ein Index verwendet wird, und es wird geschätzt, dass nur ein Index verwendet wird Es werden Daten abgerufen.
Alle Listen für jede Zeile in QEP sehen so aus:
id select_type table Partitionen (diese Spalte wird nur in der EXPLAIN PARTITIONS-Syntax angezeigt) mögliche_Schlüssel Schlüssel Schlüssel_Len Ref Zeilen gefiltert (diese Spalte wird nur angezeigt (nur in EXPLAINED EXTENDED-Syntax) Extra
Diese Spalten zeigen den QEP der SELECT-Anweisung für jede Tabelle. Eine Tabelle kann mit einer physischen Schematabelle oder einer internen temporären Tabelle verknüpft sein, die während der SQL-Ausführung generiert wird (z. B. aus einer Unterabfrage oder einem Zusammenführungsvorgang).
Weitere Informationen finden Sie im MySQL-Referenzhandbuch: http://www.php.cn/.
2.1 Schlüssel Die Schlüsselspalte gibt den vom Optimierer ausgewählten Index an. Im Allgemeinen wird in einer SQL-Abfrage nur ein Index pro Tabelle verwendet. Es gibt einige Ausnahmen von der Indexzusammenführung, beispielsweise wenn zwei oder mehr Indizes für eine bestimmte Tabelle verwendet werden. Das Folgende ist ein Beispiel für die Schlüsselspalte in QEP: Schlüssel: item_id Schlüssel: NULL Schlüssel: erster, letzter Der Befehl SHOW CREATE TABLE
, um die Tabelle anzuzeigen und die Spaltendetails zu indizieren. Zu den Spalten, die sich auf die Schlüsselspalte beziehen, gehören auch „possible_keys“, „rows“ und „key_len“.
2.2 ZEILEN
Die Zeilenspalte liefert eine Schätzung der Anzahl der Zeilen, die der MySQL-Optimierer für alle im kumulativen Ergebnissatz vorhandenen Zeilen zu analysieren versuchte. QEP macht es einfach, diese schwierige Statistik zu beschreiben. Die Gesamtzahl der Lesevorgänge in der Abfrage basiert auf der kontinuierlichen Akkumulation des Zeilenwerts jeder Zeile vor dem Zusammenführen der Zeilen. Dies ist ein verschachtelter Zeilenalgorithmus.
Nehmen Sie als Beispiel QEP, das zwei Tabellen verbindet. Der Zeilenwert der ersten Zeile, die durch die Bedingung id=1 gefunden wird, ist 1, was einem Lesevorgang für die erste Tabelle entspricht. Die zweite Zeile wird von mit der ID=2 gefunden und der Wert der Zeilen beträgt 5. Dies entspricht 5 Lesevorgängen, was der aktuellen Anhäufung von 1 entspricht. Bezogen auf beide Tabellen beträgt die Gesamtzahl der Lesevorgänge 6. In einem anderen QEP beträgt der Wert der ersten Zeilen 5 und der Wert der zweiten Zeilen 1. Dies entspricht 5 Lesevorgängen für die erste Tabelle, einem für jede der 5 Akkumulationen. Daher beträgt die Gesamtzahl der Lesevorgänge für die beiden Tabellen 10 (5+5) Mal.
Die beste Schätzung ist 1. Im Allgemeinen geschieht dies, wenn die gesuchte Zeile in der Tabelle über den Primärschlüssel oder den eindeutigen Schlüssel gefunden werden kann. Im QEP unten kann die äußere verschachtelte Schleife durch id=1 gefunden werden und ihre geschätzte physische Zeilennummer ist 1. Die zweite Schleife verarbeitete 10 Zeilen.
Sie können den Befehl SHOW STATUS verwenden, um die tatsächlichen Zeilenvorgänge anzuzeigen. Dieser Befehl bietet die beste Möglichkeit, physische Zeilenoperationen zu bestätigen. Siehe das folgende Beispiel:
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
+-----------------------+-------+
7 rows in set (0.00 sec)
Nach dem Login kopieren
Im nächsten QEP wird die äußere verschachtelte Schleife mit id=1 schätzungsweise 160 Zeilen haben. Die zweite Schleife wird auf 1 Zeile geschätzt.
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 160
Extra:
********************* 2. row ***********************
id: 1
select type: SIMPLE
table: c
type: ref
possible_keys: PRIMARY,parent_id
key: parent_id
key_len: 4
ref: test.p.parent_id
rows: 1
Extra: Using where
Nach dem Login kopieren
Die tatsächlichen Zeilenoperationen können über den Befehl SHOW STATUS angezeigt werden, der zeigt, dass die Anzahl der physischen Leseoperationen deutlich zugenommen hat. Siehe das Beispiel unten:
此列的一些示例值如下所示： key_len: 4 // INT NOT NULL key_len: 5 // INT NULL key_len: 30 // CHAR(30) NOT NULL key_len: 32 // VARCHAR(30) NOT NULL key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了 如何以最佳方式使用带有定义好的表索引的SQL 语句：
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
Nach dem Login kopieren
这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type='post'
-> AND post_status='publish'
-> AND post_date > '2010-06-01';
3. DERIVED 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的 示例： mysql> EXPLAIN SELECT MAX(id) -> FROM (SELECT id FROM users WHERE first = 'west') c;
4. ABHÄNGIGE UNTERABFRAGE Dieser Auswahltypwert ist für die Verwendung von Unterabfragen definiert. Die folgende SQL-Anweisung stellt diesen Wert bereit: EXPLAIN SELECT p.* -> FROM parent p -> 🎜>
5. UNION Dies ist ein SQL-Element in der UNION-Anweisung. 6. UNION-ERGEBNIS Dies ist das Rückgabeergebnis einer Reihe von Tabellen, die in der UNION-Anweisung definiert sind. Wenn „select_type“ dieser Wert ist, können Sie oft sehen, dass der Wert der Tabelle , ist, was bedeutet, dass die übereinstimmende ID-Zeile Teil dieser Menge ist. Das folgende SQL generiert einen UNION- und UNION-RESULT-Auswahltyp: mysql> EXPLAIN SELECT p.* FROM parent p.val LIKE 'a%' -> ; SELECT p.* FROM parent p WHERE p.id > Diese Spalte erscheint nur in der EXPLAIN PARTITIONS-Anweisung.
2.8 Extra Die Spalte „Extra“ bietet eine Reihe zusätzlicher Informationen zu verschiedenen Arten von MySQL-Optimierungspfaden. Zusätzliche Spalten können mehrere Werte enthalten und viele verschiedene Werte haben, und diese Werte nehmen mit der Veröffentlichung neuer Versionen von MySQL immer noch zu. Nachfolgend finden Sie eine Liste häufig verwendeter Werte für
. Eine umfassendere Werteliste finden Sie unter:
http://www.php.cn/. 1. Where verwenden
Dieser Wert gibt an, dass die Abfrage die where-Anweisung verwendet, um die Ergebnisse zu verarbeiten – zum Beispiel einen vollständigen Tabellenscan durchzuführen. Wenn auch Indizes verwendet werden, werden Zeileneinschränkungen erreicht, indem die erforderlichen Daten abgerufen und dann der Lesepuffer verarbeitet werden.
2. Verwendung temporär Dieser Wert gibt die Verwendung einer internen temporären (speicherbasierten) Tabelle an. Eine Abfrage kann mehrere temporäre Tabellen verwenden. Es gibt viele Gründe, warum MySQL während der Abfrageausführung temporäre Tabellen erstellt. Zwei häufige Gründe sind die Verwendung von DISTINCT für Spalten aus verschiedenen Tabellen oder die Verwendung unterschiedlicher ORDER BY- und GROUP BY-Spalten.
Weitere Informationen finden Sie unter http://www.php.cn/
of_query_execution_and_use_of_temp_tables. Sie können eine temporäre Tabelle zwingen, die festplattenbasierte MyISAM-Speicher-Engine zu verwenden . Dafür gibt es zwei Hauptgründe: Der von der internen temporären Tabelle belegte Speicherplatz überschreitet den Grenzwert der Systemvariablen min(tmp_table_size, max_ heap_table_size) Es werden TEXT/BLOB-Spalten verwendet
3. Filesort verwenden
Dies ist das Ergebnis der ORDER BY-Anweisung. Dies kann ein CPU-intensiver Prozess sein. Sie können die Leistung verbessern, indem Sie geeignete Indizes auswählen und Indizes zum Sortieren von Abfrageergebnissen verwenden. Detaillierte Vorgehensweisen finden Sie in Kapitel 4.
4. Index verwenden
Dieser Wert betont, dass nur der Index verwendet werden kann, um die Anforderungen der Abfragetabelle zu erfüllen, und dass kein direkter Zugriff auf die Tabellendaten erforderlich ist. Um diesen -Wert zu verstehen, lesen Sie bitte die detaillierten Beispiele in Kapitel 5.
5. Verwendung des Join-Puffers
Dieser Wert betont, dass beim Erhalten der Join-Bedingung kein Index verwendet wird und ein Join-Puffer zum Speichern von Zwischenergebnissen erforderlich ist. Wenn dieser Wert angezeigt wird, sollten Sie beachten, dass Sie je nach den spezifischen Bedingungen der Abfrage möglicherweise einen Index hinzufügen müssen, um die Leistung zu verbessern.
6. Unmögliches where
Dieser Wert betont, dass die where-Anweisung keine Zeilen ergibt, die die Bedingungen erfüllen. Bitte sehen Sie sich das folgende Beispiel an: mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7. Wegoptimierte Tabellen auswählen
Dieser Wert bedeutet nur durch Verwendung des Index , gibt der Optimierer möglicherweise nur eine Zeile aus dem Ergebnis der Aggregatfunktion zurück. Siehe das folgende Beispiel:
8. Distinct
Dieser Wert bedeutet, dass MySQL die Suche nach anderen Zeilen beendet, nachdem die erste übereinstimmende Zeile gefunden wurde. 9. Indexzusammenführungen
Wenn MySQL beschließt, mehr als einen Index für eine bestimmte Tabelle zu verwenden, wird eines der folgenden Formate angezeigt, in dem die verwendeten Index- und Zusammenführungstypen detailliert beschrieben werden. Verwenden von sort_union(...) Verwenden von Union(...) Verwenden von intersect(...)
2.9 id Die ID-Spalte ist eine fortlaufende Referenz auf die in QEP angezeigte Tabelle.
2.10 ref
Die Ref-Spalte kann verwendet werden, um die Spalten oder Konstanten zu identifizieren, die für den Indexvergleich verwendet werden.
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
