最近遇到的一个MongoDB索引顺序的问题---二狗不要跑

最近遇到个mongo慢查问题，查询这样子：

db.tb1.find({status:'normal', lastReviewTime:{$gte:1583038740,$lte:1585285140}}).sort({createdTime:-1}).limit(30)

执行计划如下：

> db.tb1.find({status:'normal', lastReviewTime:{$gte:1583038740,$lte:1585285140}}).sort({createdTime:-1}).limit(30).explain() {         "queryPlanner" : {                 "plannerVersion" : 1,                 "namespace" : "db1.tb1",                 "indexFilterSet" : false,                 "parsedQuery" : {                         "$and" : [                                 {                                         "status" : {                                                 "$eq" : "normal"                                         }                                 },                                 {                                         "lastReviewTime" : {                                                 "$lte" : 1585285140                                         }                                 },                                 {                                         "lastReviewTime" : {                                                 "$gte" : 1583038740                                         }                                 }                         ]                 },                 "winningPlan" : {                         "stage" : "SORT",                         "sortPattern" : {                                 "createdTime" : -1                         },                         "limitAmount" : 30,                         "inputStage" : {                                 "stage" : "SORT_KEY_GENERATOR",                                 "inputStage" : {                                         "stage" : "FETCH",                                         "filter" : {                                                 "$and" : [                                                         {                                                                 "lastReviewTime" : {                                                                         "$lte" : 1585285140                                                                 }                                                         },                                                         {                                                                 "lastReviewTime" : {                                                                         "$gte" : 1583038740                                                                 }                                                         }                                                 ]                                         },                                         "inputStage" : {                                                 "stage" : "IXSCAN",                                                 "keyPattern" : {                                                         "status" : 1,                                                         "type" : 1,                                                         "executionTime" : 1                                                 },                                                 "indexName" : "idx_stats_typ_execTime",   # 走的这个索引                                                 "isMultiKey" : false,                                                 "multiKeyPaths" : {                                                         "status" : [ ],                                                         "type" : [ ],                                                         "executionTime" : [ ]                                                 },                                                 "isUnique" : false,                                                 "isSparse" : false,                                                 "isPartial" : false,                                                 "indexVersion" : 2,                                                 "direction" : "forward",                                                 "indexBounds" : {                                                         "status" : [                                                                 "["normal", "normal"]"                                                         ],                                                         "type" : [                                                                 "[MinKey, MaxKey]"                                                         ],                                                         "executionTime" : [                                                                 "[MinKey, MaxKey]"                                                         ]                                                 }                                         }                                 }                         }                 },                 "rejectedPlans" : [                         {                                 "stage" : "SORT",                                 "sortPattern" : {                                         "createdTime" : -1                                 },                                 "limitAmount" : 30,                                 "inputStage" : {                                         "stage" : "SORT_KEY_GENERATOR",                                         "inputStage" : {                                                 "stage" : "FETCH",                                                 "inputStage" : {                                                         "stage" : "IXSCAN",                                                         "keyPattern" : {                                                                 "lastReviewTime" : 1,                                                                 "status" : 1,                                                                 "createdTime" : -1                                                         },                                                         "indexName" : "lastReviewTime_1_status_1_createdTime_-1",                                                         "isMultiKey" : false,                                                         "multiKeyPaths" : {                                                                 "lastReviewTime" : [ ],                                                                 "status" : [ ],                                                                 "createdTime" : [ ]                                                         },                                                         "isUnique" : false,                                                         "isSparse" : false,                                                         "isPartial" : false,                                                         "indexVersion" : 2,                                                         "direction" : "forward",                                                         "indexBounds" : {                                                                 "lastReviewTime" : [                                                                         "[1583038740.0, 1585285140.0]"                                                                 ],                                                                 "status" : [                                                                         "["normal", "normal"]"                                                                 ],                                                                 "createdTime" : [                                                                         "[MaxKey, MinKey]"                                                                 ]                                                         }                                                 }                                         }                                 }                         },                         {                                 "stage" : "SORT",                                 "sortPattern" : {                                         "createdTime" : -1                                 },                                 "limitAmount" : 30,                                 "inputStage" : {                                         "stage" : "SORT_KEY_GENERATOR",                                         "inputStage" : {                                                 "stage" : "FETCH",                                                 "inputStage" : {                                                         "stage" : "IXSCAN",                                                         "keyPattern" : {                                                                 "lastReviewTime" : -1,                                                                 "status" : 1                                                         },                                                         "indexName" : "lastReviewTime_-1_status_1",                                                         "isMultiKey" : false,                                                         "multiKeyPaths" : {                                                                 "lastReviewTime" : [ ],                                                                 "status" : [ ]                                                         },                                                         "isUnique" : false,                                                         "isSparse" : false,                                                         "isPartial" : false,                                                         "indexVersion" : 2,                                                         "direction" : "forward",                                                         "indexBounds" : {                                                                 "lastReviewTime" : [                                                                         "[1585285140.0, 1583038740.0]"                                                                 ],                                                                 "status" : [                                                                         "["normal", "normal"]"                                                                 ]                                                         }                                                 }                                         }                                 }                         }                 ]         },         "ok" : 1 }

这种情况下，我们的索引顺序需要注意下，这样写：

{精确匹配字段,排序字段,范围查询字段} 这样的索引排序会更为高效

db.tb1.createIndex({status:1,createdTime:-1,lastReviewTime:1},{background:true})

加完索引后，可以发现查询速度有质的飞越了。

---来自腾讯云社区的---二狗不要跑