即使在索引字段上，MongoDB排序也非常慢 [英] MongoDB sort is extremely slow even on indexed fields

问题描述

所以今天我遇到了这个问题，我的MongoDB查询极其缓慢和超时的地方。我发布了这个问题 - MongoDB有太多记录？他的建议是正确的，我必须确保索引并删除不区分大小写。我在Mongo shell中试过它，效果很好。

So I had this issue today where my MongoDB queries where extremely slow and timing out. I posted this question - MongoDB too many records? and his suggestion was right wherein I had to ensureIndex and remove case-insensitivity. I tried it in Mongo shell, it worked perfectly.

然而，当我通过PHP运行它时，它仍然是相同的:(
然后我意识到查询对id排序（不是_id） ）当我删除它时，事情变得非常快。但是排序，它真的很慢。我已经在id字段上有一个索引。
这是查询btw：

However, when I ran it via PHP, it was still the same :( I then realized the query had a sort on "id" (not _id) field and when I removed that, things were blazing fast. But with the sort, it was REALLY slow. I already had an index on the id field. This is the query btw :

db.tweet_data.find({ 
...     $or: 
...         [ 
...             { in_reply_to_screen_name: /^kunalnayyar$/, handle: /^kaleycuoco$/, id: { $gt: 0 } }, 
...             { in_reply_to_screen_name: /^kaleycuoco$/, handle: /^kunalnayyar$/, id: { $gt: 0 } } 
...         ], 
...     in_reply_to_status_id_str: { $ne: null } 
...     
...     } ).sort({id:-1})explain()

所以我的索引是:(不是复合的） {{id：-1}，{handle：1}，{in_reply_to_screen_name：1}}
经过一些阅读后，我意识到它应该是一个复合索引我试过两个变种没有成功：
1. {handle：1，in_reply_to_screen_name：1，id：-1}
2. {id：-1，handle：1，in_reply_to_screen_name：1}

So my indexes are : (not composite) { {id:-1} , {handle:1}, {in_reply_to_screen_name:1} } After some reading I realized it should have been a composite index and I tried two variations to no success : 1. {handle:1, in_reply_to_screen_name:1, id:-1} 2. {id:-1,handle:1, in_reply_to_screen_name:1}

我不确定我哪里出错了，但我很确定问题是在这里编制索引。我太蠢了，无法理解索引的顺序和字段

I am not sure where I am going wrong, but I am pretty sure the issue is indexing here. I am just too buzzed and can't understand the order and the fields to index

推荐答案

你应该运行解释反对你的查询，它将帮助你弄清楚发生了什么。

You should run explain against your query, it will help you figure out what's going on.

Mongo可能没有使用索引过滤和排序。当您使用 $或时，它可以使用多个索引来匹配选项。但是当你添加 sort 时，它可能会使它不能使用可用于过滤的索引。

It's likely that Mongo isn't using an index for both filtering and sorting. When you use an $or, it can use multiple indexes to match the options. But when you add a sort it may make it not use indexes available for filtering.

当你想要排序时在查询中，您需要确保已排序的字段位于您要命中的索引中（最后一个，或者它不能用它来排序）。

When you want to sort on a query, you need to make sure the sorted field is in the index you want to hit (last, or it can't use it to sort).

你或许可以通过传递索引提示加快速度。我不知道您的查询匹配了多少个文档，但是如果它是一个小数字并且您确保初始条件是命中索引，那么 _id 上的排序可以完成很快。

You may be able to speed it up by passing an index hint, too. I don't know how many docs your query matches, but if it's a small number and you make sure the initial conditions are hitting an index, the sort on _id can be done quickly.

这篇关于即使在索引字段上，MongoDB排序也非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！