大佬教程收集整理的这篇文章主要介绍了MongoDB之几种情况下的索引选择策略,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
一、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的使用
@R_897_4674@持正则查询,在特定的情况其也是可以利用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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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从句对索引的利用
@H_519_0@mongoDB执行$or从句的时候,会将所有的从句作为逻辑的整体,要不就都使用index,要不就都进行全表扫描;执行以下的查询语句;
db.users.find({
$or:[
{name:/^user666/},
{age:{$gte:80}}
]
}).explain('EXECUTIONStats')
在只有name_1这个index的时候,我们可以看到R_555_11845@ongoDB进行了全表扫描,全表扫描的时候进行$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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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
}
}
}
}
六、搜索数据对索引命中的影响
@H_519_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,
"@R_195_10586@lKeysExamined" : 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,
"@R_195_10586@lKeysExamined" : 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之几种情况下的索引选择策略所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。