mysql执行计划（EXPLAIN和DESCRIBE）

• EXPLAIN和DESCRIBE的关系

The DESCRIBE and EXPLAIN statements are synonyms, used either to obtain information about table structure or query execution plans.

DESCRIBE和EXPLAIN语句是同义词，用于获得表结构信息和SQL语句的执行计划。

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query). The following discussion uses the DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

DESCRIBE和EXPLAIN语句是同义词，实际上在平时使用过程中 DESCRIBE多用于获取表结构的信息，然后 EXPLAIN多用于获取SQL语句的执行计划。MySQL解析器对这两个语句是完全作为同义词对待的。

mysql> desc mysql.plugin;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| name  | varchar(64)  | NO   | PRI |         |       |

| dl    | varchar(128) | NO   |     |         |       |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> explain mysql.plugin;

+-------+--------------+------+-----+---------+-------+

| Field | Type         | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| name  | varchar(64)  | NO   | PRI |         |       |

| dl    | varchar(128) | NO   |     |         |       |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> desc select * from mysql.plugin;

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

| id | select_type | table  | type   | possible_keys | key  | key_len | ref  | rows | Extra               |

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

|  1 | SIMPLE      | plugin | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

1 row in set (0.07 sec)

mysql> explain select * from mysql.plugin;

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

| id | select_type | table  | type   | possible_keys | key  | key_len | ref  | rows | Extra               |

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

|  1 | SIMPLE      | plugin | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |

+----+-------------+--------+--------+---------------+------+---------+------+------+---------------------+

1 row in set (0.00 sec)

• EXPLAIN和DESCRIBE的语法（DESC是DESCRIBE 的缩写）

{EXPLAIN | DESCRIBE | DESC}

    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}

    [explain_type]

    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {

    EXTENDED

  | PARTITIONS

  | FORMAT = format_name

}

format_name: {

    TRADITIONAL

  | JSON

}

explainable_stmt: {

    SELECT statement

  | DELETE statement

  | INSERT statement

  | REPLACE statement

  | UPDATE statement

}

1） EXPLAIN和DESCRIBE同样可以查看表字段

{EXPLAIN | DESCRIBE | DESC}

    tbl_name [col_name | wild]

mysql> desc mysql.plugin name;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| name  | varchar(64) | NO   | PRI |         |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

2）解析类型

{EXPLAIN | DESCRIBE | DESC}

    [ explain_type]

    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {

    EXTENDED

  | PARTITIONS

  | FORMAT = format_name

}

EXPLAIN  EXTENDED：获取执行计划额外的信息

EXPLAIN PARTITIONS ：是用于涉及到分区表的语句

EXPLAIN FORMAT

mysql> EXPLAIN FORMAT=JSON  select * from mysql.user where user='root';

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "user",

      "access_type": "ALL",

      "rows": 6,

      "filtered": 100,

      "attached_condition": "(`mysql`.`user`.`User` = 'root')"

    }

  }

} |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN FORMAT=TRADITIONAL  select * from mysql.user where user='root';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

如果不添加FORMAT默认为TRADITIONAL

3）explainable_stmt

EXPLAIN 支持SELECT DELETE  INSERT REPLACE  UPDATE 语句

•  EXPLAIN Output Columns（执行计划输出的列）

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered（5.7）	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id (JSON name: select_id)

执行计划各个子任务的序号，这些序号是有序的。如果数据行指向其他行的联合结果，该值可以为空，此时会显示去说明指向的数据行。

select_type (JSON name: none)

执行计划各个子任务的类型，下面是所有的类型

select_type Value	JSON Name	Meaning
SIMPLE	None	简单查询，不使用联合查询和子查询
PRIMARY	None	最外层的查询
UNION	None	联合查询中第二个或者后面的语句
DEPENDENT UNION	dependent (true)	联合查询中第二个或者后面的语句，取决于外面的查询
UNION RESULT	union_result	联合查询的结果
SUBQUERY	None	子查询中的第一个查询
DEPENDENT SUBQUERY	dependent (true)	子查询中的第一个查询，取决于外面的查询
DERIVED	None	FROM后面的子查询
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

table (JSON name: table_name)

输出行的表的名称，也可以是下面的值

- : The row refers to the union of the rows with id values of M and N.

- : The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

- : The row refers to the result of a materialized subquery for the row with an id value of N. See Section 9.2.2.2, “Optimizing Subqueries with Materialization”.

partitions (JSON name: partitions)

查询匹配到的分区名称，如果值为NULL说明没有涉及分区表。

type (JSON name: access_type)

联合join的类型，下面是各个类型：

system                     连接系统表，表中只有一行数据

const                        读常量，且最多只会有一条数据，一般是使用主键或者唯一索引匹配常量（速度非常快）

eq_ref                       最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问或者连接（除system、const最快的连接）

ref                             Join 语句中被驱动表索引引用查询

fulltext                       使用fulltext索引

ref_or_null                  和ref唯一区别是，多了null值查询

index_merge               查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据

unique_subquery         子查询中的返回结果字段组合是主键或者唯一约束

index_subquery          子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引

range                         索引范围扫描

index                         全索引扫描（1覆盖索引的全表查询的情况，2全表查询，通过先查索引再查数据的情况）

ALL                              全表扫描

possible_keys (JSON name: possible_keys)

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

key (JSON name: key)

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len (JSON name: key_length)

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref (JSON name: ref)

ref列显示使用哪个列或常数与key一起从表中选择行。

如果该列的值为func，说明存在额外信息，可以使用SHOW WARNINGS去查看。

rows (JSON name: rows)

MySQL预估计的查询需要执行的行数。

对于InnoDB表，该值不一定准确。

filtered (JSON name: filtered)（5.7）

预估的获取的数据量在表中的百分比

Extra (JSON name: none)

这列包含了MYSQL如何处理语句的解决方案的额外信息。

Child of 'table' pushed join@1  

const row not found 

Deleting all rows

Distinct 

FirstMatch(tbl_name)  

Full scan on NULL key 

Impossible HAVING  

Impossible WHERE  

Impossible WHERE noticed after reading const tables 

LooseScan(m..n)

No matching min/max row

no matching row in const table

No matching rows after partition pruning

No tables used

Not exists

Plan isn't ready yet

Range checked for each record

Scanned N databases 

Select tables optimized away 

Skip_open_table, Open_frm_only, Open_full_table

Start temporary, End temporary

unique row not found

Using filesort    当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现

Using index     所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据

Using index condition

Using index for group-by   数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUPBY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index forgroup-by

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

Using MRR

Using sort_union(...), Using union(...), Using intersect(...) 

Using temporary     当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中

Using where     如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息

Using where with pushed condition 这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开ConditionPushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown

Zero limit

     

参考： https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

http://imysql.com/mysql-refman/5.7/sql-syntax.html#describe

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30236014/viewspace-2136074/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30236014/viewspace-2136074/