MySQL8关联查询已按索引排序仍触发filesort问题排查

场景重现 #

表结构 #

CREATE TABLE `table1` (
  `fieldToFilterBy` int NOT NULL,
  `someKindOfInt` int NOT NULL,
  `fieldToJoinOn` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`fieldToFilterBy`,`someKindOfInt`),
  UNIQUE KEY `intexOfTable1` (`fieldToFilterBy`,`fieldToJoinOn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `table2` (
  `fieldToJoinOn` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fieldToSortBy` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`fieldToSortBy`),
  UNIQUE KEY `indexOfTable2` (`fieldToJoinOn`,`fieldToSortBy`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

查询语句 #

SELECT t1.fieldToFilterBy, t1.someKindOfInt, t1.fieldToJoinOn, t2.fieldToSortBy
FROM table1 t1
INNER JOIN table2 t2
ON t1.fieldToJoinOn = t2.fieldToJoinOn
WHERE t1.fieldToFilterBy = 123
ORDER BY <....>
LIMIT 100;

执行情况 #

• 当ORDER BY t1.fieldToFilterBy：命中索引，无filesort
• 当ORDER BY t2.fieldToFilterBy：执行计划显示filesort，但结果实际有序
• 当ORDER BY t1.fieldToFilterBy, t2.fieldToSortBy（核心问题）：执行计划仍带filesort，但实际结果已排序（依赖嵌套循环连接和indexOfTable2的有序性，测试验证结果稳定）

核心问题 #

担心filesort的开销：MySQL的filesort基于快速排序或归并排序，部分场景下即使数据已有序也会产生额外开销，且可能创建临时表。由于用于分页，不能依赖无ORDER BY的隐含有序行为，希望找到让MySQL跳过排序的方法，已尝试强制索引和子查询无效。

解决方案 #

1. 先明确：MySQL为什么不跳过filesort #

MySQL优化器不会根据实际数据分布推断结果是否有序，它只认索引提供的确定性排序规则。对于ORDER BY t1.fieldToFilterBy, t2.fieldToSortBy，即使fieldToFilterBy是固定值123，优化器也不会自动简化排序逻辑，更无法确认连接后的整体结果是否严格匹配排序要求，因此会触发filesort来保证结果的确定性。

2. 针对性优化方案 #

方案一：简化ORDER BY条件（最直接）

因为WHERE t1.fieldToFilterBy=123，所有返回的t1.fieldToFilterBy值都是123，此时ORDER BY t1.fieldToFilterBy, t2.fieldToSortBy完全等价于ORDER BY t2.fieldToSortBy。修改后的查询：

SELECT t1.fieldToFilterBy, t1.someKindOfInt, t1.fieldToJoinOn, t2.fieldToSortBy
FROM table1 t1
INNER JOIN table2 t2
ON t1.fieldToJoinOn = t2.fieldToJoinOn
WHERE t1.fieldToFilterBy = 123
ORDER BY t2.fieldToSortBy
LIMIT 100;

此时MySQL可以利用table2的indexOfTable2（fieldToJoinOn, fieldToSortBy）索引：对于每个t1.fieldToJoinOn，对应的table2行已经按fieldToSortBy有序，而所有t1行的fieldToFilterBy值一致，整体结果自然符合排序要求，优化器会跳过filesort。

方案二：重构子查询+强制索引

如果必须保留原ORDER BY写法，尝试用子查询提前锁定table1的有序结果，同时强制table2使用排序索引：

SELECT t1.fieldToFilterBy, t1.someKindOfInt, t1.fieldToJoinOn, t2.fieldToSortBy
FROM (
    SELECT fieldToFilterBy, someKindOfInt, fieldToJoinOn
    FROM table1
    WHERE fieldToFilterBy = 123
    ORDER BY fieldToFilterBy, fieldToJoinOn
) t1
INNER JOIN table2 t2 FORCE INDEX (indexOfTable2)
ON t1.fieldToJoinOn = t2.fieldToJoinOn
ORDER BY t1.fieldToFilterBy, t2.fieldToSortBy
LIMIT 100;

子查询利用table1的唯一索引返回有序数据，table2通过indexOfTable2返回按fieldToSortBy有序的行，结合fieldToFilterBy固定值的特性，整体结果已符合排序要求，优化器会跳过filesort。

方案三：优化索引为覆盖索引，降低filesort开销

如果以上方案都无法消除filesort，至少可以减少其开销：将table1的唯一索引扩展为覆盖索引，包含查询所需的所有字段：

ALTER TABLE table1 DROP INDEX intexOfTable1;
ALTER TABLE table1 ADD UNIQUE KEY intexOfTable1 (`fieldToFilterBy`,`fieldToJoinOn`, `someKindOfInt`);

这样MySQL可以直接从索引中获取所有数据，无需回表查询，filesort可以在内存中完成（确保sort_buffer_size足够），大幅降低性能损耗。

3. 重要提醒 #

永远不要依赖无ORDER BY时的隐含有序行为，MySQL不保证这种行为的稳定性——数据变更、版本升级、执行计划调整都可能导致结果顺序变化，必须显式指定ORDER BY来保证分页逻辑的正确性。

内容的提问来源于stack exchange，提问作者Oleh Kubai