流行的开源数据库们有哪些维护索引的怪癖？

作者：Yves Trudeau，Percona 首席架构师，专注于 MySQL Cluster、Pacemaker 和 XtraDB 集群等分布式技术。

原文：https://www.percona.com/blog/osdb-index-maintenance/，July 23, 2025

爱可生开源社区翻译，本文约 3100 字，预计阅读需要 10 分钟。

索引维护的挑战

对于任何数据库管理者来说，索引维护都可能是一项真正的挑战，而更棘手的是，不同的开源数据库对索引维护的处理方式各不相同。在本文中，我们将深入探讨这些差异在实践中的具体体现，以及它们对您意味着什么。

当在表中添加、更新或删除行时，数据库必须调整索引，这与计算机时代之前图书馆的运作方式类似。想象一下，许多索引，例如：作者、标题、主题，都存储在装满小抽屉的柜子里的纸卡上。当添加一本书（插入书本）时，需要有人从所有索引中检索相关的卡片，在上面写入新条目，然后将它们放回原位。数据库执行类似的任务；检索卡片是读取 IO，而将其放回原位则是写入 IO。在没有任何缓存的情况下，每个索引至少需要一次读取和一次写入操作。

您可以创建一个待处理条目列表，而不是立即将其插入所有二级索引，然后在一天结束时进行处理。这样做的目的是，您可以找到两本或更多作者姓名相似或主题相同的书籍。将这些更新合并在一起可以节省我们在书架中搜索的时间。明显的缺点是，当您执行常规搜索时，您还需要查看“待处理”列表，以确定要查找的条目是否是最新的。这是 MySQL InnoDB 更改缓冲区 (CB)[1] 使用的索引维护方法。

准备工作

了解了这些介绍之后，我们来看一下使用六个大型二级索引插入 1000 万行数据所需的 IOP 数。测试表的模式（此处采用 MySQL/InnoDB 语法）如下：

CREATE TABLE`data_uuid` (
`id`intunsignedNOTNULL AUTO_INCREMENT,
`a`char(36) NOTNULL,
`b`char(36) NOTNULL,
`c`char(36) NOTNULL,
`status`INTNOTNULLDEFAULT'0',
PRIMARY KEY (`id`),
KEY`idx_ab` (`a`,`b`),
KEY`idx_ba` (`b`,`a`),
KEY`idx_ca` (`c`,`a`),
KEY`idx_cb` (`c`,`b`),
KEY`idx_acb` (`a`,`c`,`b`),
KEY`idx_bca` (`b`,`c`,`a`)
) ENGINE=InnoDB

a、b 和 c 列是随机 UUID 值（v4）。正如我在博文 《MySQL UUID – 性能不佳》[2] 中所写，我不喜欢在数据库中使用 UUID 值，尤其是在它们有索引的情况下。然而，在当前情况下，由于我们想要加剧这种不良行为，因此它们非常实用。有关配置和测试过程的所有详细信息，请参阅原文。

我的测试环境是一个 KVM 虚拟机，配备 4GB 内存和两个 vCPU。内存限制是故意为之，因为目标是突出 IO 方面。数据存储被限制在 1000 IOP/s，最大大小为 16 KB，这与当前的讨论无关。IO 限制有点类似于 AWS 对 EBS 卷的限制，只是为了确保瓶颈已知且可控。由于本文的目标是突出维护数据结构的 IO 效率，因此对持久性的要求比较宽松。

在这个小实验中，我选择了常用开源数据库的版本，而不是使用很少使用的前沿版本进行测试。这些版本包括：

• Percona Server MySQL 8.0.40-31
• Percona Server for PostgreSQL 15.10
• Percona Server for MongoDB 6.0.20

插入结果

插入 20M 行所需的读取和写入 IOP 数量如下所示。

插入 10M 行的读写 IOP

插入 10M 行的读写 IOP

下面将对这些标签进行解释，因此让我们按照使用的 IOP 总数的顺序更详细地回顾结果。

MySQL InnoDB

