MongoDB-与查询不同的是不使用索引 [英] MongoDB - distinct with query doesn't use indexes

问题描述

使用Mongo 3.2.

Using Mongo 3.2.

假设我有一个具有以下模式的集合:

Let's say I have a collection with this schema:

{ _id: 1, type: a, source: x },
{ _id: 2, type: a, source: y },
{ _id: 3, type: b, source: x },
{ _id: 4, type: b, source: y }

当然，我的数据库更大，并且具有更多的类型和来源.

Of course that my db is much larger and with many more types and sources.

我创建了4个类型和源索引组合(即使1个索引就足够了):

I have created 4 indexes combinations of type and source (even though 1 should be enough):

{type: 1}
{source: 1},
{type: 1, source: 1},
{source: 1, type: 1}

现在，我正在运行这个独特的查询:

Now, I am running this distinct query:

db.test.distinct("source", {type: "a"})

问题在于此查询花费的时间要多得多. 如果我使用runCommand运行它:

The problem is that this query takes much more time that it should take. If I run it with runCommand:

db.runCommand({distinct: 'test', key: "source", query: {type: "a"}})

这是我得到的结果:

{
    "waitedMS": 0,
    "values": [
        "x",
        "y"
    ],
    "stats": {
        "n": 19400840,
        "nscanned": 19400840,
        "nscannedObjects": 19400840,
        "timems": 14821,
        "planSummary": "IXSCAN { type: 1 }"
    },
    "ok": 1
}

由于某种原因，mongo在查询阶段仅使用type: 1索引. 它也应该在不同的阶段使用索引. 这是为什么?使用{type: 1, source: 1}索引会更好，不是吗?现在，它正在扫描具有索引的所有type: a文档.

For some reason, mongo use only the type: 1 index for the query stage. It should use the index also for the distinct stage. Why is that? Using the {type: 1, source: 1} index would be much better, no? right now it is scanning all the type: a documents while it has an index for it.

我做错什么了吗?对于这种与众不同的东西，我有更好的选择吗?

Am I doing something wrong? Do I have a better option for this kind of distinct?

推荐答案

正如Alex所提到的，显然MongoDB目前不支持此功能. 有一个未解决的问题: https://jira.mongodb.org/browse/SERVER-19507

As Alex mentioned, apparently MongoDB doesn't support this right now. There is an open issue for it: https://jira.mongodb.org/browse/SERVER-19507

这篇关于MongoDB-与查询不同的是不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！