关于 mongodb 索引，一个普通的 find->sort->limit 查询，如何优化掉 SORT stage？ - 知否问答 - 万象云+社区

db.Examples.createIndex({updatedAt:1, difficulty:1, rankFactor:1}) db.Examples.find({ updatedAt:{ $gte:new ISODate("2019-06-26T10:15:59.330+0800"), $lt: new ISODate("2019-06-27T10:15:59.330+0800") }, difficulty:{$in:[1,2,3]}, }) .sort({"rankFactor":-1}) .limit(1000) .explain("executionStats")

{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "example.Examples", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "updatedAt" : { "$lt" : ISODate("2019-06-27T10:15:59.330+08:00") } }, { "updatedAt" : { "$gte" : ISODate("2019-06-26T10:15:59.330+08:00") } }, { "difficulty" : { "$in" : [ 1, 2, 3 ] } } ] }, "winningPlan" : { "stage" : "SORT", "sortPattern" : { "rankFactor" : -1 }, "limitAmount" : 1000, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "updatedAt" : 1, "difficulty" : 1, "rankFactor" : 1 }, "indexName" : "updatedAt_1_difficulty_1_rankFactor_1", "isMultiKey" : false, "multiKeyPaths" : { "updatedAt" : [ ], "difficulty" : [ ], "rankFactor" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "updatedAt" : [ "[new Date(1561515359330), new Date(1561601759330))" ], "difficulty" : [ "[1.0, 1.0]", "[2.0, 2.0]", "[3.0, 3.0]" ], "rankFactor" : [ "[MinKey, MaxKey]" ] } } } } }, "rejectedPlans" : [ { "stage" : "SORT", "sortPattern" : { "rankFactor" : -1 }, "limitAmount" : 1000, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "updatedAt" : { "$lt" : ISODate("2019-06-27T10:15:59.330+08:00") } }, { "updatedAt" : { "$gte" : ISODate("2019-06-26T10:15:59.330+08:00") } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "difficulty" : 1, "likes" : 1 }, "indexName" : "difficulty_1_likes_1", "isMultiKey" : false, "multiKeyPaths" : { "difficulty" : [ ], "likes" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "difficulty" : [ "[1.0, 1.0]", "[2.0, 2.0]", "[3.0, 3.0]" ], "likes" : [ "[MinKey, MaxKey]" ] } } } } } ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "SORT", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 1, "needTime" : 3, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "rankFactor" : -1 }, "memUsage" : 750, "memLimit" : 33554432, "limitAmount" : 1000, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "FETCH", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "updatedAt" : 1, "difficulty" : 1, "rankFactor" : 1 }, "indexName" : "updatedAt_1_difficulty_1_rankFactor_1", "isMultiKey" : false, "multiKeyPaths" : { "updatedAt" : [ ], "difficulty" : [ ], "rankFactor" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "updatedAt" : [ "[new Date(1561515359330), new Date(1561601759330))" ], "difficulty" : [ "[1.0, 1.0]", "[2.0, 2.0]", "[3.0, 3.0]" ], "rankFactor" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 1, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } } } }, "ok" : 1 }

回答动态