数据库索引提速背后的写入代价

你的 API endpoint 耗时 800ms。你打开 query plan，发现一张包含 1200 万行数据的表正在执行 sequential scan，于是祭出经典操作：CREATE INDEX。查询耗时骤降至 12ms，所有人都欢欣鼓舞。两周后，同一张表开始承受高强度写入负载，insert 接连超时，vacuum 疯狂告警，还有人追问为什么磁盘里全是 index bloat。

这才是 index 的真实故事。它们并不是免费的性能加速按钮，而是一项需要高级工程师谨慎权衡的取舍，不是每当某个字段出现在 WHERE 子句中，就顺手给它加上的默认配置。

Index 真正解决的问题

在没有 index 的时代，数据库查找行的唯一办法，就是读取表中的每一个 page。对于小表来说，这没什么问题；但当一张表大到无法完全装入内存时，这种操作就会变成 full table scan，而且延迟会随着表的大小不断增长。

index 的存在，是为了让数据库引擎直接跳转到相关 page，而不必读取全部数据。它解决的是经典的读取与写入矛盾：大多数系统以读取为主，因此我们愿意让写入承担额外工作，以降低读取成本。一旦工作负载转为写入密集型，或者 index 集合变得过于庞大，这项权衡就会发生逆转。

这就是早期系统通常没有多少 index 的原因。当时表很小、内存又很稀缺，维护额外数据结构的成本往往高于它们带来的收益。随着数据规模爆炸式增长，任何需要可预测延迟的系统，都不可能再缺少 index。

Index 究竟如何工作

大多数关系型数据库都使用 B-tree index，或与之相近的变体。B-tree 是一种平衡树，其中：

叶子节点包含被索引字段的值，以及指向表数据的指针；对于 covering index，也可能直接包含完整的行数据。

内部节点充当路线图，让数据库引擎能够以对数时间找到正确的叶子节点。

SELECT * FROM orders WHERE user_id = 42;

沿着 user_id 的 B-tree 向下查找。

到达包含匹配条目的叶子 page。

沿着指针获取实际的数据行，或者直接使用 index 中包含的字段。

如果 index 的选择性足够高，并且表足够大，这会比 sequential scan 便宜得多。如果 index 的选择性很差——例如一个 is_active 布尔字段，其中 90% 的值都是 true——planner 往往会忽略它，仍然选择扫描整张表。

针对不同的访问模式，还有其他类型的 index：

• Hash index——仅支持等值查询，在大多数生产级数据库引擎中的用途有限。

• GiST / GIN——适用于全文搜索、数组、JSON 和地理空间数据。

• BRIN——适用于规模非常大、天然有序的数据，例如时间序列和 append-only 日志，维护成本极低。

• Partial index——只索引部分数据行，例如 WHERE status = 'active'。它往往是最具杠杆效应、却最容易被人遗忘的工具。

flowchart TD
    A[Query arrives] --> B{Planner chooses path}
    B -->|Index available & selective| C[B-tree lookup]
    B -->|No useful index| D[Sequential scan]
    C --> E[Leaf pages]
    E --> F[Heap / table fetch]
    F --> G[Return rows]
    D --> G

关键在于：index 默认不会存储完整的数据行，它存储的是 key + pointer。每当你需要的字段不在 index 中时，仍然要承担返回表中获取数据所产生的 random I/O 成本，也就是“heap fetch”。这就是 covering index 如此重要的原因。

公司为什么真的会使用 Index

公司添加 index，并不是因为教科书要求这么做，而是因为：

• 关键路径未能达到 latency SLO。

• 某条过去表现正常的查询，在业务自然增长后突然开始扫描数百万行数据。

• 客服工单中开始出现“应用下午用起来很慢”之类的反馈。

• 某条分析查询快要拖垮 primary，需要建立一台配置不同 index 的 read replica。

在大规模系统中，你还会看到一些专门设计的 index：

• 按照最常见的 filter + sort 组合排列字段的 composite index。

