服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - MongoDB - MongoDB中哪几种情况下的索引选择策略

MongoDB中哪几种情况下的索引选择策略

2022-09-27 17:07无风听海 MongoDB

这篇文章主要给大家介绍了关于MongoDB中哪几种情况下的索引选择策略的相关资料,文中通过图文以及实例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

一、MongoDB如何选择索引

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

MongoDB中哪几种情况下的索引选择策略

二、数据准备

?
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://www.cnblogs.com/wufengtinghai/p/15786782.html

  • mongodb
  • 索引
  • 策略

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    28663 　 mongodb还原 　 mongodb导出 　 mongodb导入 　 replica 　 高性能多服务器 　 修改用户密码 　 PHP驱动 　 MongoDB入门教程 　 MongoDB安装教程 　 MongoDB安装图解 　 MongoDB教程 　 分片技术 　 db.serverStatus 　 mongodb备份 　 数据库删除 　 数据库切换 　 Aggregations 　 mongodb数组查询 　 mongodb条件查询 　 脚本编程 　 编程模型 　 php分页类 　 安全设置命令 　 添加管理员 　 NumberInt32 　 数据库游标 　 循环遍历 　 cgroup 　 数据库创建 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图