关于 mongodb 索引，一个普通的 find->sort->limit 查询，如何优化掉 SORT stage？

Question

环境： mongodb 3.6

查询如下。

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")

官方文档说索引是有序的，举的例子也是有 $gt 操作的。
https://docs.mongodb.com/manu...
可我这的结果却是：

{
    "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
}

Answer 1

索引的创建方式修改为：db.Examples.createIndex({difficulty:1, rankFactor:1, updatedAt:1})
{精确匹配字段,排序字段,范围查询字段}这样的索引排序会更为高效