Detaillierte Untersuchung des MySQL-Befehls EXPLAIN

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';

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';

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

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:

  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ref
  possible_keys: item_id
  key: item_id
  key_len: 4
  ref: const
  rows: 1
  Extra:

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 f5d188ed2c074f8b944552db028f98a1 , 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.

 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: const
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: const
 rows: 10
 Extra:

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)

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

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:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
 +--------------------------------------+---------+
 | Variable_name | Value |
 +--------------------------------------+---------+
 | Handler_read_first | 1 |
 | Handler_read_key | 164 |
 | Handler_read_last | 0 |
 | Handler_read_next | 107 |
 | Handler_read_prev | 0 |
 | Handler_read_rnd | 0 |
 | Handler_read_rnd_next | 161 |
 +--------------------------------------+---------+
 相关的QEP 列还包括key列。

 
 2.3 possible_keys
 possible_keys 列指出优化器为查询选定的索引。
 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。
 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。
 相关的QEP 列还包括key 列。
 
 2.4 key_len
 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
 此列的一些示例值如下所示：
 
 此列的一些示例值如下所示：
 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

 这个表的索引包括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';
 
 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句
 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的
 最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

 mysql> EXPLAIN SELECT ID, post_title
 -> FROM wp_posts
 -> WHERE post_type='post'
 -> AND post_status='publish'
 -> AND post_date > '2010-06-01';

 
 在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date
 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索
 引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。
 相关的QEP 列还包括带有Using index 值的Extra 列。
 
 2.5 table
 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
 的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例：
 table: item
 table: 6db98b0ebf978836f3b742968b1383c4
 table: 3d79052a4b39814111818a40e6c7009c
 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type
 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE
 对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。

 2. PRIMARY
 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
 
 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 3d79052a4b39814111818a40e6c7009c,
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.

 2.11 filtered
 filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连
 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type
 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式：
  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现
  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的
  ref 这个值表示所有具有匹配的索引值的行都被用到
  range 这个值表示所有符合一个给定范围值的索引行都被用到
  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
  想了解更多信息可以访问http://www.php.cn/。
 

 3 解释EXPLAIN 输出结果
 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商
 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*
 -> FROM parent p
 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child
 c)\G
 ********************* 1. row ***********************
 id: 1
 select type: PRIMARY
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra: Using where
 ********************* 2. row ***********************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: c
 type: index_subquery
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: func
 rows: 1
 Extra: Using index
 2 rows in set (0.00 sec)

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G
 ********************* 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: parent_id
 key: parent_id
 key_len: 4
 ref: test.p.id
 rows: 1
 Extra: Using where; Using index; Not exists
 2 rows in set (0.00 sec)

 以上就是MySQL EXPLAIN 命令详解学习的内容，更多相关内容请关注PHP中文网（www.php.cn）！