MongoDB中哪几种情况下的索引选择策略
程序员文章站
2022-06-09 23:50:29
目录一、mongodb如何选择索引二、数据准备三、正则对index的使用四、$or从句对索引的利用五、sort对索引的利用六、搜索数据对索引命中的影响总结一、mongodb如何选择索引如果我们在col...
一、mongodb如何选择索引
如果我们在collection建了5个index，那么当我们查询的时候，mongodb会根据查询语句的筛选条件、sort排序等来定位可以使用的index作为候选索引；然后mongodb会创建对应数量的查询计划，并分别使用不同线程执行查询计划，最终会选择一个执行最快的index；但是这个选择也不是一成不变的，后续还会有一段时间根据实际执行情况动态调整；
二、数据准备
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)
});
}
三、正则对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" : "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从两个方向都可以充分利用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: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的选择是受到实际场景的数据影响比较大的，即与实际数据的分布规律有关，也跟实际筛选出来的数据有关系；所以我们对索引的优化和测试都需要考虑实际的数据场景才行；
由于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
}
}
}
}
总结
到此这篇关于mongodb中哪几种情况下的索引选择策略的文章就介绍到这了,更多相关mongodb索引选择策略内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
上一篇： Redis连接超时异常的处理方法