MySQL如何实现数据排序

根据explain的执行计划来看，MySQL可以分为索引排序和filesort

索引排序

• 如果查询中的order by字句包含的字段已经在索引中，且索引的排列顺序和order by子句一致，则可直接利用索引进行排序，由于索引有序，所以排序效率较高

filesort

• 使用explain分析SQL执行计划的extra字段，如果字段值为using filesort时，则说明无法应用索引的顺序来排序

• 如果排序的数据量较少，则会直接在内存sort_buffer完成排序，可通过sort_buffer_size参数来控制sort_buffer的大小（即排序的数据量小于sort_buffer_size）；如果排序的数据量大于sort_buffer_size，则需要使用磁盘临时文件完成排序，性能较差

• 内存排序又可分为双路排序和单路排序

双路排序

• 如果select 列的数据长度超过 max_length_for_sort_data，MySQL则会采用row_id排序，即将row_id & 排序字段放置到 sort_buffer中排序
• 举例：

select a,b,c from t1 where a="zhangsan" order by b;

假设单行记录超过 max_length_for_sort_data，为节省排序占用的空间，sort_buffer只会存储id和b来排序，排序后，再通过id回表查询得到a，b，c，最终将结果集返回给客户端

• 如上排序的过程需要多个回表操作，等同于需要两次查询，也即双路排序（Two-Pass Sort）

单路排序

• 如果select 列的数据没有超过 max_length_for_sort_data，则可以进行单路排序（Single-Pass Sort），即将selct 列的字段放到 sort_buffer中，排序后可直接得到结果集并返回给客户端，相比于双路排序，单路排序减少了回表操作，因此效率更高

磁盘文件临时排序

• 当查询的数据量超过sort_buffer的大小，则需要利用磁盘文件进行外部排序，一般会使用归并排序，即将数据拆分为多个文件，单独对文件排序，其后再合并成一个有序的大文件（分而治之的思想）
• 磁盘排序的效率较低，针对一些情况，可调大sort_buffer_size，以避免磁盘临时文件排序