MongoDB 4.4集群修改查询排序方向后执行变慢问题咨询

核心原因分析 #

• $or查询条件破坏了索引有序性复用逻辑
  现有apiGetTasks升序复合索引的前缀是相等匹配字段customer和finished，后续紧跟的$or是跨scoreCalculated、workflowProcessed两个字段的存在性判断，导致符合查询条件的文档在索引中不是连续存储的：
  降序排序时，MongoDB支持直接反向遍历升序索引，遍历过程中同步过滤符合$or条件的文档，返回结果天然满足全降序排序要求，无需额外排序阶段，因此执行耗时极短。
  升序排序时，查询优化器评估认为正向遍历索引需要扫描大量不符合$or条件的条目，执行代价更高，因此放弃了apiGetTasks索引，最终触发内存排序（数据量过大时甚至会溢出到磁盘排序），耗时大幅提升。

• 现有索引设计与查询逻辑不匹配
  新增同字段的降序索引无效果，是因为MongoDB对$or查询的索引优化要求每个分支都能命中独立的适配索引，现有所有索引都没有针对$or的两个分支分别做前缀适配，无论升序还是降序索引，都无法同时满足$or过滤+排序的需求，因此优化器不会选择这类索引。

• 过多索引导致优化器决策偏差
  该集合总共创建了55个索引，过多的候选索引会大幅提升查询优化器的计划评估复杂度，极易出现代价计算错误的情况，放弃更优的索引方案，反而选中需要消耗大量资源做内存排序的执行计划。

可选优化方案 #

• 拆分现有$or查询为两个独立查询，分别命中对应分支的索引后再做结果合并，规避内存排序开销。
• 新建两个复合稀疏索引：{customer:1, finished:1, scoreCalculated:1, workflowProcessed:1, createdAt:1}和{customer:1, finished:1, workflowProcessed:1, scoreCalculated:1, createdAt:1}，稀疏索引会自动过滤对应字段不存在的文档，同时适配$or两个分支的过滤和排序需求。
• 清理集合内的无效、冗余索引，减少优化器的候选计划数量，降低选错执行计划的概率。

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