我想弄明白为什么下面的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_idcreated_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/

10-09 20:19
查看更多