为什么这个mongodb查询这么慢? [英] Why is this mongodb query so slow?

问题描述

我有两个收藏集，如下所示:

I have two collections, as follows:

数据库运输
文件格式:{mmsi:长，...其他栏位}
索引:{{mmsi:1}，{unique:true}}

db.ships
document format: { mmsi: Long, ...some other fields }
indexes: { {mmsi: 1}, {unique: true} }

db.navUpdates
文件格式:{mmsi:很长，时间:ISODate，...其他一些字段}
索引:{mmsi:1}，{时间:1}

db.navUpdates
document format: { mmsi: Long, time: ISODate, ...some other fields }
indexes: { mmsi: 1 }, { time: 1 }

对于 db.ships 中的每个文档，我需要在 db.navUpdates 中找到与 mmsi 相匹配的最新文档.我不能使用 _id 查找最新的文档，因为文档不一定按时间顺序(由时间戳记 time 定义)输入.

For each document within db.ships, I need to find the most recent document within db.navUpdates that matches by mmsi. I cannot use _id to find most recent as documents are not necessarily entered in chronological (as defined by timestamp time) order.

例如:

ship document:
{ mmsi: 12345 }

navUpdate documents:
{ mmsi: 12345, time: ISODate("2012-09-19T12:00:00.000Z") }
{ mmsi: 12345, time: ISODate("2012-09-18T12:00:00.000Z") }
{ mmsi: 54321, time: ISODate("2012-09-19T12:00:00.000Z") }

因此对于带有 mmsi:12345 的 ship ，最新的 navUpdate 是上面列表中的第一个文档，带有时间:ISODate("2012-09-19T12:00:00.000Z").

So for the ship with mmsi:12345, the most recent navUpdate is the first document in the list above, with time:ISODate("2012-09-19T12:00:00.000Z").

我尝试了以下mongo shell脚本，但是它非常慢(仅10个查询就需要几秒钟)，并且

I tried the following mongo shell script, but it's incredibly slow (multiple seconds for just 10 queries), and messages appearing on the server indicate I'm missing an index.

db.ships.find().limit(10).forEach(function(ship) {
    var n = db.navUpdates.find({mmsi:ship.mmsi}).count();
    if (n==0) { return; }
    var t = db.navUpdates.find({mmsi:ship.mmsi}).sort({time:-1}).limit(1)[0].time;
    print(t);
});

为什么这个查询这么慢?我尝试向 navUpdate 添加 {time:-1} 索引，认为 sort({time:-1})可能是罪魁祸首，但仍无改善.

Why is this query so slow? I tried adding a {time: -1} index to navUpdate, thinking perhaps the sort({time: -1}) might be the culprit, but still no improvement.

还可以优化此查询吗?我在那里有 count()调用，因为在 navUpdates ship 文档的 mmsi >.

Also, can this query be optimized? I have the count() call in there because there are some ship documents whose mmsis are not found within navUpdates.

推荐答案

单个查询只能使用一个索引，因此您应添加 {mmsi:1，time:-1}的复合索引可以同时用于查找和排序需求的 navUpdates .

A single query can only use one index, so you should add a compound index of { mmsi: 1, time: -1 } to navUpdates that can be used for both your find and sort needs.

然后使用 .explain() 确定是否您的索引正在查询中使用.

Then use .explain() to determine if your indexes are being used in your queries.

这篇关于为什么这个mongodb查询这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！