根据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
,以避免磁盘临时文件排序