mysql执行计划知识点有哪些

这篇文章主要讲解了“mysql执行计划知识点有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql执行计划知识点有哪些”吧！

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

感谢各位的阅读，以上就是“mysql执行计划知识点有哪些”的内容了，经过本文的学习后，相信大家对mysql执行计划知识点有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！