跳到主要内容

MongoDB 慢查询优化

什么是慢查询

慢查询是指执行时间较长的查询操作。

通常,数据库查询都会迅速的完成,但当表的数据量非常庞大(例如数十万行或更多)时,频繁地从这些表中查询会增加数据库的负担。这可能导致硬件资源的过度使用,例如CPU占用率较高、内存使用率过大、系统负载过高。因此,查询操作会变得“慢”,单次查询耗时数百毫秒甚至数秒钟。

为了优化查询性能,我们可以采取一些措施,例如:

  • 索引优化:创建适当的索引以加速查询。
  • 查询优化:编写高效的查询方式,避免不必要的计算和数据传输。
  • 硬件优化:增加服务器的硬件资源,以处理更大的数据集。
  • 多集群存储数据:参考应用数据多集群存储行记录多集群存储,增加纵向能力,减轻业务持续增长对单一数据库持续带来压力。

索引优化

在 HAP 社区中,有一位同学以自己见解写了一篇 MongoDB的索引优化 帖子,这里作为引用,大家可以参考。

索引的基本知识与索引创建管理也可以参考 HAP 帮助文档

查询优化

对于数十万行或更多数据量的工作表,在做批量筛选、查询时,要注重查询方式,提前创建查询条件中涉及到的字段索引。

通常,最佳的查询方式是精确查询,比如使用 “等于” 条件。尤其是当字段值不重复且已建立索引时,这种查询方式的效率极高。

另外,也有一些查询方式是不会走索引的:

  • 筛选条件中,不等于、不包含、开头不是等否定条件,都不会走索引

  • 筛选条件中包含、为空的筛选条件,不走索引

  • 筛选条件中,增加 “或” 的筛选条件,不走索引

  • 工作表右上角的搜索功能为正则查询方式,不走索引

  • 选项类型字段,如果单个选项值占比高,搜索不会走索引

  • 文本字段勾选了支持拼音排序时,排序索引不生效

对于数据量庞大的工作表,尽量减少使用以上列举的大批量查询条件,可以降低数据库负载。

硬件优化

  • 扩容 CPU 核数:

    • MongoDB 是一个多线程应用程序,因此增加 CPU 核数可以提高并发处理能力。
    • 选择高性能的多核 CPU,以便更好地处理查询和写入操作。
  • 扩容内存:

    • 内存对 MongoDB 性能至关重要。增加内存可以提高缓存效率,减少磁盘 I/O。
    • MongoDB 使用内存作为工作集缓存,因此足够的内存可以减少磁盘读取次数,提高查询性能。
  • 使用高性能 SSD 磁盘:

    • SSD 磁盘比传统的机械硬盘(HDD)更快,具有更低的访问延迟和更高的吞吐量。
    • MongoDB 的默认存储引擎是 WiredTiger,它对 SSD 磁盘的支持非常好。

慢查询索引优化实践

查找日志

在 MongoDB 进程占用 CPU 较高的情况下,通常是大量慢查询语句占用,可以通过查看 MongoDB 日志,找到对应慢查询语句,进一步优化。

MongoDB 日志默认路径:/data/hap/script/volume/data/logs/mongodb.log

如自定义过数据路径,请在实际的数据路径下寻找

日志分析示例

在日志最新的内容中,可以看到很多的日志内容,这里挑选两个示例进行分析

示例一

2023-03-08T15:50:43.075+0800 I COMMAND  [conn38294] command mdwsrows.ws63083dafdf551460042a73 command: find { find: "ws63083dafdf551460042a73", filter: { 63e0bafdafa62a19751ee00: "4115fd005", status: 1.0 }, sort: { utime: -1 }, projection: { _id: 0, status: 0, sharerange: 0, wsutime: 0, keywords: 0, discussunreads: 0, users: 0, owners: 0, unreads: 0 }, limit: 1 } planSummary: COLLSCAN keysExamined:0 docsExamined:381665 hasSortStage:1 cursorExhausted:1 numYields:2981 nreturned:1 reslen:1456 locks:{ Global: { acquireCount: { r: 5964 } }, Database: { acquireCount: { r: 2982 } }, Collection: { acquireCount: { r: 2982 } } } protocol:op_query 1023ms

重点关注字段含义:

  • mdwsrows:库
  • ws63083dafdf551460042a73:表名称
  • command:具体操作命令细节
  • find:查询条件
  • 63e0bafdafa62a19751ee00:字段(控件ID)
  • COLLSCAN:全表扫描
  • docsExamined:381665:表记录总数
  • op_query 1023ms:耗时

