MongoDB $group、$lookup、$count查询与索引优化求助

集合结构与需求 #

• Customer集合：包含id、state（取值为['active', 'inactive']）字段
• Tag集合：包含cid（关联Customer.id）、date（YYYYMMDD格式整数）、name（标签名）、count（当日该用户被该标签标记的次数）字段

需求：统计指定日期范围内，被标签'tag1'标记至少2次的活跃（active）用户数量

原聚合管道：

[
    { $match : {
        date: { $gte: ..., $lte: ... },
        name: 'tag1'
    } },
    { $group: {
        _id: '$cid',
        totalCount: '$count' // 注：此处语法错误，应使用$sum累计总次数
    } },
    { $match: {
        totalCount: { $gte: 2 }
    } },
    { $lookup: {
        from: 'Customer',
        pipeline: [
            { $match: {
                "$expr": {
                    "$and": [
                        { "$eq": [ "$_id", "$$cid" ] },
                        { "$eq": [ "$state", "active" ] },
                    ]
                }
            } }
        ],
        as: 'Customer'
    } },
    { "$unwind": "$Customer" },
    { "$count": "count" }
]

第一部分：阶段1~3优化方案 #

当前问题 #

使用复合索引{ date: -1, name: 1, cid: 1 }时，$match仅耗时802ms，但$group阶段耗时约11012ms；添加count字段改为覆盖索引{ date: -1, name: 1, cid: 1, count:1 }后，查询变为覆盖索引查询，但$group阶段仍耗时约10794ms。

优化建议 #

1. 修正$group阶段逻辑错误：将totalCount: '$count'改为totalCount: { $sum: '$count' }，这是正确统计用户总标记次数的前提，否则仅会保留分组内最后一条文档的count值，既不符合需求，也可能导致后续过滤逻辑失效。
2. 利用索引有序性优化$group：在$match之后、$group之前添加$sort: { cid: 1 }，由于现有索引已包含cid字段，排序可直接复用索引的有序性，无需额外排序开销。有序的输入能让$group按顺序处理同cid的文档，减少内存中哈希表的构建与维护开销，显著提升$group效率。
   修改后的阶段1-3：

   [
       { $match : {
           date: { $gte: ..., $lte: ... },
           name: 'tag1'
       } },
       { $sort: { cid: 1 } },
       { $group: {
           _id: '$cid',
           totalCount: { $sum: '$count' }
       } },
       { $match: {
           totalCount: { $gte: 2 }
       } }
   ]
   

3. 分片架构优化（超大数据量场景）：若Tag集合数据量远超单节点处理能力，可按cid或date字段分片，将$group的计算压力分散到多个分片节点，降低单节点的CPU与内存负载。

第二部分：全阶段优化方案 #

当前问题 #

加入$lookup后，查询额外耗时约752205ms，即使在Customer集合创建了复合索引{ _id: 1, state: 1 }，性能仍极差。

优化建议 #

1. 替换$lookup为批量关联写法：原$lookup使用子管道会对每个cid发起单次查询，当符合条件的cid数量较多时，会产生大量零散查询，导致性能骤降。改用批量关联写法，MongoDB会优化为批量查询，效率大幅提升：

   { $lookup: {
       from: 'Customer',
       localField: '_id', // 对应Tag分组后的cid
       foreignField: 'id',
       as: 'customer'
   } },
   { $match: {
       'customer.state': 'active'
   } },
   { $count: 'count' }
   

   同时确保Customer集合存在{ id: 1, state: 1 }复合索引（若主键不是id则对应调整），让关联查询可使用覆盖索引扫描，无需读取文档内容。
2. 冗余字段提前过滤：若业务允许，在Tag集合中冗余customer_state字段，当Customer的state变更时同步更新对应Tag文档的该字段。这样可在$match阶段直接过滤活跃用户，完全避免$lookup操作：

   [
       { $match : {
           date: { $gte: ..., $lte: ... },
           name: 'tag1',
           customer_state: 'active'
       } },
       { $sort: { cid: 1 } },
       { $group: {
           _id: '$cid',
           totalCount: { $sum: '$count' }
       } },
       { $match: {
           totalCount: { $gte: 2 }
       } },
       { $count: 'count' }
   ]
   

   此方案性能提升最明显，但需维护数据一致性，适合state变更不频繁的场景。
3. 调整索引适配关联逻辑：若Customer集合关联的是id字段（非默认主键_id），需确保创建的索引是{ id: 1, state: 1 }，而非{ _id: 1, state: 1 }，否则关联查询无法命中索引。

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