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）在操作系统层面，优化临时文件的读写。
更多技术资讯可关注：gzitcast