日志含义:查询 ws63083dafdf551460042a73 表中字段 63e0bafdafa62a19751ee00 值为 4115fd005 的数据,全表扫描总耗时 1023 毫秒

添加索引建议:给字段 63e0bafdafa62a19751ee00 添加索引

示例二:

2023-04-25T08:12:10.736+0000 I  COMMAND  [conn1696545] command mdwsrows.ws629f242342bb5f060f3da4 command: aggregate { aggregate: "ws629f242342bb5f060f3da4", pipeline: [ { $match: { 62c9c6076c186b941274189f: "90123c1d-1498-43a4-b96b-001a69fd4bb9", 62ccea346123b943f747acf: { $in: [ "0cda5ce1-32139-4c52-b2f4-b324545ccfd1", "2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0" ] }, $and: [ { 62c9c6076c186b941274189f: "90123c1d-1498-43a4-b96b-001a69fd4bb9" }, { 62ccea346123b943f747acf: { $in: [ "0cda5ce1-32139-4c52-b2f4-b324545ccfd1", "2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0" ] } } ], status: 1.0 } }, { $group: { _id: "null", count: { $sum: 1 } } } ], cursor: {}, $db: "mdwsrows", lsid: { id: UUID("d09a5780-0115-4182-8eb2-9de7d76fd834") }, $clusterTime: { clusterTime: Timestamp(1682410329, 2213), signature: { hash: BinData(0, 6B87048FC23C103EE23E4E026B9B246CD45F), keyId: 720303239516495874 } } } planSummary: COLLSCAN keysExamined:0 docsExamined:468021 cursorExhausted:1 numYields:3656 nreturned:1 queryHash:97671D20 planCacheKey:97671D20 reslen:282 locks:{ ReplicationStateTransition: { acquireCount: { w: 3658 } }, Global: { acquireCount: { r: 3658 } }, Database: { acquireCount: { r: 3658 } }, Collection: { acquireCount: { r: 3658 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 12110018, timeReadingMicros: 11575 } } protocol:op_msg 1043ms

重点关注字段含义:

  • mdwsrows:库
  • ws629f242342bb5f060f3da4:表名称
  • command:具体操作命令细节
  • aggregate:聚合操作
  • $match:过滤条件
  • 62c9c6076c186b941274189f:字段(控件ID)
  • 62ccea346123b943f747acf:字段(控件ID)
  • $in:包含
  • COLLSCAN:全表扫描
  • docsExamined:468021表记录总数
  • op_msg 1043ms:耗时

日志含义:在 mdwsrows 数据库中的 ws629f242342bb5f060f3da4 表中,使用聚合操作查询符合以下条件的数据:62c9c6076c186b941274189f 字段值为 "90123c1d-1498-43a4-b96b-001a69fd4bb9",62ccea346123b943f747acf 字段值包含 "0cda5ce1-32139-4c52-b2f4-b324545ccfd1"、"2e0e92ec-a935-43fa-a4a3-5fbdee85c0c0",全表扫描,总耗时为 1043 毫秒

添加索引建议:为 62c9c6076c186b941274189f 和 62ccea346123b943f747acf 字段添加组合索引

日志中表字段ID与工作表字段对应关系

得到工作表ID后可以通过访问 系统地址/worksheet/工作表ID 进入工作表页面

例如在日志中已知表名称为 ws643e4f73c70d5c2cc7285ce3,去除表名称开头的 ws,643e4f73c70d5c2cc7285ce3就是工作表ID

访问 系统地址/worksheet/643e4f73c70d5c2cc7285ce3 即可进入对应工作表

进入工作表后,应用管理员可以看到应用的 API 开发文档,如下图所示

在API开发文档中有字段对照表

可以通过字段对照表看到字段ID与字段名称的对应关系

添加索引

方式一:工作表配置页面添加

在 API 开发文档中找到字段ID对应的工作表字段后,到工作表配置页面,给对应字段添加索引

方式二:数据库后台添加

给上面两个示例中提到的表字段添加索引,登录到MongoDB数据库可以执行如下命令

use mdwsrows

db.ws63083dafdf551460042a73.createIndex({"63e0bafdafa62a19751ee00":1},{background: true})

db.ws629f242342bb5f060f3da4.createIndex({"62c9c6076c186b941274189f":1,"62ccea346123b943f747acf":1},{background: true})