一步步学MongoDB之索引操作篇

最新推荐文章于 2025-12-15 21:10:21 发布

转载最新推荐文章于 2025-12-15 21:10:21 发布 · 135 阅读

文章标签：

本文通过实例展示了如何在MongoDB中使用索引来提高查询效率，包括基本索引的创建、使用及效果对比，以及复合索引的选择和使用技巧。

C:\Users\duansf>mongo
MongoDB shell version: 2.6.6
connecting to: test
> use chenfeng
switched to db chenfeng
> for(var i=0;i<100000;i++){
... db.chenfeng.insert({"name":"duansf"+i,"age":i});
... }
WriteResult({ "nInserted" : 1 })
>
查看一下所有记录:
> db.chenfeng.find()
{ "_id" : ObjectId("5671120a9886ea23576f1043"), "name" : "duansf0", "age" : 0 }
{ "_id" : ObjectId("5671120b9886ea23576f1044"), "name" : "duansf1", "age" : 1 }
{ "_id" : ObjectId("5671120b9886ea23576f1045"), "name" : "duansf2", "age" : 2 }
{ "_id" : ObjectId("5671120b9886ea23576f1046"), "name" : "duansf3", "age" : 3 }
{ "_id" : ObjectId("5671120b9886ea23576f1047"), "name" : "duansf4", "age" : 4 }
{ "_id" : ObjectId("5671120b9886ea23576f1048"), "name" : "duansf5", "age" : 5 }
{ "_id" : ObjectId("5671120b9886ea23576f1049"), "name" : "duansf6", "age" : 6 }
{ "_id" : ObjectId("5671120b9886ea23576f104a"), "name" : "duansf7", "age" : 7 }
{ "_id" : ObjectId("5671120b9886ea23576f104b"), "name" : "duansf8", "age" : 8 }
{ "_id" : ObjectId("5671120b9886ea23576f104c"), "name" : "duansf9", "age" : 9 }
{ "_id" : ObjectId("5671120b9886ea23576f104d"), "name" : "duansf10", "age" : 10
}
{ "_id" : ObjectId("5671120b9886ea23576f104e"), "name" : "duansf11", "age" : 11
}
{ "_id" : ObjectId("5671120b9886ea23576f104f"), "name" : "duansf12", "age" : 12
}
{ "_id" : ObjectId("5671120b9886ea23576f1050"), "name" : "duansf13", "age" : 13
}
{ "_id" : ObjectId("5671120b9886ea23576f1051"), "name" : "duansf14", "age" : 14
}
{ "_id" : ObjectId("5671120b9886ea23576f1052"), "name" : "duansf15", "age" : 15
}
{ "_id" : ObjectId("5671120b9886ea23576f1053"), "name" : "duansf16", "age" : 16
}
{ "_id" : ObjectId("5671120b9886ea23576f1054"), "name" : "duansf17", "age" : 17
}
{ "_id" : ObjectId("5671120b9886ea23576f1055"), "name" : "duansf18", "age" : 18
}
{ "_id" : ObjectId("5671120b9886ea23576f1056"), "name" : "duansf19", "age" : 19
}
Type "it" for more
>
用it命令显示接下来的行，共1万条记录。

查找第一万条文档的数据,查看执行计划进行分析:
> db.chenfeng.find({"name":"duansf"+10000}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 100000,
"nscanned" : 100000,
"nscannedObjectsAllPlans" : 100000,
"nscannedAllPlans" : 100000,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 782,
"nChunkSkips" : 0,
"millis" : 263,
"server" : "XCC-Duanshufeng:27017",
"filterSet" : false
}
>
部分字段解释如下：
cursor ："BasicCursor"的意思是表查找的时候，采用的是表扫描，也就是顺序查找。
nscanned：这个的意思是查找操作一共浏览了10万的数据（文档），也就是整个表的文档数量。
millis：这是我们最关心的时间了，一共用了263毫秒。

