MongoDB中哪几种情况下的索引选择策略
目录
- 一、MongoDB如何选择索引
- 二、数据准备
- 三、正则对index的使用
- 四、$or从句对索引的利用
- 五、sort对索引的利用
- 六、搜索数据对索引命中的影响
- 总结
一、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索引选择策略内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
赞 (0)
相关推荐
-
MongoDB的基础查询和索引操作方法总结
查询操作 1.查询所有记录 db.userInfo.find(); 相当于: select* from userInfo; 2.查询去掉后的当前聚集集合中的某列的重复数据 db.userInfo.distinct("name"); 会过滤掉name中的相同数据 相当于: select disttince name from userInfo; 3.查询age = 22的记录 db.userInfo.find({"age": 22}); 相当于: select * f
-
MongoDB数据库中索引(index)详解
索引:特殊的数据结构,存储表的数据的一小部分以实现快速查询 优点: 1.大大减少了服务器需要扫描的数据量 2.索引可以帮助服务器避免排序或使用临时表 3.索引可以将随机io转换为顺序io 索引评估:三星(非常好) 一星:索引如果能将相关的记录放置到一起 二星:索引中数据的存储顺序与查找标准中顺序一致 三星:如果索引中包含查询中所需要的全部数据:(覆盖索引) DBA书:关系型数据库索引设计与优化 索引类别: 顺序索引 散列索引:将索引映射至散列桶上,映射是通过散列函数进行的 评估索引的标准: 访问
-
MongoDB中创建索引需要注意的事项
上周在 ruby-china 上发了帖子<MongoDB 那些坑>,反映相当热烈,许多回复很有见地,其中一位童鞋深入的提到 MongoDB 建索引方法的问题,引发我更深入的了解了 MongoDB 建索引的方法和一些注意事项. 在 <MongoDB 那些坑>中提到,在前台直接运行建立索引命令的话,将造成整个数据库阻塞,因此索引建议使用 background 的方式建立.但是这也会带来一定的问题,在 2.6 版本之前,在 secondary server 中即使使用 backgroun
-
MongoDB索引使用详解
索引就像书的目录,如果查找某内容在没有目录的帮助下,只能全篇查找翻阅,这导致效率非常的低下:如果在借助目录情况下,就能很快的定位具体内容所在区域,效率会直线提高. 索引简介 首先打开命令行,输入mongo.默认mongodb会连接名为test的数据库. ➜ ~ mongo MongoDB shell version: 2.4.9 connecting to: test > show collections > 可以使用show collections/tables查看数据库为空. 然后在mon
-
MongoDB中哪几种情况下的索引选择策略
目录 一.MongoDB如何选择索引 二.数据准备 三.正则对index的使用 四.$or从句对索引的利用 五.sort对索引的利用 六.搜索数据对索引命中的影响 总结 一.MongoDB如何选择索引 如果我们在Collection建了5个index,那么当我们查询的时候,MongoDB会根据查询语句的筛选条件.sort排序等来定位可以使用的index作为候选索引:然后MongoDB会创建对应数量的查询计划,并分别使用不同线程执行查询计划,最终会选择一个执行最快的index:但是这个选择也不是一
-
Django如何在不停机的情况下创建索引
该框架在管理数据库更改方面非常强大和有用,但是该框架提供的灵活性受到了一定的限制.为了理解Django迁移的局限性,你将处理一个众所周知的问题:在不停机的情况下,在Django中创建一个索引. 在本教程中,你将学习: Django如何以及何时生成新的迁移: 如何检查Django生成的执行迁移的命令: 如何安全地修改迁移以满足你的需求. 本中级教程是为已经熟悉Django迁移(Migration)的读者设计的. 在Django迁移中创建索引的问题 当应用程序存储的数据增长时,通常需要进行的一个常见
-
Mysql两种情况下更新字段中部分数据的方法
Mysql更新字段中部分数据第一种情况: 复制代码 代码如下: update tab set A = concat(substring(A,1,3),'bbb'); 从A的1个字符开始取3个字符,加上'bbb',再写入a中,如果A原始值为'123aaa',那么更新之后为'123bbb'了. 如果我们要将字段A值中的包含的1,替换成2呢? 例如:a=2211,现在要替换成2222,就是把1换成2 SQl语句这么写: 复制代码 代码如下: update table set a=REPLACE(a,
-
Android中没有插入SD情况下的文件写入和读取方法
在Android开发时会遇到如下一种场合 希望应用下载到当前应用的根目录下,而非SD卡中 然后可以随时被该应用或其他应用访问这个文件,即具有被全局读取的权限 此时可以利用上下文的本地文件输出流进行文件写入 注意要为文件赋予可写可读的权限,否则当读取该文件时会报错 FileOutputStream fos = context.openFileOutput(saveFileName, Context.MODE_WORLD_WRITEABLE | Context.MODE_WORLD_READABLE
-
MySQL中无GROUP BY情况下直接使用HAVING语句的问题探究
今天有同学给我反应,有一张表,id是主键,这样的写法可以返回一条记录: "SELECT * FROM t HAVING id=MIN(id);" 但是只是把MIN换成MAX,这样返回就是空了: "SELECT * FROM t HAVING id=MAX(id);" 这是为什么呢? 我们先来做个试验,验证这种情况. 这是表结构,初始化两条记录,然后试验: root@localhost : plx 10:25:10> show create table t2G
-
js 有框架页面跳转(target)三种情况下的应用
1.跳出框架,在父页面. jsp: 复制代码 代码如下: <script language="javascript"> window.parent.frames.location.href="../welcome.en" </script> java: 复制代码 代码如下: PrintWriter out = response.getWriter(); out.write("<script type='text/javascr
-
MySQL中有哪些情况下数据库索引会失效详析
前言 要想分析MySQL查询语句中的相关信息,如是全表查询还是部分查询,就要用到explain. 索引的优点 大大减少了服务器需要扫描的数据量 可以帮助服务器避免排序或减少使用临时表排序 索引可以随机I/O变为顺序I/O 索引的缺点 需要占用磁盘空间,因此冗余低效的索引将占用大量的磁盘空间 降低DML性能,对于数据的任意增删改都需要调整对应的索引,甚至出现索引分裂 索引会产生相应的碎片,产生维护开销 一.explain 用法:explain +查询语句. id:查询语句的序列号,上面图片中只有一
-
分析在Python中何种情况下需要使用断言
这个问题是如何在一些场景下使用断言表达式,通常会有人误用它,所以我决定写一篇文章来说明何时使用断言,什么时候不用. 为那些还不清楚它的人,Python的assert是用来检查一个条件,如果它为真,就不做任何事.如果它为假,则会抛出AssertError并且包含错误信息.例如: py> x = 23 py> assert x > 0, "x is not zero or negative" py> assert x%2 == 0, "x is not a
-
iOS自定义转场动画的几种情况
前言 在开发中,无论我们使用 Push 还是 Present 推出新的 ViewController 时,系统为了提高用户体验都会为我们默认加上一些过渡动画.但是,系统默认的动画总是不能满足大家各种各样的需求的,所以系统也为我们提供了在不同场景下自定义过渡动画以及通过手势控制过渡进度的实现方案. 这篇文章记录了自定义转场动画中的几种情况: 模态跳转(Present) 导航控制器跳转(Push) UITabbarController 三方框架--Lottie 效果图 预备 首先,我们现在介绍几个在
-
MySQL问答系列之什么情况下会用到临时表
临时表介绍 什么是临时表:MySQL用于存储一些中间结果集的表,临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间.为什么会产生临时表:一般是由于复杂的SQL导致临时表被大量创建 临时表分为两种,一种是内存临时表,一种是磁盘临时表.内存临时表采用的是memory存储引擎,磁盘临时表采用的是myisam存储引擎(磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,从mysql5.7.6之后