MongoDB范围查询中$ lt和$ gt的顺序 [英] Order of $lt and $gt in MongoDB range query
问题描述
今天我注意到在MongoDB 2.0.2中,$ lt和$ gt运算符的给出顺序似乎很重要.
Today I have noticed that the order in which the $lt and $gt operators are given seem to matter in MongoDB 2.0.2.
我有一个游戏数据库. 玩家"是代表两个玩家的两个字符串的数组,"endedAtMS"是游戏结束时的时间戳.我创建了该索引:
I have a database of games. "player" is an array of two strings representing both players, "endedAtMS" is a timestamp when the game has ended. I have created this index:
db.games.ensureIndex({player:1,endedAtMS:-1})
要获得在一定时间范围内完成的我的30项游戏(按游戏结束的时间排序),我这样做:
To get 30 of my games which were finished in a certain time range, ordered by the time the games where finished, I do:
db.games.find({ "player" : "Stefan" ,
"endedAtMS" : { "$lt" : 1321284969946 ,
"$gt" : 1301284969946}}).
sort({endedAtMS:-1}).
limit(30).
explain()
{
"cursor" : "BtreeCursor player_1_endedAtMS_-1",
"nscanned" : 30,
"nscannedObjects" : 30,
"n" : 30,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"player" : [
[
"Stefan",
"Stefan"
]
],
"endedAtMS" : [
[
1321284969946,
-1.7976931348623157e+308
]
]
}
}
一切似乎都正常.但是,当我在上面的查询中更改$ lt和$ gt的顺序时,得到的是:
All seems to work fine. However when I change the order of $lt and $gt in the query above I get this:
db.games.find({ "player" : "Stefan" ,
"endedAtMS" : { "$gt":1301284969946,
"$lt" : 1321284969946}}).
sort({endedAtMS:-1}).
limit(30).
explain()
{
"cursor" : "BtreeCursor player_1_endedAtMS_-1",
"nscanned" : 126,
"nscannedObjects" : 126,
"n" : 30,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"player" : [
[
"Stefan",
"Stefan"
]
],
"endedAtMS" : [
[
1.7976931348623157e+308,
1301284969946
]
]
}
}
如您所见,需要扫描126个文档才能获得结果的30个文档.如果您查看说明输出中的indexBounds,似乎只有第一个运算符用于限制索引中的搜索空间.
As you can see 126 docs need to be scanned to get the 30 docs for the result. If you take a look at the indexBounds in the explain output it seems that only the first operator is used to limit the search space in the index.
我想念什么?为什么Mongo仅使用一个运算符来限制搜索空间?
What do I miss? Why is Mongo only using one operator to limit the search space?
推荐答案
这是一个已知问题.简短的答案是,它与以下事实有关:使用了多键索引(玩家"是一个数组),并且索引不能同时受上限和下限的限制.
This is a known issue. The short answer is that it has to do with the fact that a multikey index is used ("player" is an array), and the index cannot be constrained on both upper and lower bounds.
在Jira案例中对此进行了更详细的解释: https://jira.mongodb.org /browse/SERVER-4155 -索引绑定错误?"
This is explained in more detail in the Jira case: https://jira.mongodb.org/browse/SERVER-4155 - "Index bound incorrect?"
有一个开放的Jira票证可以改善这种行为: https://jira.mongodb.org /browse/SERVER-4180 -错误地为日期范围查询选择了索引界限(回归)",该日期定于2.1.2版中发布(此版本可能会更改).请投票!
There is an open Jira ticket to improve this behavior: https://jira.mongodb.org/browse/SERVER-4180 - "Wrong indexbounds picked for a date range query (regression)" which is slated to be released in version 2.1.2 (this version is subject to change). Please vote for it!
这篇关于MongoDB范围查询中$ lt和$ gt的顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!