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