面试里经常出现这样一道题：表上建了联合索引 (a, b, c)，SQL 写成 WHERE b = 1 ORDER BY a，问能不能用上索引排序。很多人背过结论——「最左前缀原则」——但一旦列顺序和 WHERE 条件顺序不一致、或者 WHERE 里出现范围查询、或者 ORDER BY 的列顺序与索引顺序颠倒，就开始含糊。这篇文章把「排序失效」这件事拆到可判定的程度：给出一个可以逐条套用的判定流程，配合真实执行计划（EXPLAIN / EXPLAIN FORMAT=JSON）中的 Using filesort、Using index 等信号，说明每一种排列组合下 MySQL 到底会不会退化成文件排序。
一、先明确判定目标：什么才算「用上了索引排序」
MySQL 的排序只有两条路径：
- 索引有序性直接输出：存储引擎按索引顺序回表或直接在二级索引上扫描，结果天然有序，无需额外排序。执行计划中表现为
Extra不含Using filesort，常见Using index（覆盖索引）或Using where。 - filesort：把候选行取出来，在 sort buffer 中排序。数据量小时是内存排序（quicksort），超出
sort_buffer_size后改用临时文件做归并（merge），也就是常见的Using temporary; Using filesort。执行计划中表现为Extra含Using filesort。
判定联合索引能否消除 filesort，核心只有一句话：索引扫描出来的行，其顺序是否已经是 ORDER BY 要求的顺序。而索引扫描顺序由索引定义顺序 (a, b, c) 决定，WHERE 条件的作用是把索引扫描切成若干「等值前缀段」，只要每段的内部顺序与 ORDER BY 一致，文件排序就能被省掉。
容易混淆的一点：
Using filesort不代表一定会用到磁盘临时文件。它只是「需要额外排序动作」的标记，哪怕在内存中完成排序也叫 filesort。反过来，看到Using index也不代表没有 filesort，覆盖索引和排序是两件正交的事情。
下面统一用这张表做实验，MySQL 8.0.33，InnoDB，默认隔离级别 REPEATABLE READ：
CREATE TABLE t_order (
id INT NOT NULL AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d VARCHAR(64) NOT NULL DEFAULT '',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_abc (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 造 20 万行数据，a/b/c 各有 10 种取值，形成较高基数的组合
INSERT INTO t_order (a, b, c, d)
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n % 10, (n DIV 10) % 10, (n DIV 100) % 10, CONCAT('row-', n)
FROM seq;
插入完成后执行 ANALYZE TABLE t_order; 让统计信息生效，后续的 EXPLAIN 才有参考价值。
二、核心判定模型：等值前缀 + 有序后缀
把联合索引 (a, b, c) 想象成按字典序排列的三元组序列：先按 a 排，a 相同的按 b 排，b 相同的按 c 排。一条 SQL 能否利用这个顺序，取决于两件事能否同时满足：
- WHERE 中的等值条件必须构成索引的最左连续前缀。例如
WHERE a = 1 AND b = 2是合法前缀；WHERE b = 2单独出现就断掉了最左性，索引无法用于定位（除非发生 index skip scan 或走覆盖索引全扫）。 - ORDER BY 中的列必须紧接在这个前缀之后，且顺序与索引定义一致，中间不能跳过索引中已定义的列。
用一句话概括就是：等值条件吃掉前缀，剩下的部分必须按索引顺序原样出现在 ORDER BY 里。
据此可以列出判定表（索引 (a, b, c)）：
| SQL 形态 | 等值前缀 | 剩余有序段 | 是否 filesort |
|---|---|---|---|
WHERE a=1 ORDER BY b, c |
a | b, c | 否 |
WHERE a=1 ORDER BY c, b |
a | 顺序颠倒 | 是 |
WHERE a=1 AND b=2 ORDER BY c |
a, b | c | 否 |
WHERE b=2 ORDER BY a |
无（b 非最左） | — | 是 |
WHERE a>1 ORDER BY a |
无（范围） | a | 否（范围列本身有序） |
WHERE a>1 ORDER BY b |
无（范围） | b 在多个 a 值下重复 | 是 |
WHERE a=1 ORDER BY b, c, d |
a | b, c 有序，d 不在索引 | 是 |
WHERE a=1 ORDER BY b DESC, c DESC |
a | b, c（同为逆序） | 否（8.0 支持） |
WHERE a=1 ORDER BY b ASC, c DESC |
a | 方向混用 | 是 |
注意最后两行：MySQL 8.0 起，索引可以被整体反向扫描，所以 ORDER BY b DESC, c DESC 与索引顺序完全一致（只是整体方向相反），不产生 filesort；但只要出现方向混用，比如 b ASC, c DESC，就需要降级为 filesort。8.0 还支持 DESC 索引（KEY idx_mix (a ASC, b DESC)），可以为混用方向单独建索引来消除排序。
验证实验：WHERE b=1 ORDER BY a
这是面试题最经典的形式，列顺序与 WHERE 顺序「冲突」的直觉就来自它：
EXPLAIN SELECT a, b, c FROM t_order WHERE b = 1 ORDER BY a;
输出中 type=index、key=idx_abc、rows 接近全表，Extra 显示 Using where; Using index; Using filesort。这里的关键在于：优化器选择了全索引扫描（因为要的列 a、b、c 都在索引里，走覆盖索引比回表便宜），但扫描顺序是 (a, b, c) 的字典序，而 WHERE b = 1 会把所有 a 值下 b=1 的行挑出来，这些行按 a 递增排列，但扫描过程会不断在 b≠1 的区间里「空转」，整体输出对 ORDER BY a 而言并不连续有序，因此必须 filesort。
统计信息层面也可以佐证：优化器评估 b = 1 的选择性只有约 1/10，走 idx_abc 无法有效收敛扫描范围，所以它干脆选择了 index 全扫 + filesort。如果你把条件改成 WHERE a = 1 AND b = 1 ORDER BY c，Extra 中的 Using filesort 立刻消失。
三、范围条件是最常被忽略的分界点
很多人背了「等值前缀」就以为范围条件也是前缀的一部分，结果在 WHERE a > 1 ORDER BY b 上翻车。规则很明确：索引中一旦有一列用于范围扫描，它之后的所有列都无法再用于消除排序，因为范围扫描每个 a 值内部的 b 序列是独立的，a 变了 b 又从最小值开始，全局看 b 不单调。
-- 案例 1：范围列本身参与排序，仍然有序
EXPLAIN SELECT * FROM t_order WHERE a > 1 ORDER BY a;
-- Extra: Using index condition （无 filesort）
-- 案例 2：范围之后的列参与排序，失效
EXPLAIN SELECT * FROM t_order WHERE a > 1 ORDER BY b;
-- Extra: Using index condition; Using filesort
-- 案例 3：范围列与排序列都是 a，但方向相反
EXPLAIN SELECT * FROM t_order WHERE a > 1 ORDER BY a DESC;
-- Extra: Using index condition （逆序扫描整段，仍有序）
-- 案例 4：范围 + 前缀等值也不行
EXPLAIN SELECT * FROM t_order WHERE a >= 1 AND b = 2 ORDER BY c;
-- Extra: Using index condition; Using filesort
-- 原因：a 是范围，b 的有序性在多个 a 值之间被打断
案例 4 值得单独说明。有人会想「a 是范围，但 b=2 是等值，c 在 b=2 的行里是不是有序的」。不是，因为 a >= 1 会匹配多个 a 值，索引中 (a=1,b=2,c) 到 (a=2,b=2,c) 之间隔着大量 b≠2 的条目，c 在这些「段」之间是重新开始的，不能视为全局递增。除非把条件改成 WHERE a = 1 AND b = 2 ORDER BY c（a 变成等值），才能消除 filesort。
如果业务上确实必须 WHERE a > 1 ORDER BY b，可行的优化手段是：把排序列换成能形成前缀的形式（改成 ORDER BY a, b 后与索引顺序相容，虽然语义变了），或者为这个查询单独建索引 (b, a) 并改写条件走覆盖查询，或者在应用层做分页裁剪（例如用 ORDER BY a LIMIT 先取一段再排序）。这些都不属于「让 MySQL 消除 filesort」的范畴，而是业务层面的取舍。
四、读懂执行计划与优化器追踪，确认文件排序的真实来源
光看 Extra 有时不够。一个常见误区是把 Using temporary 导致的排序算到联合索引头上。看下面这个查询：
EXPLAIN SELECT b, COUNT(*)
FROM t_order
WHERE a = 1
GROUP BY b
ORDER BY b;
这里 GROUP BY b 需要临时表（因为分组键 b 不是 a=1 前缀之后的自然连续段吗？实际上 a=1 固定后，b 是连续的，MySQL 8.0 可以直接利用 idx_abc 完成分组，Extra 通常是 Using index 而无 Using temporary）。真正会引入 temporary 的是 GROUP BY c 却 ORDER BY b 这类分组键与排序列不一致的情形。判断顺序是：先看 Using temporary，再看 Using filesort，因为临时表生成的数据本身无序，随后的排序往往源于临时表而不是索引。
要精确知道 filesort 是「在索引上没排好」还是「临时表导致的」，用 8.0 的优化器追踪：
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;
SELECT a, b, c FROM t_order WHERE b = 1 ORDER BY a;
SELECT JSON_EXTRACT(trace, '$.steps[*].join_execution.steps[*].filesort_information')
FROM information_schema.optimizer_trace;
SET optimizer_trace = 'enabled=off';
输出中 filesort_information 数组会列出排序键、是否升序、是否使用 addon 字段（回表补列）。如果排序键是 a 且没有可用的索引序，就说明 filesort 是因为索引序不匹配；如果这里的排序键与你预期不同（例如排序键变成了临时表里的列名 <temporary>.b），则说明问题出在上游的临时表。
另一个实用信号是 EXPLAIN FORMAT=JSON 中的 filesort 节点，它会给出 sort_algorithm（quicksort 或 merge）和 sort_cost。当 sort_algorithm 为 merge 时说明数据量已超过 sort_buffer_size，此时即使逻辑上排序能省，也值得认真处理，否则会看到大量临时磁盘 IO。
EXPLAIN FORMAT=JSON
SELECT a, b, c FROM t_order WHERE b = 1 ORDER BY a\G
输出片段（真实结构，字段值随数据分布变化）：
{
"query_block": {
"select_id": 1,
"cost_info": { "query_cost": "20134.55" },
"ordering_operation": {
"using_filesort": true,
"cost_info": {
"sort_cost": "20000.00"
},
"table": {
"table_name": "t_order",
"access_type": "index",
"key": "idx_abc",
"rows_examined_per_scan": 20000,
"rows_produced_per_join": 20000,
"filtered": "10.00",
"using_index": true
}
}
}
}
注意 ordering_operation.using_filesort = true 是判定排序失效的权威标志，比 Extra 字符串更可靠。同时 sort_cost 等于预估排序行数乘以系数，能直观看出这个查询的性能天花板被排序条数锁死。
五、列顺序冲突的三种典型误判与修正
面试中最容易出错的是把「列顺序冲突」笼统理解为「只要顺序不一致就用不上排序」。实际上冲突分三档，处理方式完全不同。
误判一：WHERE 列在 ORDER BY 列右侧，就判定失效
例如 WHERE a = 1 AND c = 3 ORDER BY b，索引 (a, b, c)。直觉上 c 在 b 右侧，是不是跳过 b 了？答案是仍然有序。因为 a = 1 AND c = 3 中，a 是等值前缀，索引在 a=1 这个区间内按 (b, c) 有序；过滤 c=3 不改变 b 的相对顺序，只是从每一段 b 值中挑出部分行，输出仍按 b 递增。所以 Extra 不含 Using filesort。
真正的约束是：ORDER BY 中出现的列必须相对于「等值前缀 + 索引顺序」连续，而不是相对于 WHERE 中出现的最大列位置。c 作为过滤条件「越位」使用不受限制，前提是它不能是范围条件。把上面那句改成 WHERE a = 1 AND c > 3 ORDER BY b，仍然有序，因为 c 的范围过滤同样不改变 b 的次序；只有 b 自己变成范围时才会出问题。
误判二：ORDER BY 缺列但方向一致，仍可消除排序
WHERE a = 1 ORDER BY b 中只排 b，不排 c，是否有序？有序。因为按 (b, c) 排列的行，对 b 单独看仍是非递减的（c 只是在相同 b 内部细分）。所以只要 ORDER BY 是索引剩余前缀的前缀（注意这里的前缀指列表前缀），排序就能省。真正不行的是跳过中间列：ORDER BY c（跳过了 b）在 WHERE a = 1 下会失效，因为 b 变化时 c 重新从最小值开始，c 的全局序列不单调。
-- 有序：b 是 (b, c) 的前缀
EXPLAIN SELECT * FROM t_order WHERE a = 1 ORDER BY b;
-- Extra: 无 Using filesort
-- 失效：跳过 b，直接排 c
EXPLAIN SELECT * FROM t_order WHERE a = 1 ORDER BY c;
-- Extra: Using filesort
-- 修正：补上 b 或改成 WHERE a=1 AND b=2 ORDER BY c
EXPLAIN SELECT * FROM t_order WHERE a = 1 AND b = 2 ORDER BY c;
-- Extra: 无 Using filesort
误判三：ORDER BY 引入非索引列，误以为是顺序问题
WHERE a = 1 ORDER BY b, d，d 不在索引中。这时无论顺序多么吻合，都必须 filesort，因为 d 的值不在索引序里，无法通过索引扫描直接产出正确顺序。区别只是 filesort 的规模：如果 ORDER BY b 可省而 d 只是次要键，MySQL 仍需完整排序。修正方式有二：把 d 加入索引形成 (a, b, c, d)（代价是索引膨胀），或在业务上把 d 的排序下移到应用层（只对当前页做二次排序）。
同时注意 ORDER BY b, d 中 b 已经能通过索引定位，但一旦 d 参与，MySQL 不会做「索引内排序 + 部分排序」的混合策略，它就是整体 filesort。这一点在 EXPLAIN FORMAT=JSON 里体现为 filesort_information 数组包含两个元素。
六、可操作的排查流程与常见报错
把上面的规则固化成一套按顺序执行的排查清单，遇到「排序慢」时逐条过：
- 用
EXPLAIN FORMAT=JSON确认ordering_operation.using_filesort是否为 true。为 false 说明排序已由索引承担，问题不在排序。 - 若为 true，先检查
table.access_type。为ALL说明没走索引，先解决索引可用性问题（最左前缀、隐式类型转换）。 - 为
index/range/ref时，列出 WHERE 中的等值列，与索引从左到右比对，找出最长等值前缀长度 k。 - 检查 ORDER BY 列表是否等于索引第 k+1 列开始的某个连续前缀；列顺序必须一致，方向必须全部相同或全部相反。
- 检查 WHERE 中是否存在范围条件消耗了第 k+1 列（它之后的列都失去排序能力）。
- 检查 ORDER BY 是否含索引外列；若含，评估扩展索引或应用层排序。
排查过程中几个高频报错与现象：
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COLLATE ...) for operation '='：联合索引中的字符列参与排序比较但排序规则不一致，会导致索引无法用于排序。用SHOW FULL COLUMNS FROM t_order确认列的 collation，必要时在 SQL 中显式COLLATE或在建表时统一字符集。Using where; Using filesort且rows很大：通常是 WHERE 条件上发生隐式类型转换，例如a是 INT 却写成WHERE a = '1'。字符串字面量虽然能转换，但索引的有序性判断可能受影响，尤其当列为字符类型、条件为数字时（WHERE d = 123）会直接废弃索引。执行SHOW WARNINGS;可看到Note 1003中 MySQL 重写后的语句。ERROR 1036 (HY000): Table 't_order' is read only或锁等待，与排序无关但在压测时常见，先排除锁再分析排序。- 排序结果与
ORDER BY预期不符（分页出现重复或遗漏）：说明排序列不唯一，MySQL 在 filesort 与索引序下对相等键的返回顺序并不稳定。分页场景一律在 ORDER BY 末尾补主键，如ORDER BY b, c, id，但这会引入 id 这个非索引列而触发 filesort，实务上通常改用ORDER BY b, c加WHERE (b, c) > (?, ?)的游标分页。
最后一个常见追问：ORDER BY b, c 和 ORDER BY c, b 在 WHERE a = 1 下有区别吗？有。b, c 与索引剩余段一致，无 filesort；c, b 顺序颠倒，必须 filesort。这是「列顺序冲突」最纯粹的形式——WHERE 条件完全相同，仅 ORDER BY 内部换位就决定了排序路径，面试中常被用来区分是否真正理解索引的物理有序性。
小结
联合索引能否消除 ORDER BY 的排序，不取决于 WHERE 与 ORDER BY 谁先谁后，而取决于索引扫描的物理顺序是否满足输出顺序。判定口诀：等值条件吃掉最左连续前缀，范围条件截断后续有序性，ORDER BY 必须等于剩余段的一个连续前缀，且方向统一、不含索引外列。验证手段以 EXPLAIN FORMAT=JSON 中的 ordering_operation.using_filesort 为准，配合 filesort_information 区分「索引序不匹配」和「临时表引入的排序」。
FAQ
Q1：WHERE a = 1 ORDER BY b DESC, c DESC 真的没有 filesort 吗？
在 MySQL 8.0 上，索引可以整体反向扫描，b DESC, c DESC 与索引 (a, b, c) 的正向顺序一致（只是整体逆序），所以不会 filesort。但在 5.7 上行为较弱，某些版本或优化器代价模型下可能仍选择 filesort，需要用 EXPLAIN 实测。另一个坑是 5.7 的 DESC 索引是语法接受但不真正生效的，建了也没用。
Q2：为什么 WHERE b = 1 ORDER BY a 明明索引里有 a 也有 b，还是要排序？
因为 b = 1 不是最左列，索引无法按 b 定位区间。优化器只能全索引扫描，扫描序是 (a, b, c) 字典序，而 b = 1 的过滤会让输出在 a 维度上出现跳跃，不满足 ORDER BY a 的单调性。要消除排序，需要调整索引为 (b, a)，或把条件改造成 a 在最左的形式。
Q3：ORDER BY b, a 在 WHERE a = 1 下会失效吗？
会。索引剩余段是 (b, c)，ORDER BY b, a 中的 a 虽然已经是等值列，但把它列入 ORDER BY 且位置在 b 之后，就形成了 (b, a) 这个索引中不存在的组合顺序（索引里 b 之后是 c 不是 a）。虽然 a 是常量理论上不影响排序语义，但 MySQL 8.0.20 之前确实会因此 filesort。这条在实践中常被忽略，等值列不要重复写进 ORDER BY，或者统一放到最前面。
Q4：加了 LIMIT 能缓解 filesort 吗？
对「带 LIMIT 且不需要回表大量行」的排序，MySQL 会使用优先队列排序（priority queue），只在内存中维护 LIMIT 条候选，因此能显著降低内存占用，但 Using filesort 标记仍然存在。也就是说 LIMIT 优化的是排序代价，不改变排序路径的判断结果，面试回答时不要把两者混为一谈。
Q5：覆盖索引能消除 filesort 吗？
不能直接消除。覆盖索引只减少回表，排序是否存在仍取决于索引序是否满足 ORDER BY。两者正交：可以「覆盖索引 + filesort」，也可以「非覆盖索引 + 无 filesort」（走索引序但需要回表）。不过当查询是覆盖索引时，优化器更倾向于选择 index 全扫，从而更容易暴露排序失效的问题，这也是为什么 WHERE b = 1 ORDER BY a 在你的机器上看到的 Extra 往往是 Using index; Using filesort。
Q6：有没有办法让 MySQL 强制走某个索引来做排序？
FORCE INDEX (idx_abc) 能影响访问路径选择，但它不能「创造」索引序。如果 ORDER BY 与索引序不匹配，强制使用该索引后仍会 filesort，而且可能更慢。真正的解法是让索引与查询形态对齐，或用 ORDER BY 重写使列顺序符合索引定义。可以用 EXPLAIN 对比加与不加 FORCE INDEX 的 sort_cost 来验证。