Mysql 中Explain详解

Mysql 中explain详解

explain 关键字是用于模拟优化器执行sql，分析sql执行过程，用于提升sql执行效率。在select 前添加explain关键字，Mysql会设置一个标记，返回查询的执行计划信息。

explain官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

当执行一个sql查询,并使用explain进行性能分析时:

explain select (select 1 from actor where id= 1) from (select * from film where id=1) a;

字段含义

1.id:

表示一整个sql的执行顺序,id值越大,说明执行的优先级越高.如一个sql中有多个子查询,那么值越大的子查询先执行.

2.select_type:

primary:复杂查询中最外层的select;
subquery:包含在select中的子查询,不在from子句中;
derived:包含在from字句中的子查询,Mysql会将查询结果存放在临时表中.
simple:表示这个查询是个简单的查询,不包含子查询;

3.table:

表示当前行查询对应的表. <derived> 表示临时表(衍生表) , 数字表示id所在的查询语句

4.partition:

表示是否分区

5.type:

sql的查询类型,从最优到最差:
system > const > eq_ref > ref > range > index > All
sql查询最好要达到range级别,最好是ref;
如果是null表示在查询阶段进行分解,不需要走索引,查询性能非常高.如查询一个索引的最小值.

1.system:查询的数据集中的数据只有一条数据;
2.const:根据主键或者唯一索引进行查询,查询的数据只能有一条;
3.eq_ref:根据主键索引(primary key)或者唯一索引(unique key)通过表连接查询的sql,查询出来的数据可能只有一条;
4.ref:可能使用普通索引或者唯一索引的部分前缀,或者联合索引.
5.rang:根据范围进行范围查找;
6.index:扫描全索引才能得到结果,一般是扫描某个二级索引,因为一般二级索引占用的空间比主键索引占用的空间小,
        尽量规避index.应该再使用其他条件;
7.ALL:全表扫描,扫描整个聚簇索引(主键索引),没有二级索引,扫描聚簇索引的根节点.
mysql 可以通过 force index 来强制使用索引.如:
	select * from user force index (idx_name_age) where name ='LiLei';

6.possible_keys:

如果有值表示进行查询的时候用到某个索引,与key中的参数值进行搭配;
key:表示possible_keys进行索引查询的时候的索引名字,有可能出现possible_keys没有值,
	key有值的情况.比如对成本进行计算,发现不使用索引效率要高一些.

7. key:

表示possible_keys进行索引查询的时候的索引名字,有可能出现possible_keys没有值,
key有值的情况.比如对成本进行计算,发现不使用索引效率要高一些.

8.key_len:

表示使用联合索引使用字段占用的大小总和,如使用主键索引id (int类型)占用4个字节的空间,那么key_len的值就等于4;
char(n):n个字节长度;
varchar(n):utf-8,长度=3n+2  . 2表示存储字符串的长度;
tinyint:1字节;
smallint:2字节;
int:4字节;
bigint:8字节;
date:3字节;
timestemp:4字节;
datetime:8字节;
如果 允许字段为null,需要1个字节记录是否为null;
可以通过key_len 来判断 联合索引 使用的字段数量	

9.ref:

表示索引查询的条件类型,const表示常量,如果查询类型是字段,那么显示的是字段.

10.rows:

索引查询的预估值,预计要读取的行数;对于联合索引,那么使用全部字段查询会提高查询效率.

11.filtered:

rows * filtered/100 表示估算出与其他表关联查询到的数据行数

12.Extra:

有几十种,大多数出现的情况:
如果 Extra为空 就会有回表的情况
1.Using index:使用索引覆盖,表示一种查询方式,通过一个索引树能找到所有数据.
2.Using where使用where来处理结果,查询的列没有使用索引.
3.查询的列不完全被索引覆盖,where条件是其中一个前导列的范围,如 where id>1;
4.Using temporary :使用临时表来处理查询,如 select distinct col1 from table, 可以给col1建立索引来解决;
5.Using filesort 使用文件排序. 如order by col1; col1 没有索引树,所以需要将结果集查询出来 放到内存或者磁盘中进行扫描;
6.select tables optimized away :使用聚合函数 如,min max 来访问某个索引字段. type 可能为null;
7.Using index condition: 查询的列 不完全被索引覆盖, where 条件中 是一个前导列的范围;
...

