MongoDB总数太慢 [英] MongoDB aggregate count is too much slow
问题描述
我在users
集合中有大约6万个文档,并具有以下查询:
I have around 60 thousand document in users
collection, and have the following query:
db.getCollection('users').aggregate([
{"$match":{"userType":"employer"}},
{"$lookup":{"from":"companies","localField":"_id","foreignField":"owner.id","as":"company"}},
{"$unwind":"$company"},
{"$lookup":{"from":"companytypes","localField":"company.type.id","foreignField":"_id","as":"companyType"}},
{"$unwind":"$companyType"},
{ $group: { _id: null, count: { $sum: 1 } } }
])
计数大约需要12秒钟,即使我在列表函数之前调用计数函数,但具有limit: 10
的列表函数的响应速度也要比计数快.
It takes around 12 seconds to count, even I call count function before list function, but my list function with limit: 10
response faster than count.
以下是explain
结果:
{
"stages" : [
{
"$cursor" : {
"query" : {
"userType" : "employer"
},
"fields" : {
"company" : 1,
"_id" : 1
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "jobs.users",
"indexFilterSet" : false,
"parsedQuery" : {
"userType" : {
"$eq" : "employer"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"userType" : {
"$eq" : "employer"
}
},
"direction" : "forward"
},
"rejectedPlans" : []
}
}
},
{
"$lookup" : {
"from" : "companies",
"as" : "company",
"localField" : "_id",
"foreignField" : "owner.id",
"unwinding" : {
"preserveNullAndEmptyArrays" : false
}
}
},
{
"$match" : {
"$nor" : [
{
"company" : {
"$eq" : []
}
}
]
}
},
{
"$group" : {
"_id" : {
"$const" : null
},
"total" : {
"$sum" : {
"$const" : 1
}
}
}
},
{
"$project" : {
"_id" : false,
"total" : true
}
}
],
"ok" : 1.0
}
推荐答案
$lookup
operations are slow since they mimic the left join behavior, from the DOCS:
$ lookup在localField上执行等于 from集合中的文档中的foreignField
$lookup performs an equality match on the localField to the foreignField from the documents of the from collection
因此,如果用于joining
的字段中没有索引,则将强制Mongodb进行收集扫描.
Hence if there are no indexes in the fields used for joining
the collections Mongodb is force to do a collection scan.
为foreignField
属性添加索引应防止收集扫描并提高性能,甚至达到一定程度
Adding an index for the foreignField
attributes should prevent a collection scan and increase the performance even of a magnitude
这篇关于MongoDB总数太慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!