mysql filesort 优化_MySQL的两种filesort算法介绍以及相关参数的设置和优化

一.filesort算法介绍

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

(1).two-pass

列长度之和超过max_length_for_sort_data字节时就使用这个算法，其原理是:先按照where筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲区(sort buffer)。如果排序缓冲大小不够，就在内存中运行一个快速排序操作，把排序结果存储到一个临时文件里，

用一个指针指向这个已经排序好了的块，然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录。以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓存里存储的行指针是有序的，

但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率很低。

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

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

(2).single-pass

MySQL一般使用这种算法。其原理是：按照筛选条件把SQL中涉及的字段全部读入排序缓冲区(sort buffer)里，然后依据排序字段进行排序，

如果排序缓冲不够，会将临时排序结果写入到一个临时文件里，最后合并临时排序文件，直接返回已经排序好的结果集.

优点是：不需要读取记录两次，相对于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).在操作系统层面，优化临时文件的读写。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2135906/，如需转载，请注明出处，否则将追究法律责任。