复合索引构建问题：如何针对指定查询场景设计索引以提升查询性能

你初步设计的date1:1, reason:1, status: 1索引不合理，完全无法匹配你的查询场景，性能会很差，问题如下：

• 缺少必选等值过滤字段indicator，会扫描大量不满足indicator=true的无效文档，浪费IO资源
• 索引用范围字段date1做前缀，且未包含排序字段，无法利用索引的有序性，查询时需要把所有符合过滤条件的文档加载到内存排序，数据量大时不仅性能低，还可能触发MongoDB 32MB内存排序上限报错
• 无法实现覆盖索引扫描，需要回表读取原始文档校验过滤条件，额外增加开销

推荐索引设计 #

按照MongoDB复合索引「等值过滤前置、排序字段居中匹配顺序、范围过滤后置」的原则，结合你只需要返回第一条匹配结果的需求，最优索引如下：

db.collection.createIndex({
  indicator: 1,
  status: 1,
  date2: 1,
  date3: 1,
  reason: 1,
  date1: 1
})

设计逻辑说明 #

• 前两个字段indicator:1、status:1是所有查询都要用到的等值过滤条件，放在索引最前缀可以快速排除所有不符合这两个条件的文档，缩小索引扫描范围
• 中间的date2:1、date3:1完全匹配你的排序规则，索引本身就是按该顺序有序存储的，数据库可以按排序顺序逐行校验后续过滤条件，找到第一条符合要求的文档就直接返回，完全不需要做内存排序
• 最后放置reason:1、date1:1两个范围/排除过滤字段，所有查询需要的字段都包含在索引中，整个查询可以走覆盖索引扫描，不需要回表读取原始文档，性能最优

进阶优化方案 #

如果你的所有业务查询的indicator条件都是固定为true，可以进一步改成部分索引，大幅降低索引存储体积，提升扫描效率：

db.collection.createIndex(
  {
    status: 1,
    date2: 1,
    date3: 1,
    reason: 1,
    date1: 1
  },
  {
    partialFilterExpression: {indicator: true}
  }
)

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