Java培训MySQL之排序分组优化索引的选择

发布时间:2020年06月24日作者:atguigu浏览次数:843

索引的选择

①首先,清除emp上面的所有索引,只保留主键索引!

drop index idx_age_deptid_name on emp;

②查询:年龄为30岁的,且员工编号小于101000的用户,按用户名称排序

explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

java培训学习

③全表扫描肯定是不被允许的,因此我们要考虑优化。

思路:首先需要让where的过滤条件,用上索引;

查询中,age.empno是查询的过滤条件,而name则是排序的字段,因此我们来创建一个此三个字段的复合索引:

create index idx_age_empno_name on emp(age,empno,name);

Java培训学习

再次查询,发现using filesort依然存在。

原因: empno是范围查询,因此导致了索引失效,所以name字段无法使用索引排序。

所以,三个字段的符合索引,没有意义,因为empno和name字段只能选择其一!

④解决: 鱼与熊掌不可兼得,因此,要么选择empno,要么选择name

drop index idx_age_empno_name on emp;

create index idx_age_name on emp(age,name);

create index idx_age_empno on emp(age,empno);

两个索引同时存在,mysql会选择哪个?

Java培训学习

explain SELECT SQL_NO_CACHE *  FROM emp use index(idx_age_name) WHERE age =30 AND empno <101000 ORDER BY NAME ;

Java培训学习

原因:所有的排序都是在条件过滤之后才执行的,所以如果条件过滤了大部分数据的话,几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序但实际提升性能很有限。  相对的 empno<101000 这个条件如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,使用empno字段的范围查询,过滤性更好(empno从100000开始)!

结论: 当范围条件和group by 或者 order by  的字段出现二选一时 ,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。


上一篇:
下一篇:
相关课程

java培训 大数据培训 前端培训 UI/UE设计培训

关于尚硅谷
教育理念
名师团队
学员心声
资源下载
视频下载
资料下载
工具下载
加入我们
招聘岗位
岗位介绍
招贤纳师
联系我们
全国统一咨询电话:010-56253825
地址:北京市昌平区宏福科技园2号楼3层(北京校区)

深圳市宝安区西部硅谷大厦B座C区一层(深圳校区)

上海市松江区谷阳北路166号大江商厦6层(上海校区)

武汉市东湖高新开发区东湖网谷(武汉校区)

西安市雁塔区和发智能大厦B座3层(西安校区)