最近遇到的一个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})

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