MongoDB中 同时使用$or和sort()查询性能分析

作者:袖梨 2022-06-29
 代码如下 复制代码

mongos> db.find({ "user" : "jhon"}).sort({"name" :
1}).limit(100).explain()
{
        "cursor" : "BtreeCursor user_1",
        "nscanned" : 10100,
        "nscannedObjects" : 10100,
        "n" : 100,
        "scanAndOrder" : true,
        "millis" : 116,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "user" : [
                        [
                                "jhon",
                                "jhon"
                        ]
                ]
        }
}

Second, I do $or query with sort():
mongos> db.find({ "$or" : [ { "user" : "jhon"} , { "owner" :
"jhon"}]}).sort({"name" : 1}).limit(100).explain()
{
        "cursor" : "BtreeCursor name_1",
        "nscanned" : 1010090,
        "nscannedObjects" : 1010090,
        "n" : 100,
        "millis" : 3800,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "name" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        }
}

Last, I do $or query without sort():
mongos> db.find({ "$or" : [ { "user" : "jhon"} , { "owner" :
"jhon"}]}).limit(100).explain()
{
        "cursor" : "BtreeCursor user_1",
        "nscanned" : 100,
        "nscannedObjects" : 100,
        "n" : 100,
        "millis" : 0,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "user" : [
                        [
                                "jhon",
                                "jhon"
                        ]
                ]
        }
}

可以看出:

第一次查询中, 单独使用sort()时性能很好。

第二次查询中,联合使用了$or和sort()时,性能很差。

第三次查询中,单独使用$or,性能很好。

 

相关文章

精彩推荐