一、MongoDB如何选择索引
如果我们在Collection建了5个index，那么当我们查询的时候，MongoDB会根据查询语句的筛选条件、sort排序等来定位可以使用的index作为候选索引；然后MongoDB会创建对应数量的查询计划，并分别使用不同线程执行查询计划，最终会选择一个执行最快的index；但是这个选择也不是一成不变的，后续还会有一段时间根据实际执行情况动态调整；
二、数据准备
|
1 2 3 4 5 6 7 8 |
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；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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；
|
1 |
db.users.createIndex({ name :1}) |
再次执行上边的查询，可以看到使用了我们新建的name_1索引；但是从执行状态来看，还是扫描了全体的索引的key，并不能很好的利用index；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
{ "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；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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了；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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，要不就都进行全表扫描；
执行以下的查询语句；
|
1 2 3 4 5 6 |
db.users.find({ $ or :[ { name :/^user666/}, {age:{$gte:80}} ] }).explain( 'executionStats' ) |
在只有name_1这个index的时候，我们可以看到MongoDB进行了全表扫描，全表扫描的时候进行$or从句的过滤；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
{ "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；
|
1 |
db.users.createIndex({age:1}) |
再次执行以上的查询语句，这次可以看到每个从句都利用了index，并且每个从句会单独执行并最终进行or操作；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
{ "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就会在内存中排序数据，并且数据量一大就会报错；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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阶段了；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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的前缀索引和非前缀局部索引；
新建复合索引
|
1 |
db.users.createIndex({created:-1, name :1, age:1}) |
按照复合索引的反方向进行整体排序；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
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 } } } } |
排序使用索引前缀，也需要保证字段的顺序，但是可以反方向排序；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
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需要保证前边的字段是等值筛选操作才行；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
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索引选择策略内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://HdhCmsTestcnblogs测试数据/wufengtinghai/p/15786782.html
查看更多关于MongoDB中哪几种情况下的索引选择策略的详细内容...