almost 4 years ago

這是實際遇到 mongoDB query 的案例
有 user 反應,有一link 無法點選,查 log 後,發現是 mongo time out

問題尋找

找看看是否有用 index => 有的,有建立 index
db.xxx_log_msa_summary_2014_10.getIndexes()

index 資料
/* 0 */
{
    "4" : {
        "v" : 1,
        "key" : {
            "msasummary.revtime" : 1
        },
        "ns" : "xxx.xxx_log_msa_summary_2014_10",
        "name" : "sidx_idx_log_recvtime"
    }
}

用 explain 檢查一下

mongo explain
 db.xxx_log_msa_summary_2014_10.find({"msasummary.revtime": 
        {"$lte":1413282050, "$gte": 1413282049}}
        ).explain()
explain 結果
/* 0 */
{
    "clusteredType" : "ParallelSort",
    "shards" : {
        "xxxxx" : [ 
            {
                "cursor" : "BtreeCursor sidx_idx_log_recvtime",
                "isMultiKey" : true,
                "n" : 0,
                "nscannedObjects" : 2503268,
                "nscanned" : 2503268,
                "nscannedObjectsAllPlans" : 2503268,
                "nscannedAllPlans" : 2503268,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 3229,
                "nChunkSkips" : 0,
                "millis" : 14578,
                "indexBounds" : {
                    "msasummary.revtime" : [ 
                        [ 
                            -1.797693134862316e+308, 
                            1413282050
                        ]
                    ]
                },
                "server" : "xxxx"
            }
        ]
    },
    "cursor" : "BtreeCursor sidx_idx_log_recvtime",
    "n" : 0,
    "nChunkSkips" : 0,
    "nYields" : 3229,
    "nscanned" : 2503268,
    "nscannedAllPlans" : 2503268,
    "nscannedObjects" : 2503268,
    "nscannedObjectsAllPlans" : 2503268,
    "millisShardTotal" : 14578,
    "millisShardAvg" : 14578,
    "numQueries" : 1,
    "numShards" : 1,
    "indexBounds" : {
        "msasummary.revtime" : [ 
            [ 
                -1.797693134862316e+308, 
                1413282050
            ]
        ]
    },
    "millis" : 14579
}

由 explain 中,看到怪的是
為何 msasummary.revtime 中的查詢條件是: [-1.797693134862316e+308, 1413282050]
由很小很小的值,到 輸入 mongo 查詢的上限條件

發生原因

經一連串的 google 後,找出原因如下
針對 upper and lower bounds 的 case , 在 array 的資料,只會選擇單邊處理

搜尋記錄

Order of $lt and $gt in MongoDB range query

資料摘要
mongo 語法
       "endedAtMS" : { "$lt" : 1321284969946 , 
                       "$gt" : 1301284969946}}).

mongo explain 結果
        "endedAtMS" : [
            [
                1321284969946,
                -1.7976931348623157e+308
            ]

mongo 語法
        "endedAtMS" : { "$gt":1301284969946, 
                        "$lt" : 1321284969946}}).

mongo explain 結果
        "endedAtMS" : [
            [
                1.7976931348623157e+308,
                1301284969946
            ]

The short answer is a multikey index is used ("player" is an array), and the index cannot be constrained on both upper and lower bounds.

Reference

← git 的 cherry-pick 記錄 - Fix – Remote session was disconnected because there are no Remote Desktop client access licenses →