本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
查询基础（SELECT / JOIN / 聚合 / 排序） · ORDER BY 与排序

什么是 using filesort？怎么避免？

What is using filesort and how to avoid it?

答案语言

考察要点

这道题面试官想看你是否深入理解 MySQL 的排序原理、执行计划的解读能力以及 SQL 优化的实战能力。核心考察点包括：

  1. 排序机制：是否清楚 MySQL 何时使用索引排序，何时会退化到文件排序（filesort）。
  2. 性能影响：是否明白 filesort 并非总是磁盘排序，它包含内存排序和磁盘排序两种情况，以及它们对性能的巨大差异。
  3. 优化能力：是否能通过设计和使用合适的索引来避免 filesort，从而提升查询性能。

核心概念

Using filesort 是 MySQL EXPLAIN 命令输出中 Extra 字段的一个值，它表示 MySQL 无法利用索引来完成排序操作，因此必须在结果集上执行一个额外的排序步骤。

这个名字有一定误导性，filesort 并不一定在磁盘文件上进行。

  1. 排序过程：

    • 内存排序：如果待排序的数据量不大，能够在 sort_buffer_size 系统变量定义的内存空间内完成，MySQL 会在内存中执行快速排序。这个过程相对较快。
    • 磁盘排序（外部排序）：如果待排序的数据量超过了 sort_buffer_size，MySQL 会将数据分块，对每个块进行内存排序后存入临时文件，最后再将所有排好序的临时文件进行归并排序（merge sort）。这个过程涉及大量磁盘 I/O，性能极差。

  2. Filesort 的两种算法：

    • 单路排序（Single-pass）：这是 MySQL 4.1 之后默认的优化算法。一次性读取所有需要的列（SELECT 的列 + ORDER BY 的列）到 sort_buffer 中，排序后直接返回结果。优点是减少了 I/O，但会占用更多的 sort_buffer 空间。
    • 双路排序（Two-pass）：这是早期的算法，也称为回表排序。
      1. 第一次读取：只读取排序列和主键（或行指针 rowid）到 sort_buffer 中进行排序。
      2. 第二次读取：排序完成后，根据排好序的主键/rowid 回表查询所需要的其他列。 优点是 sort_buffer 占用小，但缺点是需要两次 I/O，如果数据量大，第二次回表是大量的随机 I/O，性能很差。当 SELECT 的列总大小超过 max_length_for_sort_data 系统变量的阈值时，MySQL 会从单路排序退化为双路排序。

  3. 如何识别 Using filesort： 通过 EXPLAIN 分析 SQL 查询计划。

    示例： 假设有表 employees：

    sql
     1CREATE TABLE employees (
     2  id INT PRIMARY KEY AUTO_INCREMENT,
     3  name VARCHAR(50),
     4  age INT,
     5  hire_date DATE,
     6  INDEX idx_name (name)
     7);

    执行一个无法使用索引排序的查询：

    sql
     1EXPLAIN SELECT id, name, age FROM employees ORDER BY age;

    执行计划输出：

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
    1SIMPLEemployeesALLNULLNULLNULLNULL1000Using filesort

    这里的 type 是 ALL（全表扫描），Extra 字段显示 Using filesort，说明 MySQL 在扫描全表后，需要对结果集按 age 字段进行额外排序。

常见低分回答

  1. “filesort 就是在磁盘上排序，非常慢。”

    • 错误点：混淆了 filesort 和外部排序。filesort 是一个逻辑概念，它首先尝试在内存中排序，只有内存不足时才会使用磁盘。面试者应清晰说明内存和磁盘两种情况。

  2. “要避免 filesort，给 ORDER BY 的列加上索引就行了。”

    • 错误点：过于简化。单独为 ORDER BY 的列创建索引通常无效。优化器需要一个能够覆盖 WHERE 条件和 ORDER BY 条件的复合索引，并且列的顺序、排序方向都必须满足特定规则。例如 WHERE a=1 ORDER BY b，需要 (a, b) 索引，单 (b) 索引没用。

  3. “只要出现了 filesort，就应该调大 sort_buffer_size。”

    • 错误点：本末倒置。调大 sort_buffer_size 只是治标不治本的“安慰剂”，它仅能将磁盘排序变为内存排序，但排序动作本身并未消除。在高并发场景下，为每个会话分配巨大的 sort_buffer 会迅速耗尽服务器内存。首选方案永远是通过索引消除排序。

  4. “我给 WHERE 列和 ORDER BY 列分别建了索引，但还是 filesort。”

    • 错误点：不理解 MySQL 单次查询通常只会使用一个索引。优化器不会先用 idx_where 过滤数据，再用 idx_orderby 排序。必须创建一个能同时满足过滤和排序的复合索引。

工程实践与优化

核心思想是利用索引的有序性，使得从索引中取出的数据已经满足 ORDER BY 的顺序要求，从而避免额外的排序步骤。

