首页 > 代码库 > mongo 聚合查询
mongo 聚合查询
*****************未加索引******************************************
> var startTime = new Date();
> db.temMinlog.find({"id":0}).count();
1215
> (new Date().getTime()-startTime.getTime())/1000
194.811s
******************加索引*****************************************
> db.temMinlog.ensureIndex({"_id":1,"id":1});
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
******************加索引后查询*****************************************
> var startTime = new Date();
> db.temMinlog.find({"id":0}).count();
1215
> (new Date().getTime()-startTime.getTime())/1000
227.247s
时间范围检索
db.temMinlog.find({"_id":{$gte:1498703926535,$lte:1498704749897},"id":0}).explain("executionStats");
结果"executionStats" : {
"executionSuccess" : true,
"nReturned" : 52,
"executionTimeMillis" : 9506,
"totalKeysExamined" : 518000,
"totalDocsExamined" : 52,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 52,
"executionTimeMillisEstimate" : 2175,
"works" : 518001,
"advanced" : 52,
"needTime" : 517947,
"needYield" : 0,
"saveState" : 8151,
"restoreState" : 8151,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 52,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 52,
"executionTimeMillisEstimate" : 2145,
模糊查询
db.temMinlog.find({"_id":{$gte:1498703926535,$lte:1498704749897},"id":/0/}).explain("executionStats");
结果 "executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 9893,
"totalKeysExamined" : 518001,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 2237,
"works" : 518002,
"advanced" : 1,
"needTime" : 517999,
"needYield" : 0,
"saveState" : 8151,
"restoreState" : 8151,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"id" : {
"$regex" : "0"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 2185,
模糊count（）
> var startTime = new Date();
> db.temMinlog.find({"_id":{$gte:1498703926535,$lte:1498704749897},"id":{$in:[1,2,3]}}).count();
156
> (new Date().getTime()-startTime.getTime())/1000
11.232s
*************************group by 统计*************************************
db.temMinlog.aggregate([{$group:{_id:"$id",num_tutorial:{$sum:1}}}]);
db.temMinlog.aggregate([{$group:{_id:"$id",max_v0:{$max:"$v0"},min_v1:{$min:"$v1"},count:{$sum:1}}}],{ allowDiskUse: true });
db.temMinlog.aggregate([{$group:{_id:"$id",max_v0:{$max:"$v0"},min_v1:{$min:"$v1"},avg_v2:{$avg:"$v2"},count:{$sum:1}}}],{ allowDiskUse: true });
db.temMinlog.aggregate([{$match:{_id:{$gte:1498703926535,$lte:1498704749897}}},{$group:{_id:"$id",max_v0:{$max:"$v0"},min_v1:{$min:"$v1"},avg_v2:{$avg:"$v2"},sum_v2:{$sum:"$v2"},count:{$sum:1}}}],{ allowDiskUse: true });
结果：12.194s
db.temMinlog.aggregate([{$match:{_id:{$gte:1498703926535,$lte:1498704749897}}},{$limit:5},{$group:{_id:"$id",max_v0:{$max:"$v0"},min_v1:{$min:"$v1"},avg_v2:{$avg:"$v2"},sum_v2:{$sum:"$v2"},count:{$sum:1}}},{$sort:{_id:-1}}],{ allowDiskUse: true });
$match：匹配条件，可选
$limit：结果条数，可选
$group：聚合规则
$sort：对查询结果排序，-1表示降序
db.temMinlog.aggregate([{$match:{_id:{$gte:1498703926535,$lte:1498704749897}}},{$group:{_id:"$id",max_v0:{$max:"$v0"},min_v1:{$min:"$v1"},avg_v2:{$avg:"$v2"},sum_v2:{$sum:"$v2"},count:{$sum:1}}},{$limit:5},{$sort:{_id:-1}}]);
结果：21.048s
mongo 聚合查询