不同版本与环境下MongoDB同索引查询执行计划差异引发慢查问题

1 原索引（appIds:1, ctime:-1）生产环境失效原因 #

• MongoDB 3.2与4.0对$in+排序的查询优化策略存在差异：3.2版本对$in多个取值的场景，默认会使用索引有序归并逻辑：每个appId对应的ctime在索引中是天然倒序排列的，数据库只需要归并多个appId的有序ctime队列，取前limit条即可，不需要全量排序，所以totalDocsExamined等于limit值。
• 4.0版本中优化器的成本计算逻辑调整：当$in数组中的元素数量超过阈值（默认是20个左右），或者符合appIds匹配条件的文档总量过大时，优化器会误判归并排序的成本高于全量匹配后再排序，从而放弃索引归并逻辑，走全量匹配+内存排序的路径，导致扫描量暴增。

2 调整为ctime:-1, appIds:1后仍慢的原因 #

该索引的排序规则是先按ctime倒序排列，再按appId排列，查询时数据库需要从最新的ctime开始逐条遍历索引，判断每条索引的appId是否在$in数组中，直到凑够limit条匹配数据。如果你查询的appId对应的新数据占比极低，就会出现扫描上百万条索引才能凑够返回条数的情况，和你遇到的现象完全吻合。

方案1：强制启用原索引的归并排序逻辑 #

如果业务中$in的元素数量不会特别大（比如不超过50个），可以直接强制查询走原appIds_1_ctime_-1_background_1索引，跳过优化器的错误成本判断：

// 使用hint强制指定索引
db.newstoapp.find({"appIds":{"$in":[xxx]}}).sort({"ctime":-1}).limit(10).hint("appIds_1_ctime_-1_background_1")

同时可以调整MongoDB优化器阈值，让4.0版本优先选择归并排序逻辑：

// 临时调整，重启后失效，可写入配置文件永久生效
db.adminCommand({setParameter:1, internalQueryExecMaxBlockingSortBytes: 33554432}) // 调整排序内存阈值，避免触发磁盘排序
// 调整$in归并排序的元素数量阈值
db.adminCommand({setParameter:1, internalQueryPlannerMaxIndexedSortPaths: 100}) // 默认为20，调整到和业务$in最大元素数匹配即可

方案2：优化索引适配大数量$in场景 #

如果业务中$in的元素数量经常超过100个，归并排序成本确实较高，可以用以下方式优化：

• 如果查询不需要包含background字段过滤，删除索引中的background字段，避免索引体积过大影响查询效率
• 对高频查询的appId分组做查询路由，大的$in数组拆分为多个小$in分别查询后自行归并结果，避免触发优化器的错误选择
• 如果业务允许时间范围过滤，在查询中加入ctime的时间范围条件，比如限定查询最近7天的数据，大幅减少扫描范围

方案3：升级数据库版本 #

MongoDB 4.2及以上版本对$in+排序的索引选择逻辑做了修复，大部分场景下可以自动识别最优的归并排序路径，不需要人工强制指定索引，有条件的话可以把生产环境升级到4.2以上的稳定版本。

内容的提问来源于stack exchange，提问作者fallen.lu