MongoDb排序与查询集合的速度很慢

本文介绍了MongoDb排序与查询集合的速度很慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的mongodb数据库中有两个集合，如下所示:

I have two collections in my mongodb database as follows:

employee_details (约330000个文档)，其中有 department_id 作为部门集合的参考

employee_details with approximately 330000 documents which has department_id as a reference from departments collection

部门 集合，其中包含两个字段 _id 和 dept_name

我想通过使用lookup方法将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 }}, ])

尝试此操作后，如果您甚至想减少响应时间，都可以定义 index 在排序字段上.

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 } )