mongodb索引使用
在關係型資料庫中索引可以加快對資料輸出,非關係型資料庫也是如此,可以減小磁碟IO訪問,對大資料量有顯著的效果。目前mongodb支援B-Tree,unique,sparse,hash索引
在mongodb叢集中生成資料
[root@node2 mongodb-4.0.8]# ./bin/mongo --host 172.16.8.24 --port 27017
MongoDB shell version v4.0.8
connecting to: mongodb://172.16.8.24:27017/?gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("a1abbd3a-fe32-46ac-a959-4f8a62abd990") }
MongoDB server version: 4.0.8
Server has startup warnings:
wuhan:PRIMARY> for (var i=1;i<=1000;i++) { db.stu.insert({sn:i,name:"student"+i})}
WriteResult({ "nInserted" : 1 })
wuhan:PRIMARY> db.stu.find().count();
1000
wuhan:PRIMARY> db.stu.find();
{ "_id" : ObjectId("5ca3004015fc3dad4a419a75"), "sn" : 1, "name" : "student1" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a76"), "sn" : 2, "name" : "student2" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a77"), "sn" : 3, "name" : "student3" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a78"), "sn" : 4, "name" : "student4" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a79"), "sn" : 5, "name" : "student5" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7a"), "sn" : 6, "name" : "student6" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7b"), "sn" : 7, "name" : "student7" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7c"), "sn" : 8, "name" : "student8" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7d"), "sn" : 9, "name" : "student9" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7e"), "sn" : 10, "name" : "student10" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a7f"), "sn" : 11, "name" : "student11" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a80"), "sn" : 12, "name" : "student12" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a81"), "sn" : 13, "name" : "student13" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a82"), "sn" : 14, "name" : "student14" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a83"), "sn" : 15, "name" : "student15" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a84"), "sn" : 16, "name" : "student16" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a85"), "sn" : 17, "name" : "student17" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a86"), "sn" : 18, "name" : "student18" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a87"), "sn" : 19, "name" : "student19" }
{ "_id" : ObjectId("5ca3004015fc3dad4a419a88"), "sn" : 20, "name" : "student20" }
Type "it" for more
wuhan:PRIMARY>
一.B-Tree索引的使用
1.單列索引使用
wuhan:PRIMARY> db.stu.ensureIndex({sn:1}) --在sn欄位上建立索引
wuhan:PRIMARY> db.stu.find({sn:50}).explain(); --檢視執行計劃是否走索引
wuhan:PRIMARY> db.stu.getIndexKeys() --檢視錶中有多少鍵
[ { "_id" : 1 }, { "sn" : 1 } ]
wuhan:PRIMARY> db.stu.getIndexes() --檢視一個表所有索引
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "tong.stu" }, { "v" : 2, "key" : { "sn" : 1 }, "name" : "sn_1", "ns" : "tong.stu" } ]
wuhan:PRIMARY> db.stu.dropIndex({sn:1}); --刪除sn欄位的索引
wuhan:PRIMARY> db.stu.dropIndexes(); --刪除所有索引
2.多列索引使用
wuhan:PRIMARY> db.stu.ensureIndex({name:1},{name:"IX_name"}) --建立多列索引
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1, "operationTime" : Timestamp(1554188377, 2), "$clusterTime" : { "clusterTime" : Timestamp(1554188377, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY> db.stu.getIndexes() --檢視索引資訊
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "tong.stu" }, { "v" : 2, "key" : { "name" : 1 }, "name" : "IX_name", "ns" : "tong.stu" } ]
wuhan:PRIMARY> db.stu.dropIndex({name:"IX_name"}) --刪除其中一個索引
{ "operationTime" : Timestamp(1554188499, 1), "ok" : 0, "errmsg" : "can't find index with key: { name: \"IX_name\" }", "code" : 27, "codeName" : "IndexNotFound", "$clusterTime" : { "clusterTime" : Timestamp(1554188499, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
3.子文件索引使用
wuhan:PRIMARY> db.shop.insert({name:"Nokia",spc:{weight:120,area:"taiwan"}}) --寫入資料
WriteResult({ "nInserted" : 1 })
wuhan:PRIMARY> db.shop.insert({name:"sanxing",spc:{weight:100,area:"hanguo"}}) --寫入資料
WriteResult({ "nInserted" : 1 })
wuhan:PRIMARY> db.shop.find() --查詢資料
{ "_id" : ObjectId("5ca337ff15fc3dad4a419e5d"), "name" : "Nokia", "spc" : { "weight" : 120, "area" : "taiwan" } }
{ "_id" : ObjectId("5ca3382c15fc3dad4a419e5e"), "name" : "sanxing", "spc" : { "weight" : 100, "area" : "hanguo" } }
wuhan:PRIMARY> db.shop.find({"spc.area":"hanguo"}); --查詢子文件的資料
{ "_id" : ObjectId("5ca3382c15fc3dad4a419e5e"), "name" : "sanxing", "spc" : { "weight" : 100, "area" : "hanguo" } }
wuhan:PRIMARY> db.shop.ensureIndex({"spc.area":1}); --子文件建立索引
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1, "operationTime" : Timestamp(1554200928, 2), "$clusterTime" : { "clusterTime" : Timestamp(1554200928, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY> db.shop.getIndexes();
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "tong.shop" }, { "v" : 2, "key" : { "spc.area" : 1 }, "name" : "spc.area_1", "ns" : "tong.shop" } ]
wuhan:PRIMARY>
二.唯一索引(唯一索引中欄位值必須是唯一的)
wuhan:PRIMARY> db.stu.ensureIndex({name:1},{unique:true})
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1, "operationTime" : Timestamp(1554188549, 2), "$clusterTime" : { "clusterTime" : Timestamp(1554188549, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY> db.stu.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "tong.stu" }, { "v" : 2, "unique" : true, "key" : { "name" : 1 }, "name" : "name_1", "ns" : "tong.stu" } ]
wuhan:PRIMARY> db.stu.totalIndexSize() --檢視索引大小
40960
wuhan:PRIMARY> db.stu.totalSize()
69632
wuhan:PRIMARY>
三.稀疏索引(欄位有值就建立索引,沒有值不建立索引)
wuhan:PRIMARY> db.shop.find()
{ "_id" : ObjectId("5ca337ff15fc3dad4a419e5d"), "name" : "Nokia", "spc" : { "weight" : 120, "area" : "taiwan" } }
{ "_id" : ObjectId("5ca3382c15fc3dad4a419e5e"), "name" : "sanxing", "spc" : { "weight" : 100, "area" : "hanguo" } }
wuhan:PRIMARY> db.shop.insert({}) --插入一個空值
WriteResult({ "nInserted" : 1 })
wuhan:PRIMARY> db.shop.find();
{ "_id" : ObjectId("5ca337ff15fc3dad4a419e5d"), "name" : "Nokia", "spc" : { "weight" : 120, "area" : "taiwan" } }
{ "_id" : ObjectId("5ca3382c15fc3dad4a419e5e"), "name" : "sanxing", "spc" : { "weight" : 100, "area" : "hanguo" } }
{ "_id" : ObjectId("5ca3419c15fc3dad4a419e5f") }
wuhan:PRIMARY> db.shop.ensureIndex({name:1},{sparse:true}); --建立 稀疏索引
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1, "operationTime" : Timestamp(1554203093, 2), "$clusterTime" : { "clusterTime" : Timestamp(1554203093, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY> db.shop.find({name:"null"}); --null值不顯示
wuhan:PRIMARY>
四.哈稀索引
wuhan:PRIMARY> db.t.ensureIndex({a:"hashed"});
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1, "operationTime" : Timestamp(1554203661, 2), "$clusterTime" : { "clusterTime" : Timestamp(1554203661, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY> db.t.find({a:"25"}).explain();
{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "tong.t", "indexFilterSet" : false, "parsedQuery" : { "a" : { "$eq" : "25" } }, "winningPlan" : { "stage" : "FETCH", "filter" : { "a" : { "$eq" : "25" } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : "hashed" }, "indexName" : "a_hashed", --顯示為hash索引 "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[7200060250846542811, 7200060250846542811]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "node2", "port" : 27017, "version" : "4.0.8", "gitVersion" : "9b00696ed75f65e1ebc8d635593bed79b290cfbb" }, "ok" : 1, "operationTime" : Timestamp(1554203690, 1), "$clusterTime" : { "clusterTime" : Timestamp(1554203690, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
wuhan:PRIMARY>
五.索引重建(當索引效率不高時可以考慮重建索引)
wuhan:PRIMARY> db.t.reIndex(); --重建t表的索引
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25854343/viewspace-2640156/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MongoDB技巧——不要到處使用索引MongoDB索引
- MongoDB 索引MongoDB索引
- mongodb索引MongoDB索引
- mongoDB的索引MongoDB索引
- MongoDB索引概述MongoDB索引
- MongoDB之索引(地理資訊索引)MongoDB索引
- MongoDB之索引(全文索引)MongoDB索引
- MongoDB之索引(過期索引)MongoDB索引
- MongoDB之索引(唯一索引)MongoDB索引
- MongoDB索引,效能分析MongoDB索引
- MongoDB正規表示式在索引中的使用MongoDB索引
- mongodb建立索引和刪除索引和背景索引backgroundMongoDB索引
- mongodb 如何檢視索引MongoDB索引
- MongoDB ( 五 )高階_索引MongoDB索引
- MongoDB之索引(簡介)MongoDB索引
- MongoDB索引實戰技巧MongoDB索引
- 快速掌握mongoDB(三)——mongoDB的索引詳解MongoDB索引
- 【MongoDB學習筆記】MongoDB索引那點事MongoDB筆記索引
- MongoDB系列一(索引及C#如何操作MongoDB)MongoDB索引C#
- MongoDB索引優化詳解MongoDB索引優化
- MongoDB中的定時索引MongoDB索引
- 005.MongoDB索引及聚合MongoDB索引
- MongoDB索引的簡單理解MongoDB索引
- MongoDB慢查詢與索引MongoDB索引
- MongoDB複合索引詳解MongoDB索引
- 【Mongo】MongoDB索引管理-索引的建立、檢視、刪除MongoDB索引
- MongoDB索引與優化詳解MongoDB索引優化
- mongodb資料庫如何建立索引?MongoDB資料庫索引
- MongoDB中複合索引結構MongoDB索引
- MongoDB如何在後臺建立索引MongoDB索引
- MongoDB學習之豐富的索引MongoDB索引
- mongodb索引及查詢優化分析MongoDB索引優化
- mongodb 重複建立索引不報錯MongoDB索引
- Mysql InnoDB B+樹索引和雜湊索引的區別? MongoDB 為什麼使用B-樹?MySql索引MongoDB
- MongoDB 使用MongoDB
- Mongodb使用MongoDB
- MongoDB範圍查詢的索引優化MongoDB索引優化
- 聊聊非關係型資料庫MongoDB索引資料庫MongoDB索引