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):聚簇索引等值查询

1 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长度?

长度指 的是什么?

长度受到:数据类型 , 字符集 影响

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

数字类型:

 

上一篇:你好javascript day7


下一篇:2020 CCPC Wannafly Winter Camp Day7 (Div.1&2)