Normalerweise werden langsame Abfrageanweisungen SQL
语句时会使用EXPLAIN
命令来查看SQL
语句的执行计划,通过返回的信息,可以了解到Mysql
优化器是如何执行SQL
abgefragt, und die Analyse kann uns dabei helfen, Optimierungsideen bereitzustellen.
Der EXPLAIN-Befehl wird hauptsächlich zum Anzeigen des Ausführungsplans von SQL-Anweisungen verwendet. Dieser Befehl kann die Ausführung von SQL-Abfrageanweisungen durch den Optimierer simulieren und uns beim Schreiben und Optimieren von SQL helfen. Welche spezifischen Informationen können uns also bei der Optimierung von SQL helfen?
Tabellenlesereihenfolge
Operationstyp des Datenlesevorgangs
Welche Indizes können verwendet werden?
Welche Indizes werden tatsächlich verwendet?
Wie viele Zeilen in Jede Tabelle wird vom Optimierer abgefragt , die Liste enthält 12 Felder und die Felder beschreiben gemeinsam, wie SQL im Ausführungsplan ausgeführt wird. Die folgende Liste beschreibt die Bedeutung der Felder in der Ausführungsplantabelle im Detail:Beschreibung
Die Sequenznummer der Select-Anweisungsabfrage, die die Lesereihenfolge bestimmt der Tabelle
select_type
Der Typ der Abfrage, dTyp
mögliche_Schlüssel | Verwendbare Indizes. Wenn für das an der Abfrage beteiligte Feld ein Index vorhanden ist, wird der Index aufgelistet, aber möglicherweise nicht tatsächlich von der Abfrage verwendet. Wenn dieses Feld null ist, der Feldschlüssel jedoch nicht null ist, bedeutet diese Situation, dass bei der Suche kein sekundärer Indexbaum verwendet werden kann, der sekundäre Index jedoch die Felder enthält, die abgefragt werden müssen, sodass der Clustered-Index (Clustered-Index) vorhanden ist ) wird nicht mehr durchsucht, stattdessen wird der sekundäre Indexbaum gescannt (der sekundäre Indexbaum ist relativ klein), und zu diesem Zeitpunkt ist der allgemeine Zugriffstyp Index und der gesamte Indexbaum wird gescannt . | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Der vom eigentlichen Scan verwendete Index. Wenn es null ist, wird der Index nicht verwendet. Wenn in der Abfrage ein abdeckender Index verwendet wird, erscheint der Index nur in der Schlüsselliste. Diese Spalte kann verwendet werden, um die Länge des in der Abfrage verwendeten Index zu berechnen. Je kürzer die Länge, desto besser ist der von key_len angezeigte Wert die maximal mögliche Länge des Indexfelds und nicht die tatsächlich verwendete Länge. Das heißt, key_len basiert auf der Tabelle. Die Definition wird berechnet und nicht aus der Tabelle abgerufen. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
zeigt an, welche Spalte des Index verwendet wird. Wenn möglich, handelt es sich um eine Konstante. Welche Spalten oder Konstanten werden zum Ermitteln des Werts in der Indexspalte verwendet? Anzahl der Zeilen; | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Der Prozentsatz der verbleibenden Daten nach der Filterung nach Suchbedingungen. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Enthält zusätzliche Informationen, die nicht für die Anzeige in anderen Spalten geeignet sind, aber sehr wichtig sind | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3. Schlüsselfeldanalyse | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sequenz der Ausführung der Select-Anweisung Abfrage-Nr., enthält eine Reihe von Zahlen, die die Reihenfolge angeben, in der Auswahlklauseln oder Operationstabellen in der Abfrage ausgeführt werden. Es gibt drei Situationen: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Typname | Beschreibung | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ID ist die gleich | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ID ist unterschiedlich | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
id ist gleich, aber unterschiedlich und existiert gleichzeitig | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Typname | Beschreibung |
---|---|
Einfache Auswahlabfrage, Abfrage nicht enthalten Unterabfragen oder Unions; | |
Wenn die Abfrage komplexe Unterabfragen enthält, wird die äußerste Abfrage markiert; | |
enthält Unterabfragen in der Select- oder Where-Liste; | |
Das erste SELECT in der Unterabfrage hängt von der äußeren Abfrage ab. Das heißt, die Unterabfrage hängt von den Ergebnissen der äußeren Abfrage ab. | |
Die in der Von-Liste enthaltenen Unterabfragen werden als DERIVED (abgeleitete Tabellen) markiert. MySQL führt diese Unterabfragen rekursiv aus und fügt die Ergebnisse in die temporäre Tabelle ein Erscheint nach der Union, wird sie als Union markiert. Wenn Union in der Unterabfrage der From-Klausel enthalten ist, wird die äußere Auswahl als DERIVED markiert Union-Zusammenführung: Holen Sie sich die Ergebnisse der ausgewählten Abfrage im Satz); basierend auf den Daten in der temporären Tabelle abgeglichen. | |
UNION Die zweite oder nachfolgende Abfrageanweisung in UNION hängt von der externen Abfrage ab Was hier angezeigt wird, ist der Alias. Wenn es keine Operation für die Datentabelle gibt, wird dieser als Null angezeigt oder er kann einer der folgenden sein: | |
Beschreibung | |
gibt an, dass es sich um eine temporäre Tabelle handelt, und das folgende N ist die ID im Ausführungsplan, was angibt, dass die Ergebnisse aus dieser Abfrage generiert werden. | |
ähnelt |
Werttyp | Wertname | Beschreibung |
---|---|---|
String | CHAR(n) | n Byte. Länge |
V ARCHAR(n) | Wenn es sich um eine UTF8-Kodierung handelt, es ist 3 n + 2 Bytes; wenn es sich um utf8mb4-Kodierung handelt, sind es 4 n + 2 Bytes. | |
Numerischer Typ | TINYINT | 1 Byte |
SMALLINT | 2 Bytes | |
MEDIUMINT | 3 Bytes | |
INT | 4 Bytes | |
BIGINT | 8 Bytes | |
Zeittyp | DATE | 3 Bytes |
TIMESTAMP | . 4 Bytes | |
DATETIME | 8 Bytes | |
Feldattribute | NULL-Attribute belegen ein Byte. Wenn ein Feld NOT NULL ist, ist es nicht belegt. |
Wenn es sich um eine konstante äquivalente Abfrage handelt, wird hier const
angezeigt. Wenn es sich um eine Verbindungsabfrage handelt, werden im Ausführungsplan der gesteuerten Tabelle die zugehörigen Felder angezeigt Wenn die Bedingung einen Ausdruck oder eine Funktion verwendet oder die Bedingungsspalte einer internen impliziten Konvertierung unterzogen wird, wird sie möglicherweise als func
angezeigt. const
,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
。
这里是执行计划中估算的扫描行数,不是精确值。
使用explain extended
时会出现这个列,5.7
之后的版本默认就有这个字段,不需要使用explain extended
了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。
这个列可以显示的信息非常多,有几十种,常用的有:
1、distinct:在select
部分使用了distinct
关键字
2、no tables used:不带from
字句的查询或者From dual
查询。使用not in()
形式子查询或not exists()
运算符的连接查询,这种叫做反连接。即,一般连接查询是先查询内表,再查询外表,反连接就是先查询外表,再查询内表。
3、using filesort:说明mysql
会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql
中无法利用索引完成的排序操作称为“文件排序”。排序时无法使用到索引时,就会出现这个。常见于order by
语句中,需要尽快优化
4、using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据。
5、using join buffer(block nested loop),using join buffer(batched key accss) :5.6.x
之后的版本优化关联查询的BNL
,BKA
特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
6、using sort_union,using_union,using intersect,using sort_intersection:
and
的各个索引的条件时,该信息表示是从处理结果获取交集or
连接各个使用索引的条件时,该信息表示从处理结果获取并集and
和or
查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。7、using temporary:表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status
变量,used_tmp_table
,used_tmp_disk_table
才能看出来。常见于order by
和分组查询group by
。group by
一定要遵循所建索引的顺序与个数。需要尽快优化
8、using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server
层进行过滤。查询条件中分为限制条件和检查条件,5.6
之前,存储引擎只能根据限制条件扫描数据并返回,然后server
层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x
之后支持ICP
特性(index condition pushdown,索引下推),可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。extra
列显示using index condition
9、firstmatch(tb_name) :5.6.x
开始引入的优化子查询的新特性之一,常见于where
字句含有in()
类型的子查询。如果内表的数据量比较大,就可能出现这个
10、loosescan(m..n) :5.6.x
之后引入的优化子查询的新特性之一,在in()
(11) gefiltert
🎜🎜Diese Spalte wird angezeigt, wennexplain advanced
verwendet wird, und die Version nach 5.7
wird angezeigt standardmäßig sein. Bei diesem Feld ist die Verwendung von explain advanced
nicht erforderlich. Dieses Feld gibt den Anteil der verbleibenden Datensätze an, die die Abfrage erfüllen, nachdem die von der Speicher-Engine zurückgegebenen Daten auf der Serverebene gefiltert wurden. Beachten Sie, dass es sich um einen Prozentsatz und nicht um eine bestimmte Anzahl von Datensätzen handelt. 🎜distinct
wird im Teil >selectfrom
oder Von dual code>Query. Verwenden Sie die Formularunterabfrage <code>not in()
oder die Join-Abfrage des Operators not exist()
, die als Anti-Join bezeichnet wird. Das heißt, eine allgemeine Join-Abfrage fragt zuerst die innere Tabelle und dann die äußere Tabelle ab, während eine Anti-Join-Abfrage zuerst die äußere Tabelle und dann die innere Tabelle abfragt. 🎜🎜🎜3. Verwenden von Filesort🎜: Beschreibung mysql
sortiert die Daten mithilfe eines externen Indexes, anstatt sie in der Reihenfolge des Indexes in der Tabelle zu lesen. Der Sortiervorgang, der nicht mithilfe von Indizes in mysql
abgeschlossen werden kann, wird als „Dateisortierung“ bezeichnet. Dies tritt auf, wenn der Index beim Sortieren nicht verwendet werden kann. Wird häufig in order by
-Anweisungen verwendet und muss so schnell wie möglich mithilfe des Index optimiert werden🎜: Es ist nicht erforderlich, bei der Abfrage zur Tabelle zurückzukehren, und die Abfragedaten können sein direkt über den Index abgerufen werden. 🎜🎜🎜5. Verwendung des Join-Puffers (blockierte verschachtelte Schleife), Verwendung des Join-Puffers (Batch-Key-Zugriff)🎜: 5.6.x
und spätere Versionen optimieren BNL
für verwandte Abfragen, BKA
-Funktion. Der Hauptzweck besteht darin, die Anzahl der Schleifen in der internen Tabelle zu reduzieren und die Abfrage sequentiell zu scannen. 🎜🎜🎜6. using sort_union, using_union, using intersect, using sort_intersection: 🎜🎜und
angeben, geben diese Informationen an, dass dies der Fall ist verarbeitet aus Ergebniserfassungsschnittpunktoder
zum Verbinden von Bedingungen mithilfe von Indizes diese Informationen darauf hinweisen, dass die Union aus den Verarbeitungsergebnissen erhalten wirdand
und or
zum Abfragen einer großen Menge an Informationen angezeigt werden Zuerst wird der Datensatz abgefragt, dann sortiert und zusammengeführt. Erst dann kann der Datensatz gelesen und zurückgegeben werden. status
, used_tmp_table
und used_tmp_disk_table überprüfen
um es zu sehen. Wird häufig in sortieren nach
und Gruppenabfragen gruppieren nach
verwendet. group by
muss der Reihenfolge und Anzahl der erstellten Indizes folgen. Muss so schnell wie möglich optimiert werden🎜🎜🎜8 mit where🎜: Zeigt an, dass nicht alle von der Speicher-Engine zurückgegebenen Datensätze die Abfragebedingungen erfüllen und auf der Server
-Ebene gefiltert werden müssen. Abfragebedingungen sind in Einschränkungsbedingungen und Inspektionsbedingungen unterteilt. Vor 5.6
konnte die Speicher-Engine nur Daten scannen und sie basierend auf den Einschränkungsbedingungen zurückgeben, und dann konnte die Server
-Schicht dies tun Filtern Sie die Daten und geben Sie sie basierend auf den Prüfbedingungen zurück, die tatsächlich mit der Abfrage übereinstimmen. 5.6.x
unterstützt die ICP
-Funktion (Indexbedingungs-Pushdown, Index-Pushdown) nach 5.6.x
. Die Prüfbedingungen können auch nach unten verschoben werden Die Speicher-Engine-Schicht erfüllt die Prüfbedingungen und Einschränkungen nicht. Die Daten werden nicht direkt gelesen, was die Anzahl der von der Speicher-Engine gescannten Datensätze erheblich reduziert. In der Spalte extra
wird 🎜Indexbedingung verwenden🎜🎜🎜🎜9, firstmatch(tb_name)🎜 angezeigt: eine der neuen Funktionen der optimierten Unterabfrage, die in 5.6.x
eingeführt und häufig verwendet wird in Die where
-Klausel enthält eine Unterabfrage vom Typ in()
. Wenn die Datenmenge in der internen Tabelle relativ groß ist, kann dies auftreten🎜🎜🎜10. Loosescan(m..n)🎜: Eine der neuen Funktionen optimierter Unterabfragen, die nach 5.6.x
eingeführt wurden , in In einer Unterabfrage vom Typ in()
kann dies passieren, wenn die Unterabfrage doppelte Datensätze zurückgibt. 🎜🎜🎜4 Erklären Sie den Hauptfokus. Im Allgemeinen müssen wir nur auf a achten paar Spalten in den Ergebnissen: 🎜Spaltenname | Bemerkungen |
---|---|
Typ | Dieser Verbindungstyp der Abfragetabelle. Von hier aus können Sie die ungefähre Effizienz dieser Abfrage sehen |
Schlüssel | Der endgültig ausgewählte Index, wenn nicht In In Bezug auf die Indizierung ist die Effizienz dieser Abfrage normalerweise sehr schlecht |
key_len | Die tatsächliche Länge des Index, der für die Ergebnisfilterung in dieser Abfrage verwendet wird |
Zeilen | Die geschätzte Anzahl der Datensätze, die gescannt werden müssen , die geschätzte Anzahl der Datensätze, die gescannt werden müssen ist größer. Kleiner ist besser |
Extra | Zusätzliche zusätzliche Informationen, hauptsächlich bestätigen, ob es zwei Situationen gibt: Verwenden von Dateisortierung und Temporär verwenden Using filesort 、Using temporary 这两种情况 |
再来看下Extra
列中需要注意出现的几种情况:
关键字 | 备注 |
---|---|
Using filesort | 将用外部排序而不是按照索引顺序排列结果,数据较少时从内存排序,否则需要在磁盘完成排序,代价非常高,需要添加合适的索引 |
Using temporary | 需要创建一个临时表来存储结果,这通常发生在对没有索引的列进行GROUP BY 时,或者ORDER BY 里的列不都在索引里,需要添加合适的索引
|
Using index | 表示MySQL 使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据,这是比较好的结果之一。注意不要和type 中的index 类型混淆 |
Using where | 通常是进行了全表/全索引扫描后再用WHERE 子句完成结果过滤,需要添加合适的索引
|
Impossible WHERE | 对Where 子句判断的结果总是false而不能选择任何数据,例如where 1=0 ,无需过多关注 |
Select tables optimized away | 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()MAX()
|
Extra
beachtet werden müssen: 🎜Schlüsselwörter🎜🎜Bemerkungen🎜🎜🎜🎜 🎜🎜Die Verwendung von Filesort🎜🎜 verwendet eine externe Sortierung, anstatt die Ergebnisse in der Indexreihenfolge zu sortieren, wenn weniger Daten vorhanden sind. Andernfalls muss die Sortierung auf der Festplatte erfolgen, was sehr kostspielig ist. 🎜Es muss ein geeigneter Index hinzugefügt werden 🎜🎜🎜🎜🎜Temporär verwenden🎜🎜Sie müssen eine temporäre Tabelle erstellen, um die Ergebnisse zu speichern. Dies geschieht normalerweise, wenn kein Index vorhanden ist, wenn die Spalten in GROUP BY
ausgeführt werden ORDER BY
nicht alle im Index enthalten sind, müssen Sie entsprechende Indizes hinzufügen Es ist nicht erforderlich, zweimal in der Tabelle nach Daten zu suchen. Dies ist eines der besseren Ergebnisse. Achten Sie darauf, es nicht mit dem Typ index
in type
zu verwechseln / Die Code>-Klausel vervollständigt die Ergebnisfilterung. 🎜Sie müssen einen geeigneten Index hinzufügen. Unmögliches WHERE. Das Ergebnis der Beurteilung der Where
-Klausel ist immer falsch und es können keine Daten ausgewählt werden , wie zum Beispiel wobei 1=0
, müssen Sie nicht zu viel aufpassen das erforderliche Feld auf einmal durch den Index Die Datenzeile vervollständigt die gesamte Abfrage, z. B. MIN()MAX()
, was auch eines der besseren Ergebnisse ist🎜🎜🎜🎜🎜[Verwandte Empfehlung: 🎜 MySQL-Video-Tutorial🎜]🎜
Das obige ist der detaillierte Inhalt vonDieser Artikel hilft Ihnen, den MySQL-Ausführungsplan schnell zu verstehen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!