• 针对 lower(email) 或 date_trunc('day', created_at) 建立的 expression index。

• 专门为 foreign key 建立的 index，纯粹为了避免 cascade 和 join 性能退化。

• 只存在于 read replica、primary 完全不携带的独立 index。

实际决策几乎从来都不是“添加一个 index”，而是：“添加这个特定的 index，因为经过测量，不添加它的成本高于维护它的成本。”

当以下条件全部满足时，才应该使用 index：

• 该字段或字段组合经常出现在 WHERE、JOIN 或 ORDER BY 中。

• 它的选择性足够高，planner 确实会选择这个 index。

• 表足够大，执行 sequential scan 的成本很高。

• 你已经测量过 write amplification，并确认它在当前负载下可以接受。

• 你了解它的维护成本，包括 bloat、vacuum 压力和存储空间。

• Primary key 和 unique constraint——它们本质上就是 index。

• 经常参与 join 的 foreign key。

• 最热门 endpoint 的常用过滤条件所涉及的字段。

• 面向读取密集型 API 的 covering index，尤其是这些 API 总是返回相同的一小组字段时。

什么时候不应该使用 Index

这是大多数初级工程师容易犯错的地方。

• 低基数字段，例如 gender、只有三个值的 status、布尔标记；除非使用 partial index。

• 写入频率远高于读取频率的字段。

• “以防万一”，给所有曾在查询中出现过的字段都建立 index。

• 已经能够完全装入内存的小表字段。

• 值不断变化的字段，因为 index 会成为写入热点。

还要谨慎对待：

• 过宽的 composite index。每增加一个字段，都会增大每个叶子条目的体积，并拖慢写入。

• 对那些会在同一个 transaction 中与 primary key 一起更新的字段建立 index。你需要支付两次 index 维护成本。

• 在实时承载高写入负载的表上创建 index，却没有使用 CONCURRENTLY（Postgres）或同类机制。这可能长时间锁住整张表。

一种经典的反模式是：有人为了“加快报表速度”添加了五个 index，随后 OLTP 工作负载开始无法达到延迟目标，因为每次 insert 都要更新六个数据结构，而不是一个。

真实生产环境中的权衡

index 并不是免费的。下面才是你真正需要付出的代价：

在我参与过的一个系统中，事件表的数据变更非常频繁，而一个选择不当的 composite index 让写入延迟增加了约 40%，每天的存储空间增量也增加了 15GB。删除它，换成只索引“open”事件的 partial index 后，写入时间显著下降，同时仍然可以满足重要查询的需求。

另一个常见的现实是：让第 95 百分位查询变快的 index，可能会导致第 99.9 百分位的写入路径无法达到 SLO。你必须判断，对于这个特定服务来说，哪个百分位更加重要。

初学者常犯的错误

为 WHERE 子句中的每个字段建立 index
选择性比字段是否出现更加重要。一个缺乏选择性的 index 往往比没有 index 更糟，因为 planner 仍然需要将它纳入评估。

为 WHERE 子句中的每个字段建立 index
选择性比字段是否出现更加重要。一个缺乏选择性的 index 往往比没有 index 更糟，因为 planner 仍然需要将它纳入评估。

忽略 composite index 中字段的顺序
(user_id, created_at) 可以支持 WHERE user_id = ?，也可以支持 WHERE user_id = ? AND created_at > ?。而 (created_at, user_id) 无法高效支持第一条查询。

忽略 composite index 中字段的顺序
(user_id, created_at) 可以支持 WHERE user_id = ?，也可以支持 WHERE user_id = ? AND created_at > ?。而 (created_at, user_id) 无法高效支持第一条查询。

忘记 covering index
如果一条查询只需要三个字段，而你将它们包含在 index 中——在 Postgres 中使用 INCLUDE，或者直接将它们加入 key——就可以完全避免 heap fetch。这往往就是“还挺快”和“快如闪电”之间的差距。

