mongodb-索引

时间：2021-10-17 20:06:03

说明：创建索引时，列名:int 中的int数字指的是正序或者倒序，如果是1表明是正序，-1表示倒序

1、查询collection上的索引

db.users.getIndexes()

2、查询当前的db的所有collection所拥有的索引

db.getCollectionNames().forEach(function(collection) {

   indexes = db[collection].getIndexes();

   print("Indexes for " + collection + ":");

   printjson(indexes);

});

3、创建索引(background使创建索引的过程在后台完成，在索引创建期间可以处理其他请求，如果不加会阻塞创建索引期间的所有请求)

db.test.createIndex( {name: 1},{background:true} )

4、删除索引

db.test.dropIndex( {name :1} ) #删除name字段上的索引
db.test.dropIndexes()  #删除collection下的所有索引除_id 这个主键索引

5、重建索引

db.test.reIndex( {name :1} )

6、Embedded Field创建索引

{ "_id" : ObjectId("57a1abd6369a01a77bb7f007"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 } }

db.test.createIndex( { "contacts.phone" :1 },{ background:true} )

7、Embedded Field创建索引

{ "_id" : ObjectId("57a1abd6369a01a77bb7f007"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 } }

db.test.createIndex( { "contacts.phone" :1 },{ background:true} )

8、查询执行计划

pandatv_msg:PRIMARY> db.test.find( { "contacts.phone": 18651866297} ).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "gaoquan.test",
"indexFilterSet" : false,
"parsedQuery" : {
"contacts.phone" : {
"$eq" : 18651866297
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"contacts.phone" : 1
},
"indexName" : "contacts.phone_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"contacts.phone" : [
"[18651866297.0, 18651866297.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "mongo3v.notify.bjac.pdtv.it",
"port" : 27017,
"version" : "3.2.8",
"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok" : 1
}
pandatv_msg:PRIMARY> db.test.find( { "contacts.address": "beijing"} ).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "gaoquan.test",
"indexFilterSet" : false,
"parsedQuery" : {
"contacts.address" : {
"$eq" : "beijing"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"contacts.address" : {
"$eq" : "beijing"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "mongo3v.notify.bjac.pdtv.it",
"port" : 27017,
"version" : "3.2.8",
"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
},
"ok" : 1
}

9、Embedded Document创建索引

{ "_id" : ObjectId("57a1abd6369a01a77bb7f007"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 } }

db.test.createIndex( { "contacts.phone" :1 },{ background:true} )

10、创建组合索引

{ "_id" : ObjectId("57a1afe5369a01a77bb7f008"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 }, "blog" : "http://home.cnblogs.com/u/gaoquan/", "company" : "pandatv" }

db.test.createIndex( { blog :1 , company: 1},{ background:true} )

11、创建前缀索引

{ "_id" : ObjectId("57a1afe5369a01a77bb7f008"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 }, "blog" : "http://home.cnblogs.com/u/gaoquan/", "company" : "pandatv" }

db.test.createIndex( { blog :1 , company: 1},{ background:true} )

12、创建前缀索引

{ "_id" : ObjectId("57a1afe5369a01a77bb7f008"), "info" : { "age" : 32, "weight" : "75KG", "height" : "174cm" }, "contacts" : { "address" : "beijing", "phone" : 18651866297 }, "blog" : "http://home.cnblogs.com/u/gaoquan/", "company" : "pandatv", "hometown": "neimeng" }

db.test.createIndex( { blog :1 , company: 1, hometown: 1},{ background:true} )

说明：和关系型中的索引一样，组合索引中支持前缀索引，如上面索引，查询中包含blog，或者blog,company，或者blog,company,hometown都能使用到索引，如果是hometown则不能使用索引



13、创建多key索引


{ _id: 5, type: "food", item: "aaa", ratings: [ 5, 8, 9 ] }

{ _id: 6, type: "food", item: "bbb", ratings: [ 5, 9 ] }

{ _id: 7, type: "food", item: "ccc", ratings: [ 9, 5, 8 ] }

{ _id: 8, type: "food", item: "ddd", ratings: [ 9, 5 ] }

{ _id: 9, type: "food", item: "eee", ratings: [ 5, 9, 5 ] }


db.test.createIndex( { ratings: 1 } )


{ _id: 1,  a: [ 1, 2 ], b: [ 1,2 ] ,category: "test "}这种类型的不能创建{ a: 1, b:1 }


14、统计索引大小


db.collection.totalIndexSize()



15、强制使用索引


cursor.hint()
db.users.find().hint( {age:1} )
db.users.find().max( { item: 'apple',type: 'jonagold' } ).hint( { item:1,type:1 })


16、查询最大值


cursor.hint()
db.users.find().hint( {age:1} )




相关文章