账号密码登录
微信安全登录
微信扫描二维码登录

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

手机验证码登录
找回密码返回
邮箱找回 手机找回
注册账号返回
其他登录方式
分享
  • 收藏
    X
    mongodb走了索引,对timestamp同时 gte 和 lte 时速度慢
    13
    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
    更多回答
    扫一扫访问手机版

    回答动态

    师傅被妖怪抓走了:发布了悬赏问题火爆全网的“元宇宙”到底是什么?预计能赚取 1积分收益
    酒后略带风情:发布了悬赏问题Android内存中Graphics与Code占用过高预计能赚取 10积分收益
    扛起拖把扫天下:发布了悬赏问题typescript如何导出接口配置?求解,谢谢!预计能赚取 10积分收益
    发布了悬赏问题Vue 如何改变返回页面的路径?如让B页面只能返回到A页面.预计能赚取 11积分收益
    发布了悬赏问题vue H5移动端;底部bottom固定定位,安卓弹起键盘导致页面变形如何解决?预计能赚取 10积分收益
    发布了悬赏问题请问为什么tomcat8.5 GET请求中文正常,POST请求还需要设置UTF-8.预计能赚取 10积分收益
    发布了悬赏问题vue2.5+ts创建以及引入jq的方法预计能赚取 11积分收益
    情场浪女:发布了悬赏问题import ggplot时报错module 'pandas' has no attribute 'tslib'预计能赚取 10积分收益
    见心书画许老师18753375161:发布了悬赏问题echarts3 legend禁止点击事件,允许悬浮事件预计能赚取 11积分收益
    aa_92cb0bd09f:发布了悬赏问题codewar上的一个问题预计能赚取 11积分收益