深入理解 MySQL 的 Filesort：告别痛苦排序的原理与实践

🌟我的其他文章也讲解的比较有趣😁，如果喜欢博主的讲解方式，可以多多支持一下，感谢🤗！

其他优质专栏： 【🎇SpringBoot】【🎉多线程】【🎨Redis】【✨设计模式专栏（已完结）】…等

如果喜欢作者的讲解方式，可以点赞收藏加关注，你的支持就是我的动力
✨更多文章请看个人主页： 码熔burning

前言

你好！当我们谈论 MySQL 查询优化时，EXPLAIN 是我们的X光机，而 Extra 列中的 Using filesort 绝对是最常见的“病灶”之一。它意味着 MySQL 无法利用索引的有序性来满足 ORDER BY 子句的需求，不得不自己动手进行排序。这个“自己动手”的过程，就是 Filesort。

了解MySQL的Order by与Group by详解请看：MySQL的Order by与Group by优化详解！

Filesort 的名字可能会让人误以为它总是涉及文件操作，但实际上它可能大部分甚至全部在内存中完成。然而，一旦数据量超出内存容量，溢写到磁盘进行排序时，性能会急剧下降。

Filesort 发生的场景回顾

在深入原理之前，先简单回顾一下 Filesort 为什么会发生：

• ORDER BY 子句中的列没有索引。
• ORDER BY 子句中的列与索引的顺序或方向不匹配（例如，索引是 (A ASC, B ASC)，但查询是 ORDER BY A ASC, B DESC）。
• WHERE 子句和 ORDER BY 子句结合，导致无法使用索引（例如，WHERE 子句在索引列上使用了范围查询，而 ORDER BY 使用了索引后续的列）。
• 使用了 ORDER BY RAND()。
• 优化器认为使用 Filesort 比使用索引更便宜（极少数情况，通常意味着索引不合适或数据量非常小）。

当这些情况发生时，MySQL 的优化器就会选择 Filesort 这个后备方案。

Filesort 的工作原理：三步走（可能伴随痛苦的磁盘 I/O）

Filesort 的过程可以概括为几个主要阶段：

阶段 1：收集排序数据 (Reading and Buffering)

这是 Filesort 的第一步，MySQL 需要从表中读取需要排序的数据。读取哪些数据呢？

1. 首先，根据查询的 WHERE 子句和其他条件，MySQL 会找到所有符合条件的行。
2. 对于每一条符合条件的行，MySQL 需要收集以下信息到内存中的一块称为 “排序缓冲区”（Sort Buffer） 的区域：
   • 用于排序的列 (ORDER BY 子句中的列)。
   • 查询需要返回的列 (SELECT 列表中的列)。
   • 行的唯一标识符（通常是主键的值），以便在需要时能够回表获取行的其他信息。

这一阶段，MySQL 会尽量将所有需要的列都读入 Sort Buffer，以避免后续的回表操作。但这取决于 Sort Buffer 的大小以及一个关键的系统变量 max_length_for_sort_data。

• 单次扫描策略（Single-Pass Strategy）： 如果 用于排序的列的长度总和 + 行的唯一标识符长度 + 需要返回的列的长度总和 小于等于 max_length_for_sort_data，MySQL 会倾向于将所有需要的列都放入 Sort Buffer。这样，排序完成后数据就绪，无需回表。
• 两次扫描策略（Two-Pass Strategy）： 如果上述总长度超过了 max_length_for_sort_data，或者由于 Sort Buffer 空间限制，MySQL 可能只将 用于排序的列 + 行的唯一标识符 放入 Sort Buffer。这样，排序完成后，还需要根据排序好的唯一标识符再次回表获取其他列的数据。这会增加大量的随机 I/O。

显然，单次扫描策略通常比两次扫描策略更优，因为它避免了回表开销。max_length_for_sort_data 的默认值通常比较大 (1024 字节或 4096 字节)，在列不多的情况下，很多查询都能走单次扫描。

阶段 2：执行排序 (Sorting)

数据被收集到 Sort Buffer 后，MySQL 会根据 ORDER BY 子句指定的列和方向进行排序。

• 内存排序： 如果所有收集到的数据都能完全放入 sort_buffer_size 指定的内存区域，排序操作将在内存中高效完成。这通常使用快速排序或归并排序算法。这是 Filesort 中性能最好的情况。
• 磁盘排序 (Merge Sort / External Sort)： 这是 Filesort 性能最差、也是我们最想避免的情况。如果收集到的数据量超出了 sort_buffer_size 的限制：
  1. Sort Buffer 装满后，MySQL 会先对缓冲区内的数据进行排序。
  2. 将这个已排序的块（称为一个“Run”）写入临时文件（通常在 tmpdir 指定的目录下）。
  3. 清空 Sort Buffer，继续读取下一批数据，排序，写入另一个临时文件。
  4. 这个过程重复进行，直到所有符合条件的行都被读取、分块排序并写入多个临时文件。
  5. 归并阶段： 当所有 Run 都生成后，MySQL 需要将这些分散在不同临时文件中的有序 Run 合并成一个最终的有序结果集。这需要反复地从临时文件中读取数据块，两两或多路进行归并，并将归并结果写入新的临时文件，直到最终得到一个完全有序的结果集。这个归并过程可能需要多个 passes（趟）。

