MongoDB 范围查询中 $lt 和 $gt 的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9895888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order of $lt and $gt in MongoDB range query
提问by StefanMK
Today I have noticed that the order in which the $lt and $gt operators are given seem to matter in MongoDB 2.0.2.
今天我注意到 $lt 和 $gt 运算符的顺序在 MongoDB 2.0.2 中似乎很重要。
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:
我有一个游戏数据库。“player”是一个由两个字符串组成的数组，代表两个玩家，“endedAtMS”是游戏结束时的时间戳。我创建了这个索引：
db.games.ensureIndex({player:1,endedAtMS:-1})
To get 30 of my games which were finished in a certain time range, ordered by the time the games where finished, I do:
为了获得在特定时间范围内完成的 30 场比赛，按照比赛结束的时间排序，我这样做：
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
]
]
}
}
All seems to work fine. However when I change the order of $lt and $gt in the query above I get this:
一切似乎都很好。但是，当我在上面的查询中更改 $lt 和 $gt 的顺序时，我得到了这个：
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
]
]
}
}
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.
如您所见，需要扫描 126 个文档才能获得结果的 30 个文档。如果您查看解释输出中的 indexBounds，似乎只有第一个运算符用于限制索引中的搜索空间。
What do I miss? Why is Mongo only using one operator to limit the search space?
我想念什么？为什么 Mongo 只使用一个运算符来限制搜索空间？
采纳答案by Marc
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.
这是一个已知的问题。简短的回答是，它与使用多键索引的事实有关（“播放器”是一个数组），并且索引不能同时限制在上限和下限。
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-4155- “索引绑定不正确？”
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!
有一个开放的 Jira 票可以改善这种行为：https://jira.mongodb.org/browse/SERVER-4180 - “为日期范围查询（回归）选择了错误的索引范围”，计划在 2.1 版中发布。 2（此版本可能会更改）。请为它投票！
回答by Lenny Linus
This has been fixed in version '2.1.2'.
这已在版本“2.1.2”中得到修复。
As per: https://jira.mongodb.org/browse/SERVER-4180
根据：https: //jira.mongodb.org/browse/SERVER-4180
AWESOME!
惊人的！
