我发现,似乎
limit
和
skip
的顺序无关紧要。如果我在
limit
之前指定
skip
,MongoDB会在内部先执行
limit
再执行
skip
。
> db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty()
{
"op" : "command",
"ns" : "archiprod.userinfos",
"command" : {
"aggregate" : "userinfos",
"pipeline" : [
{
"$sort" : {
"updatedAt" : -1
}
},
{
"$limit" : 625
},
{
"$skip" : 600
}
],
},
"keysExamined" : 625,
"docsExamined" : 625,
"cursorExhausted" : true,
"numYield" : 4,
"nreturned" : 25,
"millis" : 25,
"planSummary" : "IXSCAN { updatedAt: -1 }",
/* Some fields are omitted */
}
如果我交换$skip
和$limit
会发生什么?就keysExamined
和docsExamined
而言,我得到了相同的结果。
> db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty()
{
"op" : "command",
"ns" : "archiprod.userinfos",
"command" : {
"aggregate" : "userinfos",
"pipeline" : [
{
"$sort" : {
"updatedAt" : -1
}
},
{
"$skip" : 600
},
{
"$limit" : 25
}
],
},
"keysExamined" : 625,
"docsExamined" : 625,
"cursorExhausted" : true,
"numYield" : 5,
"nreturned" : 25,
"millis" : 71,
"planSummary" : "IXSCAN { updatedAt: -1 }",
}
我随后检查了查询的解释结果。我发现在limit
阶段,totalDocsExamined
已经是625
了。
> db.userinfos.explain('executionStats').aggregate([ { "$sort" : { "updatedAt" : -1 } }, { "$limit" : 625 }, { "$skip" : 600 } ])
{
"stages" : [
{
"$cursor" : {
"sort" : {
"updatedAt" : -1
},
"limit" : NumberLong(625),
"queryPlanner" : {
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"updatedAt" : -1
},
"indexName" : "updatedAt_-1",
}
},
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 625,
"executionTimeMillis" : 22,
"totalKeysExamined" : 625,
"totalDocsExamined" : 625,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 625,
"executionTimeMillisEstimate" : 0,
"works" : 625,
"advanced" : 625,
"docsExamined" : 625,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 625,
"works" : 625,
"advanced" : 625,
"keyPattern" : {
"updatedAt" : -1
},
"indexName" : "updatedAt_-1",
"keysExamined" : 625,
}
}
}
}
},
{
"$skip" : NumberLong(600)
}
]
}
令人惊讶的是,我发现交换$skip
和$limit
得到的explain
结果相同。
> db.userinfos.explain('executionStats').aggregate([ { "$sort" : { "updatedAt" : -1 } }, { "$skip" : 600 }, { "$limit" : 25 } ])
{
"stages" : [
{
"$cursor" : {
"sort" : {
"updatedAt" : -1
},
"limit" : NumberLong(625),
"queryPlanner" : {
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 625,
"executionTimeMillis" : 31,
"totalKeysExamined" : 625,
"totalDocsExamined" : 625,
}
}
},
{
"$skip" : NumberLong(600)
}
]
}
如您所见,尽管我在$skip
之前指定了$limit
,但在explain
结果中,仍然是$limit
在$skip
之前。