MongoDB索引使用

提升数据库检索性能的手段

通过getIndexes()来获取已经存在的索引内容

db.students.getIndexes();

/* 1 */
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "mldn.students"
    }
]

创建自己的索引

范例：创建一个索引，在age列加一个将序索引

db.students.ensureIndex({"age":-1});

/* 1 */
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "mldn.students"
    },
    {
        "v" : 1,
        "key" : {
            "age" : -1.0
        },
        "name" : "age_-1",
        "ns" : "mldn.students"
    }
]

使用解释来分析索引

db.students.find({"age":19}).explain();

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mldn.students",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "age" : {
                "$eq" : 19.0
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "age" : -1.0
                },
                "indexName" : "age_-1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "age" : [ 
                        "[19.0, 19.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "serverInfo" : {
        "host" : "jiqing",
        "port" : 27017,
        "version" : "3.2.17-25-gae6f04a",
        "gitVersion" : "ae6f04a7cd143132fb3185669a8abc5d7c4ab3cc"
    },
    "ok" : 1.0
}

再分析一个没有索引的成员

db.students.find({"score":{"$gt":80}}).explain();

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mldn.students",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "score" : {
                "$gt" : 80.0
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "score" : {
                    "$gt" : 80.0
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : []
    },
    "serverInfo" : {
        "host" : "jiqing",
        "port" : 27017,
        "version" : "3.2.17-25-gae6f04a",
        "gitVersion" : "ae6f04a7cd143132fb3185669a8abc5d7c4ab3cc"
    },
    "ok" : 1.0
}

有索引和没索引的成员一起使用呢？

db.students.find({"$and":[
        {"age":{"$gt":19}},
        {"score":{"$gt":80}}
    ]}).explain();

and的时候用到了索引，or的时候没有用到。

可以定义复合索引

db.students.ensureIndex({"age":-1,"score":-1},{"name":"age_score_index"});

/* 1 */
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "mldn.students"
    },
    {
        "v" : 1,
        "key" : {
            "age" : -1.0
        },
        "name" : "age_-1",
        "ns" : "mldn.students"
    },
    {
        "v" : 1,
        "key" : {
            "age" : -1.0,
            "score" : -1.0
        },
        "name" : "age_score_index",
        "ns" : "mldn.students"
    }
]

强制使用索引

db.students.find({"$or":[
        {"age":{"$gt":19}},
        {"score":{"$gt":80}}
    ]}).hint({"age":-1,"score":-1}).explain();

删除索引

db.students.dropIndex({"age":-1,"score":-1});

删除全部索引，但是不会删除主键索引

db.students.dropIndexes();

唯一索引

主要的目的是使得某个字段上的内容不重复。

范例：创建一个唯一索引

db.students.ensureIndex({"name":1},{"unique":true});

/* 1 */
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "mldn.students"
    },
    {
        "v" : 1,
        "unique" : true,
        "key" : {
            "name" : 1.0
        },
        "name" : "name_1",
        "ns" : "mldn.students"
    }
]

这个时候如果插入的姓名已存在，将会报错。

E11000 duplicate key error collection: mldn.students index: name_1 dup key: { : "张三" }

过期索引

用于手机验证码之类的与时间期限的数据。

db.phones.ensureIndex({"time":1},{expireAfterSeconds:10});


db.phones.insert({"tel":"110","code":"110","time":new Date()});
db.phones.insert({"tel":"111","code":"111","time":new Date()});
db.phones.insert({"tel":"112","code":"112","time":new Date()});
db.phones.insert({"tel":"113","code":"113","time":new Date()});
db.phones.insert({"tel":"114","code":"114","time":new Date()});
db.phones.insert({"tel":"115","code":"115","time":new Date()});
db.phones.insert({"tel":"116","code":"116","time":new Date()});
db.phones.insert({"tel":"117","code":"117","time":new Date()});

设置过期时间10秒，但是会有延迟。时间到了后，会删除。

全文索引

db.news.insert({"title":"aaa","content":"啦啦啦"});
db.news.insert({"title":"bbb","content":"乐乐乐"});
db.news.insert({"title":"ccc","content":"咕咕咕"});
db.news.insert({"title":"ddd","content":"噜噜噜"});
db.news.insert({"title":"eee","content":"驴驴驴"});
db.news.insert({"title":"abc","content":"啦乐噜"});

db.news.ensureIndex({"title":"text","content":"text"});

地理索引

插入数据

db.shop.insert({loc:[10,10]});

db.shop.insert({loc:[11,10]});

db.shop.insert({loc:[10,11]});

db.shop.insert({loc:[12,15]});

db.shop.insert({loc:[16,17]});

db.shop.insert({loc:[90,90]});

db.shop.insert({loc:[120,130]});

范例: 为shop的集合定义2D索引

db.shop.ensureIndex({"loc":"2d"});

near,geoWithin

范例：查询[11,11]附近的点

db.shop.find({"loc":{"$near":[11,11]}});

默认返回100条

db.shop.find({"loc":{"$near":[11,11],"$maxDistance":5}});