索引
自动创建和手工创建
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":"美国"});
查询默认状态下的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"
}
]
这时索引名是自动命名的。命名规范: 字段名称_索引排序模式
索引使用分析
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
}
"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
}
"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();
原文：http://www.cnblogs.com/HKUI/p/6965464.html