震撼揭秘：线上MongoDB慢查询终极优化实战解析

数据运维 2024-05-30 共饮一杯手机阅读

背景

研发反馈指出，线上某个页面的响应速度异常缓慢，达到了16秒，严重影响了业务的正常运行。经过与研发的沟通得知，该页面调用的数据集合只会保留7天的数据，集合有6000万条记录。针对过期数据的处理，使用了根据 create_time 字段创建的过期索引，以自动使数据失效。此外，数据集合还通过 company_id 字段进行了哈希分片。

问题排查

慢语句分析

在后台拿到了慢查询语句，如下：

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：过期时间的索引

根据研发团队的反馈和对数据的分析，我们发现当前集合使用 company_id_hashed 索引进行分片存在问题。哈希索引对等值查询最为友好，但对于范围查询支持不佳。由于 company_id 是公司维度字段，相同数据较多，因此使用哈希分片并不合适。建议直接创建 company_id 和 create_time 的联合范围分片键。这样不仅能够友好地支持范围查询，还能更细粒度地拆分数据，提高查询和写入的效率。

针对当前情况就这点数据量，按理说会用到索引的，不应该执行耗时16s，接下来执行计划分析。

Explain执行计划

winningPlan

"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")

这部分显示的是查询的执行统计信息。

索引分析

通过explain的执行计划，可以看到索引的使用上存在问题，按理说company_id、create_time都已创建索引，为什么没有使用上？是什么使它失效，没有用上create_time索引？

下面列举了失效的情况：

索引选择性不高：由于查询条件是一个范围查询，create_time 字段可能有许多不同的值满足条件。因此，单键索引 create_time_1 的选择性（即索引中不同值的比例）可能不高，这使得使用该索引无法有效地减少需要检索的文档数量。
查询需要跨越多个索引键值：查询涉及到了两个字段 company_id 和 create_time。虽然索引 create_time_1 可以帮助过滤 create_time 符合条件的文档，但在执行查询时，还需要考虑 company_id 的匹配条件。因此，MongoDB 需要在两个索引之间进行查找和合并，而不是简单地使用单个索引来解决查询。
额外的查找和合并成本：在涉及多个条件的查询中，MongoDB 会尝试使用覆盖索引（Covered Index）来尽可能地减少在磁盘上的文档检索。然而，在这种情况下，create_time_1 索引不能单独满足查询条件，因此 MongoDB 还需要查找和合并从 company_id_1 索引中过滤出来的文档。这种额外的查找和合并过程会增加查询的成本，并且降低性能。

问题原因

首先，集合片键选择错误是问题的根本原因。由于集合的分片键是 company_id_hashed，查询必然会使用这个索引。然而，这引发了一系列连锁反应：即“查询需要跨越多个索引键值”和“额外的查找和合并成本”。

具体来说，由于需要进行范围查询，首先会使用 company_id_hashed 索引。然而，MongoDB 还需要查找和合并从 company_id_1 索引中过滤出来的文档。这种额外的查找和合并过程会增加查询的成本，并且降低性能。这也导致了 create_time_1 索引无法被有效利用。

针对此问题，我们将已有索引进行了整改，如下：

分片键不重做（达到毫秒级别）

//分片键不做修整
db.getCollection("visitor").createIndex({
"company_id": "hashed"
}, {
name: "company_id_hashed"
});


//添加范围联合索引
db.getCollection("js_visitor").createIndex({
    "company_id": NumberInt("1"),
    "create_time": NumberInt("1")
}, {
    name: "company_id_create_time"
});


//过期索引保留
db.getCollection("visitor").createIndex({
"create_time": NumberInt("1")
}, {
name: "create_time_1",
expireAfterSeconds: NumberInt("604800")
});


//删掉company_id
db.getCollection("visitor").createIndex({
"company_id": NumberInt("1")
}, {
name: "company_id_1"
});

分片键重做（最完美方案，但需要重新创建集合并迁移数据）

//分片键重做
sh.shardCollection("cmdb.visitor",{ "company_id": "1","create_time": "1"});


索引如下：
db.getCollection("js_visitor").createIndex({
    "company_id": NumberInt("1"),
    "create_time": NumberInt("1")
}, {
    name: "company_id_create_time"
});


//过期索引保留
db.getCollection("visitor").createIndex({
"create_time": NumberInt("1")
}, {
name: "create_time_1",
expireAfterSeconds: NumberInt("604800")
});

注意事项

1、选择合适的分片键

分片键应尽量均匀分布，以避免“热点”问题（即大多数查询集中在某些特定分片上，导致这些分片负载过重）。
常用的选择包括用户ID、时间戳等具有自然分布特性的字段。

2、查询模式

考虑主要的查询模式，选择的分片键应当能够最大化地利用分片查询。例如，如果大部分查询都是基于用户ID的，那么用户ID就是一个合适的分片键。

3、写操作分布

分片键应尽量避免集中写入。例如，使用时间戳作为分片键可能导致最新的分片上写入压力过大。

4、更改分片键

分片键在集合创建后无法更改，因此在设计时需要慎重选择。如果需要更改分片键，通常需要重新创建集合并迁移数据。

5、复合分片键

可以使用多个字段组合成复合分片键，以满足更复杂的查询需求。例如，使用 { userId: 1, timestamp: 1 } 作为分片键，可以优化基于用户ID和时间戳的查询。

6、哈希分片键

哈希分片键可以将数据均匀地分布到所有分片中，适合高并发的写入场景。例如，使用 { _id: "hashed" } 作为分片键。

总结

选择合适的分片键是MongoDB分片设计中的重要步骤。分片键的选择需要考虑数据的分布、查询模式和写操作分布等因素。理解分片键的约束和注意事项，可以帮助我们设计高效、可扩展的分布式数据库架构。

震撼揭秘：线上MongoDB慢查询终极优化实战解析

背景

问题排查

慢语句分析

索引分析

Explain执行计划

索引分析

问题原因

注意事项

总结

sql中查询重复数据怎么写

SQL中的三种去重方法小结

详解MySQL用事件调度器Event Scheduler创建定时任务

MySQL数据库启动命令(启动mysql命令语句)

「错题汇总」，一款高效易用的整理错题的数据库软件 (整理错题的数据库软件)