环境
mongodb:3.4
工具:robo 3t 1.1
前言
今天因为研究了局部索引partial Index
,所以就想优化下,以前创建过的索引!
比如我打算创建如下索引来,更好的满足业务:
db.userop_record.createIndex({user_type:1, createtime:-1, type:1, code:1,status:1},
{partialFilterExpression:{user_type:{$gt:0},type:{$eq:3},createtime:{$gte:new Date("2016/08/05")}}},{background:true})
接着我执行如下语句:
db.userop_record.find({user_type:{$gt:0},createtime:{$gte:new Date("2016/08/08")},type:3,
code:{$in:["G3_05","G3_06"]} , status:1}).sort({createtime:-1}).explain()
结果发现,其居然没有走我刚刚创建的索引,这就纳闷啦!为什么呢?
首先看下,其走的索引是createtime_1
;
我们目前可以这样理解:
mongodb
数据库中有两个索引
① “indexName" : "createtime_1"
② "indexName" : "user_type_1_createtime_-1_type_1_code_1_status_1"
上面的结果就是 走了 ① 索引,拒绝了②索引
经验
因为按照我之前总结的经验,在创建索引时,短语索引
应该放到第一位,其次是排序索引
,接着就是范围索引
。
但是,为什么这次就不管用了呢?
仔细观察后,发现放在第一位好像是范围索引
,user_type
在查询语句中,使用的是$gt
。
于是我调整下:
db.userop_record.createIndex({status:1,createtime:-1, user_type:1,type:1, code:1},
{partialFilterExpression:{user_type:{$gt:0},type:{$eq:3},createtime:{$gte:new Date("2016/08/05")}}},{background:true})
索引名:"indexName" :"status_1_createtime_-1_user_type_1_type_1_code_1"
执行再次执行:
db.userop_record.find({user_type:{$gt:0},createtime:{$gte:new Date("2016/08/08")},type:3, code:"G3_05" , status:1}).sort({createtime:-1}).explain()
结果是如我所愿,走了!
接着我又创建如下索引(为了验证我的,经验对不对):
db.userop_record.createIndex({status:1,type:1,code:1,createtime:-1, user_type:1},
{partialFilterExpression:{user_type:{$gt:0},type:{$eq:3},createtime:{$gte:new Date("2016/08/05")}}},{background:true})
索引名:"indexName" : "status_1_type_1_code_1_createtime_-1_user_type_1"
接着执行看效果:
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "shard4",
"connectionString" : "shard4/10.19.139.216:27048,10.19.30.210:27048",
"serverInfo" : {
"host" : "10-19-139-216",
"port" : 27048,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd" },
"plannerVersion" : 1,
"namespace" : "gg_user_db.userop_record",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ { "code" : { "$eq" : "G3_05" } }, { "status" : { "$eq" : 1.0 } }, { "type" : { "$eq" : 3.0 } }, { "user_type" : { "$gt" : 0.0 } }, { "createtime" : { "$gte" : ISODate("2016-08-07T16:00:00.000Z") } } ] },
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "status" : 1.0, "type" : 1.0, "code" : 1.0, "createtime" : -1.0, "user_type" : 1.0 }, "indexName" : "status_1_type_1_code_1_createtime_-1_user_type_1", "isMultiKey" : false, "multiKeyPaths" : { "status" : [], "type" : [], "code" : [], "createtime" : [], "user_type" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : true, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "status" : [ "[1.0, 1.0]" ], "type" : [ "[3.0, 3.0]" ], "code" : [ "[\"G3_05\", \"G3_05\"]" ], "createtime" : [ "[new Date(9223372036854775807), new Date(1470585600000)]" ], "user_type" : [ "(0.0, inf.0]" ] } } },
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : { "$and" : [ { "code" : { "$eq" : "G3_05" } }, { "status" : { "$eq" : 1.0 } }, { "type" : { "$eq" : 3.0 } }, { "user_type" : { "$gt" : 0.0 } } ] },
"inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "createtime" : 1 }, "indexName" : "createtime_1", "isMultiKey" : false, "multiKeyPaths" : { "createtime" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { "createtime" : [ "[new Date(9223372036854775807), new Date(1470585600000)]" ] } } },
//中间太多 省略
{
"stage" : "FETCH",
"inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "status" : 1.0, "createtime" : -1.0, "user_type" : 1.0, "type" : 1.0, "code" : 1.0 }, "indexName" : "status_1_createtime_-1_user_type_1_type_1_code_1", "isMultiKey" : false, "multiKeyPaths" : { "status" : [], "createtime" : [], "user_type" : [], "type" : [], "code" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : true, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "status" : [ "[1.0, 1.0]" ], "createtime" : [ "[new Date(9223372036854775807), new Date(1470585600000)]" ], "user_type" : [ "(0.0, inf.0]" ], "type" : [ "[3.0, 3.0]" ], "code" : [ "[\"G3_05\", \"G3_05\"]" ] } } }
//省略。。。
可以看出,其走了"indexName" : "status_1_type_1_code_1_createtime_-1_user_type_1"
这个索引,
拒绝了"status_1_createtime_-1_user_type_1_type_1_code_1"
和createtime_1
索引。
总结
自己总结的经验:
创建索引时:
① 短语索引
放在前面
②排序索引
放中间
③范围索引
放后面
个人总结,如有问题,一起探讨!