MongoDB大型聚合查询性能优化方案咨询

• 现有查询属于典型的N+1关联查询问题：$match匹配到1000个实体时，后续2个$lookup会对每个实体单独发起一次statistics集合的子查询，累计执行2000次子查询，哪怕单次子查询走索引，累计IO和调度开销也会把总耗时拉到秒级。
• 索引结构不合理：statistics集合当前只有entityId、date两个单字段索引，而子查询是同时按entityId等值匹配+date范围过滤，单字段索引每次只能命中一个，剩余条件需要在内存中过滤，扫描效率低。
• 管道写法存在冗余和错误：第二个$lookup的let块定义的是无用变量key: '$_key'，子查询实际引用的$$entityId未被定义，会触发隐式的全集合扫描；此外$match阶段不必要套了$expr表达式，会阻碍MongoDB查询优化器命中索引；代码中写的2022-06-31是无效日期（6月仅30天），也可能带来额外的异常扫描开销。
• 多次独立查询重复计算：4条同类查询每次都要重复执行实体筛选、关联的逻辑，重复开销大。

索引层优化 #

• 给statistics集合创建复合索引{ entityId: 1, date: 1 }：等值匹配字段放前面、范围匹配字段放后面，完全覆盖子查询的过滤条件，子查询的扫描范围可以从全集合降到单实体对应时间窗的几条文档，是投入产出比最高的优化手段。
• 如果entities集合的筛选条件固定同时用到filterField1、filterField2，创建对应顺序的复合索引，把$match阶段的实体筛选耗时压到毫秒级。

查询逻辑重写 #

• 彻底放弃现有先查实体、再逐行lookup的N+1写法，改成两步走的聚合逻辑，把N次子查询合并成1次集合扫描：
  1. 先直接用普通查询（不要套$expr）从entities集合拿到所有符合筛选条件的实体ID列表，1000个ID的结果集极小，内存开销可以忽略。
  2. 直接在statistics集合做聚合，用$in匹配所有实体ID、同时过滤目标时间范围，一次扫描直接算出需要的平均值、求和、计数等指标。
     参考代码如下：

// 第一步：拉取匹配的实体ID
const matchedEntityIds = db.entities.distinct("_id", {
  filterField1: "筛选值1", // 直接写键值对条件，不要用$expr，保证命中索引
  filterField2: "筛选值2"
})
// 第二步：一次聚合拿到所有时间点的指标
db.statistics.aggregate([
  {
    $match: {
      entityId: { $in: matchedEntityIds },
      // 按日存储的场景直接用$in匹配目标日期即可，不需要$gte/$lte范围扫描
      date: { $in: [
        ISODate("2022-06-01T00:00:00Z"),
        ISODate("2022-06-30T00:00:00Z")
      ] }
    }
  },
  {
    $group: {
      _id: "$date",
      avg_value: { $avg: "$statisticsValue" },
      sum_value: { $sum: "$statisticsValue" },
      total_count: { $count: {} }
    }
  }
])

改写后单条查询的子查询次数从2000次降到1次，性能会有数量级提升。

• 4条同类查询如果是基于同一批实体、不同时间范围/指标的统计，用$facet把多个聚合逻辑合并到一次请求中，减少网络往返和重复的实体筛选开销，注意不要在$facet的多个子管道里重复写相同的过滤条件。
• 修正现有代码里的无效日期、未定义变量的bug，非必要不要在$match里用$expr表达式，避免阻碍索引命中。

存储层优化 #

• 可以把statistics集合迁移为MongoDB时间序列集合，指定entityId为元字段、date为时间字段，MongoDB会自动按时间块列式存储数据，针对这类时间范围聚合查询的性能比普通集合高30%~70%，同时存储占用更低，且查询语法和普通集合完全兼容，迁移成本极低。
• 后续统计集合数据量增长到千万级以上时，可以按date字段做范围分片，分片键选择{ date: 1, entityId: 1 }，避免单集合数据量过大带来的性能衰减。

• 你当前的数据规模（80万条统计文档、单次匹配实体最多1000个）属于MongoDB完全适配的实时聚合场景，做完上述优化后，单条查询耗时通常可以降到50~100ms，4条合并后的总耗时可以控制在200ms以内，完全满足可视化仪表盘的响应要求，不需要更换其他数据源。
• 只有当后续数据量增长到亿级以上、需要支持多维度任意组合的下钻、切片OLAP分析时，再考虑对接专用的分析型引擎即可，当前阶段没有必要。

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