MySQL大结果集查询时索引未命中走filesort问题优化咨询

这个表现完全是MySQL InnoDB优化器的正常工作逻辑，不属于索引异常或者bug。
优化器选择执行路径的核心规则是对比不同方案的预估成本（包含IO开销、CPU计算开销），哪个成本低就选哪个，不存在“必须命中索引才是对的”的规则。
结合你的场景具体拆解：

1. 当时间条件匹配的结果量极小时（explain预估仅1行命中），走INDEX_by_updated_at二级索引的成本极低：只需要在索引树定位到对应时间点，回表查1行数据就能拿到结果，成本远低于全表扫描，因此优化器会选择走索引。
2. 当你调大时间阈值后，预估符合条件的行数占全表37%左右（全表共263行，约100行命中），这时候走现有二级索引的成本会快速升高：你的二级索引是单列updated_at，InnoDB二级索引叶子节点仅存储主键值（也就是owner_id、owner_platform两个字段），缺少查询需要返回的owner_address字段，每拿到一条符合条件的索引记录，都要回到主键索引查整行拿owner_address，属于随机IO，开销很高。反过来，全表扫描是顺序读取整表数据，263行的表总共才占1-2个16KB的数据页，顺序读的开销几乎可以忽略，读完在内存里做filesort排序100多条数据、取前200条的CPU开销极低，优化器算下来全表扫更划算，自然就放弃了二级索引。

额外提一句：你现在表的数据量极小，explain里显示的ALL全表扫、Using filesort不会带来任何可感知的性能损耗，不用看到这两个标识就觉得有问题，这俩只有在百万级以上大表场景下才会造成明显的性能瓶颈。

根据后续表的数据量增长情况，可以选不同的处理方式：

• 长期方案（适配未来百万级数据量场景）：替换为覆盖联合索引
  现有单列索引需要回表是核心问题，可以直接把现有索引替换成覆盖所有查询字段的联合索引，彻底消除回表成本：

  ALTER TABLE some_owner_table 
  DROP KEY INDEX_by_updated_at, 
  ADD KEY INDEX_by_updated_at_cover (updated_at DESC, owner_id, owner_address, owner_platform);
  

  这个索引有两个核心优势：
  • 索引本身按updated_at倒序存储，完全匹配你ORDER BY updated_at DESC的排序规则，执行时不需要做额外的filesort
  • 索引上已经存储了你查询需要返回的所有字段，命中索引后不需要回主键查任何数据，哪怕符合条件的行数占比高，走索引的成本也会远低于全表扫描，优化器会稳定选择这个索引。
• 短期方案（当前263行小表场景）：无需任何调整
  这个数据量级下全表扫描的耗时通常在0.1毫秒级别，刻意加hint强制走索引反而会因为随机回表IO拖慢查询，完全没必要做优化，等后续表数据量涨到10万行以上再评估即可。
• 兜底方案（大表场景暂不能改索引时）：用索引hint强制走索引
  如果后续表数据量涨上去，又暂时没有窗口调整索引结构，可以在查询中加FORCE INDEX强制优化器走时间索引：

  SELECT 
      r.owner_id, 
      r.owner_address, 
      r.owner_platform,
      r.updated_at 
  FROM some_owner_table as r FORCE INDEX(INDEX_by_updated_at)
  WHERE 
      r.updated_at > '2022-09-16 22:16:38.832' 
  ORDER BY 
      r.updated_at DESC LIMIT 200;
  

  注意这个方案不要在小表场景用，否则会劣化查询性能。

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