mongodb语法$vlookup性能分析

1 场景描述

mongodb有两个表department和user表，

department表有_id,name,level，表有记录169w条

user表有_id,name,department_id，表有记录169w条，department_id没有创建索引，department_id是department的_id。

现在需要写个vlooup方法，查询level下面有多少用户。

2 user表$vlookup表department写法

db.user.aggregate([// 连接 department 表获取 level 信息{$lookup: {from: "department",       // 关联的目标表localField: "department_id", // user 表的关联字段foreignField: "_id",      // department 表的关联字段as: "dept"                // 关联结果的别名}},// 展开 dept 数组（每个用户对应一个部门）{$unwind: "$dept"},// 按层级分组并统计用户数量{$group: {_id: "$dept.level",       // 按部门层级分组userCount: { $sum: 1 },   // 统计每个层级的用户数departmentNames: { $addToSet: "$dept.name" } // 收集部门名称}}
], { allowDiskUse: true });

3 department表$vlookup表user写法

db.department.aggregate([// 步骤1：关联 user 表（类似 VLOOKUP）{$lookup: {from: "user",                   // 关联的集合名称（user 表）localField: "_id",              // department 表的关联字段（_id）foreignField: "department_id",  // user 表的关联字段（department_id）as: "users"                     // 将匹配的 user 文档存入 users 数组}},// 步骤2：计算每个 department 的用户数{$addFields: {user_count: { $size: "$users" } // 通过数组长度获取用户数}},// 步骤3：按 level 分组统计总用户数{$group: {_id: "$level",                  // 按 level 分组total_users: { $sum: "$user_count" } // 累加用户数}}
], { allowDiskUse: true });

user表$vlookup表department写法  速度快于 department表$vlookup表user写法

为什么呢？

user表$vlookup表department写法：

先读取user表数据

读取user表字段department_id值

根据department_id值去user表_id关联查找，user表的_id有索引，所以速度快

4 将user表的数据分别切割成10w、50w、100w、150w

// 步骤 1：将符合条件的 50w 条数据写入新表
db.user.aggregate([{ $match: { /* 筛选条件，如：age > 30 */ } }, // 可选：添加筛选条件{ $limit: 1500000 },                            // 限制迁移数量{ $out: "user150" }                           // 写入新表
], { allowDiskUse: true });                      // 允许磁盘临时存储

5 分别执行user表$vlookup表department写法sql，看看执行时间

user表10w   关联department表169w耗时10098ms

user表50w   关联department表169w耗时76067ms

user表100w 关联department表169w耗时174097ms

user表150w 关联department表169w耗时297307ms

假如读取一条user表耗时x，根据索引_id读取department耗时是y

100000x+100000y=10098

500000x+500000y=76067

1000000x+1000000y=174097

1500000x+1500000y=297307

处理1条数据大概需要0.1877ms