Coding Interview Prep · 课时

索引何时有害：写入与选择性

Q: 「索引何时有害：写入与选择性」这节课中我会学到什么？

了解写放大，以及为什么低选择性列上的索引毫无用处。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解写放大，以及为什么低选择性列上的索引毫无用处。

索引何时有害：写入与选择性 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

问题背后的问题

在连续三节学习了索引为什么有用之后，面试官会换个角度问：“为什么不直接给每一列都建索引？”优秀的候选人应当解释，索引确实会产生写入成本以及缓存和存储成本，而且有些索引优化器甚至永远不会使用。

本节介绍索引可能带来损害的两个主要原因：写放大和低选择性。

每个索引都会减慢写入

索引必须与表保持同步。每次 INSERT、每次 DELETE，以及对索引列执行的每次 UPDATE，都必须同时更新索引结构。这就是写放大：一次行变更会变成一次表写入，再加上每个受影响索引的一次写入。

拥有八个索引的表，其写入工作量大致是未建立索引的表的九倍。在写入密集型或高吞吐量的表中，这是一项不容忽视的负担。

示例：写入代价

想象一个每秒接收数千行数据的事件表。每增加一个索引，每次插入都要完成更多工作，包括拆分索引页、更新叶节点以及争用缓存。

对于仅追加且写入占主导的表，正确的做法通常是除了主键之外只建立很少的索引，甚至不建索引，并将大量读取转移到副本或数据仓库上。

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

选择性意味着什么

选择性表示某列区分不同行的能力，也就是某个典型值所匹配的行占总行数的比例。高选择性意味着每个值对应的行很少（例如电子邮件地址或 UUID）；低选择性意味着每个值对应的行很多（例如布尔值，或只有三个选项的状态）。

索引在高选择性列上收益明显，因为一次查找就能排除几乎所有行。而对于低选择性列，索引通常无法带来收益。

低选择性索引为何毫无用处

假设 90% 的用户的 is_active 值为真。索引查找会返回整张表中 90% 的行，而对于这么多行，引擎需要为每行执行一次堆读取，这比一次顺序扫描整张表还慢。

因此，优化器会正确地忽略该索引并执行顺序扫描。这样一来，索引只会带来写入开销和存储成本，却完全没有读取收益。

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

粗略阈值

一个适合在面试中直接说出的经验法则是：当一个谓词匹配表中大约 5% 到 20%以上的行时，顺序扫描通常优于索引扫描，因为随机堆读取的成本高于按顺序流式读取页面。

确切的交叉点取决于行大小、缓存情况和存储速度，因此优化器会使用统计信息而不是固定数字来做决定。

部分索引来解围

如果您只查询分布偏斜列中的少数值，部分索引（PostgreSQL）可以只为这些行建立索引，体积小、选择性高且维护成本低。

如果只有 1% 的订单处于 pending 状态，而您又经常查询这些订单，就只为它们建立索引。索引会保持较小，优化器也会乐于使用它。

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

过时的统计信息会误导优化器

优化器根据列统计信息决定使用索引还是扫描。如果统计信息过时，例如在批量加载或大规模更新之后，优化器就可能误判选择性并选择错误的执行计划。

当面试官说“索引明明存在，却没有被使用”时，一个很好的回答应包括：先使用 ANALYZE 刷新统计信息，而不是先责怪索引本身。

ANALYZE orders;  -- refresh planner statistics

索引造成损害的其他方式

还可以补充一些较少人了解的成本：

存储和缓存：索引占用磁盘空间并争用内存，从而驱逐有用的数据页。
冗余或重叠索引：需要维护，却从未被选用。
膨胀：在大量更新下，B 树会产生碎片，并需要执行 REINDEX。
优化器混淆：过多相似索引会使规划变慢，也更难预测。

查找未使用的索引

为了说明实际项目中的清理依据，可以提到 PostgreSQL 会跟踪索引使用情况。idx_scan = 0 的索引就是可以考虑删除的对象：它们不断增加写入和空间成本，却从未服务过读取。

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

面试中如何表述

一个完整而平衡的总结：

“索引会带来写放大成本，每次插入、更新或删除都需要维护索引，同时还会增加存储和缓存压力。索引只有在谓词具有高选择性时才有收益；当某列匹配大多数行时，优化器会正确地优先选择顺序扫描，因此该索引只会带来额外开销。对于分布偏斜的列，我会考虑使用部分索引，并通过 ANALYZE 保持统计信息最新，同时删除未使用的索引。”

快速检查

判断哪个索引最不可能值得承担其成本。

回顾：索引何时会造成损害

要点总结：

每个索引都会增加写放大以及存储和缓存成本。
索引适用于高选择性列；对于低选择性列，优化器会优先选择顺序扫描。
当匹配行数超过总行数的大约 5% 到 20%时，扫描通常更有优势。
对于只查询少数值的分布偏斜列，应使用部分索引。
使用 ANALYZE 保持统计信息最新，并删除未使用的索引（idx_scan = 0）。

至此，索引策略课程就完成了：在索引真正能发挥作用的地方建立索引，并通过执行计划证明其价值。

常见问题解答

「索引何时有害：写入与选择性」课时是免费的吗？

是的 — 「索引何时有害：写入与选择性」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「索引何时有害：写入与选择性」这节课中我会学到什么？

了解写放大，以及为什么低选择性列上的索引毫无用处。你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「索引何时有害：写入与选择性」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep