江苏龙网

  1. 主页 > 生活百科 > >

线上MongoDB查询慢，如何通过索引优化直降响应时间？

MongoDB

作者 | 吴守阳
审校 | 重楼
背景

线上MongoDB查询慢，如何通过索引优化直降响应时间？

文章插图
线上某个页面的响应速度异常缓慢，达到了16秒，严重影响了业务的正常运行 。经过与研发的沟通得知 ， 该页面调用的数据集合只会保留7天的数据，集合有6000万条记录 。针对过期数据的处理，使用了根据 create_time 字段创建的过期索引，以自动使数据失效 。此外 ， 数据集合还通过 company_id 字段进行了哈希分片 。
问题排查慢语句分析【线上MongoDB查询慢，如何通过索引优化直降响应时间？】在后台拿到了慢查询语句 ， 如下：
db.visitor.find({ "company_id": 13272, "create_time": { "$gte": ISODate("2024-04-11T00:00:00.000+0800"), "$lte": ISODate("2024-04-11T23:59:59.000+0800") }});db.visitor.find({ "company_id": 13272, "create_time": { "$gte": ISODate("2024-04-12T00:00:00.000+0800"), "$lte": ISODate("2024-04-18T23:59:59.000+0800") }});很简单的一个查询，语句上没有再优化的必要了 。如果索引都在不应该出现这种十多秒的耗时，接下来开始分析索引 。
索引分析索引如下：
db.getCollection("visitor").createIndex({ "company_id": "hashed"}, { name: "company_id_hashed"});db.getCollection("visitor").createIndex({ "company_id": NumberInt("1")}, { name: "company_id_1"});db.getCollection("visitor").createIndex({ "create_time": NumberInt("1")}, { name: "create_time_1", expireAfterSeconds: NumberInt("604800")});其中：
  • company_id_hashed：创建集合分片使用的hash索引
  • company_id_1：普通查询的索引
  • create_time_1：过期时间的索引
    就这点数据量，按理说会用到索引的，不应该执行耗时16s，接下来执行计划分析 。
ExplAIn执行计划winningPlan"stage": "SHARDING_FILTER", "inputStage": { "stage": "FETCH", "filter": { "$and": [ { "company_id": { "$eq": 13272 } }, { "create_time": { "$lte": ISODate("2024-04-17T15:59:59.000Z") } }, { "create_time": { "$gte": ISODate("2024-04-10T16:00:00.000Z") } } ] }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "company_id": "hashed" }, "indexName": "company_id_hashed", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "company_id": [ "[7977521071453068053, 7977521071453068053]"这部分显示只用到了company_id_hashed索引，没有用到create_time_1索引 。
rejectedPlans"stage": "SHARDING_FILTER", "inputStage": { "stage": "FETCH", "filter": { "company_id": { "$eq": 13272 } }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "create_time": 1 }, "indexName": "create_time_1", "isMultiKey": false, "multiKeyPaths": { "create_time": [ ] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "create_time": [ "[new Date(1712764800000), new Date(1713369599000)]" ] } } } }, { "stage": "SHARDING_FILTER", "inputStage": { "stage": "FETCH", "filter": { "$and": [ { "create_time": { "$lte": ISODate("2024-04-17T15:59:59.000Z") } }, { "create_time": { "$gte": ISODate("2024-04-10T16:00:00.000Z") } } ] }, "inputStage": { "stage": "IXSCAN", "keyPattern": { "company_id": 1 }, "indexName": "company_id_1", "isMultiKey": false, "multiKeyPaths": { "company_id": [ ] },这部分显示的是被拒绝的执行计划列表，不会用到company_id_1、create_time_1索引 。
executionStats"nReturned": NumberInt("229707"), "executionTimeMillis": NumberInt("15668"), "totalKeysExamined": NumberInt("238012"), "totalDocsExamined": NumberInt("238012"), "executionStages": { "stage": "SINGLE_SHARD", "nReturned": NumberInt("229707"), "executionTimeMillis": NumberInt("15668"), "totalKeysExamined": NumberInt("238012"), "totalDocsExamined": NumberInt("238012"), "totalChildMillis": NumberLong("15667"), "shards": [ { "shardName": "d-m5eee03fdeaeaee4", "executionSuccess": true, "executionStages": { "stage": "SHARDING_FILTER", "nReturned": NumberInt("229707"), "executionTimeMillisEstimate": NumberInt("14996"), "works": NumberInt("238013"), "advanced": NumberInt("229707"), "needTime": NumberInt("8305"), "needYield": NumberInt("0"), "saveState": NumberInt("1980"), "restoreState": NumberInt("1980"), "iseoF": NumberInt("1"), "chunkSkips": NumberInt("0"), "inputStage": { "stage": "FETCH", "filter": { "$and": [ { "company_id": { "$eq": 13272 } }, { "create_time": { "$lte": ISODate("2024-04-17T15:59:59.000Z") } }, { "create_time": { "$gte": ISODate("2024-04-10T16:00:00.000Z") } } ] }, "nReturned": NumberInt("229707"), "executionTimeMillisEstimate": NumberInt("14595"), "works": NumberInt("238013"), "advanced": NumberInt("229707"), "needTime": NumberInt("8305"), "needYield": NumberInt("0"), "saveState": NumberInt("1980"), "restoreState": NumberInt("1980"), "isEOF": NumberInt("1"), "docsExamined": NumberInt("238012"), "alreadyHasObj": NumberInt("0"), "inputStage": { "stage": "IXSCAN", "nReturned": NumberInt("238012"), "executionTimeMillisEstimate": NumberInt("251"), "works": NumberInt("238013"), "advanced": NumberInt("238012"), "needTime": NumberInt("0"), "needYield": NumberInt("0"), "saveState": NumberInt("1980"), "restoreState": NumberInt("1980"), "isEOF": NumberInt("1"), "keyPattern": { "company_id": "hashed" }, "indexName": "company_id_hashed", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": NumberInt("2"), "direction": "forward", "indexBounds": { "company_id": [ "[7977521071453068053, 7977521071453068053]" ] }, "keysExamined": NumberInt("238012"), "seeks": NumberInt("1"), "dupsTested": NumberInt("0"), "dupsDropped": NumberInt("0")


推荐阅读


上一篇：猜猜骗子的经济学修养有多深

下一篇：引入缓存竟然会带来这么多问题？