普通查询
db.getCollection("forums").explain(true)
.find({})
.sort({id:-1})
.skip(10000)
.limit(10)
{
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10.0,
"executionTimeMillis" : 11.0,
"totalKeysExamined" : 10010.0,
"totalDocsExamined" : 10.0,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 10.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10011.0,
"advanced" : 10.0,
"needTime" : 10000.0,
"needYield" : 0.0,
"saveState" : 78.0,
"restoreState" : 78.0,
"isEOF" : 1.0,
"invalidates" : 0.0,
"limitAmount" : 10.0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 10.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10010.0,
"advanced" : 10.0,
"needTime" : 10000.0,
"needYield" : 0.0,
"saveState" : 78.0,
"restoreState" : 78.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"docsExamined" : 10.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "SKIP",
"nReturned" : 10.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10010.0,
"advanced" : 10.0,
"needTime" : 10000.0,
"needYield" : 0.0,
"saveState" : 78.0,
"restoreState" : 78.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"skipAmount" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10010.0,
"advanced" : 10010.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 78.0,
"restoreState" : 78.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"id" : 1.0
},
"indexName" : "id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"id" : [
]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "backward",
"indexBounds" : {
"id" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 10010.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
}
}
},
}
聚合
db.getCollection("forums").explain(true).aggregate([
{$match:{}},
{$sort:{id:-1}},
{$skip:10000},
{$limit:10},
])
{
"stages" : [
{
"$cursor" : {
"query" : {
},
"sort" : {
"id" : -1.0
},
"limit" : NumberLong(10010),
"queryPlanner" : {
"plannerVersion" : 1.0,
"namespace" : "revdol.forums",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"id" : 1.0
},
"indexName" : "id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"id" : [
]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "backward",
"indexBounds" : {
"id" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10010.0,
"executionTimeMillis" : 96.0,
"totalKeysExamined" : 10010.0,
"totalDocsExamined" : 10010.0,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 22.0,
"works" : 10010.0,
"advanced" : 10010.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 88.0,
"restoreState" : 88.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"docsExamined" : 10010.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10010.0,
"advanced" : 10010.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 88.0,
"restoreState" : 88.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"id" : 1.0
},
"indexName" : "id_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"id" : [
]
},
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "backward",
"indexBounds" : {
"id" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 10010.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
},
"allPlansExecution" : [
]
}
}
},
{
"$skip" : NumberLong(10000)
}
],
"ok" : 1.0
}
解析
首先来看explain结果的四个字段：
普通查询：
"nReturned" : 10.0,
"executionTimeMillis" : 13.0,
"totalKeysExamined" : 10010.0,
"totalDocsExamined" : 10.0,
聚合
"nReturned" : 10010.0,
"executionTimeMillis" : 96.0,
"totalKeysExamined" : 10010.0,
"totalDocsExamined" : 10010.0,
nReturned 和 totalDocsExamined 的差距十分巨大，也就导致 executionTimeMillis 的巨大差距。
而导致这个的结果的原因在这儿(删掉了一些无关字段)：
普通查询
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 10.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 10010.0,
"docsExamined" : 10.0,
"inputStage" : {
"stage" : "SKIP",
"nReturned" : 10.0,
"executionTimeMillisEstimate" : 0.0,
"skipAmount" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 0.0,
},
}
}
聚合
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 22.0,
"works" : 10010.0,
"docsExamined" : 10010.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10010.0,
"executionTimeMillisEstimate" : 0.0,
}
}
- 二者都扫描了10010个索引key，
- 聚合 FETCH 了10010个文档，
- 而普通查询只 FETCH 了10个 document。
FETCH 操作会获取完整文档，相对于IXSCAN是十分耗时的操作
总结
- 在聚合操作做 skip 操作会触发 FETCH 数量为
skip_num + limit_num的document，然后在 document 的粒度上 skip。 - 而普通查询则是 IXSCAN 数量为
skip_num + limit_num的索引key，然后在索引的粒度上skip。 - 而究其根本：聚合操作是pipline流水线操作，流水线的每一个stage阶段的输入与输出都是以文档为粒度的（除了流水线的第一个阶段的输入可以触发索引）
- 在使用聚合操作时要多考虑一下是否适合，大范围的 skip 是肯定不行的。
注意
db.getCollection("forums").explain(true).aggregate([
{$match:{}},
{$sort:{id:-1}},
{$skip:10000},
{$limit:10},
])
和
db.getCollection("forums").explain(true).aggregate([
{$sort:{id:-1}},
{$skip:10000},
{$limit:10},
])
是没有区别的
写这篇笔记的原因
原来是做 forums 和 users 的连接查询，返回分页的结果。而在 mongodb 中做连接查询，需要用到 $lookup，而$lookup只能在聚合中使用。
最早的做法是
- $sort
- $lookup
- $skip
- $limit
查询时间 2.4s
改变聚合的stage顺序后
- $sort
- $skip
- $limit
- $lookup
skip为0时，查询时间 0ms，skip为10000时 90ms
改用普通查询，不做连接操作
skip为0时，查询时间 0ms，skip为10000时 9ms
在skip很大时依然不行。因为mongo用的是B-树，不能像数组一样简单的index_num+skip_num就行。只能一个一个访问skip_num次。
终极策略
不使用skip做分页查询，每次做分页查询时，使用上一页的最后一个item的标识，这样就不用skip。而这个也有一个问题：这个策略建立在一页一页的按顺序访问的的前提下的，不能跳页。
还用另外一个常见策略：限制能够访问的页数，例如：只能访问前10页
参考链接
https://docs.mongodb.com/manual/reference/explain-results/
https://www.jianshu.com/p/3e09f660a651
网友评论