忘记 covering index
如果一条查询只需要三个字段，而你将它们包含在 index 中——在 Postgres 中使用 INCLUDE，或者直接将它们加入 key——就可以完全避免 heap fetch。这往往就是“还挺快”和“快如闪电”之间的差距。

在流量高峰期直接在 primary 上创建 index，却不使用并发选项
对于大型表，这可能将写入操作锁住几分钟，甚至几小时。

在流量高峰期直接在 primary 上创建 index，却不使用并发选项
对于大型表，这可能将写入操作锁住几分钟，甚至几小时。

从不查看 pg_stat_user_indexes 或同类统计信息
从未被使用的 index，仍会在每次写入时产生开销。无用的 index 只有成本，没有收益。

从不查看 pg_stat_user_indexes 或同类统计信息
从未被使用的 index，仍会在每次写入时产生开销。无用的 index 只有成本，没有收益。

认为 index 会永远保持健康
大量 update 会造成 bloat。如果没有正确调整 autovacuum，或者定期执行 REINDEX，性能就会悄无声息地恶化。

认为 index 会永远保持健康
大量 update 会造成 bloat。如果没有正确调整 autovacuum，或者定期执行 REINDEX，性能就会悄无声息地恶化。

把 query planner 当成魔法
有时你仍然需要重写查询、补充统计信息，或者强制指定执行计划。index 只是工具之一。

把 query planner 当成魔法
有时你仍然需要重写查询、补充统计信息，或者强制指定执行计划。index 只是工具之一。

高级工程师如何思考这项决策

高级工程师不会问：“我应该添加 index 吗？”他们会提出一系列更加精确的问题：

• 在真实负载下，这条查询实际的延迟分布是什么样的？

• 这个 predicate 在生产数据中的选择性如何，而不是在数据量很小的 staging 数据集中表现如何？

• 当前的写入 QPS 是多少，我们还剩多少余量？

• 这条查询是否位于用户操作的关键路径上，还是只用于后台任务或数据分析？

• 能不能通过更好的 schema、materialized view、read replica 或应用层缓存解决问题？

• 如果添加这个 index，回滚方案是什么？又该如何衡量它的影响？

他们也会从整个生命周期的成本出发思考问题。一个今天能节省 200ms，却会在未来五年持续增加写入开销和存储空间增长的 index，可能并不是一笔划算的交易。

在许多大规模系统中，最终决策通常会是这样：

“我们将针对 (user_id) 添加一个 partial covering index，通过 INCLUDE (status, amount) 包含额外字段，并且只索引 status IN ('pending', 'processing') 的数据行。我们会在低流量时段并发创建它，持续 48 小时监控写入延迟和 index 大小，同时准备好删除脚本。”

这才是工程实践，而不是对 index 的 cargo cult。

创建 Index 之前的实用检查清单

• 使用与生产环境相近的数据规模，对真实查询运行 EXPLAIN (ANALYZE, BUFFERS)。

• 检查选择性：predicate 通常会返回多少行？

• 查看现有 index：能否扩展已有 index，而不是再创建一个？

• 评估写入影响：这张表的写入频率有多高？

• 条件允许时，优先使用 partial index 或 covering index。

• 如果表规模较大且正在实时承载业务，请并发创建 index。

• 为 index 大小、bloat 和使用情况统计添加监控。

• 记录创建这个 index 的原因，以及在什么条件下可以将其删除。

index 是关系型数据库中最具杠杆效应的工具之一，同时也是最容易在不知不觉中摧毁写入性能、浪费存储成本的工具之一。

初级工程师和高级工程师之间的区别，不在于他们是否知道如何创建 index，而在于他们是否理解它的完整成本，是否会在变更前后进行测量，以及是否愿意删除一个已经无法覆盖自身成本的 index。

下次查询变慢时，不要第一时间伸手去拿 CREATE INDEX。先查看 query plan 和统计信息，弄清楚自己即将接受怎样的权衡。

只有这样，才能让系统在未来几年持续保持高性能，而不只是撑过下一次 deploy。

如需采取进一步措施，你可以考虑屏蔽此人和/或举报滥用行为。