如何优化命中索引仍执行缓慢的MongoDB聚合管道查询

问题根因 #

• 命中索引只是避免了全集合扫描，你当前的聚合逻辑仍然存在额外开销：
  • $facet的totalCount分支需要全量遍历所有匹配templateId的50万条索引条目完成计数，即使走索引，遍历50万条数据仍然会产生可观耗时
  • 存在回表开销：你的索引仅包含templateId和_id两个字段，如果查询需要返回文档的完整内容，索引扫描完成后还需要逐个回表拉取完整文档数据，产生大量随机IO
  • $facet的两个分支是独立处理输入的全量匹配数据，相当于对50万条匹配数据做了两次遍历，额外放大了计算开销

优化方案 #

• 方案1：拆分聚合查询，弃用$facet #

  把计数和分页查询拆为两个独立的优化后查询，充分利用索引特性：

  1. 分页查询直接使用find接口，完全复用现有索引的排序能力，仅需返回前100条数据：

  db.collection.find({templateId:ObjectId('blabla')}).sort({_id:1}).skip(0).limit(100)
  

  如果可以明确需要返回的字段，可以把字段添加到现有复合索引中做成覆盖索引，比如需要返回name, createTime，就把索引更新为{templateId:1, _id:1, name:1, createTime:1}，查询时指定projection仅返回这些字段，即可完全避免回表，耗时可以降到毫秒级。
  2. 计数查询使用MongoDB专门优化的计数接口，性能远高于聚合管道内的$count：

  db.collection.countDocuments({templateId:ObjectId('blabla')})
  

• 方案2：缓存总计数 #

  如果符合templateId的总数据量不会高频实时变化，可以把计数值存在本地缓存、Redis或者专门的统计集合中，定时更新，查询时直接读取缓存值，无需每次遍历50万条数据做计数，查询耗时可以直接降到10ms以内。

• 方案3：必须保留$facet的优化逻辑 #

  如果业务逻辑必须使用$facet，可以提前过滤无用字段减少数据传输，同时配置覆盖索引避免回表：

  [
    {'$match':{templateId:ObjectId('blabla')}},
    {'$project':{_id:1, 业务需要的其他字段:1}}, // 提前剔除不需要的字段
    {"$sort" : {"_id" : 1}},
    {
      "$facet" : {
        "paginatedResult" : [{"$skip" : 0},{"$limit" : 100}],
        "totalCount" : [{"$count" : "count"}]
      }
    }
  ]
  

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