版权声明:本文为博主原创文章,无需授权即可转载,甚至无需保留以上版权声明,转载时请务必注明作者。
https://blog.csdn.net/weixin_43453386/article/details/89326727
Mysql——》执行计划
- 一、执行计划的作用
- 二、执行计划的语法
- 三、执行计划的查看
- 1、示例
- 2、执行计划的分析
- 1)id:(JSON: select_id)
- 2)select_type: (JSON: none)
- 3)table:(JSON : table_name)
- 4)partitions (JSON : partitions)
- 5)type (JSON : access_type)
- 6)possible_keys (JSON : possible_keys)
- 7)key (JSON : key)
- 8)key_len (JSON : key_length)
- 9)ref (JSON : ref)
- 10)rows (JSON : rows)
- 11)filtered (JSON : filtered)
- 12)Extra (JSON : none)
一、执行计划的作用
进行数据库的sql语句调优时依据的一个重要依据
- 可以看到应该向表添加索引的位置,以便通过使用索引查找行来更快地执行语句
- 检查优化程序是否以最佳顺序连接表
二、执行计划的语法
- 适用:SELECT,DELETE,INSERT,REPLACE和UPDATE语句
- FORMAT:选择输出格式,默认 【TRADITIONAL】
- TRADITIONAL:以表格格式显示输出
- JSON:以JSON格式显示输出
1、生成执行计划——普通表——以表格格式显示输出
在SELECT前面加EXPLAIN,如:EXPLAIN SELECT......
2、生成执行计划——分区表——以表格格式显示输出
在SELECT前面加EXPLAIN PARTITIONS,如:EXPLAIN PARTITIONS SELECT......
3、生成执行计划——普通表——以JSON格式显示输出
在SELECT前面加EXPLAIN FORMAT = JSON ,如:EXPLAIN FORMAT = JSON SELECT......
三、执行计划的查看
1、示例
1)以表格格式显示输出
EXPLAIN SELECT
id,
community_id,
community_name
FROM
house_hub_fence.tb_fence_community fc
WHERE
fc.community_id IN (
SELECT
f.community_id
FROM
house_hub_fence.tb_fence_community f
LEFT JOIN house_asset.community hc ON f.community_id = hc.community_id
WHERE
hc.community_id IS NULL
OR hc.community_id = ''
)
2)以JSON格式显示输出
EXPLAIN FORMAT = JSON SELECT
id,
community_id,
community_name
FROM
house_hub_fence.tb_fence_community fc
WHERE
fc.community_id IN (
SELECT
f.community_id
FROM
house_hub_fence.tb_fence_community f
LEFT JOIN house_asset.community hc ON f.community_id = hc.community_id
WHERE
hc.community_id IS NULL
OR hc.community_id = ''
)
{
"query_block":{
"select_id":1,
"cost_info":{
"query_cost":"3468.00"
},
"nested_loop":[
{
"table":{
"table_name":"fc",
"access_type":"ALL",
"possible_keys":[
"_community"
],
"rows_examined_per_scan":1702,
"rows_produced_per_join":1702,
"filtered":"100.00",
"cost_info":{
"read_cost":"31.00",
"eval_cost":"340.40",
"prefix_cost":"371.40",
"data_read_per_join":"43M"
},
"used_columns":[
"id",
"community_id",
"community_name"
],
"attached_condition":"(`house_hub_fence`.`fc`.`community_id` is not null)"
}
},
{
"table":{
"table_name":"<subquery2>",
"access_type":"eq_ref",
"key":"<auto_key>",
"key_length":"9",
"ref":[
"house_hub_fence.fc.community_id"
],
"rows_examined_per_scan":1,
"materialized_from_subquery":{
"using_temporary_table":true,
"query_block":{
"nested_loop":[
{
"table":{
"table_name":"f",
"access_type":"index",
"possible_keys":[
"_community"
],
"key":"_community",
"used_key_parts":[
"community_id"
],
"key_length":"9",
"rows_examined_per_scan":1702,
"rows_produced_per_join":1702,
"filtered":"100.00",
"using_index":true,
"cost_info":{
"read_cost":"31.00",
"eval_cost":"340.40",
"prefix_cost":"371.40",
"data_read_per_join":"43M"
},
"used_columns":[
"community_id"
]
}
},
{
"table":{
"table_name":"hc",
"access_type":"ref",
"possible_keys":[
"_community",
"idx_community_communityId"
],
"key":"_community",
"used_key_parts":[
"community_id"
],
"key_length":"9",
"ref":[
"house_hub_fence.f.community_id"
],
"rows_examined_per_scan":1,
"rows_produced_per_join":1702,
"filtered":"100.00",
"using_index":true,
"cost_info":{
"read_cost":"1702.00",
"eval_cost":"340.40",
"prefix_cost":"2413.80",
"data_read_per_join":"31M"
},
"used_columns":[
"community_id"
],
"attached_condition":"<if>(found_match(hc), (isnull(`house_asset`.`hc`.`community_id`) or (`house_asset`.`hc`.`community_id` = 0)), true)"
}
}
]
}
}
}
}
]
}
}
2、执行计划的分析
序号 | 表格字段 | JSON字段 | 说明 |
---|---|---|---|
1 | id | select_id | 表示查询中执行select子句或操作表的顺序(数字) |
2 | select_type | None | 表示查询中每个select子句的类型(简单 OR复杂) |
3 | table | table_name | 输出的行所引用的表 |
4 | partitions | partitions | 匹配的分区 |
5 | type | access_type | 表示MySQL在表中找到所需行的方式,又称“访问类型” |
6 | possible_keys | possible_keys | 指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用 |
7 | key | key | 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL |
8 | key_len | key_len | 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度 |
9 | ref | ref | 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值 |
10 | rows | rows | 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数 |
11 | filtered | filtered | 按表条件过滤行的百分比 |
12 | Extra | None | 包含不适合在其他列中显示但十分重要的额外信息 |
1)id:(JSON: select_id)
表示查询中执行 select 子句或操作表的顺序
- id是一个数字
- id相同的为一组
- 相同id组,由上至下执行
- 所有组中,id值越大,越先执行
2)select_type: (JSON: none)
表示查询中每个select子句的类型(简单 OR复杂)
序号 | 表格字段值 | JSON字段值 | 说明 |
---|---|---|---|
1 | SIMPLE | None | 简单的SELECT(不使用UNION或子查询) |
2 | PRIMARY | None | 最外层的SELECT |
3 | UNION | None | UNION 中的第二个或随后的 select 查询, 不依赖于外部查询的结果集 |
4 | DEPENDENT UNION | dependent (true) | UNION中的第二个或随后的SELECT语句,依赖于外部查询 |
5 | UNION RESULT | union_result | UNION 查询的结果集 |
6 | SUBQUERY | None | 子查询中的第一个SELECT查询,不依赖于外部查询的结果集 |
7 | DEPENDENT SUBQUERY | dependent (true) | 子查询中的第一个SELECT,依赖于外部查询的结果集 |
8 | DERIVED | None | 用于 from 子句里有子查询的情况。 MySQL会递归执行这些子查询,把结果放在临时表里 |
9 | MATERIALIZED | materialized_from_subquery | 物化子查询 |
11 | UNCACHEABLE SUBQUERY | cacheable (false) | 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估 |
12 | UNCACHEABLE UNION | cacheable (false) | UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询 |
3)table:(JSON : table_name)
输出行引用的表的名称
- <unionM,N>:该行指的是id值为M和N的行的并集。
- <derivedN>:该行引用id值为N的行的派生表结果。例如,派生表可能来自FROM子句中的子查询
- <subqueryN>:该行引用id值为N的行的具体化子查询的结果
4)partitions (JSON : partitions)
记录将与查询匹配的分区。对于非分区表,该值为NULL。
5)type (JSON : access_type)
连接类型。用于描述不同的连接类型,按表格顺序,由最差到最好
序号 | 值 | 说明 |
---|---|---|
1 | All | 全表扫描:MySQL将遍历全表以找到匹配的行 |
2 | index | 全索引扫描:index与ALL区别为index类型只遍历索引树 |
3 | range | 索引范围扫描:对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>、in等的查询 |
4 | ref | 非唯一性索引扫描:返回匹配某个单独值的所有行。常见于使用非唯一索引的查找 |
5 | eq_ref | 唯一性索引扫描:对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
6 | const、system | 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量 system是const类型的特例,当查询的表只有一行的情况下, 使用system |
7 | NULL | MySQL在优化过程中分解语句,执行时甚至不用访问表或索引 |
- All
全表扫描:MySQL将遍历全表以找到匹配的行
EXPLAIN SELECT * FROM house_asset.community WHERE community_addr = '';
- index
全索引扫描:index与ALL区别为index类型只遍历索引树
EXPLAIN SELECT community_id FROM house_asset.community;
- range
索引范围扫描:对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>、in等的查询
EXPLAIN SELECT community_id FROM house_asset.community WHERE community_id BETWEEN 1 AND 20; EXPLAIN SELECT community_id FROM house_asset.community WHERE community_id in (1,2,3,4,5); EXPLAIN SELECT community_id FROM house_asset.community WHERE community_id >1 and community_id<10;
- ref
非唯一性索引扫描:返回匹配某个单独值的所有行。常见于使用非唯一索引的查找
EXPLAIN SELECT * FROM house_asset.community WHERE community_id = 1
- eq_ref
唯一性索引扫描:对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
EXPLAIN SELECT * FROM house_asset.community t1, house_asset.community_fence t2 WHERE t1.id = t2.id;
- const、system
当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
EXPLAIN SELECT * FROM house_asset.community WHERE id = 1;
- NULL
MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
EXPLAIN SELECT * FROM house_asset.community WHERE id = (SELECT min(id) FROM community_addr);
6)possible_keys (JSON : possible_keys)
- 指出 MySQL 能在该表中使用哪些索引有助查询。
- 如果为空,说明没有可用的索引
- 列出可能使用到的索引,但不一定被查询使用
EXPLAIN SELECT community_id FROM house_asset.community
7)key (JSON : key)
- MySQL实际决定使用的key(索引)。
- 可以使用possible_keys其中一个索引
- 也可以使用不在possible_keys值中不存在的索引
EXPLAIN SELECT community_id FROM house_asset.community
8)key_len (JSON : key_length)
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
- 在不损失精确性的情况下,长度越短越好
EXPLAIN SELECT community_id FROM house_asset.community
9)ref (JSON : ref)
显示哪些列或常量与key列中指定的索引进行比较,以从表中选择行。
如果值为func,则使用的值是某个函数的结果。
要查看哪个函数,请使用EXPLAIN之后的SHOW WARNINGS来查看扩展的EXPLAIN输出。
该函数实际上可能是算术运算符等运算符。
10)rows (JSON : rows)
rows列指示MySQL认为必须检查以执行查询的行数。对于InnoDB表,此数字是估计值,可能并不总是准确的。
11)filtered (JSON : filtered)
filtered列指示将按表条件筛选的表行的估计百分比。
最大值为100,这意味着不会对行进行过滤。
值从100开始减少表示过滤量增加。
rows显示已检查的估计行数,rows×filtered显示将与下表连接的行数。
例如,如果行为1000且过滤为50.00(50%),则使用下表连接的行数为1000×50%= 500。
12)Extra (JSON : none)
此列包含有关MySQL如何解析查询的其他信息。
序号 | 值 | 说明 |
---|---|---|
1 | Child of ‘table’ pushed join@1 (JSON: message text) | 在连接中被引用为表的子节点,可以将其下推到NDB内核。 仅当启用了 pushed-down连接时,才适用于NDB群集。 |
2 | const row not found (JSON property: const_row_not_found) | 对于诸如SELECT … FROM tbl_name之类的查询,该表为空。 |
3 | Deleting all rows (JSON property: message) | 对于DELETE,某些存储引擎(如MyISAM)支持一种处理程序方法,该方法以简单快捷的方式删除所有表行。 如果引擎使用此优化,则会显示此附加值。 |
4 | Distinct (JSON property: distinct) | MySQL正在寻找不同的值,因此它在找到第一个匹配行后停止为当前行组合搜索更多行。 |
5 | FirstMatch(tbl_name) (JSON property: first_match) | 半连接FirstMatch连接快捷方式 |
6 | Full scan on NULL key (JSON property: message) | 当优化程序无法使用 index-lookup 访问时,子查询优化将作为回退策略发生。 |
7 | Impossible HAVING (JSON property: message) | HAVING子句始终为false,无法查询任何行。 |
8 | Impossible WHERE (JSON property: message) | WHERE子句始终为false,无法查询任何行。 |
9 | Impossible WHERE noticed after reading const tables (JSON property: message) | MySQL已经读取了所有const(和system)表,并注意到WHERE子句始终为false。 |
10 | LooseScan(m…n) (JSON property: message) | 使用半连接LooseScan策略。 m和n是关键部件号。 |
11 | No matching min/max row (JSON property: message) | 没有行满足查询的条件,例如SELECT MIN(…)FROM … WHERE条件。 |
12 | No matching rows after partition pruning (JSON property: message) | 对于DELETE或UPDATE,优化器在分区修剪后未发现任何要删除或更新的内容。 它与SELECT语句的Impossible WHERE的含义相似。 |
13 | No tables used (JSON property: message) | 查询语句没有制定from子句,或者from dual子句。 对于INSERT或REPLACE语句,EXPLAIN在没有SELECT部分时显示该值。例如,它出现在EXPLAIN INSERT INTO VALUES(10)中,因为它等同于EXPLAIN INSERT INTO SELECT 101 FROM DUAL。 |
14 | Not exists (JSON property: message) | MySQL能够对查询执行LEFT JOIN优化,并且在找到与LEFT JOIN条件匹配的行之后,不会检查此表中针对上一行组合的更多行。 以下是可以通过以下方式优化的查询类型的示例:SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.idWHERE t2.id IS NULL;假设t2.id被定义为NOT NULL。 在这种情况下,MySQL扫描t1并使用t1.id的值在t2中查找行。 如果MySQL在t2中找到匹配的行,则它知道t2.id永远不能为NULL,并且不会扫描t2中具有相同id值的其余行。 换句话说,对于t1中的每一行,MySQL需要在t2中只进行一次查找,而不管t2中实际匹配多少行。 |
15 | Plan isn’t ready yet (JSON property: none) | 当优化器尚未完成为在命名连接中执行的语句创建执行计划时,EXPLAIN FOR CONNECTION会出现此值。 如果执行计划输出包含多行,则其中的任何一个或全部都可以具有此Extra值,具体取决于优化程序在确定完整执行计划时的进度。 |
16 | Range checked for each record (index map: N) (JSON property: message) | MySQL发现没有好的索引可以使用,但发现在前面的表的列值已知后可能会使用某些索引。 对于上表中的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来检索行。 这不是很快,但比执行没有索引的连接更快。 |
17 | Scanned N databases (JSON property: message) | 这表示在处理INFORMATION_SCHEMA表的查询时服务器执行的目录扫描数。 N的值可以是0,1或all。 |
18 | Select tables optimized away (JSON property: message) | 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()\MAX(),这种也是比较好的结果之一 |
19 | Skip_open_table, Open_frm_only, Open_full_table (JSON property: message) | 这些值表示适用于INFORMATION_SCHEMA表的查询的文件打开优化。Skip_open_table |
20 | Start temporary, End temporary (JSON property: message) | 这表示临时表用于半连接Duplicate Weedout策略。 |
21 | unique row not found (JSON property: message) | 对于诸如SELECT … FROM tbl_name之类的查询,没有行满足表上UNIQUE索引或PRIMARY KEY的条件。 |
22 | Using filesort (JSON property: using_filesort) | 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。 MySQL 中无法利用索引完成的排序操作称为“文件排序”EXPLAIN SELECT community_no FROM community order by community_id |
23 | Using index (JSON property: using_index) | 仅使用索引树中的信息从表中检索列信息,而不必另外寻找读取实际行。 当查询仅使用属于单个索引的列时,可以使用此策略。对于具有用户定义的聚簇索引的InnoDB表,即使Extra列中不存在使用索引,也可以使用该索引。 如果type是index并且key是PRIMARY,则会出现这种情况。EXPLAIN SELECT DISTINCT community_id FROM community; |
24 | Using index condition (JSON property: using_index_condition) | 会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行。EXPLAIN SELECT community_no FROM community where community_id in (1,2,3,4,5); |
25 | Using index for group-by (JSON property: using_index_for_group_by) | 与Using index table访问方法类似,Using group for group-by表示MySQL找到了一个索引,可用于检索GROUP BY或DISTINCT查询的所有列,而无需对实际表进行任何额外的磁盘访问。 此外,索引以最有效的方式使用,因此对于每个组,只读取少数索引条目。 有关详细信息,请参考“GROUP BY优化”。 |
26 | Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer) | 将联接中的表分成几部分读入连接缓冲区,然后从缓冲区中使用它们的行来与当前表执行连接。 (Block Nested Loop)表示使用块嵌套循环算法,(Batched Key Access)表示使用批量密钥访问算法。 也就是说,来自EXPLAIN输出前一行的表中的键将被缓冲,匹配的行将从连接缓冲区的行的表中批量提取。 |
27 | Using MRR (JSON property: message) | 使用多范围读取优化策略读取表,详见mrr |
28 | Using sort_union(…), Using union(…), Using intersect(…) (JSON property: message) | 显示如何为index_merge连接类型合并索引扫描。 请参考“索引合并优化”。 |
29 | Using temporary (JSON property: using_temporary_table) | 表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by和分组查询group byEXPLAIN SELECT community_no FROM community GROUP BY community_name; |
30 | Using where (JSON property: attached_condition) | 通常是进行了全表引扫描后再用WHERE子句完成结果过滤,需要添加合适的索引EXPLAIN SELECT community_name FROM community WHERE community_id > 10; |
31 | Using where with pushed condition (JSON property: message) | 此项仅适用于NDB表。 这意味着NDB Cluster正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。 |
32 | Zero limit (JSON property: message) | 该查询具有LIMIT 0子句,无法选择任何行。EXPLAIN SELECT distinct community_addr,community_addr FROM community limit 0 |