京公网安备11010502063487号totalDocsExamined 怎么变成 0?这篇在 20 万个文档上用 explain 一个个验证:要去掉 _id、字段都在索引里,以及什么情况下覆盖失效。
test> db.u.find({email: ...}, {email: 1, _id: 0})
{
plan: 'PROJECTION_COVERED > IXSCAN(email_1)',
nReturned: 1,
keysExamined: 1,
docsExamined: 0,
ms: 2
}
test> db.u.find({email: ...}, {email: 1}) // 投影里带着 _id
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(email_1)',
nReturned: 1,
keysExamined: 1,
docsExamined: 1,
ms: 0
}
test> db.u.find({email: ...}, {email: 1, name: 1, _id: 0}) // name 不在索引里
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(email_1)',
nReturned: 1,
keysExamined: 1,
docsExamined: 1,
ms: 0
}查询条件和返回的字段都在同一个索引里,并且投影去掉 _id(索引里不存 _id),就是 PROJECTION_COVERED,docsExamined 为 0。
test> hint("email_id") 的 db.u.find({email: ...}, {email: 1})
{
plan: 'PROJECTION_COVERED > IXSCAN(email_id)',
nReturned: 1,
keysExamined: 1,
docsExamined: 0,
ms: 3
}
test> db.u.find({age: 30, city: "c10"}, {age: 1, city: 1, _id: 0})
{
plan: 'PROJECTION_COVERED > IXSCAN(age_1_city_1)',
nReturned: 500,
keysExamined: 500,
docsExamined: 0,
ms: 1
}把 _id 放进索引,带 _id 的投影也能覆盖;复合索引覆盖对它所含字段的查询。
test> aggregate [{$match: {age: 30}}, {$group: {_id: "$city", n: {$sum: 1}}}]
{
plan: 'GROUP > PROJECTION_COVERED > IXSCAN(age_1_city_1)',
nReturned: 5,
keysExamined: 2500,
docsExamined: 0,
ms: 1
}
test> aggregate [{$match: {age: 30}}, {$group: {_id: "$name", n: {$sum: 1}}}] // 需要 name
{
plan: 'GROUP > FETCH > IXSCAN(age_1_city_1)',
nReturned: 2500,
keysExamined: 2500,
docsExamined: 2500,
ms: 9
}
test> db.u.explain("executionStats").count({age: 30})
{ stage: '"COUNT"', docsExamined: 0, keysExamined: 2501 }count 走 COUNT 阶段只数索引键;聚合只用到索引字段时,$group 也是覆盖的。
test> db.arr.find({tags: "a3"}, {tags: 1, _id: 0})
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(tags_1)',
nReturned: 2000,
keysExamined: 2000,
docsExamined: 2000,
ms: 1
}
test> db.arr.find({tags: "a3"}, {k: 1, _id: 0})
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(tags_1)',
nReturned: 2000,
keysExamined: 2000,
docsExamined: 2000,
ms: 1
}数组字段上的 multikey 索引里存的是拆开的元素,取不回完整的数组,所以不能覆盖。
覆盖 {age, city, _id:0}: 1.7 ms;需要 FETCH {age, city, name, _id:0}: 2.7 ms
docsExamined: 覆盖 0,FETCH 2500覆盖查询读 0 个文档,耗时 1.7 ms;需要 FETCH 读 2500 个文档,2.7 ms。文档在缓存里时差别有限。
test> db.u.find({age: 30, name: "name30"}, {age: 1, _id: 0}) // 条件里有 name
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(age_1_city_1)',
nReturned: 1,
keysExamined: 2500,
docsExamined: 2500,
ms: 2
}
test> db.u.find({age: 30}, {age: 1, _id: 0}).sort({city: 1}) // city 在索引里,可以覆盖并按索引顺序
{
plan: 'PROJECTION_COVERED > IXSCAN(age_1_city_1)',
nReturned: 2500,
keysExamined: 2500,
docsExamined: 0,
ms: 0
}条件里有索引外的字段、要返回索引外的字段,都要 FETCH;排序字段在索引里则不影响覆盖,还省掉了 SORT。