day7-mysql执行计划分析

执行计划分析

什么是执行计划

select * from t1 where name='zs';

分析的是优化器按照内置的cost计算算法，最终选择后的执行计划

查看执行计划

explain select * from world.city;
或
desc select * from world.city;

执行计划显示结果分析

显示	说明
table	此次查询涉及到的表
type	查询类型：全表扫、索引扫
possible_keys	可能用到的索引
key	最后选择的索引
key_len	索引覆盖长度
rows	此次查询需要扫描的行数
Extra	额外的信息

输出信息介绍

table

此次查询涉及到的表，针对一个查询中多个表时，精确到问题表

desc select country.name,city.name 
from city join country on 
city.countrycode=country.code
where city.population='CHN';

type 查询类型

全表扫描：不用任何的索引 ALL

desc select * from city where countrycode='CHN';
desc select * from city where countrycode != 'CHN';
desc select * from city where 1=1;
desc select * from  city where countrycode like '%ch%';
desc select * from city where countrycode not in ('CHN','USA');

索引扫描：index < range < eq_ref < counst(system) 注：越往右性能越好

index：全索引扫描

desc select countrycode from world.city;

range：索引范围查询 > < >= <= like in or between and

desc select * from city where id<10;
desc select * from city where countrycode like 'CH%';
desc select * from city where countrtcode in ('CHN','USA');
--->改写为 union all
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';

特殊情况：查询条件为主键时走 range

desc select * from city where id !='10'
desc select * from city where id not in (10,20);

ref：辅助索引等值查询

desc select * from city where countrycode='CHN';

eq_ref：多表链表中，非驱动表链接条件是主键或唯一键。

desc select country.name,city.name
from city join country
on city.countrycode=country.code
where city.population='CHN';

const(system)：聚簇索引等值查询

desc select * from city where id=10;

possible_keys，key

1. 介绍

possible_keys：可能会走的索引，所有和此次查询有关的索引。

key：此次查询选择的索引。

key_len联合索引覆盖长度

对于联合索引index(a,b,c) ,我们希望来的查询语句，对于联合索引应用越充分越好。

ket_len 可以帮助我们判断，此次查询，走了联合索引的几部分。

例如：idx(a,b,c) ----> a ab abc

全部覆盖

 

select * from t1 where a= and b= and c=
select * from t1 where a in and b in and  c in
select * from t1 where b= and c= and a=
select * from t1 where a and b order by c

部分覆盖

select * from t1 where a= and b=
select * from t1 where a=
select * from t1 where a= and c=
select * from t1 where a= and b > < >= <= like and c=
select xxxx from t1 where a order by b

2. key_len的计算：idx(a,b,c)

假设：某条查询可以完全覆盖三列联合索引。例如：

select * from t1 where a= and b= and c=

key_len= a长度？ +b长度？ +c长度?

长度指 的是什么？

长度受到：数据类型 ， 字符集 影响

长度指的是，列的最大储值字节长度

数字类型：

	not null	没有not null
tinyint	1	1+1
int	4	4+1
bigint	8	8+1

key_len：

a int not null ---> 4

a int ---> 5

字符类型： utf8 一个字符最大占3个字符

	not null	没有not null
char(10)	3*10	3*10+1
varchar(10)	3*10+2	3*10+2+1

b char(10) not null 30

b char(10) 31

C varchar(10) not null 32

C varchar(10) 33

create table t1 (
a  int not null ,                      4
b  int ,                               5
c  char(10) not null ,                 40
d  varchar(10)                         43
)charset = utf8mb4

index(a,b,c,d)

mysql> desc select * from t1  where a=1  and b=1  and  c='a'  and d='a';
mysql> desc select * from t1  where a=1  and b=1  and  c='a' ;
mysql> desc select * from t1  where a=1  and b=1 ;
mysql> desc select * from t1  where a=1 ;

练习：根据 key_len计算验证一下结论：

全部覆盖

select * from t1  where a=  and b=  and  c=  
select * from t1  where a in   and b in  and  c in 
select * from t1  where  b=  and  c=  and a=  
select * from t1 where a and b  order by c  

部分覆盖

select * from t1  where a=  and b= 
select * from t1  where a=  
select * from t1  where a= and  c=  
select * from t1  where a=  and b > < >= <= like   and  c=  
select xxx  from t1 where  a    order by b

不覆盖

bc、b、c

extra

using filesort: 表示此次查询使用到了 文件排序，说明在查询中的排序操作： order by group by distinct ..

desc select * from city where countrycode='CHN' order by population;

未完待续....