MySQL的排序和分组

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。

扫描二维码