虽然设置了索引，但简单的MongoDB查询非常慢 [英] Simple MongoDB query very slow although index is set

问题描述

我有一个MongoDB集合，可以容纳大约1亿个文档。

I've got a MongoDB collection that holds about 100M documents.

文档基本上是这样的：

_id             : ObjectId("asd1234567890")
_reference_1_id : ObjectId("fgh4567890123")
_reference_2_id : ObjectId("jkl7890123456")
name            : "Test1"
id              : "4815162342"
created_time    : Date( 1331882436000 )
_contexts       : ["context1", "context2"]
...

设置了一些索引，这里是 db.mycoll.getIndexes（）的输出;

There are some indexes set, here's the output of db.mycoll.getIndexes();

[
{
    "v" : 1,
    "key" : {
        "_id" : 1
    },
    "ns" : "mydb.mycoll",
    "name" : "_id_"
},
{
    "v" : 1,
    "key" : {
        "_reference_1_id" : 1,
        "_reference_2_id" : 1,
        "id" : 1
    },
    "unique" : true,
    "ns" : "mydb.mycoll",
    "name" : "_reference_1_id_1__reference_2_id_1_id_1"
},
{
    "v" : 1,
    "key" : {
        "_reference_1_id" : 1,
        "_reference_2_id" : 1,
        "_contexts" : 1,
        "created_time" : 1
    },
    "ns" : "mydb.mycoll",
    "name" : "_reference_1_id_1__reference_2_id_1__contexts_1_created_time_1"
}
]

当我执行类似

When I execute a query like

db.mycoll.find({"_reference_2_id" : ObjectId("jkl7890123456")})

它需要一个多小时（！），直到它完成，无论是否有结果。
任何想法？

it takes over an hour (!) until it's finished, no matter if there are results or not. Any ideas?

更新：
这是

db.mycoll.find({"_reference_2_id" : ObjectId("jkl7890123456")}).explain();

看起来像：

{
"cursor" : "BasicCursor",
"nscanned" : 99209163,
"nscannedObjects" : 99209163,
"n" : 5007,
"millis" : 5705175,
"nYields" : 17389,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}

推荐答案

你没有任何mongo会自动使用的索引，所以它正在进行全表扫描。

You don't have any index that mongo will automatically use for that, so it's doing a full table scan.

As在文档中提到

如果查询中没有[索引]的第一个键，则只有在显式提示时才会使用索引。

If the first key [of the index] is not present in the query, the index will only be used if hinted explicitly.

为什么

如果您有a，b上的索引 - 单独搜索 a - 将自动使用索引。这是因为它是索引的开始（这很快），db可以忽略索引值的其余部分。

If you have an index on a,b - and you search by a alone - an index will automatically be used. This is because it's the start of the index (which is fast to do), the db can just ignore the rest of the index value.

单独搜索 b 时a，b的索引效率低只是因为它没有提供使用以thisfixedstring开头的索引搜索的可能性。

An index on a,b is inefficient when searching by b alone simply because it doesn't give the possibility to use the index searching with "starts with thisfixedstring".

所以，要么：

• 在查询中包含_reference_1_id（可能不相关）


• 或者在_reference_2_id上添加索引（如果您经常按字段查询）


• 或使用提示

提示

现在可能是您的最低成本选项。

Probably your lowest-cost option right now.

添加查询提示以强制使用 _reference_1_id_1__reference_2_id_1_id_1 索引。这可能比全表扫描快得多，但仍然比从您在查询中使用的字段开始的索引慢很多。

Add a query hint to force using your _reference_1_id_1__reference_2_id_1_id_1 index. Which is likely to be a lot faster than a full table scan, but still a lot slower than an index which starts with the field you are using in the query.

即。

db.mycoll
    .find({"_reference_2_id" : ObjectId("jkl7890123456")})
    .hint("_reference_1_id_1__reference_2_id_1_id_1");

这篇关于虽然设置了索引，但简单的MongoDB查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！