如何使用Datetime范围优化此mongoDB聚合
如何使用Datetime范围优化此mongoDB聚合
提问于 2015-06-11 17:05:11
当我尝试在mongoDB 3.0.3中使用日期范围时，我遇到了一个非常慢的聚合命令问题
首先是一些背景知识：
有问题的集合是来自VoIP系统的CDR(呼叫详细记录)。目前有337988521条记录，但我的查询只涉及一小部分。以下是提供范围概念的计数：
db.CDRs.find({"Date" : { $gte : new Date("2015-06-10T07:00:00Z") } }).count()
116114
db.CDRs.find({"Date" : { $gte : new Date("2015-06-10T08:00:00Z") } }).count()
0以下是我的索引：
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "HS4XC.CDRs"
},
{
"v" : 1,
"unique" : true,
"key" : {
"Date" : 1,
"CallID" : 1,
"TerminationAttempts.Attempt" : 1
},
"name" : "Date_1_CallID_1_TerminationAttempts.Attempt_1",
"ns" : "HS4XC.CDRs"
},
{
"v" : 1,
"key" : {
"Originator" : 1,
"TerminationAttempts.Terminator" : 1
},
"name" : "Originator_1_TerminationAttempts.Terminator_1",
"ns" : "HS4XC.CDRs"
},
{
"v" : 1,
"key" : {
"TerminationAttempts.Terminator" : 1,
"Originator" : 1
},
"name" : "TerminationAttempts.Terminator_1_Originator_1",
"ns" : "HS4XC.CDRs"
}所以你可以看到我在Date对象上有一个索引。
以下是集合中的示例文档(隐藏了公司数据)：
{
"_id" : ObjectId("5577e072e988475f14b9a159"),
"CallID" : "BW090000946100615-1715027548@XX.XX.XX.XX",
"Date" : ISODate("2015-06-10T07:00:00Z"),
"CallSetupTime" : NumberLong(36400),
"CallPDD" : NumberLong(324),
"TerminationAttempts" : [
{
"TermID" : "1234",
"Attempt" : NumberLong(1),
"DisconnectReason" : "503",
"TermCallSetupTime" : NumberLong(324),
"Media" : "XC RTP Proxy2",
"RoutingGroupID" : "0",
"ToIP" : "XX.XX.XX.XX",
"TermSrcID" : "5",
"BsideIP" : "XX.XX.XX.XX",
"TermPDD" : NumberLong(324),
"Terminator" : "terminator_name",
"RoutingResponse" : "sip34655938784@XX.XX.XX.XX;orig=1896;rate=1071;term=9035;cost=1071;fed=2,467;cliValid=0;Spam-Score=0;Test-No=0",
"ModifyTS" : ISODate("2015-06-10T07:00:09Z"),
"RouteType" : "0"
},
{
"MatchedRoutingPrefix" : "346",
"TermCallSetupTime" : NumberLong(36075),
"Media" : "XC RTP Proxy2",
"TermSrcID" : "1",
"TermPDD" : NumberLong(3545),
"RingingType" : "D",
"RoutingResponse" : "sip34655938784@.XX.XX.XX.XX;orig=1896;rate=1071;term=9035;cost=1071;fed=2,467;cliValid=0;Spam-Score=0;Test-No=0",
"FinalToNumber" : "+555-555-5555",
"RouteType" : "0",
"TermID" : "7472",
"Attempt" : NumberLong(2),
"DisconnectReason" : "487",
"RoutingGroupID" : "56",
"ToIP" : "XX.XX.XX.XX",
"BsideIP" : "XX.XX.XX.XX",
"Terminator" : "terminator_name",
"ModifyTS" : ISODate("2015-06-10T07:00:48Z")
}
],
"LocationName" : "Spain",
"OrigToNumber" : "+555-555-5555",
"CreatorID" : "16120",
"Authentication" : "XX.XX.XX.XX",
"Aside" : "XC Opensips A Side",
"UserID" : "GAA-Z",
"Originator" : "Originator_name",
"ToNumber" : "555-555-5555",
"CountryID" : "807",
"UserGroupID" : "1896",
"LocationID" : "14530",
"FederationID" : "0",
"PDD" : NumberLong(9219)
}所以如果我运行这个查询，它是非常快的：
db.CDRs.aggregate([
{$match: {"Date": {"$gte": new Date("2015-06-10T07:00:00Z")} } },
{$unwind: '$TerminationAttempts'},
{$group: {
_id: {"Originator": "$Originator"},
"minutes": {$sum: "$TerminationAttempts.Duration" }
}}
])但是如果我运行这个查询，它需要几个小时
db.CDRs.aggregate([
{$match: {"Date": {"$gte": new Date("2015-06-10T07:00:00Z"), "$lt": new Date("2015-06-10T07:10:00Z") } } },
{$unwind: '$TerminationAttempts'},
{$group: {
_id: {"Originator": "$Originator"},
"minutes": {$sum: "$TerminationAttempts.Duration" }
}}
])最后是对慢查询的解释：
{
"stages" : [
{
"$cursor" : {
"query" : {
"Date" : {
"$gte" : ISODate("2015-06-10T07:00:00Z"),
"$lt" : ISODate("2015-06-10T07:10:00Z")
}
},
"fields" : {
"Originator" : 1,
"TerminationAttempts" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "HS4XC.CDRs",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Date" : {
"$lt" : ISODate("2015-06-10T07:10:00Z")
}
},
{
"Date" : {
"$gte" : ISODate("2015-06-10T07:00:00Z")
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"Date" : {
"$gte" : ISODate("2015-06-10T07:00:00Z")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Date" : 1,
"CallID" : 1,
"TerminationAttempts.Attempt" : 1
},
"indexName" : "Date_1_CallID_1_TerminationAttempts.Attempt_1",
"isMultiKey" : true,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"(true, new Date(1433920200000))"
],
"CallID" : [
"[MinKey, MaxKey]"
],
"TerminationAttempts.Attempt" : [
"[MinKey, MaxKey]"
]
}
}
}
},
"rejectedPlans" : [ ]
}
}
},
{
"$unwind" : "$TerminationAttempts"
},
{
"$group" : {
"_id" : {
"Originator" : "$Originator"
},
"minutes" : {
"$sum" : "$TerminationAttempts.Duration"
}
}
}
],
"ok" : 1
}帮助!
回答 1
Stack Overflow用户
发布于 2015-06-11 18:53:31
在添加了Date only索引之后，请求新的explain输出。谢谢@user3561036
{
"stages" : [
{
"$cursor" : {
"query" : {
"Date" : {
"$gte" : ISODate("2015-06-10T07:00:00Z"),
"$lt" : ISODate("2015-06-10T07:10:00Z")
}
},
"fields" : {
"Originator" : 1,
"TerminationAttempts" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "HS4XC.CDRs",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Date" : {
"$lt" : ISODate("2015-06-10T07:10:00Z")
}
},
{
"Date" : {
"$gte" : ISODate("2015-06-10T07:00:00Z")
}
}
]
},
"winningPlan" : {
"stage" : "CACHED_PLAN",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Date" : 1
},
"indexName" : "Date_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(1433919600000), new Date(1433920200000))"
]
}
}
}
},
"rejectedPlans" : [ ]
}
}
},
{
"$unwind" : "$TerminationAttempts"
},
{
"$group" : {
"_id" : {
"Originator" : "$Originator"
},
"minutes" : {
"$sum" : "$TerminationAttempts.Duration"
}
}
}
],
"ok" : 1
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30776268
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号