账号密码登录
忘记密码注册账号
微信安全登录
微信扫描二维码登录

登录后绑定QQ、微信即可实现信息互通

手机验证码登录
获取验证码
找回密码返回
邮箱找回 手机找回
获取验证码
注册账号返回
获取验证码
其他登录方式
登录即同意《用户使用协议》和《用户隐私条款》
问答主页 -> 电脑网络
分享
  • 收藏
    X
    mongodb走了索引,对timestamp同时 gte 和 lte 时速度慢
    33
    0
    1，数据结构如下 "_id" : ObjectId("61c3cf02215baac3fdc00a71"), "ST" : "31", "CN" : "2011", "PW" : "123456", "MN" : "202101070000000000000002", "w21003-Rtd" : "0.086", "w21003-Flag" : "N", "w21011-Rtd" : "0.196", "w21011-Flag" : "N", "w01018-Rtd" : "13.00", "w01018-Flag" : "N", "timestamp" : 16402224712，查询语句如下db.getCollection('xxx').find({'timestamp': {"$gte": 1639670400, "$lte": 1640275199}, 'MN': "202101070000000000000002", 'CN': "2011"}).skip(0).limit(20)执行计划如下{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "xxx", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "CN" : { "$eq" : "2011" } }, { "MN" : { "$eq" : "202101070000000000000002" } }, { "timestamp" : { "$lte" : 1640275199.0 } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "winningPlan" : { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "timestamp" : -1.0, "MN" : 1.0, "CN" : 1.0, "log_key" : 1.0 }, "indexName" : "timestamp_-1_MN_1_CN_1_log_key_1", "isMultiKey" : false, "multiKeyPaths" : { "timestamp" : [], "MN" : [], "CN" : [], "log_key" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "timestamp" : [ "[1640275199.0, 1639670400.0]" ], "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "log_key" : [ "[MinKey, MaxKey]" ] } } } }, "rejectedPlans" : [ { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "CN" : { "$eq" : "2011" } }, { "timestamp" : { "$lte" : 1640275199.0 } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0 }, "indexName" : "MN_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ] } } } }, { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "timestamp" : { "$lte" : 1640275199.0 } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0, "CN" : 1.0, "QN" : 1.0 }, "indexName" : "MN_1_CN_1_QN_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [], "CN" : [], "QN" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "QN" : [ "[MinKey, MaxKey]" ] } } } }, { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "timestamp" : { "$lte" : 1640275199.0 } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0, "CN" : 1.0, "DataTime" : 1.0 }, "indexName" : "MN_1_CN_1_DataTime_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [], "CN" : [], "DataTime" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "DataTime" : [ "[MinKey, MaxKey]" ] } } } } ] } "ok" : 1.0}查询耗时5秒3，更换查询语句如下db.getCollection('xxxx').find({'timestamp': {"$gte": 1639670400}, 'MN': "202101070000000000000002", 'CN': "2011"}).skip(0).limit(20)执行计划{ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "xxxx", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "CN" : { "$eq" : "2011" } }, { "MN" : { "$eq" : "202101070000000000000002" } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "winningPlan" : { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "timestamp" : -1.0, "MN" : 1.0, "CN" : 1.0, "log_key" : 1.0 }, "indexName" : "timestamp_-1_MN_1_CN_1_log_key_1", "isMultiKey" : false, "multiKeyPaths" : { "timestamp" : [], "MN" : [], "CN" : [], "log_key" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "timestamp" : [ "[inf.0, 1639670400.0]" ], "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "log_key" : [ "[MinKey, MaxKey]" ] } } } }, "rejectedPlans" : [ { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "CN" : { "$eq" : "2011" } }, { "timestamp" : { "$gte" : 1639670400.0 } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0 }, "indexName" : "MN_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ] } } } }, { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "timestamp" : { "$gte" : 1639670400.0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0, "CN" : 1.0, "QN" : 1.0 }, "indexName" : "MN_1_CN_1_QN_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [], "CN" : [], "QN" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "QN" : [ "[MinKey, MaxKey]" ] } } } }, { "stage" : "LIMIT", "limitAmount" : 20, "inputStage" : { "stage" : "FETCH", "filter" : { "timestamp" : { "$gte" : 1639670400.0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "MN" : 1.0, "CN" : 1.0, "DataTime" : 1.0 }, "indexName" : "MN_1_CN_1_DataTime_1", "isMultiKey" : false, "multiKeyPaths" : { "MN" : [], "CN" : [], "DataTime" : [] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "MN" : [ "[\"202101070000000000000002\", \"202101070000000000000002\"]" ], "CN" : [ "[\"2011\", \"2011\"]" ], "DataTime" : [ "[MinKey, MaxKey]" ] } } } } ] },}查询耗时:0.07秒4，服务器内存消耗MongoDB 版本 version v4.0.12
    0
    打赏
    收藏
    点击回答
        全部回答
    • 共 0 条
    0 打赏 回复
    更多回答
    网站公告
    扫一扫访问手机版

    回答动态

    • 回到顶部
    • 回到顶部