MongoDB中哪幾種情況下的索引選擇策略
更新時(shí)間：2022年01月26日 11:02:54 作者：無(wú)風(fēng)聽(tīng)海
這篇文章主要給大家介紹了關(guān)于MongoDB中哪幾種情況下的索引選擇策略的相關(guān)資料,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
一、MongoDB如何選擇索引
如果我們?cè)贑ollection建了5個(gè)index，那么當(dāng)我們查詢(xún)的時(shí)候，MongoDB會(huì)根據(jù)查詢(xún)語(yǔ)句的篩選條件、sort排序等來(lái)定位可以使用的index作為候選索引；然后MongoDB會(huì)創(chuàng)建對(duì)應(yīng)數(shù)量的查詢(xún)計(jì)劃，并分別使用不同線(xiàn)程執(zhí)行查詢(xún)計(jì)劃，最終會(huì)選擇一個(gè)執(zhí)行最快的index；但是這個(gè)選擇也不是一成不變的，后續(xù)還會(huì)有一段時(shí)間根據(jù)實(shí)際執(zhí)行情況動(dòng)態(tài)調(diào)整；
二、數(shù)據(jù)準(zhǔn)備
for(let i = 0;i<1000000;i++){
db.users.insertOne({
"id":i,
"name":'user'+i,
"age":Math.floor(Math.random()*120),
"created":new Date(ISODate().getTime() - 1000 * 60*i)
});
}
三、正則對(duì)index的使用
MongoDB支持正則查詢(xún)，在特定的情況其也是可以利用index獲得查詢(xún)性能的提升；
雖然MongDB執(zhí)行正則會(huì)最大限度的使用index，但是不同的用法還是會(huì)影響對(duì)index的利用程度的；
執(zhí)行以下普通正則表達(dá)式
從queryPlanner.winningPlan部分的COLLSCAN，可以看到正則表達(dá)式默認(rèn)會(huì)進(jìn)行全表的掃描；
從executionStats.executionStages部分可以看到COLLSCAN共掃描了1000000個(gè)文檔，并返回1111個(gè)文檔，總耗時(shí)794ms；
db.users.find({
name:/user999/
}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$regex" : "user999"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1111,
"executionTimeMillis" : 909,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$regex" : "user999"
}
},
"nReturned" : 1111,
"executionTimeMillisEstimate" : 794,
"works" : 1000002,
"advanced" : 1111,
"needTime" : 998890,
"needYield" : 0,
"saveState" : 7830,
"restoreState" : 7830,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1000000
}
}
}
創(chuàng)建一個(gè)包含name的index；
db.users.createIndex({name:1})
再次執(zhí)行上邊的查詢(xún)，可以看到使用了我們新建的name_1索引；但是從執(zhí)行狀態(tài)來(lái)看，還是掃描了全體的索引的key，并不能很好的利用index；
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$regex" : "user999"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "user999"
}
},
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1111,
"executionTimeMillis" : 971,
"totalKeysExamined" : 1000000,
"totalDocsExamined" : 1111,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1111,
"executionTimeMillisEstimate" : 887,
"docsExamined" : 1111,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "user999"
}
},
"nReturned" : 1111,
"executionTimeMillisEstimate" : 876,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"keysExamined" : 1000000
}
}
}
}
使用前綴匹配的話(huà)可以最大限度的利用index，從執(zhí)行狀態(tài)可以看到只檢測(cè)了1111個(gè)index key；
db.users.find({
name:/^user999/
}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$regex" : "^user999"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1111,
"executionTimeMillis" : 2,
"totalKeysExamined" : 1111,
"totalDocsExamined" : 1111,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1111,
"executionTimeMillisEstimate" : 0
"docsExamined" : 1111
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1111,
"executionTimeMillisEstimate" : 0,
"indexName" : "name_1",
"keysExamined" : 1111
}
}
}
}
即使是前綴匹配，如果忽略大小寫(xiě)的話(huà)也無(wú)法充分利用index了；
db.users.find({
name:/^user999/i
}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$regex" : "user999",
"$options" : "i"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "user999",
"$options" : "i"
}
},
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1111,
"executionTimeMillis" : 943,
"totalKeysExamined" : 1000000,
"totalDocsExamined" : 1111,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1111,
"executionTimeMillisEstimate" : 833,
"works" : 1000001,
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "user999",
"$options" : "i"
}
},
"nReturned" : 1111,
"executionTimeMillisEstimate" : 833,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1"
"keysExamined" : 1000000
}
}
}
}
四、$or從句對(duì)索引的利用
MongoDB執(zhí)行$or從句的時(shí)候，會(huì)將所有的從句作為邏輯的整體，要不就都使用index，要不就都進(jìn)行全表掃描；
執(zhí)行以下的查詢(xún)語(yǔ)句；
db.users.find({
$or:[
{name:/^user666/},
{age:{$gte:80}}
]
}).explain('executionStats')
在只有name_1這個(gè)index的時(shí)候，我們可以看到MongoDB進(jìn)行了全表掃描，全表掃描的時(shí)候進(jìn)行$or從句的過(guò)濾；
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gte" : 20
}
},
{
"name" : {
"$regex" : "^user666"
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gte" : 20
}
},
{
"name" : {
"$regex" : "^user666"
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 833995,
"executionTimeMillis" : 576,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 833995,
"executionTimeMillisEstimate" : 447,
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gte" : 20
}
},
{
"name" : {
"$regex" : "^user666"
}
}
]
},
"nReturned" : 833995,
"executionTimeMillisEstimate" : 447,
"docsExamined" : 1000000
}
}
}
}
我們對(duì)name字段新建一個(gè)index；
db.users.createIndex({age:1})
再次執(zhí)行以上的查詢(xún)語(yǔ)句，這次可以看到每個(gè)從句都利用了index，并且每個(gè)從句會(huì)單獨(dú)執(zhí)行并最終進(jìn)行or操作；
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gte" : 80
}
},
{
"name" : {
"$regex" : "^user666"
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "OR",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user666\", \"user667\")",
"[/^user666/, /^user666/]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[80.0, inf.0]"
]
}
}
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 333736,
"executionTimeMillis" : 741,
"totalKeysExamined" : 334102,
"totalDocsExamined" : 333736,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 333736,
"executionTimeMillisEstimate" : 703,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 333736,
"executionTimeMillisEstimate" : 682
"docsExamined" : 333736,
"inputStage" : {
"stage" : "OR",
"nReturned" : 333736,
"executionTimeMillisEstimate" : 366,
"inputStages" : [
{
"stage" : "IXSCAN",
"nReturned" : 1111,
"executionTimeMillisEstimate" : 0,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"indexBounds" : {
"name" : [
"[\"user666\", \"user667\")",
"[/^user666/, /^user666/]"
]
},
"keysExamined" : 1112
},
{
"stage" : "IXSCAN",
"nReturned" : 332990,
"executionTimeMillisEstimate" : 212,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"indexBounds" : {
"age" : [
"[80.0, inf.0]"
]
},
"keysExamined" : 332990
}
]
}
}
}
}
}
五、sort對(duì)索引的利用
如果sort操作無(wú)法利用index，則MongoDB就會(huì)在內(nèi)存中排序數(shù)據(jù)，并且數(shù)據(jù)量一大就會(huì)報(bào)錯(cuò)；
db.users.find().sort({created: -1}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"created" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : false,
"errorMessage" : "Exec error resulting in state FAILURE :: caused by :: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.",
"errorCode" : 96,
"nReturned" : 0,
"executionTimeMillis" : 959,
"totalKeysExamined" : 0,
"totalDocsExamined" : 361996,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 922,
"sortPattern" : {
"created" : -1
},
"memUsage" : 33554518,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 361996,
"executionTimeMillisEstimate" : 590,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 361996,
"executionTimeMillisEstimate" : 147,
"direction" : "forward",
"docsExamined" : 361996
}
}
}
}
}
如果是單字段index，sort從兩個(gè)方向都可以充分利用index；可以看到MongoDB直接按照index的順序返回結(jié)果，直接就沒(méi)有sort階段了；
db.users.find().sort({name: -1}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"direction" : "backward",
"indexBounds" : {
"name" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1000000,
"executionTimeMillis" : 1317,
"totalKeysExamined" : 1000000,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 1180,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 560,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"name" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 1000000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
對(duì)于復(fù)合索引，sort除了可以從整體上從兩個(gè)方向利用index，也可以利用index的前綴索引和非前綴局部索引；
新建復(fù)合索引
db.users.createIndex({created:-1, name:1, age:1})
按照復(fù)合索引的反方向進(jìn)行整體排序；
db.users.find().sort({created:1, name:-1, age:-1}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[MinKey, MaxKey]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1000000,
"executionTimeMillis" : 1518,
"totalKeysExamined" : 1000000,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 1364,
"docsExamined" : 1000000,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 816,
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[MinKey, MaxKey]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 1000000
}
}
}
}
排序使用索引前綴，也需要保證字段的順序，但是可以反方向排序；
db.users.find().sort({created:1, name:-1, age:-1}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[MinKey, MaxKey]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1000000,
"executionTimeMillis" : 1487,
"totalKeysExamined" : 1000000,
"totalDocsExamined" : 1000000,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 1339,
"works" : 1000001,
"advanced" : 1000000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 7845,
"restoreState" : 7845,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1000000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1000000,
"executionTimeMillisEstimate" : 769,
"works" : 1000001,
"advanced" : 1000000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 7845,
"restoreState" : 7845,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[MinKey, MaxKey]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 1000000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
排序如果使用的是非前綴的局部字典排序，name需要保證前邊的字段是等值篩選操作才行；
db.users.find({created:new Date("2021-10-30T08:17:01.184Z")}).sort({name:-1}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"created" : {
"$eq" : ISODate("2021-10-30T08:17:01.184Z")
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[new Date(1635581821184), new Date(1635581821184)]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"created" : -1,
"name" : 1,
"age" : 1
},
"indexName" : "created_-1_name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"created" : [ ],
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"created" : [
"[new Date(1635581821184), new Date(1635581821184)]"
],
"name" : [
"[MaxKey, MinKey]"
],
"age" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 0,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
六、搜索數(shù)據(jù)對(duì)索引命中的影響
MongoDB對(duì)index的選擇是受到實(shí)際場(chǎng)景的數(shù)據(jù)影響比較大的，即與實(shí)際數(shù)據(jù)的分布規(guī)律有關(guān)，也跟實(shí)際篩選出來(lái)的數(shù)據(jù)有關(guān)系；所以我們對(duì)索引的優(yōu)化和測(cè)試都需要考慮實(shí)際的數(shù)據(jù)場(chǎng)景才行；
由于name的字段值篩選出來(lái)的key太多，不能充分利用index，所以MongoDB拒絕了name_1并選擇了age_1;
db.users.find({
name:/^user/,
age:{$gte:110}
}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$gte" : 110
}
},
{
"name" : {
"$regex" : "^user"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"name" : {
"$regex" : "^user"
}
},
"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" : [
"[110.0, inf.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"age" : {
"$gte" : 110
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user\", \"uses\")",
"[/^user/, /^user/]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 83215,
"executionTimeMillis" : 246,
"totalKeysExamined" : 83215,
"totalDocsExamined" : 83215,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"name" : {
"$regex" : "^user"
}
},
"nReturned" : 83215,
"executionTimeMillisEstimate" : 232,
"works" : 83216,
"advanced" : 83215,
"needTime" : 0,
"needYield" : 0,
"saveState" : 658,
"restoreState" : 658,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 83215,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 83215,
"executionTimeMillisEstimate" : 43,
"works" : 83216,
"advanced" : 83215,
"needTime" : 0,
"needYield" : 0,
"saveState" : 658,
"restoreState" : 658,
"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" : [
"[110.0, inf.0]"
]
},
"keysExamined" : 83215,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
我們修改一下name篩選條件的值，進(jìn)一步縮小命中的范圍，可以看到這次MongoDB選擇了name_1;
db.users.find({
name:/^user8888/,
age:{$gte:110}
}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$gte" : 110
}
},
{
"name" : {
"$regex" : "^user8888"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"age" : {
"$gte" : 110
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user8888\", \"user8889\")",
"[/^user8888/, /^user8888/]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"name" : {
"$regex" : "^user8888"
}
},
"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" : [
"[110.0, inf.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10,
"executionTimeMillis" : 0,
"totalKeysExamined" : 112,
"totalDocsExamined" : 111,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"age" : {
"$gte" : 110
}
},
"nReturned" : 10,
"executionTimeMillisEstimate" : 0,
"works" : 114,
"advanced" : 10,
"needTime" : 102,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 111,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 111,
"executionTimeMillisEstimate" : 0,
"works" : 113,
"advanced" : 111,
"needTime" : 1,
"needYield" : 0,
"saveState" : 1,
"restoreState" : 1,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user8888\", \"user8889\")",
"[/^user8888/, /^user8888/]"
]
},
"keysExamined" : 112,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
總結(jié)
到此這篇關(guān)于MongoDB中哪幾種情況下的索引選擇策略的文章就介紹到這了,更多相關(guān)MongoDB索引選擇策略?xún)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MongoDB是一個(gè)NoSQL數(shù)據(jù)庫(kù)系統(tǒng)：一個(gè)數(shù)據(jù)庫(kù)可以包含多個(gè)集合（Collection），每個(gè)集合對(duì)應(yīng)于關(guān)系數(shù)據(jù)庫(kù)中的表；而每個(gè)集合中可以存儲(chǔ)一組由列標(biāo)識(shí)的記錄，列是可以自由定義的，非常靈活，由一組列標(biāo)識(shí)的實(shí)體的集合對(duì)應(yīng)于關(guān)系數(shù)據(jù)庫(kù)表中的行2016-01-01
$divide聚合運(yùn)算符返回兩個(gè)表達(dá)式相除的結(jié)果,參數(shù)通過(guò)數(shù)組傳遞給$divide運(yùn)算符,這篇文章介紹了MongoDB聚合運(yùn)算符$divide的相關(guān)知識(shí),感興趣的朋友跟隨小編一起看看吧2024-03-03
Windows系統(tǒng)下安裝Mongodb 3.2.x的步驟詳解
mongodb3.x版本有好多新功能，關(guān)于這方面參考官網(wǎng)即可，下面這篇文章主要給大家介紹了在Windows系統(tǒng)下安裝Mongodb 3.2.x的詳細(xì)步驟，文中介紹的非常詳細(xì)，需要的朋友們可以參考學(xué)習(xí)，下面來(lái)一起看看吧。2017-03-03
分布式文檔存儲(chǔ)數(shù)據(jù)庫(kù)之MongoDB分片集群的問(wèn)題
這篇文章主要介紹了分布式文檔存儲(chǔ)數(shù)據(jù)庫(kù)之MongoDB分片集群的問(wèn)題，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-11-11
MongoDB添加仲裁節(jié)點(diǎn)報(bào)錯(cuò)：replica set IDs do not match的解決方法
這篇文章主要給大家介紹了關(guān)于MongoDB添加仲裁節(jié)點(diǎn)報(bào)錯(cuò)：replica set IDs do not match的解決方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-11-11
cgroup限制mongodb進(jìn)程內(nèi)存大小
這篇文章主要介紹了cgroup限制mongodb進(jìn)程內(nèi)存大小,需要的朋友可以參考下2014-07-07
之前給大家分享了MongoDB中數(shù)據(jù)查詢(xún)的相關(guān)方法和技巧，那么下面這篇文章主要給大家介紹了MongoDB中數(shù)據(jù)更新方法的相關(guān)資料，文中給出了詳細(xì)的示例代碼，需要的朋友可以參考借鑒，下面來(lái)一起看看吧。2017-05-05
關(guān)于MongoDB索引管理－索引的創(chuàng)建、查看、刪除操作詳解
本文講述了關(guān)于MongoDB索引管理包括索引的創(chuàng)建、查看索引、刪除索引各方面的命令及使用方法2018-03-03
這篇文章主要給大家介紹了關(guān)于MongoDB中參數(shù)限制與閥值的相關(guān)資料，文中通過(guò)示例代碼以及圖文介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
下面開(kāi)始學(xué)習(xí)MongoDB最重要也是最基礎(chǔ)的部分：C（創(chuàng)建）R（查詢(xún)）U（更新）D（刪除）；由于R（查詢(xún)）操作相對(duì)來(lái)說(shuō)內(nèi)容比較多，也比較繁瑣，同時(shí)使用頻率也比較高，所以下一篇會(huì)拿出來(lái)單獨(dú)介紹。廢話(huà)不多說(shuō)，連上服務(wù)器，我們直接進(jìn)入正題2017-05-05