MySQL大复合主键场景下游标分页加入第二主键触发filesort问题

问题根因 #

你的现有二级索引ts_idx仅包含ts字段，InnoDB二级索引的叶子节点会自动附带完整的主键值（也就是primary_1、primary_2），同一ts值对应的行本身就按照primary_1→primary_2的顺序排列。
当你的ORDER BY只有ts, primary_1时，优化器可以识别到索引返回的行天然符合排序要求，直接扫描索引取前5条即可，不需要额外排序。当你加入primary_2到排序字段后，MySQL优化器没有识别到索引返回结果已经满足完整排序逻辑，误判需要全表扫描后做全量排序，所以触发了Using filesort。

解决方法 #

方案1：调整索引（推荐） #

将现有ts_idx替换为复合索引，直接匹配你的排序顺序：

ALTER TABLE big_table DROP INDEX ts_idx, ADD INDEX ts_pk_idx (ts, primary_1, primary_2);

调整后索引的字段顺序和ORDER BY ts, primary_1, primary_2完全匹配，优化器会直接走索引扫描取前N条，不需要排序，性能和之前单字段索引的流水线执行一致。

方案2：强制走现有索引 #

如果不方便修改索引，也可以强制查询走ts_idx，因为索引返回的行天然满足排序要求：

select * from big_table FORCE INDEX(ts_idx) order by ts, primary_1, primary_2 limit 5;

该方案不需要修改表结构，但要注意测试你当前使用的MySQL版本的兼容性，确认二级索引附带的主键排序逻辑符合预期。

游标分页的写法注意 #

后续翻页的查询条件要配合排序规则写三元组判定，即可实现完全的索引范围扫描，不需要全表排序：

select * from big_table 
where (ts > ?) 
   OR (ts = ? AND primary_1 > ?) 
   OR (ts = ? AND primary_1 = ? AND primary_2 > ?)
order by ts, primary_1, primary_2 limit 5;

把上一页最后一条记录的ts、primary_1、primary_2值代入对应占位符即可。

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