mysql中的文件排序(filesort)

在MySQL中的ORDER BY有两种排序实现方式： 
1. 利用有序索引获取有序数据 
2. 文件排序

在explain中分析查询的时候，利用有序索引获取有序数据显示Using index ，文件排序显示 Using filesort。

只有当ORDER BY中所有的列必须包含在相同的索引，并且索引的顺序和order by子句中的顺序完全一致，并且所有列的排序方向（升序或者降序）一样才有，（混合使用ASC模式和DESC模式则不使用索引）

where语句 与 order by 语句组合满足最左前缀

在其他的情况下 使用 文件排序如下：

 

       1） where语句与order by语句，使用了不同的索引

 

  2） 检查的行数过多，且没有使用覆盖索引

 

  3） ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引

 

  4） 对索引列同时使用了ASC和DESC

 

  5） where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式

 

  6） where 语句与ORDER BY语句组合满足最左前缀，但where语句中查找的是范围。

filesort

 

这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作。即在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为文件排序（filesort）

 

  文件排序是通过相应的排序算法,将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

filesort分两种

双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。排序后再吧查询字段依照行指针取出，共执行两次磁盘io。

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。 执行一次磁盘io。

MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。

 

如果order by的子句只引用了联接中的第一个表，MySQL会先对第一个表进行排序，然后进行联接。也就是expain中的Extra的Using Filesort.否则MySQL先把结果保存到临时表(Temporary Table),然后再对临时表的数据进行排序.此时expain中的Extra的显示Using temporary Using Filesort.