为什么 MongoDb 排序在查找集合中很慢 [英] Why MongoDb sort is slow with lookup collections
问题描述
我的 mongodb 数据库中有两个集合,如下所示:
I have two collections in my mongodb database as follows:
employee_details 包含大约 330000 个文档,其中 department_id 作为部门集合的参考
部门 集合,包含 2 个字段 _id 和 dept_name
我想通过查找方法使用department_id作为外键加入上述两个集合.加入工作正常,但当我添加排序时,mongo 查询执行需要很长时间.
I want to join the above two collections using department_id as foreign key by using lookup method. Join works fine but the mongo query execution takes long time when I add sort.
注意:如果我删除排序对象或删除查找方法,执行速度会很快.
Note: The execution is fast If I remove the sort object or If I remove the lookup method.
我在不同的博客和 SO 中引用了几篇文章,但没有一个给出排序的解决方案.
I have referred several posts in different blogs and SO, but none of them give a solution with sort.
我的查询如下:
db.getCollection("employee_details").aggregate([
{
$lookup: {
from: "departments",
localField: "department_id",
foreignField: "_id",
as: "Department"
}
},
{ $unwind: { path: "$Department", preserveNullAndEmptyArrays: true } },
{ $sort: { employee_fname: -1 } },
{ $limit: 10 }
]);
有人可以提供一种方法来使上述查询毫不延迟地工作,因为我的客户不能在性能延迟上妥协.我希望有一些方法可以解决性能问题,因为 nosql 旨在处理大型数据库.
Can someone give a method to make the above query to work without delay, as my client cannot compromise with the performance delay. I hope there is some method to fix the performance issue as nosql is intented to handle large database.
那里有可用的索引方法吗?这样我就可以将它与我相同的集合结构一起使用.
Any indexing methods is available there? so that I can use it along with my same collection structure.
提前致谢.
推荐答案
目前每个employee_details都会进行330000次的查找,但是如果我们在查找之前先进行排序和限制,那么只会是10次.这将大大减少查询时间.
Currently lookup will be made for every employee_details which means for 330000 times, but if we first sort and limit before lookup, it will be only 10 times. This will greatly decrease query time.
db.getCollection('employee_details').aggregate([
{$sort : {employee_fname: -1}},
{$limit :10},
{
$lookup : {
from : "departments",
localField : "department_id",
foreignField : "_id",
as : "Department"
}
},
{ $unwind : { path: "$Department", preserveNullAndEmptyArrays: true }},
])
尝试之后,如果您甚至想减少响应时间,您可以定义一个索引 在排序字段上.
After trying this, if you even want to decrease the response time you can define an index on the sort field.
db.employee_details.createIndex( { employee_fname: -1 } )
这篇关于为什么 MongoDb 排序在查找集合中很慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!