MongoDB索引边界约束 [英] MongoDB Index boundry constraints

问题描述

在我使用MongoDB的过程中，我开始了解MongoDB索引的问题。问题是MongoDB索引有时不会强制要求查询的两端边界。这是我在查询数据库时遇到的输出之一：

During my hands on with MongoDB I came to understand about a problem with MongoDB indexes. Problem is that MongoDB indexes sometimes doesn't enforce the two-end boundaries to query. Here's one of the output I encountered while querying the database:

查询：

db.user.find({transaction:{$elemMatch:{product:"mobile", firstTransaction:{$gte:ISODate("2015-01-01"), $lt:ISODate("2015-01-02")}}}}).hint("transaction.product_1_transaction.firstTransaction_1").explain()

输出：

"cursor" : "BtreeCursor transaction.firstTransaction_1_transaction.product_1",
                            "isMultiKey" : true,
                            "n" : 622,
                            "nscannedObjects" : 350931,
                            "nscanned" : 6188185,
                            "nscannedObjectsAllPlans" : 350931,
                            "nscannedAllPlans" : 6188185,
                            "scanAndOrder" : false,
                            "indexOnly" : false,
                            "nYields" : 235851,
                            "nChunkSkips" : 0,
                            "millis" : 407579,
                            "indexBounds" : {
                                    "transaction.firstTransaction" : [
                                            [
                                                    true,
                                                    ISODate("2015-01-02T00:00:00Z")
                                            ]
                                    ],
                                    "transaction.product" : [
                                            [
                                                    "mobile",
                                                    "mobile"
                                            ]
                                    ]
                            },

正如你在上面的例子中看到的第一个事务字段t的一端他的约束是真的，而不是我提到的日期。我发现这个的解决方法是min（），max（）函数。我试过这些，但他们似乎没有使用嵌入式文档（事务是一个子文档数组，其中包含firstTransaction，product等字段）。我收到以下错误：

As you can see in above example for firstTransaction field one end of the bound is true instead of date I mentioned. I found the workaround for this is min(), max() functions. I tried those but they not seem to be working with embedded document (transaction is an array of sub document which contains fields like firstTransaction, product etc). I get following error:

查询：

db.user.find({transaction:{$elemMatch:{product:'mobile'}}}).min({transaction:{$elemMatch:{firstTransaction:ISODate("2015-01-01")}}}).max({transaction:{$elemMatch:{firstTransaction:ISODate("2015-01-02")}}})

输出：

planner returned error: unable to find relevant index for max/min query

firstTransaction字段已被索引，但产品和&他们的复合指数也是。我不知道这里出了什么问题。

firstTransaction field is indexed though as well as product & their compound index too. I don't know what is going wrong here.

示例文件：

{
_id: UUID (indexed by default),
name: string,
dob: ISODate,
addr: string,
createdAt: ISODate (indexed),
.
.
.,
transaction:[
{
firstTransaction: ISODate(indexed),
lastTransaction: ISODate(indexed),
amount: float,
product: string (indexed),
.
.
.
},...
],
other sub documents...
}

推荐答案

这是正确的行为。您不能总是与 $ lte 和 $ gte 的索引边界相交 - 有时它会给出不正确的结果。例如，考虑文档

This is the correct behavior. You cannot always intersect the index bounds for $lte and $gte - sometimes it would give incorrect results. For example, consider the document

{ "x" : [{ "a" : [4, 6] }] }

此文件与查询匹配

db.test.find({ "x" : { "$elemMatch" : { "a" : { "$gte" : 5, "$lte" : 5 } } } });

如果我们在 {xa：1}上定义索引，两个索引边界将是 [5，无穷大] ，以及 [ - infinity，5] 。相交它们将给出 [5,5] 并使用此索引绑定与文档不匹配 - 错误！

If we define an index on { "x.a" : 1 }, the two index bounds would be [5, infinity], and [-infinity, 5]. Intersecting them would give [5, 5] and using this index bound would not match the document - incorrectly!

您是否可以提供示例文档并告诉我们有关您尝试对查询执行的操作的更多信息？使用上下文，可能有另一种方法来编写使用更严格的索引边界的查询。

Can you provide a sample document and tell us more about what you're trying to do with the query? With context, there may be another way to write the query that uses tighter index bounds.

这篇关于MongoDB索引边界约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！