- 文件排序（filesort）是通过相应的排序算法，将取得的数据在内存中进行排序。
- MySQL 会给每个线程分配分配一块内存用于排序，称为 sort_buffer，可以通过 sort_buffer_size 参数来手动设置 sort buffer 的大小
SHOW VARIABLES LIKE '%sort_buffer_size%' -- 262144字节 256KB
SET GLOBAL sort_buffer_size = 1024*1024; -- 1M
- mysql 需要将数据在内存中进行排序，所使用的内存区域是通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个线程独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。
- 如果当 sort buffer 空间无法容纳我们需要排序的数据时，就需要在磁盘创建临时文件存放排序数据。临时文件排序采用归并排序的算法，首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作，然后把多个排好序的文件合并成一个结果集返回，因此这时排序速度会慢很多。
- 为了避免使用临时文件排序，在 MySQL 中会通过系统参数 max_length_for_sort_data 的大小（默认 1024 ），和查询语句所需取出的字段大小的总和进行比较，来切换排序算法来对排序进行优化。
SHOW VARIABLES LIKE 'max_length_for_sort_data';
- 文件排序（filesort）主要有两种排序模式：
|
排序模式 |
说明 |
|
全字段排序 |
一次性取出所有字段，然后在 sort_buffer 中进行排序，排序结束后直接返回结果集 |
|
rowid 排序 |
取出id和排序字段，放入到sort_buffer中，然后排序，排序后需要回表再次查询，然后返回结果集。 |
- 全字段排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是同时会使用更多的空间。
- 默认情况下，文件排序（filesort）优先使用更快的全字段排序，即直接将查询所需的字段全部读取到 sort_buffer 中，然后在 sort buffer 中进行排序后返回结果。
- 当 max_length_for_sort_data 大小不足时，会使用 rowid 排序，取出id和排序字段，放入到sort_buffer中，然后排序，排序后需要回表再次查询，然后返回结果集。
- 如果想优化文件排序（filesort）的速度，可以适当调整 max_length_for_sort_data 参数的大小。
SET max_length_for_sort_data = 1024;
注
- 在 MySQL 8.0.20 之后 max_length_for_sort_data 已被弃用，即使参数仍然能够修改，但是在新的优化器中，根据数据大小和其他因素动态选择排序算法更为有效。因此优化排序应该增加 sort_buffer_size 参数的大小。理想情况下，该值应该足够大，以使整个结果集能够放入排序缓冲区
- 当表中数据量非常大时，更推荐的做法是避免使用文件排序（filesort），给 order by 的排序字段加索引，利用有序索引排序。