排序分组优化

1.说明

  在使用order by时,经常会出现Using fileSort

  在使用group by时,使用不当,会出现Using temporay

 

二:排序

1..Using fileSort

  说明会对数据使用一个外部的索引排序

  而不是内部的索引顺序进行

  mysql无法利用索引完成排序操作称为文件排序

  

1.索引使用情况

explain
select * from employee where name = '鲁班' and dep_id = 1 order by  age;

  效果:

  排序分组优化

 

 

explain
select name,dep_id,age from employee order by  name,dep_id,age;

  排序分组优化

 

 

explain
select * from employee order by  name,dep_id,age;

  排序分组优化

 

 

explain
select name,dep_id,age from employee order by  name,age;

  排序分组优化

 

 

explain
select * from employee where name = '鲁班' order by  dep_id,age;

  排序分组优化

 

 

explain
select name,dep_id,age from employee where name = '鲁班' order by  dep_id,age;

  排序分组优化

 

 

explain
select name,dep_id,age from employee where name = '鲁班' and dep_id > 1 order by age;

  排序分组优化

 

explain
select name,dep_id,age from employee where name = '鲁班'  order by age,dep_id;

  排序分组优化

 

 

explain
select name,dep_id,age from employee where name = '鲁班'  order by dep_id asc, age desc;

  排序分组优化

 

 

  结论:

  使用覆盖索引,同时order by符合索引顺序。也不能一升一降。

 

 

三:分组

1.说明

  分组前必排序

 

2.

 

上一篇:explain都不懂,还说会SQL调优?


下一篇:Explain关键字解析