介绍

如果一个查询的ORDER BY从句无法使用索引，那么MySQL会执行filesort操作来读取表的行数据并对这些行进行排序。在查询执行中filsort会使用额外的排序阶段。

为了让filesort操作得到内存，在MySQL8.0.12版本及以后，优化器会为filesort增量分配内存缓冲，知道系统变量设置的sort_buffer_size阈值，而不是在MySQL 8.0.12版本以前会直接根据sort_buffer_size的配置直接分配固定大小的内存缓冲。通过按需分配内存缓冲可以让用户将sort_buffer_size设置为一个很大的值来加速大的filesort排序，而不用担心小的filesort会浪费内存空间。（WIndows上依然会根据sort_buffer_size的配置分配固定大小的内存缓冲，因为Windows有一个较弱的多线程malloc函数。）

如果结果集太大超过了内存缓冲区大小，那么filesort操作会使用临时文件来存储数据。一些类型的查询可以完成通过in-memory的filesort操作来完成。例如，优化器可以在内存中高效的完成filesort而不用使用临时文件，下列是查询或子查询的ORDER BY形式：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

下列查询通常是web应用程序，这些通常是从一个大的结果集中展示几行：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

原文链接

1. https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html#order-by-filesort