DevOps开发运维
成长之路

MySQL执行计划获取及索引类型

是辅助优化手段,获取到的是优化器选择完成的,他认为代价最小最优的执行计划。
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题。如果业务中出现了慢语句,我们也需要借助此命令进行语句的评估,分析优化方案。语句运行好坏和执行计划密切相关,执行计划分析完再让其运行,获取执行计划就是语句在执行过程中的优化器位置获取。

desc select * from t_100w where k2='34ab';

table 查询的表
type 查询的类型
possiable_keys 可能会用到的索引:判断走没走索引,没用到索引的可以清除
key 使用到的索引:判断走的索引是否合理
key_len 应用索引的长度 --判断联合索引是否合理
rows 查询结果集的长度
extra 额外的信息 --filesort

type 查询的类型

至少保证range及以上索引级别

ALL

全表扫描,不走索引,需要处理
1. 查询条件列,没有索引
desc select * from t_100w where k2='34ab';
2. 查询条件出现以下语句(辅助索引列)
use world;
desc city;
desc select * from city where countrycode <> 'CHN';
desc select * from city where countrycode not in ('CHN','USA');
desc select * from city where countrycode like '%CH%';
注意:对于聚集索引列,使用这种语句,依然会走索引,早期版本也不走,新版本都走索引
desc select * from city where id <>10;

INDEX

全索引扫描,就像所有的书目录先全部看一遍
查询需要获取整个索引树中的值时:
DESC SELECT countrycode FROM city;
联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c) ---> a ab abc
SELECT * FROM t1 WHERE b
SELECT * FROM t1 WHERE c

RANGE

索引范围扫描
辅助配合索引>=,<=,like,in,or
1.desc SELECT * FROM city WHERE id<5;
2.desc SELECT * FROM city WHERE countrycode LIKE 'CH%';
3.desc SELECT * FROM city WHERE countrycode IN('CHN','USA');
注意:
1和2例子中,可以享受到B+树叶子节点双向指针的优势,但是3例子中是不能享受的,因为查找条件是不连续的。
所以,我们可以将3号列子改写:
DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
主键配合>,<,<>,not in

ref

非唯一性普通 
辅助索引配合等值查询
DESC SELECT * FROM city WHERE countrycode='CHN'

eq_ref

在多表连接时,join连接条件使用了唯一索引(uk pK)
DESC SELECT country.name,city.name FROM city
JOIN country
ON city.countrycode=country.code
WHERE city.population <100;
因为city表的countrycode列为country表中的主键,但是city表索引类型依然是ALL

system或const

system和const为一样的级别,是唯一索引的等值查询
DESC SELECT * FROM city WHERE id=10;

NULL

不返回数据的时候性能最好
DESC SELECT * FROM city WHERE id=-10;

extra 额外的信息-filesort 文件排序

show index from city;
alter table city add index CountryCode(countrycode);
alter table city drop index idx_c_p;
索引生成B树结构时,需要对整个列值进行排序,排序完落到叶子节点均匀存储
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population;
alter table city add index idx_c_p(countrycode,population);
alter table city drop index CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population;

结论
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现,是没有走索引的
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引,删除原来的单列索引
4.如果查询语句中包含having,则只需要给having前的条件添加联合索引,后面的order by即使添加也不走索引
解决办法:把having前面查询的值构建一个临时表(create temporary table xxxx as 分割出来的having前的语句),
having表改为where语句,然后把临时表与where后面的order by构建一个联合索引
赞(3)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图