MySQL中的排序

排序的分类

在mysql中，如果要进行排序，需要使用order by，排序过程中，如果排序字段命中索引，则利用索引排序，否则，使用文件排序（filesort）。在使用文件排序的时候，如果数据量少，则在内存中排序，如果数据量大，则利用磁盘文件进行外部排序，一般使用归并排序

if 命中索引 =》 索引排序
else 文件排序{
    if 数据量少 =》 内存排序
    else 利用磁盘外部排序
}

order by无法使用前缀索引

create table user
(
    `id` int(11) not null primary key ,
    `username` varchar(64) not null
);
insert into user(id, username) values
                                   (12345,'abcreopwropwrwop'),
                                   (34567,'adcjjioeowporwek'),
                                   (7896,'abdokfsldkflskflf'),
                                   (4567,'bafdjj0eolalks;lk');
# 创建前缀索引
create index username_index on user(username(3));

explain select * from user order by username;

从上面的查询结果看，可能第一感觉是使用了索引排序，因为是有顺序的，但是通过explain发现并没有使用索引排序，而是使用文件排序

因为前缀索引，只是字符串的前几个字符有序，但是整个字符串并不是有序的，所以使用order by对前缀索引进行排序，会使用文件排序

文件排序

当使用explain分析sql发现执行计划的extra包含using filesort，说明使用了文件排序，如果排序的数据比较少，则直接在内存中通过sort_buffer排序，如果数据量超出了其大小，就需要利用磁盘临时文件排序，性能较差，可以通过sort_buffer_size来控制其大小

当在内存中进行排序的时候，又可以分为双路排序和单路排序

• 有一个参数max_length_for_sort_data，默认是4096字节，如果select列的数据长度超过了它（即select的单行数据），则将主键+排序字段放在sort_buffer中排序，排序过后，再通过主键id回表查询，将最后的结果返回给客户端

• 如果单行数据长度没有超过其大小，则将该行数据放入到sort_buffer进行排序，排序完成后，直接返回结果，不用进行回表

磁盘文件临时排序

如果数据量太大了，超过sort_buffer，说明内存放不下，需要利用磁盘文件进行外部排序，一般会使用归并排序，利用磁盘效率更低，可是调大sort_buffer_size，避免使用磁盘文件临时排序