MongoDB+Mongoose多关联查询优化：复杂Schema任务数据查询需求

需求说明 #

用户登录后需获取满足以下条件的所有tasks：

• task处于active状态
• 当前日期处于task的fromDate和toDate之间（无这两个日期也符合条件）
• task属于当前用户关联的活跃群组（群组active字段为true）

同时需为每个查询到的task补充以下关联数据：

• 关联的actions（仅保留active: true的记录，只取_id）
• 若当前用户是学生，需获取关联的responses（关联当前用户和对应actions），并统计已完成动作数doneActions、已评分动作数gradeActions
• 关联群组的名称groupname

原实现代码 #

const {groups} = await User.findOne({ username, active:true })
    .select("groups")
    .lean()
    .exec();
const tasksQuery = {
    active: true,
    group: { $in: groups },
    $or: [
        {
            fromDate: { $exists: false },
            toDate: { $exists: false },
        },
        {
            $expr: {
                $and: [
                    { $lte: ["$fromDate", "$$NOW"] },
                    { $gte: ["$toDate", "$$NOW"] },
                ],
            },
        },
    ],
};
const tasks = await Task.find(tasksQuery).lean()

const tasksWithGroup = await Promise.all(
    tasks.map(async (task) => {
        const group = await Group.findById(task.group).lean().exec();
        const actions = await Action.find({task:task._id, active:true}).select("_id").lean().exec();
        const numActions = actions?.length
        let doneActions = 0
        let gradeActions=0
        let responses=[]
        // 学生用户获取响应数据
        if(numActions && req.isStudent){
            responses = await Response.find({username:req.user ,action:{ $in: actions } }).select(["_id","grade"]).lean().exec();
            if(responses.length) {
                doneActions = responses.length
                gradeActions = responses.filter(c=>c.grade > -1).length
            }
        }
        return { ...task, groupname: group?.name , numActions, actions,doneActions ,gradeActions, responses};
    })
);

优化方案：使用MongoDB聚合框架一次完成查询 #

原方案存在N+1查询问题：每个task都要单独查询group、actions、responses，数据库交互次数随task数量线性增长。改用聚合框架，通过一次查询完成所有关联、过滤和统计，大幅减少数据库请求。

优化后代码 #

const isStudent = req.isStudent;
const username = req.user; // 假设当前登录用户的username

const tasksWithData = await User.aggregate([
    // 1. 匹配当前活跃用户
    {
        $match: { username, active: true }
    },
    // 2. 关联Group集合，过滤出活跃群组，仅保留_id和name
    {
        $lookup: {
            from: "groups",
            localField: "groups",
            foreignField: "_id",
            as: "userGroups",
            pipeline: [
                { $match: { active: true } },
                { $project: { _id: 1, name: 1 } }
            ]
        }
    },
    // 3. 展开群组数组，为后续关联Task做准备
    { $unwind: "$userGroups" },
    // 4. 关联Task集合，过滤符合条件的任务
    {
        $lookup: {
            from: "tasks",
            localField: "userGroups._id",
            foreignField: "group",
            as: "tasks",
            pipeline: [
                { $match: { active: true } },
                // 处理日期条件：无from/toDate 或 当前时间在区间内
                {
                    $match: {
                        $or: [
                            { fromDate: { $exists: false }, toDate: { $exists: false } },
                            {
                                $expr: {
                                    $and: [
                                        { $lte: ["$fromDate", "$$NOW"] },
                                        { $gte: ["$toDate", "$$NOW"] }
                                    ]
                                }
                            }
                        ]
                    }
                }
            ]
        }
    },
    // 5. 展开tasks数组
    { $unwind: "$tasks" },
    // 6. 关联Action集合，获取当前task的活跃actions（仅取_id）
    {
        $lookup: {
            from: "actions",
            localField: "tasks._id",
            foreignField: "task",
            as: "tasks.actions",
            pipeline: [
                { $match: { active: true } },
                { $project: { _id: 1 } }
            ]
        }
    },
    // 7. 添加actions数量字段
    {
        $addFields: {
            "tasks.numActions": { $size: "$tasks.actions" }
        }
    },
    // 8. 根据是否是学生，处理responses及统计数据
    ...(isStudent ? [
        {
            $lookup: {
                from: "responses",
                let: { actionIds: "$tasks.actions._id", username: username },
                pipeline: [
                    { $match: { $expr: { $and: [ { $eq: ["$username", "$$username"] }, { $in: ["$action", "$$actionIds"] } ] } } },
                    { $project: { _id: 1, grade: 1 } }
                ],
                as: "tasks.responses"
            }
        },
        {
            $addFields: {
                "tasks.doneActions": { $size: "$tasks.responses" },
                "tasks.gradeActions": {
                    $size: {
                        $filter: {
                            input: "$tasks.responses",
                            cond: { $gt: ["$$this.grade", -1] }
                        }
                    }
                }
            }
        }
    ] : [
        {
            $addFields: {
                "tasks.responses": [],
                "tasks.doneActions": 0,
                "tasks.gradeActions": 0
            }
        }
    ]),
    // 9. 整理输出字段，合并task和关联数据
    {
        $project: {
            _id: "$tasks._id",
            active: "$tasks.active",
            group: "$tasks.group",
            fromDate: "$tasks.fromDate",
            toDate: "$tasks.toDate",
            // 补充其他task需要的字段
            groupname: "$userGroups.name",
            numActions: "$tasks.numActions",
            actions: "$tasks.actions",
            doneActions: "$tasks.doneActions",
            gradeActions: "$tasks.gradeActions",
            responses: "$tasks.responses"
        }
    }
]).lean();

优化点说明 #

1. 减少查询次数：从原来的1 + 1 + N*(1+1+1)次查询，变为仅1次聚合查询，彻底消除N+1问题
2. 逻辑内聚：所有过滤、关联、统计逻辑都在数据库端完成，减少应用层与数据库的交互开销
3. 性能提升：利用MongoDB的索引优化关联查询（确保groups._id、tasks.group、actions.task、responses.username和responses.action字段有合适的索引）
4. 代码简洁：避免了Promise.all循环嵌套，代码结构更清晰

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