最近遇到个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})
加完索引后,可以发现查询速度有质的飞越了。