EXPLAIN and TRACE
一、Explain
1.数据准备
user表:
role表:
role_user表:
2.测试
explain select * from user where id =2;
一共有12个字段,下面介绍每个字段含义!
1.id字段
id表示执行select查询语句的序号,它是sql执行的顺序的标识,sql按照id从大到小执行,id相同的为一组,从上到下执行。
explain select * from user where id in (select user_id from role_user);
若是id不同,例如执行下面的sql:
explain select (select 1 from user limit 1) from role;
id越大的就越先执行,可以看到id=2的执行的是user表,也就是子查询部分,最后执行最外层的部分。
2.select_type字段
select_type表示查询的类型,也就是对应的是简单查询还是复杂查询。下面就分别来看看select_type中的所有查询类型。
SIMPLE
simple表示简单查询,不含有任何的复杂查询。
PRIMARY
复杂查询中「最外层的select语句的查询类型就是PRIMARY」。
explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2));
这里,最外层的select * from role where id =?就被标记为PRIMARY类型。
SUBQUERY
在「select或者where中包含的子查询」会被表示为SUBQUERY类型,例如上一句执行的sql中就有两次的子查询为SUBQUERY。
DERIVED
在from包含的子查询中会被表示为DERIVED类型,Mysql会递归执行这些子查询,并且把结果放在临时表中。在Mysql 5.7以上的版本中对其做了优化,新增了derived_merge(派生合并),可以加快查询效率。
explain select * from (select name from user union select name from role) a where a.name = '张三';
UNION
在出现「UNION查询语句中,第二个select的查询语句就会被表示为UNION」。上图可以看到。
UNION RESULT
「UNION查询语句的结果被标记为UNION RESULT」,如上图。
第四行记录中从table字段中可以看出,第四行的记录来源于第二行和第三行<union2,3>,因此一个UNION查询语句的结果就会被标记为UNION RESULT。
其它
上面的七个select_type都是比较常见的,还有一些不常见的,作为了解就好:
1、DEPENDENT UNION:也表示UNION查询语句中第二个或者后面的语句,但是取决于外面的查询。
2、DEPENDENT SUBQUERY:子查询中的第一个select语句,也是依赖于外部的查询。
3、UNCACHEABLE SUBQUERY:子查询的结果不能被缓存,必须重新评估外连接的第一行。
3.table字段
table字段表示的是查询的是哪个表,也可以表示衍生表。
4.type字段
「type字段表示的sql关联的类型或者说是访问的类型」
从这个字段中我们可以确定这条sql查找数据库表的时候,查找记录的大概范围是怎么样的,直接就能体现sql的效率问题。
type字段的类型也是有比较多,主要常见掌握的有以下几个:
system、const 、eq_ref 、ref 、range 、index、ALL。
它的性能体现是从高到低,即:
system > const > eq_ref > ref > range > index > ALL
system
system是const的特例,「表示表中只有一行记录」,这个几乎不会出现,也作为了解。
const
const表示通过索引一次就查找到了数据,一般const出现在「唯一索引或者主键索引中使用等值查询」,因为表中只有一条数据匹配,所以查找的速度很快。
explain select * from user where id =2;
eq_ref
eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件,对于每一个索引键,表中只有一条记录与之匹配。
explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;
ref
ref性能比eq_ref差,也表示表的连接匹配条件,与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。ref扫描后的结果可能会找到多条符合条件的行数据,本质上是一种索引访问,返回匹配的行。
explain select * from user where name = '张三';
range
「range使用索引来检索给定范围的行数据,一般是在where后面使用between、<、>、in等查询语句就会出现range」
explain select * from user where id > 2;
index
index表示会遍历索引树,index回避ALL速度快一些,但是出现index说明需要检查自己的索引是否使用正确。
explain select id from user;
ALL
「ALL与index的区别就是ALL是从硬盘中读取,而index是从索引文件中读取」,ALL全表扫描意味着Mysql会从表的头到尾进行扫描,这时候表示通常需要增加索引来进行优化了,或者说是查询中并没有使用索引作为条件进行查询。
explain select * from user;
5.possible_keys
possible_keys表示这一列查询语句可能使用到的索引,列出来的索引并不一定真正的使用到。
当没有使用索引为NULL时,说明需要增加索引来优化查询了,若是表的数据比较少的话,数据库觉得全表扫描更快,也可能为NULL。
6.key字段
表示真正使用到的索引,即possible_keys中包含key的值。
7.key_len字段
表示sql查询语句中索引使用到的字节数,这个字节数并不是实际的长度,显示的是索引字段最大的可能长度。
一般来说在不损失精度的前提下,key_len是越小越好,比如上面的测试表的id为int类型,int类型由4个字节组成:
explain select * from user where id =2;
key_len对于不同的类型有自己的计算规则,具体的计算规则如下所示:
数据类型 | 所占字节数 |
---|---|
字符串 | char(n):n字节长度;varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2 |
数值类型 | tinyint:1字节;smallint:2字节;int:4字节;bigint:8字节 |
时间类型 | date:3字节;timestamp:4字节;datetime:8字节 |
若是索引为字符串类型的时候,实际存储的字符串非常长,已经超出了字符串类型的存储最大长度(768字节),mysql,就会使用类似左前缀索引来处理。
8.ref字段
ref表示列与索引的比较,表连接的匹配条件,表示哪些列或者常量被用于查询索引列上的值。
9.rows字段
rows表示估算的要扫描的行数,一般Mysql会根据统计表信息和索引的选用情况,估算出查找记录所要扫描的行数。
10.partitions、filtered字段
partitions表示所匹配的分区;filtered表示的是查询表行所占表的百分比。
11.Extra字段
该字段显示的是sql查询的额外信息,主要有以下几种情况:
Using index
表示查询的列被索引覆盖,这个是查询性能比较高的体现,即所要查询的信息搜在索引里面可以得到,不用回表,索引被正确的使用。
explain select id from user where id =2;
Using where
该属性与Using index相反,查询的列并没有被索引覆盖,where条件后面使用的是非索引的前导列,它仅仅是使用了where条件而已。
explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;
Using temporary
「Using temporary表示使用了临时表存储中间的结果,一般在对结果排序的时候会使用临时表」,例如:排序order by 和分组查询group by。
explain select * from (select name from user union select name from role) a where a.name = '张三';
Using filesort
Using filesort表示文件排序,说明Mysql对数据使用了外部的索引进行排序,并没有使用表中的索引进行排序。
explain select * from user order by name;
Using join buffer
Using join buffer表示使用连接缓存。
它强调在获取连接条件时,并没有使用索引,而是使用连接缓冲区来存储中间结果,若是出现该值,一般说明需要添加索引来进行优化了。
Impossible where
Impossible where会出现在where后的条件一直为false的情况下,这种可以忽视,比较少出现。
explain select * from user where name = 'hah' and name = 'sfsd';
Select tables optimized away
表示select语句没有遍历表或者索引就返回数据了。
explain select min(id) from user;
在使用索引进行查询的时候,不要做任何的函数操作,不然会导致索引失效。
在数据库的查询中不要使用(!=或者<>)等判条件和is null,is not null、like关键词中以%开头来判断,不然也会使索引失效。
二、Trace
1.作用
对SQL跟踪,可以知道SQL是如何执行的, trace 工具主要看的是 MySQL 大致的优化和计算成本过程,比EXPLAIN更强大。
2.使用
开启trace工具会影响mysql性能,所以只能临时分析sql使用,用完之后立即关闭。
开启trace:
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启
set session optimizer_trace="enabled=off"; #关闭
EXPLAIN:
第一条 SQL 中 possible_keys 有可以使用的索引 idx_name_age_position ,但是最后实际上 MySQL 没有使用 idx_name_age_position ,而是使用了全表扫描。因为第二条 SQL name > ‘itwxe’ 扫描的联合索引 idx_name_age_position 的结果太多,回表成本太大,所以 MySQL 选择了全表扫描。
3.使用步骤
-- 首先开启trace
mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;
-- 执行查询SQL
mysql> select * from employees where name > 'wei' order by position;
-- 查询trace字段
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 当分析完SQL,关闭trace
mysql> set session optimizer_trace="enabled=off";