MongoDB 创建索引以提高查询效率
🏷️ MongoDB
db.phones
中有 11w 条记录,通过 explain
方法解析查询语句:
js
db.phones.find({display: "+1 800-5550010"}).explain("executionStats")
1
分析结果如下:
点击查看分析结果
json
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "book.phones",
"indexFilterSet" : false,
"parsedQuery" : {
"display" : {
"$eq" : "+1 800-5550010"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"display" : {
"$eq" : "+1 800-5550010"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 52,
"totalKeysExamined" : 0,
"totalDocsExamined" : 110000,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"display" : {
"$eq" : "+1 800-5550010"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 30,
"works" : 110002,
"advanced" : 1,
"needTime" : 110000,
"needYield" : 0,
"saveState" : 859,
"restoreState" : 859,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 110000
}
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "3.2.4",
"gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
},
"ok" : 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
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
可以看出执行需要 52ms,总共扫描了 110000 条记录;
使用 ensureIndex
命令在 display
属性上创建唯一索引:
json
db.phones.ensureIndex(
{display : 1},
{unique : true, dropDups : true }
)
1
2
3
4
2
3
4
执行结果:
json
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
1
2
3
4
5
6
2
3
4
5
6
再次执行 explain
结果如下:
点击查看 explain 结果
json
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "book.phones",
"indexFilterSet" : false,
"parsedQuery" : {
"display" : {
"$eq" : "+1 800-5550010"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"display" : 1
},
"indexName" : "display_1",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"display" : [
"[\"+1 800-5550010\", \"+1 800-5550010\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 1,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"display" : 1
},
"indexName" : "display_1",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"display" : [
"[\"+1 800-5550010\", \"+1 800-5550010\"]"
]
},
"keysExamined" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "3.2.4",
"gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
},
"ok" : 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
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
执行只需要 1ms,扫描的记录也变成了 1.