无法获取覆盖查询以仅在mongodb中使用索引 [英] Unable to get a covered query to use index only in mongodb

问题描述

我正在尝试使用覆盖索引在我的应用程序上使用mongodb实现词干文本搜索。

I am trying to use a covering index to implement stemming text search on my app which uses mongodb.

我有以下索引集：

ensureIndex({st: 1, n: 1, _id: 1});

但是当我在查询上运行explain（）时，我永远无法将indexOnly读取为true，无论我做什么。

But when I run explain() on my query, I can never get the indexOnly to read true, no matter what I do.

db.merchants.find({st: "Blue"}, {n:1,_id:1}).explain()
{
    "cursor" : "BtreeCursor st_1_n_1__id_1",
    "nscanned" : 8,
    "nscannedObjects" : 8,
    "n" : 8,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
        "st" : [
            [
                "Blue",
                "Blue"
            ]
        ],
        "n" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "_id" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    }
}

我已经发现索引中的键的排序方式不知何故。例如，如果我使用{_id，n：1，st：1}，则根本不使用此索引来执行查询。我还在某处读到，使用explain（）的文档太少会触发不可预测的行为，因为多个策略同样快。但在这种情况下，我看到它使用正确的索引，但它不仅使用索引。这是怎么回事？

I've already figured out that the ordering of the keys in the index matter somehow. For instance if I used {_id, n:1, st:1} it wasn't using this index at all to perform the query. I also read somewhere that too few documents could trigger unpredictable behaviour with explain() since multiple strategies are equally fast. But in this case, I see that its using the right index, but its not using just the index. What is this happening?

我使用的是mongoid，mongo 2.0.8我相信。

I am using mongoid, and mongo 2.0.8 I believe.

更新：

切换到使用Mongoid v3.1.4和mongod v2.2

Switched over to using Mongoid v3.1.4 and mongod v2.2

以下是mongod的查询从mongoid看：7月15日星期一10:47:26 [conn14] runQuery名为spl_development.merchants {$ query：{st：{$ regex：cr，$ options：i}}，$ explain：true} Mon 7月15日10:47:26 [conn14]查询spl_development.merchants查询：{$ query：{st：{$ regex：cr，$ options：i}}，$ explain：true} ntoreturn：0 keyUpdates： 0个锁（微）r：212 nreturned：1 reslen：393 0ms

Here is the query that mongod is seeing from mongoid: Mon Jul 15 10:47:26 [conn14] runQuery called spl_development.merchants { $query: { st: { $regex: "cr", $options: "i" } }, $explain: true } Mon Jul 15 10:47:26 [conn14] query spl_development.merchants query: { $query: { st: { $regex: "cr", $options: "i" } }, $explain: true } ntoreturn:0 keyUpdates:0 locks(micros) r:212 nreturned:1 reslen:393 0ms

因此投影没有发送到mongod层，只是在应用程序中处理它层。不理想！

So the projection isn't being sent to the mongod layer and only just handles it in the application layer. Not ideal!

这已经被认为是mongoid中的一个错误，可以在这里跟踪：
https://github.com/mongoid/mongoid/issues/3142

This has been recognized as a bug in mongoid and can be tracked here: https://github.com/mongoid/mongoid/issues/3142

推荐答案

我希望您的查询不能使用覆盖索引，因为您有一个包含在索引中的数组的字段。这在isMultiKey的解释中建议：true 。

I expect your query cannot use a covered index because you have a field with an array included in the index. This is suggested in the explain with "isMultiKey" : true.

如文档中所述（创建支持涵盖查询的索引）：

如果集合中任何文档中的任何索引字段包含数组，则MongoDB无法使用覆盖查询。如果索引字段是数组，则索引将成为多键索引，并且不能支持覆盖查询。

MongoDB cannot use a covered query if any of the indexed fields in any of the documents in the collection includes an array. If an indexed field is an array, the index becomes a multi-key index and cannot support a covered query.

这篇关于无法获取覆盖查询以仅在mongodb中使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！