建立索引
> db.chenfeng.ensureIndex({"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>

建立索引时，自定义索引名称：
删掉重建:
> db.chenfeng.dropIndex({"name":1})
{ "nIndexesWas" : 2, "ok" : 1 }

建一个索引名为chenfeng的索引:
> db.chenfeng.ensureIndex({"name":1},{name:'chenfeng'})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>

查看执行计划:
> db.chenfeng.find({"name":"duansf"+10000}).explain()
{
"cursor" : "BtreeCursor chenfeng",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 99,
"indexBounds" : {
"name" : [
[
"duansf10000",
"duansf10000"
]
]
},
"server" : "XCC-Duanshufeng:27017",
"filterSet" : false
}
>
millis：建了索引后用了99毫秒,速度提升了很多。

删除索引:
> db.chenfeng.dropIndex({"name":1})
{ "nIndexesWas" : 2, "ok" : 1 }

创建唯一索引(重复的键是不能插入的):
> db.chenfeng.ensureIndex({"name":1},{"unique":true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>

> db.chenfeng.find({"name":"duansf"+5000}).explain()
{
"cursor" : "BtreeCursor name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"duansf5000",
"duansf5000"
]
]
},
"server" : "XCC-Duanshufeng:27017",
"filterSet" : false
}

组合索引例子:
> db.chenfeng.insert({"name":"duanyu","age":26,"birthday":"1986-5-4"})
WriteResult({ "nInserted" : 1 })
> db.chenfeng.insert({"name":"wangyuyan","age":26,"birthday":"1986-4-4"})
WriteResult({ "nInserted" : 1 })
> db.chenfeng.insert({"name":"murongfu","age":26,"birthday":"1986-3-4"})
WriteResult({ "nInserted" : 1 })
> db.chenfeng.insert({"name":"qiaofeng","age":26,"birthday":"1986-2-4"})
WriteResult({ "nInserted" : 1 })
> db.chenfeng.insert({"name":"duanzhengchun","age":26,"birthday":"1986-1-4"})
WriteResult({ "nInserted" : 1 })
> db.chenfeng.insert({"name":"duansf","age":36,"birthday":"1979-7-23"})
WriteResult({ "nInserted" : 1 })
>
>

> db.chenfeng.ensureIndex({"name":1,"birthday":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
>

> db.chenfeng.ensureIndex({"birthday":1,"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
>

> db.chenfeng.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "chenfeng.chenfeng"
},
{
"v" : 1,
"unique" : true,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "chenfeng.chenfeng"
},
{
"v" : 1,
"key" : {
"name" : 1,
"birthday" : 1
},
"name" : "name_1_birthday_1",
"ns" : "chenfeng.chenfeng"
},
{
"v" : 1,
"key" : {
"birthday" : 1,
"name" : 1
},
"name" : "birthday_1_name_1",
"ns" : "chenfeng.chenfeng"
}
]
>

我们采用查询的时候，优化器会采用最优的索引去查询：
> db.chenfeng.find({"birthday":"1979-7-23","name":"duansf"}).explain()
{
"cursor" : "BtreeCursor birthday_1_name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 3,
"nscannedAllPlans" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"birthday" : [
[
"1979-7-23",
"1979-7-23"
]
],
"name" : [
[
"duansf",
"duansf"
]
]
},
"server" : "XCC-Duanshufeng:27017",
"filterSet" : false
}
>

跟Oracle相似，可以强制使用hint:
> db.chenfeng.find({"birthday":"1979-7-23","name":"duansf"}).hint({"birthday":1,"name":1}).explain()
{
"cursor" : "BtreeCursor birthday_1_name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"birthday" : [
[
"1979-7-23",
"1979-7-23"
]
],
"name" : [
[
"duansf",
"duansf"
]
]
},
"server" : "XCC-Duanshufeng:27017",
"filterSet" : false
}
>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-1873355/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-1873355/