如何通过索引避免 filesort：

  1. 创建合适的复合索引 索引的列顺序必须遵循最左前缀原则，并与 WHERE、ORDER BY 子句的列顺序和类型相匹配。 黄金法则：将 WHERE 中的等值条件列、范围条件列、ORDER BY 列依次放入复合索引中。

    • 场景：SELECT * FROM employees WHERE name = 'Alice' ORDER BY hire_date;
    • 错误索引：INDEX(hire_date) 或 INDEX(name) 单独存在。
    • 正确索引：CREATE INDEX idx_name_hire_date ON employees(name, hire_date);
    • 原理：MySQL 通过 idx_name_hire_date 索引找到 name = 'Alice' 的所有记录。由于索引内部是按 (name, hire_date) 排序的，所以对于 name 相同的数据，其 hire_date 已经天然有序。MySQL 只需按顺序读取即可，无需 filesort。

  2. 保证 ORDER BY 的排序方向与索引一致

    • 场景：... ORDER BY a ASC, b DESC;
    • MySQL 8.0+：支持降序索引。可以创建 CREATE INDEX idx_a_asc_b_desc ON t(a ASC, b DESC); 来精确匹配，避免 filesort。
    • MySQL 5.7 及更早版本：索引只能按同一方向（默认 ASC）存储。此时，MySQL 可以利用索引满足 a ASC，但无法满足 b DESC，仍然会触发 filesort。一种妥协是拆分查询或在应用层排序。

  3. WHERE 子句中的范围查询会中断索引排序效果

    • 场景：SELECT * FROM employees WHERE name > 'A' ORDER BY hire_date;
    • 索引：INDEX(name, hire_date)
    • 结果：仍然会 filesort。因为 name > 'A' 是一个范围查询，匹配这个范围的记录，其 hire_date 并不是全局有序的。优化器会使用索引 (name) 来过滤数据，但无法利用其排序特性来满足 ORDER BY hire_date。

  4. ORDER BY 的列必须是索引的一部分且没有跳过中间列

    • 场景：SELECT * FROM t WHERE a = 1 ORDER BY c;
    • 索引：INDEX(a, b, c)
    • 结果：会 filesort。因为 ORDER BY 跳过了索引中的 b 列。MySQL 无法保证在 a 固定的情况下，c 是有序的。
    • 正确用法：... WHERE a = 1 ORDER BY b, c; 或 ... WHERE a = 1 AND b = 2 ORDER BY c;

验证优化效果： 创建 idx_name_hire_date 索引后，再次执行 EXPLAIN：

sql
 1EXPLAIN SELECT id, name, hire_date FROM employees WHERE name = 'Alice' ORDER BY hire_date;

优化后执行计划：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEemployeesrefidx_name_hire_dateidx_name_hire_date202const10Using index

Extra 字段的 Using filesort 消失了。如果查询的列都在索引中（覆盖索引），Extra 会显示 Using index，性能最佳。

常见追问与延伸题目

  1. 追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？

    • 回答要点 1 (有 filesort)：如果存在 filesort，性能会急剧恶化。数据量增长可能导致内存排序变为磁盘排序，查询耗时可能从毫秒级增长到分钟级甚至更长，并且会大量消耗磁盘 I/O，影响整个数据库的性能。
    • 回答要点 2 (无 filesort)：如果通过索引避免了 filesort，性能下降会平缓得多。查询性能主要取决于索引扫描的行数。即使数据量增长 100 倍，只要 WHERE 条件有高选择性（如 name = 'Alice' 结果集很小），查询依然很快。性能瓶颈会从 CPU/IO（排序）转移到索引的 B+ 树深度和磁盘读取上。优化方向是确保 WHERE 条件能高效过滤，并尽可能使用覆盖索引来避免回表。

  2. 追问：ORDER BY RAND() 为什么性能极差，如何优化获取随机记录的需求？

    • 回答要点 1 (原因)：ORDER BY RAND() 会为表中的每一行计算一个随机值，然后对整个表基于这个随机值进行 filesort。这是一个计算密集且 I/O 密集的操作，数据量稍大就会导致数据库崩溃。
    • 回答要点 2 (优化方案)：
      • ID 范围法（适用于自增且连续的 ID）：先获取最大和最小 ID，然后随机一个 ID 去查询。SELECT * FROM t WHERE id >= (RAND() * (SELECT MAX(id) FROM t)) LIMIT 1;。这有数据空洞问题。
      • LIMIT 偏移法：先 COUNT(*) 得到总行数 N，在应用层生成一个 0 到 N-1 的随机偏移量 offset，然后 SELECT * FROM t LIMIT offset, 1;。缺点是 LIMIT 大偏移量时，MySQL 仍需扫描 offset 行数据，性能差。
      • 工程解法：如果随机推荐的场景对实时性要求不高，可以离线计算一个随机 ID 池（比如用定时任务每天生成 1000 个随机商品 ID 存入 Redis），线上直接从 Redis 中随机取一个 ID，再去数据库查询，这样性能最好。

  3. 追问：EXPLAIN 中 Using temporary 和 Using filesort 有什么区别和联系？

    • 回答要点 1 (区别)：
      • Using temporary：表示 MySQL 需要创建一个内部临时表来保存中间结果。常见于 GROUP BY 和 ORDER BY 的列不一致、DISTINCT 和 ORDER BY 的列不一致、UNION 等场景。临时表可能在内存（Memory 引擎）或磁盘（InnoDB/MyISAM 引擎）。
      • Using filesort：表示需要对结果集进行一次独立的排序操作。
    • 回答要点 2 (联系)：两者可能同时出现，是性能的“重灾区”。例如 SELECT COUNT(*) FROM t GROUP BY col1 ORDER BY col2;。MySQL 首先会创建一个临时表来执行 GROUP BY（存储 col1 和 COUNT(*) 的结果），然后对这个临时表进行 filesort 以满足 ORDER BY col2。Using temporary; Using filesort 意味着查询既有创建表的开销，又有排序的开销，必须优先优化。
← Previous
MySQL 的 ORDER BY 内部使用什么算法？(filesort / index 排序 / 归并排序)
Next →
ORDER BY rand() 为什么慢？大表如何高效随机取 N 行？