一步步學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
}
>
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一步步學MongoDB之Windows安裝篇MongoDBWindows
- 一步步學MongoDB之增,刪,改查等操作MongoDB
- MongoDB學習之聚合操作MongoDB
- MongoDB學習之豐富的索引MongoDB索引
- MongoDB之索引（地理資訊索引）MongoDB索引
- MongoDB之索引（全文索引）MongoDB索引
- MongoDB之索引（過期索引）MongoDB索引
- MongoDB之索引（唯一索引）MongoDB索引
- 一步步學MongoDB之插入記錄總結MongoDB
- MongoDB系列一(索引及C#如何操作MongoDB)MongoDB索引C#
- MongoDB之索引（簡介）MongoDB索引
- MongoDB之基本操作MongoDB
- MySQL 學習之索引篇和查詢MySql索引
- MongoDB學習筆記之索引用法和效率分析MongoDB筆記索引
- 一步步學MongoDB之使用迴圈來遍歷遊標MongoDB
- 【MongoDB學習筆記】MongoDB索引那點事MongoDB筆記索引
- MongoDB 索引MongoDB索引
- mongodb索引MongoDB索引
- MongoDB之資料增加操作MongoDB
- mongodb基礎整理篇————常規操作[二]MongoDB
- MongoDB之資料查詢操作MongoDB
- mongodb索引使用MongoDB索引
- mongoDB的索引MongoDB索引
- MongoDB索引概述MongoDB索引
- MongoDB資料庫操作詳解：基礎篇MongoDB資料庫
- 小丸子學MongoDB系列之——安裝MongoDBMongoDB
- 精通MySQL之索引篇，這篇注重練習！MySql索引
- 【MongoDB學習筆記】-使用 MongoDB 進行 CRUD 操作（上）MongoDB筆記
- 【MongoDB學習筆記】-使用 MongoDB 進行 CRUD 操作（下）MongoDB筆記
- MongoDB索引，效能分析MongoDB索引
- MySQL學習之索引MySql索引
- mongoDB操作MongoDB
- MongoDB操作之遍歷集和條件查詢操作MongoDB
- 【mongodb】增刪改mongodb文件（Document）操作,看這一篇就夠了MongoDB
- 小丸子學MongoDB系列之——部署MongoDB副本集MongoDB
- mongodb建立索引和刪除索引和背景索引backgroundMongoDB索引
- mongodb 如何檢視索引MongoDB索引
- MongoDB （ 五 ）高階_索引MongoDB索引