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