回表:

在二级索引中查找到数据之后,根据二级索引中的主键id再到一级索引中根据id找到对应的数据.

覆盖索引:

Extra中 using index ,针对辅助索引,只需要通过辅助索引就能拿到结果集,
而不需要通过回表从主键索引中获取到数据.

判断sql查询是否使用索引,假设索引为 idx(a,b,c) —a,b,c 三个字段组成的联合索引

where语句条件	索引是否被使用
where a=3	是,使用到索引 a
where a=3 and b=4	是,使用到a,b
where a=3 and b=4 and c=5	是,使用到a,b,c
where b=3或where b=3 and c=4或where c=4	否
where a=3 and c=5	使用到a,但c未使用,因为索引b中间断层了
where a=3 and b>4 and c=5	使用到 a,b索引,c不能在范围查询之后
where a=3 and b like ‘kk%’ and c=4	是,使用到a,b,c索引
where a=3 and b like ‘%kk’ and c=4	是,使用到a
where a=3 and b like ‘%kk%’ and c=4	是,使用到a
where a=3 and b like ‘k%kk%’ and c=4	是,使用到a,b,c

索引优化样例

1.全值匹配:

index(col1,col2,col3)
select * from table where col1 ='a' and col2 = 'b' and col3='c'; 
select * from table where col1 ='a' and col2 = 'b';
select * from table where col1 ='a';
在联合索引中,3个sql,第三条sql的查询效率逐层递减;

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
index(col1,col2,col3) 
符合要求的查询:
select * from table where col1 ='a' and col2 ='b' and col3='c'; 
不符合要求的查询:
select * from table where  col2 = 'b' and col3='c';//跳过了col1; 
select * from table where col1 ='a'  and col3='c'; //跳过了col2;
select * from table where col3 ='c' and col2 = 'b' and col1='a'; //对于这样的sql ,mysql会按照最左前缀原则 将sql重新排列之后再执行sql,
但是会影响sql	执行效率;

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

普通的查询:
select * from table where col1 ='a';
触发索引失效的查询:
select * from table where left(col1,3) = 'a';//mysql会先查询出所有的结果集,然后再进行匹配,从而产生索引失效;

4.存储引擎不能使用索引中范围条件右边的列

正常的查询:
select * from table where col1 ='a' and col2 = 'b' and col3='c'; 
索引失效的查询:
select * from table where col1 ='a' and col2 > 'b' and col3='c';// col2 >b 
当B+Tree中的索引节点只能查询到col2这个位置,而不能查询到col3,因为col3 排序有可能已经乱掉;

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

select * from table where col1 ='a' and col2 = 'b' and col3='c'; 
修改成:
select col1,col2,col3 from table where col1 ='a' and col2 = 'b' and col3='c'; 

6.mysql在使用不等于（！=或者<>），not in ，not exists

mysql在执行这些范围查询时无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
select * from table where col1 !='a' ;

7.is null,is not null 一般情况下也无法使用索引

select * from table where col1 is null ;

8.like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作

select * from table where col1 like '%a' ;
但是 select * from table where col1 like  'a%'  ;//相当于
select * from table where col1 like  'a'  ; 然后再将结果集进行匹配;
解决like'%字符串%'索引不被使用的方法？
(1)使用覆盖索引，查询字段必须是建立覆盖索引字段;
(2)如果不能使用覆盖索引则可能需要借助搜索引擎;

9.字符串不加单引号索引失效
select * from table where col1 like ‘a’;
select * from table where col1 like a;

10.少用or或in

用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引
select * from table where col1 ='a' or col2 ='b'

11.范围查询优化

select * from table where col4 >=1 and col5 <=2000;
修改成 
select * from table where col4 >=1 and col5 <=1000;
select * from table where col4 >=1000 and col5 <=2000;
因为mysql会根据返回查询条件确定是否需要通过索引进行查询,如果数值很大,那么mysql会直接选择全表查询;