使用点表示法的 Mongodb 查询速度很慢 [英] Mongodb query slow with dot notation

问题描述

我有这个文档结构:

{
  "key": {
    "a": Int32,
    "b": String
  }
}

key 上有唯一索引，key.a 和 key.b 上有索引(非唯一).

with a unique index on key and indexes (non-unique) on key.a and key.b.

然而，这个查询会扫描(慢):

Yet, this query scans (slooow):

{"key.a": 456213154}

并且此查询不会:

{"key": {
  "a": 456213154,
  "b": {"$exists": true}
}}

为什么这是必要的，应该是吗?

Why is that necessary, and should it be?

(我应该提到这是 v2.0.3)

(I should mention that this is v2.0.3)

添加解释:

> db.collection.find({"key.a": 456213154}).explain()
{
        "cursor" : "BtreeCursor key.a_1",
        "nscanned" : 10962,
        "nscannedObjects" : 10962,
        "n" : 10962,
        "millis" : 20,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "key.a" : [
                        [
                                456213154,
                                456213154
                        ]
                ]
        }
}
> db.collection.find({"key": {"a": 456213154, "b": {"$exists":true}}}).explain()
{
        "cursor" : "BtreeCursor key_1",
        "nscanned" : 0,
        "nscannedObjects" : 0,
        "n" : 0,
        "millis" : 0,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "key" : [
                        [
                                {
                                        "a" : 456213154,
                                        "b" : {
                                                "$exists" : true
                                        }
                                },
                                {
                                        "a" : 456213154,
                                        "b" : {
                                                "$exists" : true
                                        }
                                }
                        ]
                ]
        }
}

我尝试删除两个非唯一索引(key.a_1 和 key.b_1)，看看这是否会影响查询.不是:

I tried removing the two non-unique indices (key.a_1 and key.b_1) to see if that perhaps was hurting the query. It was not:

> db.collection.find({"key.a": 456213154}).explain()
{
        "cursor" : "BasicCursor",
        "nscanned" : 23240518,
        "nscannedObjects" : 23240518,
        "n" : 10962,
        "millis" : 15047,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}

推荐答案

您的 explain() 输出表明:

1. 有 10962 个对象具有 key.a : 456213154.您的 db.collection.find({"key.a": 456213154}) 查询使用了 key.a 上的索引，并返回了 10962 个对象.

1. There are 10962 objects that have key.a : 456213154. Your db.collection.find({"key.a": 456213154}) query used the index on key.a, and returned 10962 objects.

您的集合中有 0 个对象具有 key.a : 456213154 并且具有 key.b : { $exists : true }.db.collection.find({"key": {"a": 456213154, "b": {"$exists":true}}}) 查询确实在键上使用了您的索引.

There are 0 objects in your collection that have key.a : 456213154 and have key.b : { $exists : true }. The db.collection.find({"key": {"a": 456213154, "b": {"$exists":true}}}) query did use your index on key.

查看每个查询的 n 值 - 这是返回的数字；和 cursor 值 - 如果使用索引，则为 BtreeCursor.在这种情况下，为什么第一个查询需要更长的时间是有道理的，因为它有更多的对象要返回.

See the n value for each query - this is the number returned; and the cursor value - this is BtreeCursor if an index is used. In this case it would make sense why the first query takes much longer, because it has significantly more objects to return.

您确定具有 key.a : 456213154 值的文档也具有 key.b 值吗?

Are you sure that the documents with key.a : 456213154 values also have key.b values?

带有 $exists 参数的查询是检查嵌入文档中存在的错误语法.

The query with the $exists param is the wrong syntax for checking existence within embedded documents.

试试 db.collection.find({ "key.a" : 456213154, "key.b" : { "$exists" : true } }).

这篇关于使用点表示法的 Mongodb 查询速度很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！