我注意到使用聚合和$lookup和$count对文档进行计数有点慢(在本地运行 20k 文档的收集为 ~1.4 秒(，如果没有$lookup则需要 ~14 毫秒。 关于索引：查找的前置字段是_id的，为了安全起见，我也在"localField"上创建了索引。 这样的表现"正常"吗？
带有 Node.JS 驱动程序的 Windows 机器上的 MongoDB 版本 4.2.0。
这是我的测试代码：
var mongo = require('mongodb');
async function start() {
console.log('Connecting...');
var client = await mongo.MongoClient.connect('mongodb://test:test@127.0.0.1:27017/test', { useNewUrlParser: true, useUnifiedTopology: true });
var db = client.db();
console.log('Creating data...');
await db.collection('users').drop();
var res = await db.collection('users').insertOne({name: 'user', email: 'user@test.com'});
var projects = Array(20000).fill().map((x, i) => ({ name: `project-${i}`, user: res.insertedId }));
await db.collection('projects').drop();
await db.collection('projects').insertMany(projects);
await db.collection('projects').createIndex({user: 1}, {name: 'user_1'});
await runAggregate(db, 'Fetching with $lookup...', [
{$lookup: {from: 'users', localField: 'user', foreignField: '_id', as: 'user'}},
{$match: {/* In real I will have here some query involving fields from user */}},
{$count: 'total'}
]);
await runAggregate(db, 'Fetching without $lookup...', [
{$match: {/* In real I will have here some query involving fields from user */}},
{$count: 'total'}
]);
}
async function runAggregate(db, msg, agg) {
console.log('Waiting 2 seconds...');
await wait(2000)
console.log(msg);
var start = Date.now();
var res = await db.collection('projects').aggregate(agg);
res = await res.toArray();
var span = Date.now() - start;
console.log('Aggregate took: %s msec', span);
}
function wait(ms) {
return new Promise(res => setTimeout(res, ms));
}
start().then(process.exit);
这是输出：
Connecting...
Creating data...
Waiting 2 seconds...
Fetching with $lookup...
Aggregate took: 1429 msec
Waiting 2 seconds...
Fetching without $lookup...
Aggregate took: 14 msec
谢谢。
以防万一其他人发现了这一点 - 帮助我的是从右表中选择_id，然后将它们喂入左表中查找。 有时分批使用，它仍然比在聚合中使用匹配 + 计数快几倍