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屋!