初始化
创建 collection
可以跳过，插入数据会默认创建。
初始化数据
[plaintext]
1for(i=1;i<=10000;i++){db.test.insert({"id":i,"name":"test", "amount": i+10000})}
[plaintext]
1for(i=1;i<=10000;i++){db.test.insert({"id":i,"name":"test2", "amount": i+10000})}
[plaintext]
1for(i=1;i<=10000;i++){db.test.insert({"id":i,"name":"test3", "amount": i+10000})}
[plaintext]
1for(i=1;i<=30000;i++){db.test.insert({"id":i,"name":"test4", "amount": i+10000})}
创建索引
[plaintext]
1db.getCollection('test').createIndex({"name":1, "amount":1}, {"name":"name_amount_ix"})
总数查看
[plaintext]
1db.getCollection('test').count()
共计初始化 6W 条数据。
执行测试
基础测试
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test"} },
{ $group: { _id: "$name", totalAmount: { $sum: "$amount" }, totalCount: {$sum:1}} }
])
耗时：0.015s 和数量基本没有关系。
降低查询的数量
确定是否耗时和数量成线性
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test", id: {$lt: 5000}} },
{ $group: { _id: "$name", totalAmount: { $sum: "$amount" }, totalCount: {$sum:1}} }
])
发现耗时平均为 0.018s 反而耗时更多了。因为这里没有命中索引，不具有代表性
降低查询的数量-命中索引
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test", amount: {$lt: 15000}} },
{ $group: { _id: "$name", totalAmount: { $sum: "$amount" }, totalCount: {$sum:1}} }
])
耗时 0.008 接近降低一半。
经过测试耗时和数量是线性的关系。
是否为聚合运算耗时测试
有聚合
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test" }},
{ $group: { _id: "$name", totalAmount: { $sum: "$amount" }, totalCount: {$sum:1}} }
])
耗时：0.014s
- 执行计划
[plaintext]
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{
"stages" : [
{
"$cursor" : {
"query" : {
"name" : "test"
},
"fields" : {
"amount" : 1,
"name" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.test",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "test"
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"amount" : 1,
"name" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1.0,
"amount" : 1.0
},
"indexName" : "name_amount_ix",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [],
"amount" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"test\", \"test\"]"
],
"amount" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : []
}
}
},
{
"$group" : {
"_id" : "$name",
"totalAmount" : {
"$sum" : "$amount"
},
"totalCount" : {
"$sum" : {
"$const" : 1.0
}
}
}
}
],
"ok" : 1.0
}
不进行集合计算
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test" }},
{ $group: { _id: "$name", totalCount: {$sum:1}} }
])
耗时：0.013 s
- 执行计划
[plaintext]
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/* 1 */
{
"stages" : [
{
"$cursor" : {
"query" : {
"name" : "test"
},
"fields" : {
"name" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.test",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "test"
}
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"name" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1.0,
"amount" : 1.0
},
"indexName" : "name_amount_ix",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [],
"amount" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"test\", \"test\"]"
],
"amount" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : []
}
}
},
{
"$group" : {
"_id" : "$name",
"totalCount" : {
"$sum" : {
"$const" : 1.0
}
}
}
}
],
"ok" : 1.0
}
去掉 group
[plaintext]
1
2
3
4db.getCollection('test').aggregate([
{ $match: {name:"test" }},
{ $group: { _id: null, totalCount: {$sum:1}} }
])
耗时：0.005s
集合状态
[plaintext]
1db.getCollection('test').stats()
