您好,登錄后才能下訂單哦!
這篇“MongoDB中什么情況下索引會選擇策略”文章的知識點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價(jià)值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“MongoDB中什么情況下索引會選擇策略”文章吧。
如果我們在Collection建了5個(gè)index,那么當(dāng)我們查詢的時(shí)候,MongoDB會根據(jù)查詢語句的篩選條件、sort排序等來定位可以使用的index作為候選索引;然后MongoDB會創(chuàng)建對應(yīng)數(shù)量的查詢計(jì)劃,并分別使用不同線程執(zhí)行查詢計(jì)劃,最終會選擇一個(gè)執(zhí)行最快的index;但是這個(gè)選擇也不是一成不變的,后續(xù)還會有一段時(shí)間根據(jù)實(shí)際執(zhí)行情況動(dòng)態(tài)調(diào)整;
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) }); }
MongoDB支持正則查詢,在特定的情況其也是可以利用index獲得查詢性能的提升;
雖然MongDB執(zhí)行正則會最大限度的使用index,但是不同的用法還是會影響對index的利用程度的;
執(zhí)行以下普通正則表達(dá)式
從queryPlanner.winningPlan部分的COLLSCAN,可以看到正則表達(dá)式默認(rèn)會進(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í)行上邊的查詢,可以看到使用了我們新建的name_1索引;但是從執(zhí)行狀態(tà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 } } } }
使用前綴匹配的話可以最大限度的利用index,從執(zhí)行狀態(tài)可以看到只檢測了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 } } } }
即使是前綴匹配,如果忽略大小寫的話也無法充分利用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 } } } }
MongoDB執(zhí)行$or從句的時(shí)候,會將所有的從句作為邏輯的整體,要不就都使用index,要不就都進(jìn)行全表掃描;
執(zhí)行以下的查詢語句;
db.users.find({ $or:[ {name:/^user666/}, {age:{$gte:80}} ] }).explain('executionStats')
在只有name_1這個(gè)index的時(shí)候,我們可以看到MongoDB進(jìn)行了全表掃描,全表掃描的時(shí)候進(jìn)行$or從句的過濾;
{ "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 } } } }
我們對name字段新建一個(gè)index;
db.users.createIndex({age:1})
再次執(zhí)行以上的查詢語句,這次可以看到每個(gè)從句都利用了index,并且每個(gè)從句會單獨(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操作無法利用index,則MongoDB就會在內(nèi)存中排序數(shù)據(jù),并且數(shù)據(jù)量一大就會報(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é)果,直接就沒有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 } } } }
對于復(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 } } } }
MongoDB對index的選擇是受到實(shí)際場景的數(shù)據(jù)影響比較大的,即與實(shí)際數(shù)據(jù)的分布規(guī)律有關(guān),也跟實(shí)際篩選出來的數(shù)據(jù)有關(guān)系;所以我們對索引的優(yōu)化和測試都需要考慮實(shí)際的數(shù)據(jù)場景才行;
由于name的字段值篩選出來的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 } } } }
以上就是關(guān)于“MongoDB中什么情況下索引會選擇策略”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對大家有幫助,若想了解更多相關(guān)的知識內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。