sort_buffer_size 和 max_length_for_sort_data 的设置、待排序数据量、以及磁盘 I/O 性能是影响这个阶段速度的关键因素。大量使用临时文件进行归并排序会产生巨大的磁盘 I/O 开销，是 Filesort 成为性能瓶颈的主要原因。

阶段 3：返回结果 (Returning Results)

排序完成后，MySQL 将排序好的数据（或者指向排序好行的指针）发送给客户端。

• 如果使用的是单次扫描策略： 排序好的数据已经包含了所有需要的列，直接发送即可。
• 如果使用的是两次扫描策略： MySQL 需要根据排序好的行唯一标识符（主键），逐一到表中进行回表查询，获取该行的所有列数据，然后再将完整的行发送给客户端。这一步会产生大量的随机回表 I/O，尤其是在排在前面的行分散在表的各个地方时，开销更大。

为什么 Filesort 这么慢？

总结一下 Filesort 的主要性能开销：

1. CPU 开销： 排序算法本身需要计算。
2. 内存开销： 需要分配和管理 Sort Buffer。
3. 磁盘 I/O 开销：
   • 读取原始数据。
   • 如果数据量大，需要将排序的 Run 写入临时文件。
   • 需要从多个临时文件读取数据进行归并。
   • 如果使用的是两次扫描策略，还需要进行回表操作。

特别是磁盘 I/O，它是比内存操作和 CPU 计算慢几个数量级的操作。一旦 Filesort 导致数据溢写到磁盘，性能就会急剧恶化。

Filesort 在 EXPLAIN 中

EXPLAIN 的 Extra 列显示 Using filesort 就表明发生了 Filesort。

有时你可能会在 Extra 列看到一些与 Filesort 相关的变种，比如：

• Using filesort: 通用的 Filesort 标识。
• Using temporary; Using filesort: 通常表示 GROUP BY 操作无法使用索引，MySQL 创建了临时表，并可能在该临时表上进行了 Filesort。

如何避免或优化 Filesort？

理解了 Filesort 的原理，优化方向就明确了：尽量让 MySQL 不需要 Filesort。

1. 创建合适的索引： 这是最根本、最有效的手段。根据 ORDER BY 子句的列和方向，结合 WHERE 子句，创建能够覆盖排序需求的索引。确保 ORDER BY 的列是索引的前缀，或者能接在 WHERE 子句使用的索引前缀之后。
2. 调整 sort_buffer_size (谨慎)： 如果 EXPLAIN 显示 Using filesort，并且状态变量 Sort_merge_passes 很高（表明发生了多次磁盘归并），说明 sort_buffer_size 太小了。适当增加这个值可以减少磁盘操作，但要注意这是每个需要排序的连接都会分配的内存，设置过大可能导致服务器内存耗尽。通常建议通过索引优化来解决，而不是仅仅增大缓冲区。
3. 调整 max_length_for_sort_data (谨慎)： 如果 Filesort 发生，并且你怀疑是由于采用了两次扫描策略导致的回表开销，可以适当增加这个值，让 MySQL 更倾向于单次扫描策略。但这同样会增加 Sort Buffer 的内存需求。
4. 优化查询本身： 有时可以尝试重写查询，或者在应用层进行排序，或者使用更适合排序的存储引擎（虽然 InnoDB 在大多数场景下已经足够优秀）。
5. 限制结果集大小 (LIMIT)： 如果只需要排序结果的前 N 条，使用 LIMIT 子句可以大大减少需要排序的数据量，即使发生 Filesort，性能也会好很多。如果能结合索引，LIMIT 优化效果更佳（MySQL 找到前 N 条符合索引顺序的记录就可以停止）。

总结

Using filesort 是 MySQL 在无法使用索引进行排序时采取的后备方案。它通过在内存（可能溢写到磁盘）中对数据进行收集、排序、然后返回结果来实现。Filesort 特别是涉及到磁盘操作时，会带来显著的 CPU、内存和磁盘 I/O 开销，严重影响查询性能。

理解 Filesort 的原理，尤其是单次/两次扫描策略和磁盘归并过程，能帮助我们更清楚地认识其代价。优化 Filesort 的核心策略是通过创建合适的索引来避免它，让 MySQL 直接利用索引的有序性。实在无法避免时，可以通过调整 sort_buffer_size 等参数来缓解磁盘 I/O 压力，但这不是首选的优化手段。

记住，EXPLAIN 永远是你分析和优化 Filesort 的起点！看到 Using filesort，就要开始你的索引优化之旅了！🛠️

希望这次对 Filesort 原理的详细讲解对你有所帮助！理解原理才能更好地对症下药。