DocumentDB默认选择错误索引致查询性能低下的问题排查

我使用MongoDB 4.0.0搭配DocumentDB，transactions集合包含以下两个索引：

第一个索引：

{
  "creation_date": -1
}

第二个联合索引：

{
  "project_id": 1,
  "mid.id": 1  
}

执行的查询语句：

db.transactions.explain('executionStats').find({
  "project_id": "1",
  "mid.id": {
    $in: [/* 包含47个ID的数组 */]
  }
}).sort({creation_date: -1}).skip(0).limit(50)

现象 #

• 执行统计显示，DocumentDB默认选择creation_date索引，查询耗时长达445秒；
• 手动通过hint指定使用project_id和mid.id的联合索引时，查询仅耗时10毫秒。

已尝试操作 #

• 使用MongoDB Compass连接DocumentDB执行该查询，无需指定索引即可正常运行；
• 将mid.id的$in数组缩小至14个元素时，查询也可正常运行。

请问为何DocumentDB会默认选择错误的索引？我的查询是否存在问题？

索引选择错误的原因 #

DocumentDB的查询优化器基于统计信息和成本估算选择索引，出现该问题的核心原因如下：

1. 统计信息不准确/过时：优化器未正确计算project_id + mid.id联合索引的过滤效率，当$in数组元素较多时，错误判断了两种索引的扫描成本。
2. limit的误导：查询使用了limit(50)，优化器可能认为通过creation_date索引可以快速返回排序好的前50条数据，忽略了需要先过滤大量不符合project_id和mid.id条件的文档——当集合数据量较大时，这种「先排序后过滤」的方式会扫描巨量无关数据，导致耗时暴增。
3. $in数组长度的影响：当$in仅包含14个元素时，优化器重新估算成本后，认为联合索引的过滤代价更低；而元素数量增加到47个时，错误判断为排序索引的成本更优。
4. Compass的差异：MongoDB Compass执行查询时，内部可能做了额外优化，或使用了更准确的统计信息，因此自动选择了正确的索引。

查询本身的优化建议 #

你的查询逻辑无问题，但可以调整索引让优化器更容易选对方向：

• 创建覆盖索引，将creation_date加入联合索引末尾，这样既满足过滤条件，又能直接利用索引完成排序，避免额外的排序操作：

  {
    "project_id": 1,
    "mid.id": 1,
    "creation_date": -1
  }
  

  该索引同时支持查询过滤与排序，优化器会更倾向于选择它，性能也会优于单独使用联合索引后再排序的方式。

临时解决方案 #

如果暂时不想创建新索引，可采用以下方式：

• 强制使用hint指定联合索引，这是最直接有效的方式；
• 更新集合统计信息：执行db.transactions.runCommand({collStats: 1})触发统计更新，让优化器获得更准确的数据分布信息（不同版本DocumentDB的操作可能有差异）。

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