order by和group by这两个要十分注意，因为一不小心就会产生文件内排序，即file sort，这个性能是十分差的。下面来看具体的案例分析。
首先建表：
create table `tblA`(
`id` int not null primary key auto_increment comment '主键',
`age` int not null comment '年龄',
`birth` timestamp not null comment '生日'
) ;
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
create index idx_age_birth on tblA(age, birth);
1. order by：
看看下面语句的执行计划：
explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by age,birth;
这两个个毫无疑问，可以用到索引。
执行计划
再来看看这个：
explain select * from tblA where age > 20 order by birth;
执行计划
显然我们可以看到这里产生了filesort，为什么呢？因为age是范围，且order by的直接是二楼，带头大哥没了，所以索引失效了。
那这样呢？
explain select * from tblA where age > 20 order by birth, age;
explain select * from tblA where age > 20 order by age, birth;
执行计划
第一个还是不行，因为范围后失效，且order by是从birth二楼开始的。第二个可以用到索引，不会产生filesort，是因为，虽然前面的age是范围，但是order by的又是从age开始，带头大哥在。
上面这些都好理解，看看这个：
explain select * from tblA order by age desc, birth asc;
执行计划
奇了怪了，带头大哥在，也没有范围，为啥就出现了filesort了呢？
这是因为age是降序，birth又是升序，一升一降，就会导致索引用不上，就会产生filesort了。如果把两个都改成desc或者asc，那就没问题了。
注意：
MySQL的filesort有两种策略，
MySQL4.1之前，叫双路排序。
就是会进行两次磁盘I/O操作。读取行指针和order by的列，
对它们排序，然后扫描排好序的表，再从磁盘中取出数据来。
4.1之后的版本，叫单路排序，只进行一次I/O。
先将数据从磁盘读到内存中，然后在内存中排序。
但是，如果内存，即sort_buffer_size不够大，性能反而不如双路排序。
order by优化小总结：
- 尽量避免
select *; - 尝试增大
sort_buffer_size，不管用哪种算法，增大这个都可以提高效率； - 尝试增大
max_length_for_sort_data，增大这个，会增加用改进算法的概率。
2. group by：
group by 其实和order by一样，也是先排序，不过多了一个分组，也遵从最佳左前缀原则。要注意的一点是，where优于having，能用where时就不要用having。
网友评论