为什么MongoDB不同的查询计划显示不同的nReturned值? [英] Why MongoDB different query plans show different nReturned value?
问题描述
我的MongoDB数据库中有一个集合faults，每个文档都具有以下字段:rack_name，timestamp
I have a collection faults in my MongoDB database which every document has these fields: rack_name, timestamp
为了测试和比较性能，我创建了这两个索引:
Just for sake of testing and comparing performances, I have created these two indexes:
rack -> {'rack_name': 1}
和
time -> {'timestamp': 1}
现在，我使用explain()执行以下查询:
Now I executed the following query with explain():
db.faults.find({
'rack_name': {
$in: [ 'providence1', 'helena2' ]
},
'timestamp': {
$gt: 1501548359000
}
})
.explain('allPlansExecution')
这是结果:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "quicktester_clone.faults",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"timestamp" : {
"$gt" : 1501548359000.0
}
},
{
"rack_name" : {
"$in" : [
"helena2",
"providence1"
]
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"timestamp" : {
"$gt" : 1501548359000.0
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"rack_name" : 1
},
"indexName" : "rack",
"isMultiKey" : false,
"multiKeyPaths" : {
"rack_name" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"rack_name" : [
"[\"helena2\", \"helena2\"]",
"[\"providence1\", \"providence1\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"rack_name" : {
"$in" : [
"helena2",
"providence1"
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"timestamp" : 1
},
"indexName" : "time",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"(1501548359000.0, inf.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 43,
"executionTimeMillis" : 1512,
"totalKeysExamined" : 221,
"totalDocsExamined" : 219,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"timestamp" : {
"$gt" : 1501548359000.0
}
},
"nReturned" : 43,
"executionTimeMillisEstimate" : 1431,
"works" : 222,
"advanced" : 43,
"needTime" : 177,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 219,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 219,
"executionTimeMillisEstimate" : 71,
"works" : 221,
"advanced" : 219,
"needTime" : 1,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"rack_name" : 1
},
"indexName" : "rack",
"isMultiKey" : false,
"multiKeyPaths" : {
"rack_name" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"rack_name" : [
"[\"helena2\", \"helena2\"]",
"[\"providence1\", \"providence1\"]"
]
},
"keysExamined" : 221,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
"allPlansExecution" : [
{
"nReturned" : 2,
"executionTimeMillisEstimate" : 31,
"totalKeysExamined" : 221,
"totalDocsExamined" : 221,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"rack_name" : {
"$in" : [
"helena2",
"providence1"
]
}
},
"nReturned" : 2,
"executionTimeMillisEstimate" : 31,
"works" : 221,
"advanced" : 2,
"needTime" : 219,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 221,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 221,
"executionTimeMillisEstimate" : 10,
"works" : 221,
"advanced" : 221,
"needTime" : 0,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"timestamp" : 1
},
"indexName" : "time",
"isMultiKey" : false,
"multiKeyPaths" : {
"timestamp" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"timestamp" : [
"(1501548359000.0, inf.0]"
]
},
"keysExamined" : 221,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
{
"nReturned" : 43,
"executionTimeMillisEstimate" : 1431,
"totalKeysExamined" : 221,
"totalDocsExamined" : 219,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"timestamp" : {
"$gt" : 1501548359000.0
}
},
"nReturned" : 43,
"executionTimeMillisEstimate" : 1431,
"works" : 221,
"advanced" : 43,
"needTime" : 177,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 219,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 219,
"executionTimeMillisEstimate" : 71,
"works" : 221,
"advanced" : 219,
"needTime" : 1,
"needYield" : 0,
"saveState" : 64,
"restoreState" : 64,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"rack_name" : 1
},
"indexName" : "rack",
"isMultiKey" : false,
"multiKeyPaths" : {
"rack_name" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"rack_name" : [
"[\"helena2\", \"helena2\"]",
"[\"providence1\", \"providence1\"]"
]
},
"keysExamined" : 221,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
]
},
"serverInfo" : {
"host" : "dtauto-sna01.mascorp.com",
"port" : 27017,
"version" : "3.4.4",
"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
},
"ok" : 1.0
}
我不明白的两件事:
-
当您查看
AllPlansExecution时，nReturned键具有不同的含义 每个计划的价值.第二个计划(索引:机架)实际上是赢家 计划并返回43个结果，这是 整个查询，但是第一个查询的2个nReturned是什么
When you look at
AllPlansExecutionthenReturnedkey has different value on each plan. Second plan (index: rack) is actually the winner plan and returns 43 results which is the actual return result of the whole query, but what are those 2nReturnedresult from the first
更多挑战到了这一点，这就是为什么第一个计划(索引:
报告了被拒绝的计划的时间)
executionTimeMillis值比获胜者计划高31
executionTimeMillis 1431?
More challenge comes to this point that why the first plan (index:
time which is the rejected plan) has been reported with less
executionTimeMillis value 31 than the winner plan
executionTimeMillis 1431?
这是怎么回事?
推荐答案
MongoDB运行查询优化器以选择获胜计划，并执行获胜计划以完成.在"allPlansExecution"模式下，MongoDB返回描述获胜计划执行情况的统计信息以及在计划选择期间捕获的其他候选计划的统计信息.
MongoDB runs the query optimizer to choose the winning plan and executes the winning plan to completion. In "allPlansExecution" mode, MongoDB returns statistics describing the execution of the winning plan as well as statistics for the other candidate plans captured during plan selection. 在选择计划期间，如果有多个索引可以满足查询要求，则MongoDB将使用所有有效计划进行一次试验，以确定哪个执行的最佳.有关此过程的详细信息，请参见查询计划. During plan selection, if there are more than one index that can satisfy a query, MongoDB will run a trial using all the valid plans to determine which one performed to be the best. See Query Plans for details regarding this process. 从MongoDB 3.4.6开始，计划选择涉及在竞赛"中并行运行候选计划，并查看哪个候选计划首先返回101个结果.在上面的示例中，当获胜计划在比赛中返回101个结果时，失败计划仅处理了2个结果.然后中奖计划将执行完毕.这就是失败计划在统计数据中仅显示 As of MongoDB 3.4.6, the plan selection involves running candidate plans in parallel in a "race", and see which candidate plan returns 101 results first. In your example above, by the time the winning plan returned 101 results in the race, the losing plan managed only 2 results. The winning plan then gets executed to completion. This is the reason why the losing plan shows only 之所以执行此竞赛"，是因为如果有两个外观相同的计划，由于JSON文档的灵活性，MongoDB不知道哪种计划最适合特定的查询(例如SQL不同于表结构)是已知的).当然，MongoDB完全有可能猜错了，最终得到的计划要比绩效计划差，因为这是一个经验过程.因此，最好创建支持查询的索引，这样MongoDB不必猜测.否则，您可以使用 This "race" is performed since if there are two identical-looking plans, MongoDB doesn't know which plan is the best for a particular query due to the flexibility of JSON documents (unlike e.g. SQL where the structure of the tables are known). Of course, it is entirely possible that MongoDB guesses wrong, and end up with a less than performant plan, since it is an empirical process. For this reason, it's best to create indexes that supports your queries so MongoDB doesn't have to guess. Otherwise, you can use 因此: 注释1 :您看到的两个计划都不是很好.中奖计划显示 Note 1: Neither of the two plans you saw were great. The winning plan shows 注释2 :尝试创建复合索引 Note 2: Try creating the compound index 注释3 :请注意，由于已指定 Note 3: Note that since 这篇关于为什么MongoDB不同的查询计划显示不同的nReturned值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
nReturned: 2的原因.nReturned: 2 in the stats.hint()告诉MongoDB特定查询要使用哪个索引.hint() to tell MongoDB which index to use for a certain query.
"nReturned" : 43，"totalKeysExamined" : 221和"totalDocsExamined" : 219.这意味着MongoDB仅需要检查219个文档即可返回其中的43个文档:仅20％的效率.理想情况下，您希望nReturned编号等于totalDocsExamined."nReturned" : 43, "totalKeysExamined" : 221, and "totalDocsExamined" : 219. This means that MongoDB needs to examine 219 documents only to return 43 of them: only 20% efficiency. Ideally, you want to have the nReturned numbers equal to totalDocsExamined.{'rack_name': 1, 'timestamp': 1}.使用相同的查询，您应该获得更好的效率数字.{'rack_name': 1, 'timestamp': 1}. With the same query, you should get a better efficiency number.allPlansExecution，因此所有统计信息将由MongoDB适当地返回给您，以确保其准确性，而与最终结果无关. nReturned结果.这是一个被拒绝的计划，nReturned: 2号可能令人困惑.如果使用executionStats设置，则不会看到此统计信息.首先，allPlansExecution用于微调和确定为什么拒绝某些计划.allPlansExecution was specified, all statistics is duly returned to you by MongoDB for thoroughness, while it has no bearing whatsoever to the final nReturned result. It was a rejected plan, and the nReturned: 2 number can be confusing. You won't see this statistics if you use executionStats setting. Primarily, the allPlansExecution is used for fine-tuning and determining why some plans are rejected.
