我想弄明白为什么下面的mongodb聚合查询需要这么长时间,而条件是18$or
。
我可以删除任何一个条件(使其成为17),查询在不到100毫秒的时间内完成,但是18个条件会跳到大约20秒。
我可以理解是否有特定的情况导致了问题,但是我无法理解为什么我可以移除其中任何一个,并且响应时间下降到大约100毫秒。
我还检查了运行此命令时的内存使用情况,它只占框总内存的15%左右。据我所知,MongoDB没有指定的内存限制?
此外,查询会返回预期的结果。
任何帮助/见解将不胜感激!
提前谢谢!
db.getCollection('messages').aggregate([
{
$match: {
$or: [
{"$and":[{"channel_id":8},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":64},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":9},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":77},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":76},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":6},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":62},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":63},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":84},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":99},{"created_date":{"$gt":1436793531100}}]},
{"$and":[{"channel_id":1},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":93},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":148},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":114},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":145},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":174},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":175},{"created_date":{"$gt":0}}]},
{"$and":[{"channel_id":146},{"created_date":{"$gt":0}}]}
]
}
},
{
$group: { _id : '$channel_id', count: { $sum: 1 }}
}
]);
编辑1:
我试过删除sammaye指出的多余的
$and
语句,结果是一样的。我还注意到,我可以添加19
$or
条件,它在100毫秒内返回。我也在MongoDB的2.6.7版本上。
我在
channel_id
和created_date
字段上都有索引。我试图向aggregate命令添加
explain
选项,正如ishamael所建议的,但是它似乎对输出没有任何影响。另外,在查询的末尾添加.hint( { channel_id: 1 } )
或.hint( { created_date: 1 } )
似乎也不起作用,我最终得到一个typeerror。下面是更新后的查询-打开
explain
:db.getCollection('messages').aggregate([
{
$match: {
$or: [
{"channel_id":8, "created_date":{"$gt":0}},
{"channel_id":64, "created_date":{"$gt":0}},
{"channel_id":9, "created_date":{"$gt":0}},
{"channel_id":77, "created_date":{"$gt":0}},
{"channel_id":76, "created_date":{"$gt":0}},
{"channel_id":6, "created_date":{"$gt":0}},
{"channel_id":62, "created_date":{"$gt":0}},
{"channel_id":63, "created_date":{"$gt":0}},
{"channel_id":84, "created_date":{"$gt":0}},
{"channel_id":99, "created_date":{"$gt":1436793531100}},
{"channel_id":1, "created_date":{"$gt":0}},
{"channel_id":93, "created_date":{"$gt":0}},
{"channel_id":148, "created_date":{"$gt":0}},
{"channel_id":114, "created_date":{"$gt":0}},
{"channel_id":145, "created_date":{"$gt":0}},
{"channel_id":174, "created_date":{"$gt":0}},
{"channel_id":175, "created_date":{"$gt":0}},
{"channel_id":146, "created_date":{"$gt":0}}
]
}
},
{
$group: { _id : '$channel_id', count: { $sum: 1 }}
}
], { explain: true });
结果是:
{
"result" : [
{
"_id" : 175,
"count" : 4.0000000000000000
},
{
"_id" : 146,
"count" : 1.0000000000000000
},
{
"_id" : 145,
"count" : 3.0000000000000000
},
{
"_id" : 8,
"count" : 17.0000000000000000
},
{
"_id" : 148,
"count" : 1.0000000000000000
},
{
"_id" : 84,
"count" : 16.0000000000000000
},
{
"_id" : 1,
"count" : 5067.0000000000000000
},
{
"_id" : 76,
"count" : 15.0000000000000000
},
{
"_id" : 77,
"count" : 2.0000000000000000
},
{
"_id" : 174,
"count" : 8.0000000000000000
},
{
"_id" : 64,
"count" : 2.0000000000000000
},
{
"_id" : 93,
"count" : 7.0000000000000000
},
{
"_id" : 6,
"count" : 93.0000000000000000
},
{
"_id" : 114,
"count" : 6.0000000000000000
}
],
"ok" : 1.0000000000000000
}
最佳答案
仍然不能百分之百确定这是否是2.6.7中的一个bug,但是升级到最新版本(发布时是3.0.4)似乎可以解决我的问题。
关于mongodb - MongoDB $ or/$ and命令优化,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31389746/