MongoDB之索引（簡介）
在任何資料庫之中，索引都是一種提升資料庫檢索效能的手段。
MongoDB資料庫裡面依然會存在兩種索引建立：一種是自動建立，另外一種是手工建立。
範例：重新準備一個新的簡單集合。
db.emp.drop();
db.emp.insert({"name":"趙一","sex":"男","age":30,"sal":1000,"loc":"北京"});
db.emp.insert({"name":"錢二","sex":"女","age":22,"sal":5000,"loc":"上海"});
db.emp.insert({"name":"孫三","sex":"男","age":40,"sal":2000,"loc":"深圳"});
db.emp.insert({"name":"李四","sex":"女","age":30,"sal":7000,"loc":"北京"});
db.emp.insert({"name":"週五","sex":"女","age":30,"sal":6400,"loc":"北京"});
db.emp.insert({"name":"吳六","sex":"男","age":30,"sal":2500,"loc":"重慶"});
db.emp.insert({"name":"鄭七","sex":"女","age":50,"sal":4700,"loc":"成都"});
db.emp.insert({"name":"王八","sex":"男","age":35,"sal":8000,"loc":"北京"});
此時在emp的集合上並沒有去設定任何的索引，可以透過getIndexes()函式來觀察在emp集合中的索引。
範例：查詢預設狀態下emp集合的索引內容
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
現在發現會存在有一個“_id”列的索引內容。但是如果要想建立自己的索引，則可以使用如下的語法：
索引建立：db.集合名稱.createIndex({列:1})
設定的1表示索引按照升序的方式進行排序，如果使用降序則使用“-1”
範例：建立一個索引，在age欄位上設定一個降序索引
> db.emp.createIndex({"age":-1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
此時並沒有設定索引的名字，所以名字是自動命名的。命名的規範：“欄位名稱_索引的排序模式”
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
範例：針對於當前的age欄位上的索引做一個分析
> db.emp.find({"age":30}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 30
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此時的查詢使用了索引的技術，但是下面再來觀察一個查詢，不使用索引欄位
範例：針對於sal欄位進行查詢
> db.emp.find({"sal":{"$gt":5000}}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"sal" : {
"$gt" : 5000
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 3,
"needTime" : 6,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此時在sal欄位上並沒有設定索引，所以當前的索引形式就變成了全集合掃描的模式。
但是如果說，現在換一種形式，年齡和薪水一起執行查詢：
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 1,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
這個時候雖然age欄位上面存在索引，由於sal欄位上面沒有索引，所以依然使用的是全表掃描操作。為了解決此時的問題，可以使用一個複合索引。
> db.emp.createIndex({"age":-1,"sal":-1},{name:"age_-1_sal_-1_index"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
範例：預設使用索引
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
但是如果換到了以下條件：
> db.emp.find({"$or":[{"age":{"$gt":30}},{"sal":{"$gt":5000}}]}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
現在發現並沒有使用索引，所以這個時候看能否強制使用一次索引：hint()函式為強制使用索引操作。
範例：強制使用索引
> db.emp.find({"$or":[{"age":{"$gt":50}},{"sal":{"$gt":3000}}]}).hint("age_-1_sal_-1_index").explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 8,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 5,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 8,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 8,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 8,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 8,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
如果正常來講，這個程式碼根本就不可能呼叫預設的索引執行，如果需要可以使用hint()函式強制MongoDB必須使用索引。
如果在一個集合裡面設定了過多的索引，實際上會導致效能下降。那麼可以刪除索引。
範例：刪除一個索引
> db.emp.dropIndex("age_-1_sal_-1_index")
{ "nIndexesWas" : 3, "ok" : 1 }
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
範例：刪除非"_id"的索引，也就是自定義的索引
> db.emp.dropIndexes();
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
MongoDB資料庫裡面依然會存在兩種索引建立：一種是自動建立，另外一種是手工建立。
範例：重新準備一個新的簡單集合。
db.emp.drop();
db.emp.insert({"name":"趙一","sex":"男","age":30,"sal":1000,"loc":"北京"});
db.emp.insert({"name":"錢二","sex":"女","age":22,"sal":5000,"loc":"上海"});
db.emp.insert({"name":"孫三","sex":"男","age":40,"sal":2000,"loc":"深圳"});
db.emp.insert({"name":"李四","sex":"女","age":30,"sal":7000,"loc":"北京"});
db.emp.insert({"name":"週五","sex":"女","age":30,"sal":6400,"loc":"北京"});
db.emp.insert({"name":"吳六","sex":"男","age":30,"sal":2500,"loc":"重慶"});
db.emp.insert({"name":"鄭七","sex":"女","age":50,"sal":4700,"loc":"成都"});
db.emp.insert({"name":"王八","sex":"男","age":35,"sal":8000,"loc":"北京"});
此時在emp的集合上並沒有去設定任何的索引，可以透過getIndexes()函式來觀察在emp集合中的索引。
範例：查詢預設狀態下emp集合的索引內容
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
現在發現會存在有一個“_id”列的索引內容。但是如果要想建立自己的索引，則可以使用如下的語法：
索引建立：db.集合名稱.createIndex({列:1})
設定的1表示索引按照升序的方式進行排序，如果使用降序則使用“-1”
範例：建立一個索引，在age欄位上設定一個降序索引
> db.emp.createIndex({"age":-1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
此時並沒有設定索引的名字，所以名字是自動命名的。命名的規範：“欄位名稱_索引的排序模式”
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
範例：針對於當前的age欄位上的索引做一個分析
> db.emp.find({"age":30}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 30
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此時的查詢使用了索引的技術，但是下面再來觀察一個查詢，不使用索引欄位
範例：針對於sal欄位進行查詢
> db.emp.find({"sal":{"$gt":5000}}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"sal" : {
"$gt" : 5000
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 3,
"needTime" : 6,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此時在sal欄位上並沒有設定索引，所以當前的索引形式就變成了全集合掃描的模式。
但是如果說，現在換一種形式，年齡和薪水一起執行查詢：
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 1,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
這個時候雖然age欄位上面存在索引，由於sal欄位上面沒有索引，所以依然使用的是全表掃描操作。為了解決此時的問題，可以使用一個複合索引。
> db.emp.createIndex({"age":-1,"sal":-1},{name:"age_-1_sal_-1_index"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
範例：預設使用索引
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
但是如果換到了以下條件：
> db.emp.find({"$or":[{"age":{"$gt":30}},{"sal":{"$gt":5000}}]}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
現在發現並沒有使用索引，所以這個時候看能否強制使用一次索引：hint()函式為強制使用索引操作。
範例：強制使用索引
> db.emp.find({"$or":[{"age":{"$gt":50}},{"sal":{"$gt":3000}}]}).hint("age_-1_sal_-1_index").explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 8,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 5,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 8,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 8,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 8,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 8,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
如果正常來講，這個程式碼根本就不可能呼叫預設的索引執行，如果需要可以使用hint()函式強制MongoDB必須使用索引。
如果在一個集合裡面設定了過多的索引，實際上會導致效能下降。那麼可以刪除索引。
範例：刪除一個索引
> db.emp.dropIndex("age_-1_sal_-1_index")
{ "nIndexesWas" : 3, "ok" : 1 }
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
範例：刪除非"_id"的索引，也就是自定義的索引
> db.emp.dropIndexes();
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-2144101/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MongoDB之索引（地理資訊索引）MongoDB索引
- MongoDB之索引（全文索引）MongoDB索引
- MongoDB之索引（過期索引）MongoDB索引
- MongoDB之索引（唯一索引）MongoDB索引
- MongoDB簡介MongoDB
- MongoDB索引的簡單理解MongoDB索引
- 常用索引簡介索引
- 【Mysql】索引簡介MySql索引
- MySQL 索引簡介MySql索引
- MySQL索引簡介MySql索引
- MongoDB - 副本集簡介MongoDB
- MongoDB資料庫簡介MongoDB資料庫
- MongoDB學習之豐富的索引MongoDB索引
- MongoDB 索引MongoDB索引
- mongodb索引MongoDB索引
- MongoDB入門簡介|MongoDB下載安裝|MongoDB語法MongoDB
- 01 . MongoDB簡介及部署配置MongoDB
- MySQL資料庫索引簡介MySql資料庫索引
- mongodb索引使用MongoDB索引
- mongoDB的索引MongoDB索引
- MongoDB索引概述MongoDB索引
- MongoDB JVM 開發庫使用簡介MongoDBJVM
- A Brief Introduce of Database Index（索引簡介）DatabaseIndex索引
- node.js操作mongodb簡單介紹Node.jsMongoDB
- NoSLQ之MongoDB簡單入門MongoDB
- MongoDB索引，效能分析MongoDB索引
- QPM 之簡介
- ELK之簡介
- mongodb 如何檢視索引MongoDB索引
- MongoDB （ 五 ）高階_索引MongoDB索引
- MongoDB索引實戰技巧MongoDB索引
- 快速掌握mongoDB(三)——mongoDB的索引詳解MongoDB索引
- 分散式文件儲存資料庫之MongoDB索引管理分散式資料庫MongoDB索引
- MongoDB學習筆記之索引用法和效率分析MongoDB筆記索引
- 一步步學MongoDB之索引操作篇MongoDB索引
- 達夢資料庫全文索引簡介資料庫索引
- 【MongoDB學習筆記】MongoDB索引那點事MongoDB筆記索引
- MongoDB系列一(索引及C#如何操作MongoDB)MongoDB索引C#