MongoDB中什么情况下索引会选择策略(mongodb,开发技术)

一、MongoDB如何选择索引

如果我们在Collection建了5个index，那么当我们查询的时候，MongoDB会根据查询语句的筛选条件、sort排序等来定位可以使用的index作为候选索引；然后MongoDB会创建对应数量的查询计划，并分别使用不同线程执行查询计划，最终会选择一个执行最快的index；但是这个选择也不是一成不变的，后续还会有一段时间根据实际执行情况动态调整；

二、数据准备

for(leti=0;i<1000000;i++){db.users.insertOne({"id":i,"name":'user'+i,"age":Math.floor(Math.random()*120),"created":newDate(ISODate().getTime()-1000*60*i)});}

三、正则对index的使用

MongoDB支持正则查询，在特定的情况其也是可以利用index获得查询性能的提升；

虽然MongDB执行正则会最大限度的使用index，但是不同的用法还是会影响对index的利用程度的；

执行以下普通正则表达式

从queryPlanner.winningPlan部分的COLLSCAN，可以看到正则表达式默认会进行全表的扫描；

从executionStats.executionStages部分可以看到COLLSCAN共扫描了1000000个文档，并返回1111个文档，总耗时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}}}

创建一个包含name的index；

db.users.createIndex({name:1})

再次执行上边的查询，可以看到使用了我们新建的name_1索引；但是从执行状态来看，还是扫描了全体的索引的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，从执行状态可以看到只检测了1111个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}}}}

四、$or从句对索引的利用

MongoDB执行$or从句的时候，会将所有的从句作为逻辑的整体，要不就都使用index，要不就都进行全表扫描；

执行以下的查询语句；

db.users.find({$or:[{name:/^user666/},{age:{$gte:80}}]}).explain('executionStats')

在只有name_1这个index的时候，我们可以看到MongoDB进行了全表扫描，全表扫描的时候进行$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字段新建一个index；

db.users.createIndex({age:1})

再次执行以上的查询语句，这次可以看到每个从句都利用了index，并且每个从句会单独执行并最终进行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对索引的利用

如果sort操作无法利用index，则MongoDB就会在内存中排序数据，并且数据量一大就会报错；

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":"ExecerrorresultinginstateFAILURE::causedby::Sortoperationusedmorethanthemaximum33554432bytesofRAM.Addanindex,orspecifyasmallerlimit.","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从两个方向都可以充分利用index；可以看到MongoDB直接按照index的顺序返回结果，直接就没有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}}}}

对于复合索引，sort除了可以从整体上从两个方向利用index，也可以利用index的前缀索引和非前缀局部索引；

新建复合索引

db.users.createIndex({created:-1,name:1,age:1})

按照复合索引的反方向进行整体排序；

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:newDate("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":["[newDate(1635581821184),newDate(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":["[newDate(1635581821184),newDate(1635581821184)]"],"name":["[MaxKey,MinKey]"],"age":["[MaxKey,MinKey]"]},"keysExamined":0,"seeks":1,"dupsTested":0,"dupsDropped":0,"seenInvalidated":0}}}}

六、搜索数据对索引命中的影响

MongoDB对index的选择是受到实际场景的数据影响比较大的，即与实际数据的分布规律有关，也跟实际筛选出来的数据有关系；所以我们对索引的优化和测试都需要考虑实际的数据场景才行；

由于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筛选条件的值，进一步缩小命中的范围，可以看到这次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}}}}