京公网安备11010502063487号explain 的 queryPlanner、executionStats、allPlansExecution 有什么区别?哪一种会真的执行?这篇用 10 万个文档和三个候选索引,在同一条查询上把三种模式的输出逐个拆开,再验证 update / delete / aggregate 的 explain。
test> db.t.find({a: 5, b: {$gte: 500}}).explain("queryPlanner")
{
keys: [
'explainVersion',
'queryPlanner',
'queryShapeHash',
'command',
'serverInfo',
'serverParameters',
'ok'
],
plan: 'FETCH > IXSCAN(a_1_b_1)',
rejected: [ 'FETCH > IXSCAN(b_1)', 'FETCH > IXSCAN(a_1)' ],
hasExecutionStats: false
}默认模式只做计划选择,不执行查询,输出里没有 executionStats;能看到赢家(这里是复合索引 a_1_b_1)和落选的计划。
test> db.t.find({a: 5, b: {$gte: 500}}).explain("executionStats")
{
plan: 'FETCH > IXSCAN(a_1_b_1)',
nReturned: 500,
keysExamined: 500,
docsExamined: 500,
ms: 1
}
test> executionStats.executionStages 沿 inputStage 往下展开
[
{ stage: 'FETCH', nReturned: 500, works: 501, advanced: 500 },
{ stage: 'IXSCAN', nReturned: 500, works: 501, advanced: 500 }
]真的执行查询,给出 nReturned、totalKeysExamined、totalDocsExamined 和每个阶段的 works / advanced。
test> db.t.find({a: 5, b: {$gte: 500}}).explain("allPlansExecution")
{
winner: 'classic',
candidates: [
{
nReturned: 101,
keysExamined: 101,
docsExamined: 101,
works: 101
},
{ nReturned: 0, keysExamined: 101, docsExamined: 101, works: 101 },
{ nReturned: 50, keysExamined: 101, docsExamined: 101, works: 101 }
]
}在 executionStats 之上多出 allPlansExecution:每个候选计划试跑的统计,可以看到赢家在同样的 works 里返回得最多。
test> db.t.countDocuments({})
100000
test> var e = db.runCommand({explain: {update: "t", updates: [{q: {a: 5, b: 505}, u: {$set: {z: 1}}, multi: true}]}, verbosity: "executionStats"}); ({stage: e.executionStats.executionStages.stage, nWouldModify: e.executionStats.executionStages.nWouldModify, docsExamined: e.executionStats.totalDocsExamined})
{ stage: 'UPDATE', nWouldModify: 100, docsExamined: 100 }
test> var e = db.runCommand({explain: {delete: "t", deletes: [{q: {a: 5}, limit: 0}]}, verbosity: "executionStats"}); ({stage: e.executionStats.executionStages.stage, nWouldDelete: e.executionStats.executionStages.nWouldDelete, docsExamined: e.executionStats.totalDocsExamined})
{ stage: 'BATCHED_DELETE', nWouldDelete: 1000, docsExamined: 1000 }
test> db.t.countDocuments({}) + " 个文档;有 z 字段的 " + db.t.countDocuments({z: {$exists: true}}) + " 个"
100000 个文档;有 z 字段的 0 个对 update、delete 做 explain 只给出会修改 / 删除多少文档(nWouldModify、nWouldDelete),数据不变。
test> db.t.explain("executionStats").aggregate([{$match: {a: 5, b: {$gte: 500}}}, {$group: {_id: "$a", n: {$sum: 1}}}])
{
stages: '(没有 stages 字段)',
plan: 'GROUP > PROJECTION_COVERED > IXSCAN(a_1_b_1)',
docsExamined: 0
}aggregate 的 explain 里 $match 与 $group 可以一起下推,整个 pipeline 成为一个计划;字段都在索引里时还是覆盖查询。
# 比较三种模式的输出里有哪些键
test> var b = Object.keys(db.t.find({a: 5, b: {$gte: 500}}).explain("executionStats").executionStats), d = Object.keys(db.t.find({a: 5, b: {$gte: 500}}).explain(true).executionStats); ({queryPlanner_has_executionStats: Object.keys(db.t.find({a: 5, b: {$gte: 500}}).explain("queryPlanner")).includes("executionStats"), executionStats_keys: b, allPlans_extra: d.filter(k => !b.includes(k))})
{
queryPlanner_has_executionStats: false,
executionStats_keys: [
'executionSuccess',
'nReturned',
'executionTimeMillis',
'executionTimeMicros',
'totalKeysExamined',
'totalDocsExamined',
'executionStages'
],
allPlans_extra: [ 'allPlansExecution' ]
}queryPlanner < executionStats < allPlansExecution,信息量逐级增加;explain(true) 等价于 allPlansExecution。