深入学习MongoDB之索引与执行计划

mongodb索引
创建索引:
db.getCollection("test").ensureIndex({"name":1});
或者db.getCollection("test").createIndex({"name":1});

{
    "raw" : {
        "shard3/192.168.3.18:20004,192.168.3.3:10004" : {
            "createdCollectionAutomatically" : false,
            "numIndexesBefore" : 1,
            "numIndexesAfter" : 2,
            "ok" : 1.0,
            "$gleStats" : {
                "lastOpTime" : Timestamp(6577988859047444, 1),
                "electionId" : ObjectId("5b48c90f0000000000001c96")
            }
        }
    },
    "ok" : 1.0
}
索引创建成功;

查询索引状态
db.getCollection("test").getIndexes({"name":1});

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "dev.test"
    },
    {
        "v" : 1,
        "key" : {
            "name" : 1.0
        },
        "name" : "name_1",
        "ns" : "dev.test"
    }
]

可以看到_id索引(默认的),和name索引

删除索引
db.getCollection("test").dropIndex({"name":1});
删除结果
{
    "raw" : {
        "shard3/192.168.3.18:20004,192.168.3.3:10004" : {
            "nIndexesWas" : 2,
            "ok" : 1.0,
            "$gleStats" : {
                "lastOpTime" : Timestamp(6577989129630384, 1),
                "electionId" : ObjectId("5b48c90f0000000000001c96")
            }
        }
    },
    "ok" : 1.0
}

再次查询
db.getCollection("test").getIndexes({"name":1});
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "dev.test"
    }
]

索引删除成功;

再次创建索引,并查看执行计划;
db.getCollection("test").ensureIndex({"name":1});
db.getCollection("test").find({name:"张三"}).explain();
{
    "queryPlanner" : {
        "mongosPlannerVersion" : 1,
        "winningPlan" : {
            "stage" : "SINGLE_SHARD",
            "shards" : [
                {
                    "shardName" : "shard3",
                    "connectionString" : "shard3/192.168.3.18:20004,192.168.3.3:10004",
                    "serverInfo" : {
                        "host" : "8ef2e84b1b94",
                        "port" : 27017,
                        "version" : "3.2.0",
                        "gitVersion" : "45d947729a0315accb6d4f15a6b06be6d9c19fe7"
                    },
                    "plannerVersion" : 1,
                    "namespace" : "dev.test",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "name" : {
                            "$eq" : "张三"
                        }
                    },
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "name" : 1.0
                            },
                            "indexName" : "name_1",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,