Detailed study of MySQL EXPLAIN command

MySQL EXPLAIN command detailed explanation

The EXPLAIN command of MySQL is used for the query execution plan (QEP) of the SQL statement. The output of this command allows us to understand how the MySQL optimizer executes
SQL statements. This command does not provide any tuning suggestions, but it can provide important information to help you make tuning decisions.

1 Syntax
MySQL's EXPLAIN syntax can be run on a SELECT statement or a specific table. If acting on a table, this command is equivalent to the DESC table command. UPDATE
and DELETE commands also need performance improvements. When these commands are not run directly on the main key of the table, in order to ensure optimal index usage, they need to be
written into SELECT statements (so that they Execute EXPLAIN command). Please see the following example:

UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';

##This UPDATE statement can be rewritten into a SELECT statement like the following:

SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';

In version 5.6.10, you can directly perform explain analysis operations on dml statements.

MySQL The optimizer works based on cost and does not provide any QEP position. This means that QEP is calculated dynamically as each SQL statement is executed. SQL statements in MySQL stored procedures also calculate QEP each time they are executed. The stored procedure cache only parses the query tree.

2 Detailed explanation of each column

The MySQL EXPLAIN command can generate the following information for each table in the SQL statement:

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

This QEP shows that no index is used (that is, a full table scan) and a large number of rows are processed to satisfy the query. For the same SELECT statement, an optimized QEP is as follows:

  ********************* 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 this QEP, we see that an index is used, and it is estimated that only one row of data will be obtained.

All lists for each row in the QEP look like this:

 id
 select_type table
 partitions(This column will only appear in the EXPLAIN PARTITIONS syntax)
 possible_keys
 key
 key_len
 ref
 rows
 filtered(This column will only Appears only in EXPLAINED EXTENDED syntax)
 Extra

These columns show the QEP of the SELECT statement for each table. A table may be associated with a physical schema table or an internal temporary table generated during SQL execution (for example, from a subquery or a merge operation).

You can refer to the MySQL Reference Manual for more information: http://www.php.cn/.

2.1 key
The key column indicates the index chosen by the optimizer. Generally speaking, only one index is used per table in a SQL query. There are a few exceptions to index merging, such as when two or more indexes are used on a given table. The following is an example of the key column in QEP:
key: item_id
key: NULL
key: first, last
The SHOW CREATE TABLE f5d188ed2c074f8b944552db028f98a1 command is the simplest to view the table and How to index column details. Columns related to the key column also include possible_keys, rows and key_len.

2.2 ROWS

The rows column provides an estimate of the number of rows the MySQL optimizer attempted to analyze all of those that existed in the cumulative result set. QEP makes it easy to describe this difficult statistic. The total number of read operations in the query is based on the continuous accumulation of the rows value of each row before merging the rows. This is a nested row algorithm.

Take QEP connecting two tables as an example. The rows value of the first row found through the condition id=1 is 1, which is equivalent to a read operation on the first table. The second row is
found through id=2, and the value of rows is 5. This equates to 5 reads matching the current accumulation of 1. Referring to both tables, the total number of read operations is 6. In another QEP
, the value of the first rows is 5 and the value of the second rows is 1. This equates to 5 reads for the first table, one for each of the 5 accumulations. Therefore, the total number of read operations for the two tables
is 10 (5+5) times.

The best estimate is 1. Generally this happens when the row you are looking for can be found in the table by the primary key or unique key.
In the QEP below, the outer nested loop can be found by id=1, and its estimated physical row number is 1. The second loop processed 10 rows.

 ********************* 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:

You can use the SHOW STATUS command to view the actual row operations. This command provides the best way to confirm physical row operations. Please see the following example:

 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)

In the next QEP, the outer nested loop found with id=1 is estimated to have 160 lines. The second loop is estimated to be 1 row.

 ********************* 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

You can view the actual row operations through the SHOW STATUS command, which shows that the number of physical read operations has increased significantly. Please see the example below:

 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. DEPENDENT SUBQUERY
This select-type value is defined for using subqueries. The following SQL statement provides this value:
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

5. UNION
This is one of the SQL elements in the UNION statement.
6. UNION RESULT
This is the return result of a series of tables defined in the UNION statement. When select_type is this value, you can often see that the value of table is 3d79052a4b39814111818a40e6c7009c,
This means that the matching id row is part of this set. The following SQL generates a UNION and UNION RESULT select-type:
mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val
LIKE 'a%'
-> UNION
- > SELECT p.* FROM parent p WHERE p.id > 5;

2.7 partitions
The partitions column represents the partitions used by the given table. This column will only appear in the EXPLAIN
PARTITIONS statement.

2.8 Extra
The Extra column provides a series of
additional information about different kinds of MySQL optimizer paths. Extra columns can contain multiple values ​​and can have many different values, and
these values ​​are still increasing with the release of new versions of MySQL. Below is a list of commonly used values ​​
. You can find a more comprehensive list of values ​​at:
http://www.php.cn/.

1. Using where
This value indicates that the query uses the where statement to process the results - for example, perform a
full table scan. If indexes are also used, row constraints are achieved by obtaining the necessary data and then processing the read buffer.

2. Using temporary This value indicates the use of an internal temporary (memory-based) table. A query may
use multiple temporary tables. There are many reasons why MySQL
creates temporary tables during query execution. Two common reasons are using
DISTINCT on columns from different tables, or using different ORDER BY and GROUP BY columns.
For more information, please visit http://www.php.cn/
of_query_execution_and_use_of_temp_tables.
You can force a temporary table to use the disk-based MyISAM storage engine
. There are two main reasons for this:
 The space occupied by the internal temporary table exceeds the limit of the min (tmp_table_size, max_
heap_table_size) system variable
 TEXT/BLOB columns are used

3. Using filesort This is the result of the ORDER BY statement. This can be a CPU-intensive process.
You can improve performance by choosing an appropriate index and use the index to sort query results. Please refer to Chapter 4 for detailed procedures.

4. Using index This value emphasizes that only the index can be used to meet the requirements of the query table, and there is no need to directly access the table data. Please refer to Chapter 5 for detailed examples to understand these
values.

5. Using join buffer This value emphasizes that no index is used when obtaining the join condition, and a join buffer is needed to store intermediate results.
If this value appears, you should note that depending on the specific conditions of the query, you may need to add an index to improve performance.

6. Impossible where This value emphasizes that the where statement will result in no rows that meet the conditions. Please see the following example:
mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

7. Select tables optimized away This value means that only by using the index , the optimizer may return only one row from the aggregate function result. See the following example:

8. Distinct This value means that MySQL will stop searching for other rows after finding the first matching row.

9. Index merges When MySQL decides to use more than one index on a given table, one of the following formats will appear, detailing the use Index and merge types.
 Using sort_union(...)
 Using union(...)
 Using intersect(...)

2.9 id The id column is a continuous reference to the table displayed in QEP.

2.10 ref The ref column can be used to identify columns or constants used for index comparisons.

 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）！