就总 IOP 而言，最高效的技术是启用了更改缓冲区的 InnoDB。说实话，我原本期待它能有不错的性能，但没想到能达到那样的水平。其影响非常显著，尤其是在与禁用该功能（InnoDB No CB）进行比较时。在这个特定的基准测试中，启用更改缓冲区可将写入 IOP 数量降低 5 倍，读取 IOP 数量降低 10 倍！如果没有更改缓冲区，InnoDB 索引维护性能会很差，尤其是在写入 IOP 数量方面。这一点需要注意，因为在 MySQL 8.4.x 中更改缓冲区默认是禁用的。

当然，这些低 IOP 值是有代价的。插入操作结束时，索引处于“降级”状态，因为其 B 树中缺少条目。InnoDB 会在后台更新索引，但这可能需要数小时，具体取决于服务器负载。强制立即扫描所有二级索引大约花费了 33 分钟，并增加了近 140 万的写入 IOP。后续扫描仅花费了大约 7 分钟，并且没有额外的写入操作。它也是一个复杂而精密的软件，多年来一直存在许多问题，这可能是在 MySQL 8.4.x 中默认 禁用更改缓冲区[3] 的主要原因。

MySQL MyRocks/RocksDB

在我们的小实验中，使用 RocksDB（又名 MyRocks 引擎）的 MySQL 是效率第二高的技术。InnoDB 基于 B+树，而 MyRocks 使用 LSM 树作为其底层数据结构。虽然 LSM 树存储引擎在写入方面表现出色，但我没想到在随机顺序执行时插入操作会如此出色。写入 IOP 仅增加了约 28% ，但读取 IOP 却增加了四倍多。这些读取 IOP 完全来自压缩操作。

MySQL InnoDB 压缩

我天真地以为添加 InnoDB 压缩 (CMP) 会减少写入的数据量，但事实并非如此。虽然对数据文件的写入次数减少了，但对双写缓冲区和重做日志文件的写入次数却显著增加。部分原因是 innodb_log_compressed_page。此变量会导致将重新压缩的页面写入重做日志文件，以防止在升级 zlib 时出现损坏问题。使用此变量，重做日志变成了一种三重写缓冲区。如果您不打算升级 MySQL 并使用 InnoDB 压缩，可以考虑将此变量设置为 OFF。如果没有此行为 (NOLOG)，写入 IOP 的数量将减少近一半。然而，与原始 InnoDB 数字相比，这仍然增加了 60% 以上。

PostgreSQL

PostgreSQL 在索引维护方面毫不作弊。每次插入操作都会更新索引，效果非常好。它比没有更改缓冲区（无 CB）的 InnoDB 性能好得多。为了实现这些较低的写入 IOP 值，我不得不将 checkpoint_timeout 设置为 30 分钟，并将 max_wal_size 设置为 12GB。PostgreSQL 不仅将 WAL 用于重做日志，还将其用作一种双写缓冲区。因此，对于有 MySQL 经验的人来说，必须将 max_wal_size 设置为比 innodb_redo_log_capacity 大得多的值。

MongoDB

最后，MongoDB/WiredTiger 排在最后，但由于我没有花太多时间进行调优，因此它更像是一个参考。就写入 IOP 数量而言，结果仍然略优于没有更改缓冲区（无 CB）的 InnoDB。看来 InnoDB 确实没有针对没有更改缓冲区的操作进行很好的调优。我很好奇为什么读取 IOP 如此之高，高于任何 MySQL 的结果。MongoDB 部分依赖于文件缓存进行读取。这似乎效率低下，因为两个缓存系统堆叠在一起，很可能缓存的是相同的数据。

数据插入率稳定性

虽然重点是索引维护，但我还是不得不关注插入率的稳定性。数据库性能稳定性非常重要，因为性能的突然下降会给整个基础架构的稳定性带来压力。

MySQL/InnoDB 5.0 版本在高写入负载下的性能稳定性非常糟糕。这是由于粗糙的检查点算法导致频繁发生刷新同步事件造成的。InnoDB 在 5.1 版本中引入了自适应刷新功能，朝着解决这个问题迈出了一大步。现在，InnoDB 至少在稳定的写入负载下，提供了相当稳定的写入性能。

InnoDB 插入率稳定性

InnoDB 插入率稳定性

虽然有一些小的波动，但并不严重。这令人印象深刻。如果你想了解更多，我和同事 Francisco 几年前写过一篇关于 InnoDB 刷新工作原理[4]的文章。

