京公网安备11010502063487号候选计划各试跑多少文档?谁来决定赢家?这篇用 allPlansExecution 看每个候选的 nReturned 和 works,改参数看试跑如何缩短,并验证排序阻塞和结果很少时的情形。
test> function chain(p) { if (!p) return ""; var s = p.stage + (p.indexName ? "(" + p.indexName + ")" : ""); if (p.inputStages) s += "[" + p.inputStages.map(chain).join(" | ") + "]"; if (p.inputStage) s += " > " + chain(p.inputStage); if (p.shards) s += "{shards}"; return s }; function ex(e) { var wp = e.queryPlanner.winningPlan; var root = wp.queryPlan || wp; var st = e.executionStats || {}; return {plan: chain(root), nReturned: st.nReturned, keysExamined: st.totalKeysExamined, docsExamined: st.totalDocsExamined, ms: st.executionTimeMillis} }; function plans(e) { var all = e.executionStats.allPlansExecution; var win = e.queryPlanner.winningPlan; return all.map(function(p) { var s = p.executionStages; var chain = []; while (s) { chain.push(s.stage + (s.indexName ? "(" + s.indexName + ")" : "")); s = s.inputStage }; return {plan: chain.reverse().join(" > "), nReturned: p.nReturned, works: p.executionStages.works, keys: p.totalKeysExamined, docs: p.totalDocsExamined} }) }; var e = db.t.find({a: 3, b: 8}).explain("allPlansExecution"); ({winner: chain(e.queryPlanner.winningPlan), rejected: e.queryPlanner.rejectedPlans.map(p => chain(p)), trial: plans(e)})
{
winner: 'FETCH > IXSCAN(b_1)',
rejected: [ 'FETCH > IXSCAN(a_1)' ],
trial: [
{
plan: 'IXSCAN(b_1) > FETCH',
nReturned: 101,
works: 101,
keys: 101,
docs: 101
},
{
plan: 'IXSCAN(a_1) > FETCH',
nReturned: 1,
works: 101,
keys: 101,
docs: 101
}
]
}一条查询有多个候选索引时,每个候选计划试跑一小段,看同样的 works 里谁返回的结果更多。b_1 返回 101 条而 a_1 只返回 1 条,所以 b_1 赢。
test> db.adminCommand({getParameter: 1, internalQueryPlanEvaluationMaxResults: 1, internalQueryPlanEvaluationWorks: 1, internalQueryPlanEvaluationCollFraction: 1, internalQueryCacheEvictionRatio: 1, internalQueryCacheWorksGrowthCoefficient: 1})
{
internalQueryCacheEvictionRatio: 10,
internalQueryCacheWorksGrowthCoefficient: 2,
internalQueryPlanEvaluationCollFraction: 0.3,
internalQueryPlanEvaluationMaxResults: 101,
internalQueryPlanEvaluationWorks: 10000,
ok: 1
}由参数控制:最多取 101 条结果(internalQueryPlanEvaluationMaxResults)、最多 10000 次 works,或不超过集合的 30%。
test> db.adminCommand({setParameter: 1, internalQueryPlanEvaluationMaxResults: 10})
{ was: 101, ok: 1 }
test> db.t.find({a: 3, b: 8}).explain("allPlansExecution") // MaxResults = 10
[
{
plan: 'IXSCAN(b_1) > FETCH',
nReturned: 10,
works: 10,
keys: 10,
docs: 10
},
{
plan: 'IXSCAN(a_1) > FETCH',
nReturned: 1,
works: 10,
keys: 10,
docs: 10
}
]
test> db.adminCommand({setParameter: 1, internalQueryPlanEvaluationMaxResults: 101})
{ was: 10, ok: 1 }把 MaxResults 调成 10,试跑在第一个计划返回 10 条时结束,其它候选陪跑到同样的 works。
test> function chain(p) { if (!p) return ""; var s = p.stage + (p.indexName ? "(" + p.indexName + ")" : ""); if (p.inputStages) s += "[" + p.inputStages.map(chain).join(" | ") + "]"; if (p.inputStage) s += " > " + chain(p.inputStage); if (p.shards) s += "{shards}"; return s }; function ex(e) { var wp = e.queryPlanner.winningPlan; var root = wp.queryPlan || wp; var st = e.executionStats || {}; return {plan: chain(root), nReturned: st.nReturned, keysExamined: st.totalKeysExamined, docsExamined: st.totalDocsExamined, ms: st.executionTimeMillis} }; function plans(e) { var all = e.executionStats.allPlansExecution; var win = e.queryPlanner.winningPlan; return all.map(function(p) { var s = p.executionStages; var chain = []; while (s) { chain.push(s.stage + (s.indexName ? "(" + s.indexName + ")" : "")); s = s.inputStage }; return {plan: chain.reverse().join(" > "), nReturned: p.nReturned, works: p.executionStages.works, keys: p.totalKeysExamined, docs: p.totalDocsExamined} }) }; var e = db.t.find({a: 3}).sort({c: 1}).explain("allPlansExecution"); ({winner: chain(e.queryPlanner.winningPlan), trial: plans(e)})
{
winner: 'FETCH > IXSCAN(c_1)',
trial: [
{
plan: 'IXSCAN(c_1) > FETCH',
nReturned: 101,
works: 497,
keys: 497,
docs: 497
},
{
plan: 'IXSCAN(a_1) > FETCH > SORT',
nReturned: 0,
works: 497,
keys: 497,
docs: 497
}
]
}
test> 加 limit(10) 之后
{
plan: 'LIMIT > FETCH > IXSCAN(c_1)',
nReturned: 10,
keysExamined: 56,
docsExamined: 56,
ms: 0
}内存 SORT 要读完所有输入才会输出第一条,试跑期间返回 0 条;按索引顺序扫的计划边扫边出结果,在竞选里更占便宜。
test> function chain(p) { if (!p) return ""; var s = p.stage + (p.indexName ? "(" + p.indexName + ")" : ""); if (p.inputStages) s += "[" + p.inputStages.map(chain).join(" | ") + "]"; if (p.inputStage) s += " > " + chain(p.inputStage); if (p.shards) s += "{shards}"; return s }; function ex(e) { var wp = e.queryPlanner.winningPlan; var root = wp.queryPlan || wp; var st = e.executionStats || {}; return {plan: chain(root), nReturned: st.nReturned, keysExamined: st.totalKeysExamined, docsExamined: st.totalDocsExamined, ms: st.executionTimeMillis} }; function plans(e) { var all = e.executionStats.allPlansExecution; var win = e.queryPlanner.winningPlan; return all.map(function(p) { var s = p.executionStages; var chain = []; while (s) { chain.push(s.stage + (s.indexName ? "(" + s.indexName + ")" : "")); s = s.inputStage }; return {plan: chain.reverse().join(" > "), nReturned: p.nReturned, works: p.executionStages.works, keys: p.totalKeysExamined, docs: p.totalDocsExamined} }) }; var e = db.t.find({c: 5000, a: {$gte: 0}}).explain("allPlansExecution"); ({winner: chain(e.queryPlanner.winningPlan), trial: plans(e)})
{
winner: 'FETCH > IXSCAN(c_1)',
trial: [
{
plan: 'IXSCAN(c_1) > FETCH',
nReturned: 2,
works: 3,
keys: 2,
docs: 2
},
{
plan: 'IXSCAN(a_1) > FETCH',
nReturned: 0,
works: 3,
keys: 3,
docs: 3
}
]
}某个计划几次 works 就把所有结果返回完,试跑立刻结束,它直接获胜。
hint(a_1): 16 ms hint(b_1): 0.5 ms hint(c_1): 135 ms 不 hint(优化器选): 0.3 ms
逐个 hint 的完整耗时是 16 / 0.5 / 135 ms,优化器自己选的 0.3 ms,选中了最快的计划。