mongodb 索引的优先问题

环境

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索引。

总结

自己总结的经验：

创建索引时：
① 短语索引放在前面
②排序索引放中间
③范围索引放后面

个人总结，如有问题，一起探讨！