MySql的filesort的优化

有时我们使用EXPLAIN工具，可以看到查询计划的输出中的Extra列有filesort。filesort往往意味着你没有利用到索引进行排序。filesort的字面意思可能会导致混淆，它和文件排序没有任何关系，可以理解为不能利用索引实现排序。

排序一个带JOIN（连接）的查询，如果ORDERBY子句参考的是JOIN顺序里的第一张表的列且不能利用索引进行排序，那么MySQL会对这个表进行文件排序（filesort），EXPLAIN输出中的Extra列就有filesort。如果排序的列来自于其他的表，且需要临时文件来帮助排序，那么EXPLAIN输出的Extra列就有“Using temporary;Using filesort”字样。对于MySQL 5.1，如果有LIMIT子句，那么是在filesort之后执行LIMIT的，这样做效率可能会很差，因为需要排序过多的记录

(一)两种filesort算法

MySQL有两种filesort算法：two-pass和single-pass。

(1) two-pass

这是旧的算法。列长度之和超过max_length_for_sort_data字节时就使用这个算法，其原理是：先按照WHERE筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲（sort buffer）。如果排序缓冲大小不够，就在内存中运行一个快速排序（quick sort）操作，把排序结果存储到一个临时文件里，用一个指针指向这个已经排序好了的块。然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录.

然后合并如上的临时文件，进行排序。

然后依据排序结果再去读取所需要的数据，读入行缓冲（rowbuffer，由read_rnd_buffer_size参数设定其大小）。这是第二次读取记录。

以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓冲里存储的行指针是有序的，但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率不佳。

优点： 排序的数据量较小，一般在内存中即可完成。

缺点： 需要读取记录两次，第二次读取时，可能会产生许多随机I/O，成本可能会比较高。

(2) single-pass

MySQL一般使用这种算法。其原理是：按筛选条件，把SQL中涉及的字段全部读入排序缓冲中，然后依据排序字段进行排序，如果排序缓冲不够，则会将临时排序结果写入到一个临时文件中，最后合并临时排序文件，直接返回已经排序好的结果集。

优点： 不需要读取记录两次，相对于two-pass，可以减少I/O开销。

缺点： 由于要读入所有字段，排序缓冲可能不够，需要额外的临时文件协助进行排序，导致增加额外的I/O成本。

(二) 相关参数的设置和优化

相关参数如下。

max_length_for_sort_data：如果各列长度之和（包括选择列、排序列）超过了max_length_for_sort_data字节，那么就使用two-pass算法。如果排序BLOB、TEXT字段，使用的也是two-pass算法，那么这个值设置得太高会导致系统I/O上升，CPU下降，建议不要将max_length_for_sort_data设置得太高。

max_sort_length：如果排序BLOB、TEXT字段，则仅排序前max_sort_length个字节。

可以考虑的优化方向如下：

（1）加大sort_buffer_size。

一般情况下使用默认的single-pass算法即可。可以考虑加大sort_buffer_size以减少I/O。

需要留意的是字段长度之和不要超过max_length_for_sort_data，只查询所需要的列，注意列的类型、长度。MySQL目前读取和计算列的长度是按照定义的最大的度进行的，所以在设计表结构的时候，不要将VARCHAR类型的字段设置得过大，虽然对于VARCHAR类型来说,在物理磁盘中的实际存储可以做到紧凑，但在排序的时候，是会分配最大定义的长度的，有时排序阶段所产生的临时文件甚至比原始表还要大。MySQL 5.7版本在这方面做了一些优化，有兴趣的同学可以去了解一下。

（2）对于two-pass算法，可以考虑增大read_rnd_buffer_size，但由于这个全局变量是对所有连接都生效的，因此建议只在会话级别进行设置，以加速一些特殊的大操作。

（3）在操作系统层面，优化临时文件的读写。

MySql的filesort的优化

有时我们使用EXPLAIN工具，可以看到查询计划的输出中的Extra列有filesort。filesort往往意味着你没有利用到索引进行排序。filesort的字面意思可能会导致混淆，它和文件排序没有任何关系，可以理解为不能利用索引实现排序。

排序一个带JOIN（连接）的查询，如果ORDERBY子句参考的是JOIN顺序里的第一张表的列且不能利用索引进行排序，那么MySQL会对这个表进行文件排序（filesort），EXPLAIN输出中的Extra列就有filesort。如果排序的列来自于其他的表，且需要临时文件来帮助排序，那么EXPLAIN输出的Extra列就有“Using temporary;Using filesort”字样。对于MySQL 5.1，如果有LIMIT子句，那么是在filesort之后执行LIMIT的，这样做效率可能会很差，因为需要排序过多的记录

(一)两种filesort算法

MySQL有两种filesort算法：two-pass和single-pass。

(1) two-pass

这是旧的算法。列长度之和超过max_length_for_sort_data字节时就使用这个算法，其原理是：先按照WHERE筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲（sort buffer）。如果排序缓冲大小不够，就在内存中运行一个快速排序（quick sort）操作，把排序结果存储到一个临时文件里，用一个指针指向这个已经排序好了的块。然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录.

然后合并如上的临时文件，进行排序。

然后依据排序结果再去读取所需要的数据，读入行缓冲（rowbuffer，由read_rnd_buffer_size参数设定其大小）。这是第二次读取记录。

以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓冲里存储的行指针是有序的，但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率不佳。

优点： 排序的数据量较小，一般在内存中即可完成。

缺点： 需要读取记录两次，第二次读取时，可能会产生许多随机I/O，成本可能会比较高。

(2) single-pass

MySQL一般使用这种算法。其原理是：按筛选条件，把SQL中涉及的字段全部读入排序缓冲中，然后依据排序字段进行排序，如果排序缓冲不够，则会将临时排序结果写入到一个临时文件中，最后合并临时排序文件，直接返回已经排序好的结果集。

优点： 不需要读取记录两次，相对于two-pass，可以减少I/O开销。

缺点： 由于要读入所有字段，排序缓冲可能不够，需要额外的临时文件协助进行排序，导致增加额外的I/O成本。

(二) 相关参数的设置和优化

相关参数如下。

max_length_for_sort_data：如果各列长度之和（包括选择列、排序列）超过了max_length_for_sort_data字节，那么就使用two-pass算法。如果排序BLOB、TEXT字段，使用的也是two-pass算法，那么这个值设置得太高会导致系统I/O上升，CPU下降，建议不要将max_length_for_sort_data设置得太高。

max_sort_length：如果排序BLOB、TEXT字段，则仅排序前max_sort_length个字节。

可以考虑的优化方向如下：

（1）加大sort_buffer_size。

一般情况下使用默认的single-pass算法即可。可以考虑加大sort_buffer_size以减少I/O。

需要留意的是字段长度之和不要超过max_length_for_sort_data，只查询所需要的列，注意列的类型、长度。MySQL目前读取和计算列的长度是按照定义的最大的度进行的，所以在设计表结构的时候，不要将VARCHAR类型的字段设置得过大，虽然对于VARCHAR类型来说,在物理磁盘中的实际存储可以做到紧凑，但在排序的时候，是会分配最大定义的长度的，有时排序阶段所产生的临时文件甚至比原始表还要大。MySQL 5.7版本在这方面做了一些优化，有兴趣的同学可以去了解一下。

（2）对于two-pass算法，可以考虑增大read_rnd_buffer_size，但由于这个全局变量是对所有连接都生效的，因此建议只在会话级别进行设置，以加速一些特殊的大操作。

（3）在操作系统层面，优化临时文件的读写。