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.