为什么索引不能覆盖包含$或表达式的查询?
我在MongoDB 3.2中做了几个“覆盖查询”测试,注意到它不包含一个包含$or
表达式的查询(只在同一个字段上?)。但是,如果在同一个查询中用$or
表达式代替$in
表达式,则它工作得很好。
我用于测试的指数如下:
db.test_collection.createIndex({ a: 1, b: 1, c: 1 });
这是$or
查询不是通过索引
db.test_collection.find({
a: "string",
$or: [
{ b: true },
{ b: false },
],
c: "string"
}, { a: 1, b: 1, c: 1, _id: 0 });
覆盖,这是$in
查询is covered
db.test_collection.find({
a: "string",
b: { $in: [ true, false ] },
c: "string"
}, { a: 1, b: 1, c: 1, _id: 0 });
字段是相同的,投影是相同的。那么,为什么索引不能覆盖$or
查询?
这是explain()
输出为$or
查询:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.test_collection",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"$or" : [
{
"b" : {
"$eq" : true
}
},
{
"b" : {
"$eq" : false
}
}
]
},
{
"a" : {
"$eq" : "string"
}
},
{
"c" : {
"$eq" : "string"
}
}
]
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"a" : 1,
"b" : 1,
"c" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"b" : {
"$eq" : true
}
},
{
"b" : {
"$eq" : false
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1,
"c" : 1
},
"indexName" : "a_1_b_1_c_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[\"string\", \"string\"]"
],
"b" : [
"[MinKey, MaxKey]"
],
"c" : [
"[\"string\", \"string\"]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 9,
"totalKeysExamined" : 2,
"totalDocsExamined" : 2,
"executionStages" : {
"stage" : "PROJECTION",
"nReturned" : 2,
"executionTimeMillisEstimate" : 10,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"transformBy" : {
"a" : 1,
"b" : 1,
"c" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"b" : {
"$eq" : true
}
},
{
"b" : {
"$eq" : false
}
}
]
},
"nReturned" : 2,
"executionTimeMillisEstimate" : 10,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 10,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1,
"b" : 1,
"c" : 1
},
"indexName" : "a_1_b_1_c_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[\"string\", \"string\"]"
],
"b" : [
"[MinKey, MaxKey]"
],
"c" : [
"[\"string\", \"string\"]"
]
},
"keysExamined" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "VM-TOMLIN-HP",
"port" : 27017,
"version" : "3.2.6",
"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
},
"ok" : 1
}
,这是explain()
输出为$in
查询:由于的方式
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.test_collection",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : "string"
}
},
{
"c" : {
"$eq" : "string"
}
},
{
"b" : {
"$in" : [
false,
true
]
}
}
]
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"a" : 1,
"b" : 1,
"c" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1,
"c" : 1
},
"indexName" : "a_1_b_1_c_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[\"string\", \"string\"]"
],
"b" : [
"[false, false]",
"[true, true]"
],
"c" : [
"[\"string\", \"string\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 0,
"totalKeysExamined" : 2,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"transformBy" : {
"a" : 1,
"b" : 1,
"c" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1,
"b" : 1,
"c" : 1
},
"indexName" : "a_1_b_1_c_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[\"string\", \"string\"]"
],
"b" : [
"[false, false]",
"[true, true]"
],
"c" : [
"[\"string\", \"string\"]"
]
},
"keysExamined" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "VM-TOMLIN-HP",
"port" : 27017,
"version" : "3.2.6",
"gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
},
"ok" : 1
}
的$或处理运算符。按照video从官方MongoDB的过程:
的$或运营商需要一个数组,数组包含的文件,每个 文件被视为一个单独的查询和$或操作任何匹配任何文件匹配 这些查询在数组内,所以它实际上计算这些查询的联合。
此外,作为documentation状态:
MongoDB的使用索引来评估一个$或表达,所有的 条款在$或表达必须通过索引来支持。 否则,MongoDB将执行收集扫描。
db.test_collection.find({ $or: [ { a: "string", b: true, c: "string"}, { a: "string", b: false, c: "string" }, ], }, { a: 1, b: 1, c: 1, _id: 0 });
此外,文件直接recommends使用$在操作来检查同一领域的平等:
,如果你重写了查询,像这样给定的指标将用于。
当然,它建议'$ in',并且你的例子确实有效,但我没有得到区别。特别是在你的'$或'查询和我的。对我来说,这些都是完全相同的查询。我想知道为什么它不起作用(因为这不在文档中描述),而不仅仅是它没有。我已经知道从测试:) – Tom
据我了解官方mongodb课程的[视频](https://www.youtube.com/watch?v=IE2d5DBjNsQ),区别在于命令的目的。 ** $或**运算符中的每个表达式都被视为一个单独的查询,并返回其结果的联合,而不是**中的** $,它只是将文档中的某个字段与一组值相匹配。 –
视频的最后一分钟实际上已经清理了一些东西。他们应该在文档中提及:/感谢您找到并链接它。如果您可以重写您的答案以包含此信息,我会很乐意将其标记为“已接受”:) – Tom
您使用的是什么MongoDB版本?另外,你可以发布两个查询的'explain()'结果吗? –
@KevinAdistambha我正在使用3.2.6。在这个问题中增加了这个以及'explain()'输出。 – Tom