| 參數(shù) | 類型 | 描述 |
|---|---|---|
| background | boolean | 創(chuàng)建索引在后臺(tái)運(yùn)行，不會(huì)阻止其他對(duì)數(shù)據(jù)庫(kù)操作 |
| unique | boolean | 創(chuàng)建唯一索引，文檔的值不會(huì)重復(fù) |
| name | string | 索引名稱，默認(rèn)是：字段名_排序類型 開(kāi)始排序 |
| sparse | boolean | 過(guò)濾掉null，不存在的字段 |
查看索引
db.collection.getIndexes()
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1 //索引字段
},
"name" : "name_1", //索引名稱
"ns" : "leyue.userdatas"
}
刪除索引
db.collection.dropIndex(index) 刪除指定的索引。
db.collection.dropIndexes() 刪除除了_id 以外的所有索引。
創(chuàng)建/查看/刪除 示例
查看數(shù)據(jù)
db.userdatas.find()
{ "_id" : ObjectId("597f357a09c84cf58880e412"), "name" : "u3", "age" : 32 }
{ "_id" : ObjectId("597f357a09c84cf58880e411"), "name" : "u4", "age" : 30, "score" : [ 7, 4, 2, 0 ] }
{ "_id" : ObjectId("597fcc0f411f2b2fd30d0b3f"), "age" : 20, "score" : [ 7, 4, 2, 0, 10, 9, 8, 7 ], "name" : "lihao" }
{ "_id" : ObjectId("597f357a09c84cf58880e413"), "name" : "u2", "age" : 33, "wendang" : { "yw" : 80, "xw" : 90 } }
{ "_id" : ObjectId("5983f5c88eec53fbcd56a7ca"), "date" : ISODate("2017-08-04T04:19:20.693Z") }
{ "_id" : ObjectId("597f357a09c84cf58880e40e"), "name" : "u1", "age" : 26, "address" : "中國(guó)碭山" }
{ "_id" : ObjectId("597f357a09c84cf58880e40f"), "name" : "u1", "age" : 37, "score" : [ 10, 203, 12, 43, 56, 22 ] }
{ "_id" : ObjectId("597f357a09c84cf58880e410"), "name" : "u5", "age" : 78, "address" : "china beijing chaoyang" }
給字段name 創(chuàng)建索引
// 創(chuàng)建索引
db.userdatas.createIndex({"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
// 查看索引
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "leyue.userdatas"
}
]
給字段name 創(chuàng)建索引并命名為myindex
db.userdatas.createIndex({"name":1})
db.userdatas.createIndex({"name":1},{"name":"myindex"})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "myindex",
"ns" : "leyue.userdatas"
}
]
給字段name 創(chuàng)建索引 創(chuàng)建的過(guò)程在后臺(tái)執(zhí)行
當(dāng)mongodb 集合里面的數(shù)據(jù)過(guò)大時(shí) 創(chuàng)建索引很耗時(shí)，可以在放在后臺(tái)運(yùn)行。
db.userdatas.dropIndex("myindex")
db.userdatas.createIndex({"name":1},{"name":"myindex","background":true})
給age 字段創(chuàng)建唯一索引
db.userdatas.createIndex({"age":-1},{"name":"ageIndex","unique":true,"sparse":true})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "myindex",
"ns" : "leyue.userdatas",
"background" : true
},
{
"v" : 1,
"unique" : true,
"key" : {
"age" : -1
},
"name" : "ageIndex",
"ns" : "leyue.userdatas",
"sparse" : true
}
]
// 插入一個(gè)已存在的age
db.userdatas.insert({ "name" : "u8", "age" : 32})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error index: leyue.userdatas.$ageIndex dup key: { : 32.0 }"
}
})
創(chuàng)建復(fù)合索引
db.userdatas.createIndex({"name":1,"age":-1})
db.userdatas.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "leyue.userdatas"
},
{
"v" : 1,
"key" : {
"name" : 1,
"age" : -1
},
"name" : "name_1_age_-1",
"ns" : "leyue.userdatas"
}
]
所有的字段都存在集合 system.indexes 中
db.system.indexes.find()
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.userdatas" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.scores" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "user" : 1, "name" : 1 }, "name" : "myindex", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "leyue.mycapped" }
{ "v" : 1, "key" : { "user" : 1 }, "name" : "user_1", "ns" : "leyue.test" }
{ "v" : 1, "key" : { "name" : 1 }, "name" : "myindex", "ns" : "leyue.userdatas" }
索引總結(jié)
1:創(chuàng)建索引時(shí),1表示按升序存儲(chǔ),-1表示按降序存儲(chǔ)。
2:可以創(chuàng)建復(fù)合索引,如果想用到復(fù)合索引,必須在查詢條件中包含復(fù)合索引中的前N個(gè)索引列
3: 如果查詢條件中的鍵值順序和復(fù)合索引中的創(chuàng)建順序不一致的話,
MongoDB可以智能的幫助我們調(diào)整該順序,以便使復(fù)合索引可以為查詢所用。
4: 可以為內(nèi)嵌文檔創(chuàng)建索引,其規(guī)則和普通文檔創(chuàng)建索引是一樣的。
5: 一次查詢中只能使用一個(gè)索引,$or特殊,可以在每個(gè)分支條件上使用一個(gè)索引。
6: $where,$exists不能使用索引,還有一些低效率的操作符,比如:$ne,$not,$nin等。
7: 設(shè)計(jì)多個(gè)字段的索引時(shí),應(yīng)該盡量將用于精確匹配的字段放在索引的前面。
explain 使用
語(yǔ)法
db.collection.explain().method(...)>
explain() 可以設(shè)置參數(shù) ：
示例
for(var i=0;i100000;i++) {
db.test.insert({"user":"user"+i});
}
沒(méi)有使用索引
db.test.explain("executionStats").find({"user":"user200000"})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "leyue.test",
"indexFilterSet" : false,
"parsedQuery" : {
"user" : {
"$eq" : "user200000"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"user" : {
"$eq" : "user200000"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 326,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1006497,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"user" : {
"$eq" : "user200000"
}
},
"nReturned" : 2,
"executionTimeMillisEstimate" : 270,
"works" : 1006499,
"advanced" : 2,
"needTime" : 1006496,
"needYield" : 0,
"saveState" : 7863,
"restoreState" : 7863,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1006497
}
},
"serverInfo" : {
"host" : "lihaodeMacBook-Pro.local",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
executionStats.executionTimeMillis: query的整體查詢時(shí)間。executionStats.nReturned : 查詢返回的條目。executionStats.totalKeysExamined ： 索引掃描條目。executionStats.totalDocsExamined： 文檔掃描條目。executionTimeMillis = 326 query 執(zhí)行時(shí)間
nReturned=2 返回兩條數(shù)據(jù)
totalKeysExamined=0 沒(méi)有用到索引
totalDocsExamined 全文檔掃描
理想狀態(tài)：
nReturned=totalKeysExamined totalDocsExamined=0
Stage狀態(tài)分析
| stage | 描述 |
|---|---|
| COLLSCAN | 全表掃描 |
| IXSCAN | 掃描索引 |
| FETCH | 根據(jù)索引去檢索指定document |
| SHARD_MERGE | 將各個(gè)分片返回?cái)?shù)據(jù)進(jìn)行merge |
| SORT | 表明在內(nèi)存中進(jìn)行了排序 |
| LIMIT | 使用limit限制返回?cái)?shù) |
| SKIP | 使用skip進(jìn)行跳過(guò) |
| IDHACK | 針對(duì)_id進(jìn)行查詢 |
| SHARDING_FILTER | 通過(guò)mongos對(duì)分片數(shù)據(jù)進(jìn)行查詢 |
| COUNT | 利用db.coll.explain().count()之類進(jìn)行count運(yùn)算 |
| COUNTSCAN | count不使用Index進(jìn)行count時(shí)的stage返回 |
| COUNT_SCAN | count使用了Index進(jìn)行count時(shí)的stage返回 |
| SUBPLA | 未使用到索引的$or查詢的stage返回 |
| TEXT | 使用全文索引進(jìn)行查詢時(shí)候的stage返回 |
| PROJECTION | 限定返回字段時(shí)候stage的返回 |
對(duì)于普通查詢，我希望看到stage的組合(查詢的時(shí)候盡可能用上索引)：
Fetch+IDHACK
Fetch+ixscan
Limit+（Fetch+ixscan）
PROJECTION+ixscan
SHARDING_FITER+ixscan
COUNT_SCAN
不希望看到包含如下的stage：
COLLSCAN(全表掃描),SORT(使用sort但是無(wú)index),不合理的SKIP,SUBPLA(未用到index的$or),COUNTSCAN(不使用index進(jìn)行count)
使用索引
db.test.createIndex({"user":1},{"name":"myindex","background":true})
db.test.explain("executionStats").find({"user":"user200000"})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "leyue.test",
"indexFilterSet" : false,
"parsedQuery" : {
"user" : {
"$eq" : "user200000"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"user" : 1
},
"indexName" : "myindex",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"user" : [
"[\"user200000\", \"user200000\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 0,
"totalKeysExamined" : 2,
"totalDocsExamined" : 2,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"user" : 1
},
"indexName" : "myindex",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"user" : [
"[\"user200000\", \"user200000\"]"
]
},
"keysExamined" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "lihaodeMacBook-Pro.local",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
executionTimeMillis: 0
totalKeysExamined: 2
totalDocsExamined:2
nReturned:2
stage:IXSCAN
使用索引和不使用差距很大，合理使用索引，一個(gè)集合適合做 4-5 個(gè)索引。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
http://www.mongoing.com/eshu_explain3
https://docs.mongodb.com/v3.2/reference/explain-results/#queryplanner
標(biāo)簽：自貢 無(wú)錫 雞西 廈門 興安盟 遼陽(yáng) 玉林 泰安
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MongoDB數(shù)據(jù)庫(kù)中索引和explain的使用教程》，本文關(guān)鍵詞 MongoDB,數(shù)據(jù)庫(kù),中,索引,和,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。