指定查询模式下MongoDB集合应如何创建合适索引

直接在目标集合上创建两个轻量复合索引即可，创建命令如下：

db.targetColl.createIndex({a: 1, i: 1})
db.targetColl.createIndex({a: 1, j: 1})

• 优先排除无收益的冗余索引字段
  • 字段a1的取值和a完全一致，查询语句中携带的a1过滤条件属于完全冗余逻辑，和仅用a做过滤的返回结果100%重合。把a1加入索引只会增加索引存储占用、降低写入效率，不会带来任何查询性能提升，完全不需要加入索引。
  • 字段s是全局固定静态常量，全集合所有文档的s取值完全相同，这个字段没有任何过滤区分度。把s加入索引不仅会产生额外存储开销，甚至会徒增索引树层级降低查询效率，属于纯负收益的索引字段，直接排除。
• 完全匹配MongoDB索引规则，查询性能最优
  MongoDB复合索引采用最左前缀匹配规则，上述两个索引刚好对应两类业务查询：
  • 针对find(a=a, a1=a, s=s, i=i)的查询，{a:1, i:1}索引可以直接通过前缀匹配快速定位到所有符合a、i条件的文档，后续内存校验a1、s字段时，由于字段本身的取值特性，校验通过率为100%，几乎没有额外过滤开销。
  • 针对find(a=a, a1=a, s=s, j=j)的查询，{a:1, j:1}索引同理可以直接定位到符合a、j条件的文档，查询效率拉满。
• 其他常见方案的缺陷
  • 仅建{a:1}单字段索引：只能定位到a匹配的文档，后续i、j的条件需要全量扫描该a值下的所有文档做过滤，当单个a值关联的文档量级较大时，查询性能会出现明显下降。
  • 建{a:1, i:1, j:1}三字段复合索引：查询j的场景缺少i的过滤条件，无法命中j字段的索引规则，只能用到a的前缀匹配，和单字段a索引的查询效果基本一致，浪费j字段的索引存储空间。
  • 将a1、s加入索引字段：如前所述，这类零区分度的冗余字段加入索引只有成本没有任何实际收益。

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