Mongo查询未按ESR规则选用正确索引，原因何在？

执行的Mongo查询 #

{
  "command": {
    "getMore": 7229634113631845000,
    "collection": "data",
    "batchSize": 4899
    // 省略其他内容
  },
  "originatingCommand": {
    "find": "data",
    "filter": {
      "accountId": "AAA-367YTGSA",
      "customIterator": {
        "$gte": {
          "$date": "2072-11-05T01:41:58.041Z"
        }
      },
      "startTime": {
        "$lte": {
          "$date": "2022-12-06T17:00:00Z"
        }
      },
      "type": {
        "$in": ["TYPE_A", "TYPE_B"]
      }
    },
    "sort": {
      "accountId": 1,
      "customIterator": 1
    },
    "limit": 5000,
    "maxTimeMS": 300000
    // 省略其他内容
  },
  "planSummary": [
    {
      "IXSCAN": {
        "accountId": 1,
        "customIterator": 1,
        "startTime": 1,
        "type": 1
      }
    }
  ]
}

现有两个索引 #

第一个索引 #

accountId_customIterator_startTime_type
accountId:1 customIterator:1 startTime:1 type:1

第二个索引 #

accountId_type_customIterator_startTime
accountId:1 type:1 customIterator:1 startTime:1

疑问 #

按照ESR（相等匹配→排序→范围匹配）规则，我认为该查询应该使用第二个索引，但planSummary显示实际使用了第一个索引，请问我忽略了什么？

原因分析 #

• 排序优先级高于ESR理想顺序：查询明确指定了sort: {accountId:1, customIterator:1}，第一个索引的前缀恰好完全覆盖这两个排序字段。MongoDB会优先选择能避免内存排序的索引——利用索引的有序性直接返回结果，无需额外排序操作，这对性能的提升远大于严格遵循ESR顺序带来的收益。

• ESR是指导原则而非强制规则：查询优化器会综合评估多个维度：

  • 第一个索引的accountId + customIterator前缀，既满足了accountId的等值匹配，又直接覆盖排序需求，后续的startTime和type字段也能辅助过滤；
  • 第二个索引将type（$in被MongoDB视为类等值匹配）放在排序字段customIterator之前，导致使用该索引时，数据库需要先按accountId+type定位数据，再对customIterator进行排序或跳过不符合排序的条目，额外成本更高。

• 范围过滤的实际效率：customIterator的$gte范围过滤如果能筛选掉大量数据，第一个索引可以直接从符合范围的位置开始扫描，配合limit:5000能快速返回结果，无需遍历更多无关数据。

• 查询优化器缓存影响：如果该查询之前使用第一个索引时性能表现良好，优化器会缓存这个选择，除非数据分布发生显著变化，否则不会自动切换。可以用hint()强制指定第二个索引，对比executionStats中的实际执行耗时、扫描行数等指标，验证是否符合预期。

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