MySQL按created_at排序触发filesort及内存不足问题求助

你的第二个查询触发filesort并导致内存不足的核心原因是现有索引的字段顺序无法匹配过滤+排序需求：

• 第一个查询使用的number_revision_autosave索引，前缀为number→revision→autosave，过滤条件number=1+autosave=0配合排序revision desc时，索引本身的有序性可被直接利用（反向扫描），无需额外排序。
• 你新增的number_revision_autosave_created_at索引字段顺序是number→revision→autosave→created_at，但查询未对revision做过滤，导致符合number=1+autosave=0的记录在索引中分散在不同revision分组下，无法直接通过索引获取按created_at排序的结果，MySQL只能取出所有符合条件的记录后在内存中执行filesort，大体积blob字段会迅速耗尽sort_buffer。

创建一个以过滤字段为前缀、排序字段紧跟其后的复合索引，让MySQL可直接通过索引扫描得到有序结果：

-- 创建适配查询需求的复合索引
CREATE INDEX number_autosave_created_at ON test (number, autosave, created_at) USING BTREE;

-- 若原新增索引无其他用途，可删除以节省存储空间
DROP INDEX number_revision_autosave_created_at ON test;

执行目标查询时无需强制指定索引，MySQL会自动选择上述新索引，执行计划的Extra字段会变为Using where; Backward index scan，和第一个查询一致，彻底避免filesort。

InnoDB的复合索引按前缀字段有序排列：

• 新索引(number, autosave, created_at)中，所有number=1且autosave=0的记录会被集中存储，且内部按created_at升序排列。
• 当执行order by created_at desc时，MySQL会反向扫描该索引段，直接得到降序结果，无需将数据加载到sort_buffer中排序，从根源上解决内存不足问题。
• 由于查询是select *，MySQL会通过索引中的主键值回表取出完整数据，但排序阶段完全依赖索引的有序性，不会涉及大体积blob字段的排序操作。

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