explain工具介绍
使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈,因为SQL语句再执行过程中会在MySQL中进行一系列的分析,优化等等的操作。
它返回的不是查询后的数据而是执行计划的信息,使用如下:
explain select * from actor;
返回的结果就是下图
1、id列
id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的 顺序增长的。并且id越大执行优先级就越高,id相同就从上往下执行,如果为null那么就是最后执行。
2、select_type
顾名思义,反应的是查询的类型是复杂查询还是简单查询,图上就是一个简单查询:simple;这个字段还有其它的值
1.primary:复杂查询中最外层的select语句
2.subquery:包含在 select 中的子查询(不在 from 子句中)
3.derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为 派生表(MySQL新版本都是将衍生表有一个合并优化,所以一般不显示这个字段)
set session optimizer_switch='derived_merge=off';#关闭衍生表的合并优化
下面用一个例子解释primary、subquery类型
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der
id越大越优先执行,也就是说subquery 优先执行,根据上面的介绍也就是在select语句中的子句,而且不在from中那么就是
(select 1 from actor where id = 1)#subquery
由于我自己的MySQL版本有点高,这个合并优化的语句不管用。。。。。。,大致是这么个意思。
4.union:在 union 中的第二个和随后的 select
explain select 1 union all select 1;
结果如下
也就是union all 后面的语句select 1
3、table列
也就是表示当前查询访问的是哪一张表。
4、 type列
这一列比较重要,反应的是当前查询语句的好坏,就是说MySQL决定如何查找表中的行,查找数据行记录的大概 范围
依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL,在优化SQL语句的时候保证到range级别。
NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在 索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表。
const, system:mysql能对查询的某部分进行优化并将其转化成一个常量,用于 primary key 或 unique key 的所有列与常数比较时,所以表最多 有一个匹配行,读取1次,速度比较快。system是const的特例,表里只有一条元组匹配时为 system。
eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合 条件的记录。
ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀(最左前缀),索引要和某个值相比较,可能会找到多个符合条件的行。
range:范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定 范围的行。
explain select * from actor where id > 1;
ALL:即全表扫描,意味着mysql需要从头到尾去查找所需要的行。一般这种情况是需要进行SQL优化了。
5、possible_keys列
该列反应的是当前查询语句可能用到的索引。explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况可能是因为表中的数据不多,MySQL在语句优化阶段判断可以不走索引,直接全表扫描
6. key列 这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引,则该列是 NULL。
7、key_len列
这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。这个列也是常常用来推断select语句使用到了那些索引字段
比如一个table中的id是int类型,因为int是4字节。所以该字段就使用了id这个字段的索引。
key_len计算规则如下:
字符串 char(n):3n字节长度 varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
(如果字段允许为null,则key_len长度还需要+1)
数值类型 tinyint:1字节 smallint:2字节 int:4字节 bigint:8字节
时间类型 date:3字节 timestamp:4字节 datetime:8字节
如果字段允许为 NULL,需要1字节记录是否为 NULL 一般来说最好不要有为空的字段。
8. ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常 量),字段名
9. rows列
这一列是mysql估计要读取并检测的行数,这里并不是最终的检索行数,而是一个估计数值
10. Extra列
这一列展示的是额外信息。常见的重要值如下:
Using index:使用覆盖索引
Using where:使用 where 语句来处理结果,查询的列未被索引覆盖
Using index condition:查询的列不完全被索引覆盖,where只是提供了一个范围
Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行 优化的,首先是想到用索引来优化
Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序,这种情况就是效率相当慢的
SQL语句的优化建议
1、查询时尽量使用全值匹配,使用索引作为条件时值要确定
2、最左前缀法则
单列索引要满足前面的字段如:select * from A where like ‘xx%’ 这样like就不会全表扫描,如果是联合索引那么查询从索引的最左前列开始并且不跳过索引 中的列。意思就是如果有一张表A有一个联合索引(a,b,c)在使用联合索引查询时where中使用了a和c那么生效的只有a,c是不会生效的。
3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转 向全表扫描
4、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),并确定查询的字段。
索引失效的情况还有:
.is null,is not null 也无法使用索引,所以建表的时候不要有可以为空的列
.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
.字符串不加单引号索引失效
少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引