强制使用索引复杂的MongoDB查询？(force use of index on complex MongoDB query?)

我有一大堆带有'to'，'from'，'type'和'visible_to'字段的“消息”，我想通过一个相当复杂的查询来查询，这些查询仅向/从特定用户提取消息该用户可见的一组特定类型。这是一个实际的例子：

{ "$and": [ { "$and": [ { "$or": [ { "to": "52f65f592f1d88ebcb00004f" }, { "from": "52f65f592f1d88ebcb00004f" } ] }, { "$or": [ { "type": "command" }, { "type": "image" } ] } ] }, { "$or": [ { "public": true }, { "visible_to": "52f65f592f1d88ebcb00004f" } ] } ] }

有索引：

[ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "n2-mongodb.messages", "name" : "_id_" }, { "v" : 1, "key" : { "expires" : 1 }, "ns" : "n2-mongodb.messages", "name" : "expires_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "from" : 1 }, "ns" : "n2-mongodb.messages", "name" : "from_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "type" : 1 }, "ns" : "n2-mongodb.messages", "name" : "type_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "ts" : 1, "type" : -1 }, "ns" : "n2-mongodb.messages", "name" : "ts_1_type_-1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "to_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "visible_to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "visible_to_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "public" : 1, "visible_to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "public_1_visible_to_1" }, { "v" : 1, "key" : { "to" : 1, "from" : 1 }, "ns" : "n2-mongodb.messages", "name" : "to_1_from_1" } ]

这是MongoDB 2.2.2实例的解释（true）输出，看起来像一个完整的扫描：

{ "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 35702, "nscanned" : 35702, "nscannedObjectsAllPlans" : 35702, "nscannedAllPlans" : 35702, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 1, "nChunkSkips" : 0, "millis" : 85, "indexBounds" : { }, "allPlans" : [ { "cursor" : "BasicCursor", "n" : 0, "nscannedObjects" : 35702, "nscanned" : 35702, "indexBounds" : { } } ], "server" : "XXXXXXXX" }

看一下explain输出，MongoDB没有使用任何索引 - 有没有办法让它至少使用复合索引{to：1，from：1}来大大缩小搜索空间？或者有更好的方法来优化此查询？或者MongoDB完全不适合这样的查询？

i have a large collection of "messages" with 'to', 'from', 'type', and 'visible_to' fields that I want to query against with a fairly complex query that pulls only the messages to/from a particular user of a particular set of types that are visible to that user. Here is an actual example:

{ "$and": [ { "$and": [ { "$or": [ { "to": "52f65f592f1d88ebcb00004f" }, { "from": "52f65f592f1d88ebcb00004f" } ] }, { "$or": [ { "type": "command" }, { "type": "image" } ] } ] }, { "$or": [ { "public": true }, { "visible_to": "52f65f592f1d88ebcb00004f" } ] } ] }

With indexes:

[ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "n2-mongodb.messages", "name" : "_id_" }, { "v" : 1, "key" : { "expires" : 1 }, "ns" : "n2-mongodb.messages", "name" : "expires_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "from" : 1 }, "ns" : "n2-mongodb.messages", "name" : "from_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "type" : 1 }, "ns" : "n2-mongodb.messages", "name" : "type_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "ts" : 1, "type" : -1 }, "ns" : "n2-mongodb.messages", "name" : "ts_1_type_-1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "to_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "visible_to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "visible_to_1", "background" : true, "safe" : null }, { "v" : 1, "key" : { "public" : 1, "visible_to" : 1 }, "ns" : "n2-mongodb.messages", "name" : "public_1_visible_to_1" }, { "v" : 1, "key" : { "to" : 1, "from" : 1 }, "ns" : "n2-mongodb.messages", "name" : "to_1_from_1" } ]

And here is the explain(true) output from our MongoDB 2.2.2 instance, which looks like a full scan:

{ "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 35702, "nscanned" : 35702, "nscannedObjectsAllPlans" : 35702, "nscannedAllPlans" : 35702, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 1, "nChunkSkips" : 0, "millis" : 85, "indexBounds" : { }, "allPlans" : [ { "cursor" : "BasicCursor", "n" : 0, "nscannedObjects" : 35702, "nscanned" : 35702, "indexBounds" : { } } ], "server" : "XXXXXXXX" }

Looking at the explain output, MongoDB is not using any indexes for this - is there a way to get it to use at least the compound index {to: 1, from: 1} to dramatically narrow the search space? Or is there a better way to optimize this query? Or is MongoDB wholly unsuited for a query like this?

最满意答案

要强制MongoDB查询优化器采用特定方法，可以使用$ hint运算符。从文档中，

The $hint operator forces the query optimizer to use a specific index to fulfill the query. Specify the index either by the index name or by document.

The query optimizer in MongoDB 2.6 will include support for applying indexes to complex queries.

更多推荐