首页 > 代码库 > mongo索引

mongo索引

2024-10-03 05:05:02   214人阅读

索引
自动创建和手工创建

技术分享 
db.stu.drop();
db.stu.insert({"name":"张三","sex":"男","age":18,"score":70,"address":"河南"});
db.stu.insert({"name":"李四","sex":"女","age":20,"score":60,"address":"山东"});
db.stu.insert({"name":"王五","sex":"男","age":17,"score":44,"address":"江苏"});
db.stu.insert({"name":"赵六","sex":"男","age":21,"score":80,"address":"山东"});
db.stu.insert({"name":"孙七","sex":"女","age":23,"score":50,"address":"湖北"});
db.stu.insert({"name":"tom","sex":"男","age":24,"score":20,"address":"海南"});
db.stu.insert({"name":"lucy","sex":"女","age":21,"score":62,"address":"浙江"});
db.stu.insert({"name":"jack","sex":"男","age":20,"score":90,"address":"美国"});
db.stu.insert({"name":"smith","sex":"男","age":19,"score":88,"address":"美国"});
View Code

查询默认状态下的stu集合索引内容
db.stu.getIndexes();

> db.stu.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.stu"
        }
]

v:索引版本
_id:1 表示升序

索引创建
db.集合.ensureIndex({列:1})
1表示升序 -1降序
db.stu.ensureIndex({"age":-1});

技术分享 
> db.stu.ensureIndex({"age":-1});
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
db.stu.getIndexes();
> db.stu.getIndexes();
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "test.stu"
        },
        {
                "v" : 2,
                "key" : {
                        "age" : -1
                },
                "name" : "age_-1",
                "ns" : "test.stu"
        }
]
View Code

这时索引名是自动命名的。命名规范: 字段名称_索引排序模式
索引使用分析

技术分享 
db.stu.find({"age":21}).explain();
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.stu",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "age" : {
                "$eq" : 21
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "age" : -1
                },
                "indexName" : "age_-1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "age" : [
                        "[21.0, 21.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "centos1",
        "port" : 27000,
        "version" : "3.4.4",
        "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
    },
    "ok" : 1
}
View Code

"stage" : "IXSCAN"

在非索引的列上

技术分享 
> db.stu.find({"score":{"$gt":60}}).explain();
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.stu",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "score" : {
                "$gt" : 60
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "score" : {
                    "$gt" : 60
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "centos1",
        "port" : 27000,
        "version" : "3.4.4",
        "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
    },
    "ok" : 1
}
View Code

"stage" : "COLLSCAN"

db.stu.find({"$or":[
    {"age":{"$gt":21}},
    {"score":{"$gt":60}}
    ]}).explain();

此时age上有索引,score上没有，使用的是全表扫描
"stage" : "COLLSCAN",

这时可以使用符合索引
db.stu.ensureIndex({"age":-1,"score":-1},{"name":"age_-1_score_-1"})

执行查询
db.stu.find({"$or":[
    {"age":{"$gt":21}},
    {"score":{"$gt":60}}
    ]}).explain();

"stage" : "COLLSCAN",
db.stu.find({"$or":[
    {"age":21},
    {"score":80}
    ]}).explain();
依然
"stage" : "COLLSCAN"

强制使用索引

 

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

"stage" : "IXSCAN"
db.stu.find({"$or":[
    {"age":21},
    {"score":80}
    ]}).hint({"age":-1,"score":1}).explain();
会报错
db.stu.find({"$or":[
    {"age":{"$gt":21}},
    {"score":{"$gt":60}}
    ]}).hint({"age":-1,"score":-1}).explain();

stage" : "IXSCAN"

 

删除索引
db.stu.dropIndex({"age":-1,"score":-1});
删除全部索引(除了_id外)
db.stu.dropIndexes();

 

mongo索引


联系
我们