在测试环境中，由于 IOP 受限，像 RocksDB 这样的 LSM 存储引擎很难保持稳定的性能。当仅写入 WAL 时，插入速度会比较快，而当写入缓冲区被刷新或触发 compaction 时，速度会比较慢。RocksDB 的结果如下所示，确实存在很大差异。

MyRocks/RocksDB 插入率稳定性

MyRocks/RocksDB 插入率稳定性

虽然上述结果是存储引擎设计所预期的，但我对 PostgreSQL 的初步结果却令人失望，如下所示：

PostgreSQL 神秘的插入停滞

PostgreSQL 神秘的插入停滞

虽然大多数时候插入速率都比较稳定，但也会出现持续 20 到 40 秒的长时间下降。这种不稳定的情况让我困惑了一段时间。这样的问题对 PostgreSQL 用户来说可能影响很大，但显然，情况并非如此。感谢我的同事 Jorge Torralba（我们的 PostgreSQL 专家之一），我找到了罪魁祸首：

2025-06-04 00:54:35.212 UTC [] [4075872]: [1-1] user=,db=,host= LOG: automatic analyze of table "dbbench.public.data_uuid"
avg read rate: 3.595 MB/s, avg write rate: 0.421 MB/s
buffer usage: 1216 hits, 29167 misses, 3415 dirtied
system usage: CPU: user: 1.65 s, system: 3.87 s, elapsed: 63.39 s

默认情况下，当大约 10% 的元组被修改时，PostgreSQL 会触发统计信息刷新。对于 MySQL，当启用 innodb_stats_auto_recalc（默认）时，InnoDB 也会执行类似的操作。不过，不同之处在于用于统计信息的样本大小。如果我没有看错上面的日志消息，PostgreSQL 访问了超过 30k 个块来刷新统计信息，每个 B 树超过 4k。相比之下，默认情况下，InnoDB 对每个 B 树进行 20 次 16KB 页面遍历，这意味着访问了大约 100 个块，具体取决于 B 树的层数。这或许是 InnoDB 统计信息如此糟糕的原因之一。总之，在使用以下命令禁用自动统计信息刷新后：

alter table data_uuid set (autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=11000000);

我的行为得到了很大的改善：

禁用统计刷新的 PostgreSQL 插入率

禁用统计刷新的 PostgreSQL 插入率

这种行为很容易预测。如果您遇到由 autovaccum analyze table 进程引起的类似性能下降，请考虑禁用该自动进程。但是，请务必设置一个 cron 作业，以便在夜间或负载较低时刷新统计信息；否则，您的统计信息将很快下降。

结论

数据库引擎是多年开发和设计选择的成果，正如本文所示，即使是像索引维护这样非常简单的任务，它们的行为也大相径庭。我来总结一下我们在 IO 受限的环境中通过这个插入实验学到的几点：

• InnoDB 更改缓冲区非常高效，但会产生 IO 债务
• InnoDB 在没有更改缓冲区的情况下写入性能最差
• InnoDB 压缩增加了写入量
• MyRocks 在 IO 方面写入效率很高，但插入率波动很大
• PostgreSQL 效率相当高，因为它完全更新了二级索引
• MongoDB 排名最后，但这是由于未调整的配置
• PostgreSQL 统计信息收集并非玩笑，但在 IO 受限的环境中影响很大

与 PostgreSQL 相比，InnoDB 的默认统计信息几乎不重要。这也是 InnoDB 在处理大表时执行计划不稳定的原因之一。

我对从如此简单的实验中收集到的知识量感到非常惊讶，我打算测试其他场景，看看还能学到什么。

参考资料

[1]

innodb-change-buffer: https://dev.mysql.com/doc/refman/8.4/en/innodb-change-buffer.html

[2]

MySQL UUID – 性能不佳: https://www.percona.com/blog/uuids-are-popular-but-bad-for-performance-lets-discuss/

[3]

禁用更改缓冲区: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_change_buffering

[4]

InnoDB 刷新工作原理: https://www.percona.com/blog/innodb-flushing-in-action-for-percona-server-for-mysql/

本文关键字：#MySQL #PostgreSQL #MongoDB #InnoDB #索引 #翻译