Mysql Explain的使用
explain的作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
1. id
1.)id相同 (按顺序执行)
EXPLAIN SELECT * FROM resource, deployment, resource_bundle WHERE resource.deployment_id = deployment.id AND resource.resource_bundle_id = resource_bundle.id AND resource.id = '00274ec2-d273-4a10-88c1-9817e067f9e6';
2.)id不同(递增,序号越大越先执行)
EXPLAIN SELECT * FROM deployment_allocation_bundle WHERE deployment_id = (SELECT id FROM deployment WHERE id = (SELECT resource.deployment_id FROM resource WHERE id = '00274ec2-d273-4a10-88c1-9817e067f9e6'));
3.)id有相同和不同(序号越大越先执行,出现相同的则按顺序执行)
EXPLAIN SELECT deployment_allocation_bundle.* FROM (SELECT id FROM deployment WHERE deployment.id = (SELECT deployment_id FROM resource WHERE resource.id = '00274ec2-d273-4a10-88c1-9817e067f9e6')) AS s1, deployment_allocation_bundle WHERE deployment_allocation_bundle.deployment_id = s1.id;
2. select_type
simple:简单的查询,不包含子查询和uinion
primary:包含任何复杂的部分,则最外层被标记为primary
subquery:from后的子查询被标记为DERIVED(衍生),mysql会递归执行这些子查询,把结果放到临时表里
derived:衍生union:连接
union_result:从union表获取的结果select
3. type
All最差,system最好(表中只有一条数据的情况)
system>const>eq_ref>ref>range>index>ALL
1) All
EXPLAIN SELECT * FROM resource;
2) index
EXPLAIN SELECT id FROM resource; //查询主键(唯一索引),没有where条件
3)range
EXPLAIN SELECT * FROM resource WHERE deployment_id IN ('5828a12a-289c-46d6-8bd3-ff23654d80d6','82e79c12-169b-47a3-8176-47e343b669ec');
where后字段使用了索引,且是个范围,in或between
4)ref
EXPLAIN SELECT * FROM resource WHERE deployment_id='5828a12a-289c-46d6-8bd3-ff23654d80d6'; //索引字段,使用了=
6)const
EXPLAIN SELECT * FROM resource WHERE id = '007f20f3-65b8-4840-a515-66b6e8b535d2';
唯一主键,使用了where,查询只有一条数据
4.possible_keys
- 显示可能应用在这张表的索引,一个或多个。
- 查询涉及到的字段若存在索引,则该索引将被列出,但不一定被查询实际使用
5.key
实际使用的索引,若没有使用则为null
注:在查询中若使用覆盖索引,则该索引将会出现在key列表中
EXPLAIN SELECT deployment_id,node_instance_id FROM resource;
因为查询的deployment_id,node_instance_id和索引的顺序数量一样(即覆盖了索引),所以使用了复合索引,所以key不为空
6.key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不失精度的情况下,长度越短越好。
- key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即ken_len是根据计算而得,不是通过表内检索出的
例
EXPLAIN SELECT * FROM resource WHERE deployment_id='01705a1d-cbae-4ea0-b5a0-49e429d6941b'; EXPLAIN SELECT * FROM resource WHERE deployment_id='01705a1d-cbae-4ea0-b5a0-49e429d6941b' AND node_instance_id='Server_0b47gs';
因为SELECT * FROM resource WHERE deployment_id='01705a1d-cbae-4ea0-b5a0-49e429d6941b';只使用了复合索引中一个字段deployment_id
而SELECT * FROM resource WHERE deployment_id='01705a1d-cbae-4ea0-b5a0-49e429d6941b' AND node_instance_id='Server_0b47gs';则使用了deployment_id和node_instance_id两个字段,且这两个字段都是char(64),所以第二个查询的key_len 390是第一个查询的key_len 195 2倍
7.ref
- 显示索引的那一列被使用了,如果可能的话,是一个常量。哪些列或常量被用于查找索引列上的值
EXPLAIN SELECT *
FROM resource
RIGHT JOIN deployment ON resource.deployment_id = deployment.id;
ref为空表示未使用引用的索引;不为空,表示,数据库.表.字段 被使用了索引
8.rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
EXPLAIN SELECT *
FROM resource
RIGHT JOIN deployment ON resource.deployment_id = deployment.id;
9.extra
重要的额外信息
- 1. Using filesort : 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行排序;Mysql中无法利用索引完成的排序操作称为“文件排序”
以下实例,复合索引,deployment_id和node_instance_id 若查询语句排序中使用了node_instance_id则会导致文件排序(按英文翻译),会导致性能下降,原因是排序时,索引不连续
EXPLAIN SELECT * FROM resource WHERE deployment_id='5828a12a-289c-46d6-8bd3-ff23654d80d6' ORDER BY node_instance_id; EXPLAIN SELECT * FROM resource ORDER BY deployment_id; EXPLAIN SELECT * FROM resource ORDER BY deployment_id, node_instance_id;
- 2. Using temporary : 使用了临时表保存中间结果,Mysql在查询结果排序时使用临时表,常见于排序order by 和分组查询Group By
EXPLAIN SELECT node_instance_id FROM resource WHERE cloud_entry_id IN('007f20f3-65b8-4840-a515-66b6e8b535d2') GROUP BY node_instance_id;
extra出现了 Using temporary; Using filesort 性能非常差,所以在orderBy或者groupBy时最好按索引进行排序
EXPLAIN SELECT deployment_id, node_instance_id FROM resource WHERE deployment_id IN('5828a12a-289c-46d6-8bd3-ff23654d80d6') GROUP BY node_instance_id;
- 3. using index
- 表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错。
- 如果同时出现using where ,表明索引被用来执行索引键值的查找;
- 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
1.覆盖索引:就是select 的数据列只用从索引中就能够取得,不必读取数据行,Mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被建的索引覆盖
注意:如果使用覆盖索引,select列表只取出需要的列,不可select * 因为如果所有字段一起做索引会导致索引文件过大,查询性能下降
2.同时出现using where ,表明索引被用来执行索引键值的查找;
EXPLAIN SELECT deployment_id, node_instance_id FROM resource WHERE deployment_id IN('5828a12a-289c-46d6-8bd3-ff23654d80d6') GROUP BY node_instance_id;
3.没有同时出现using where,索引用来读取数据而非执行查找动作
EXPLAIN SELECT deployment_id FROM resource;
作者:guanbin —— 纵码万里千山
出处:https://www.cnblogs.com/guanbin-529/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。