KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA

09 · 索引变更的验收：写侧代价与前后对照 — keel 龙骨

这一章回答：索引加对了之后，怎么证明它真的在起作用、又怎么知道自己付了多少。

这一章回答：索引加对了之后，怎么证明它真的在起作用、又怎么知道自己付了多少。

前八章都在回答"怎么改"：怎么读计划、怎么排字段、哪些写法会让索引失效、大表怎么变更。这一章回答最后一个问题——改完之后拿什么说这次改动是成功的。

在真实团队里，索引变更是这样翻车的：

列表页 P99 从 800 ms 降到 40 ms，上线成功。三天后上游的订单导入任务从 8 分钟涨到 34 分钟。

改动本身没错，读确实变快了。错在验收只看了读这一侧。

所以一次索引变更要验收三件事，缺一件就不算完成：

1. 读是真的变快了（用页数，不是用感觉）；
2. 写要付多少（这是加法，必须写进变更单）；
3. 有没有连带影响（别人会不会变慢）。

这三件事的做法和判据是本章的全部内容。至于怎么选列、怎么排查失效、大表上用什么工具做 Online DDL，分别是 02、03、04 章的事，本章不重复。

本章实测出自 PostgreSQL 16.15（Windows，页大小 8 KB）。之所以用它取证，是因为本章要讲清的"更新为什么有时不碰索引"这个机制在它这里有一个可读的计数器；换到别的引擎，结论的结构一样，观测手段和名字不同（见第三节末的对照表）。

一、读侧验收：凭据是页数，不是毫秒

毫秒是所有验收指标里最不可靠的一个。同一套 SQL，跑第一次和跑第二次能差一个数量级——因为区别不在 SQL，在页在不在缓存里。

所以读侧验收的凭据要落在"这次查询碰了多少个页"上，它由数据分布和索引结构决定，不受当时的负载和缓存状态影响（只影响 hit 与 read 的分配，两者加起来是稳定的）。

未建索引：Buffers: shared hit=14775 read=10225   → 25000 页
建索引后：Buffers: shared hit=1     read=3       →     4 页

这两行才是"改对了"的证据。 耗时（347.865 ms → 0.050 ms）只是它的一个后果。

验收要点：

• 前后各记一次，中间不改别的东西。同一台机器、同一份数据、同一套 SQL。如果同时改了业务代码，就没法归因。
• 看 hit + read 之和，不要只看 hit。只有 read 说明数据不在缓存，两者之和才代表"这次查询总共碰了多少页"。
• 换算成比值：扫描页数 ÷ 返回行数。这个比值接近 1 说明定位精确；接近表的总页数说明退化成了扫描。比值可以跨机器、跨数据量比较，页数绝对值不行。
• 记录 Rows Removed by Filter。它等于"读了但没用的行"，是"索引只帮了一半"的信号。

这条判据不只用在变更后，也用在决定要不要改的时候：先量出比值，再决定值不值得付下面第二节的代价。

二、写侧代价：多一个索引，到底多付多少

"索引会带来写放大"这句话人人会说，但它几乎从不被量化，于是"要不要加这个索引"的讨论就永远停在感觉上。下面把它量化。

同一份数据（20 万行，行内容完全一样）、同一台机器，只改索引数量，各插一次：

（这份数字复跑过一次，耗时差异在 15% 以内、WAL 差异在 10% 以内、索引体积完全一致。）

读出来的东西有三个，第三个最容易被忽略：

① 三个索引让插入慢到 4.1 倍，WAL 涨到 2.8 倍。 这不是"慢一点"，是一次主键写放大的量级变化。

② 边际成本不是常数。 0 → 1 多付了 578 ms，1 → 3 多付了 2932 ms（每多一个约 1466 ms）。因为第三个索引建在一个 md5 文本键上，键更宽、树更深、每页装的条目更少。"每多一个索引增加固定百分比"是错的，成本取决于键的宽度。

③ 看 WAL，不只看着 SQL 的耗时。 在这次变更里真正会伤到别人的，是 WAL 从 35 MB 涨到 97 MB：

• 主从复制要传的就是 WAL，写放大直接变成复制延迟；
• 备份与归档的体积跟着涨；
• 磁盘水位涨得更快——这张表本身只有 16 MB，它的索引合计 27 MB，已经超过表本身。索引不是"一点点元数据"，它是等比增长的副本。

SQL 的耗时只影响发起写入的那个进程；WAL 的量影响整条复制链路。只盯着"导入任务慢了多少秒"会低估这事。

一个必须写进验收纪律的坑：这类测量要跑两趟

上面这张表如果只跑一趟，数字是不可信的。实测第一次跑和第二次跑的同一件事：

第一趟：  89 MB / 108 MB / 103 MB
第二趟：  48 MB /  74 MB /  66 MB      ← 同一批表、同一批更新

差一倍。原因是 checkpoint 之后，每个页的第一次修改要先写一整页的镜像进 WAL。第一趟承担了这些整页写，第二趟没有。

所以：任何以 WAL 为指标的测量，都要先跑一趟丢掉，报第二趟的数。 只用耗时做指标时没有这个问题，但那又会回到"毫秒不可靠"。这个坑本身就是"验收纪律"存在的理由——没跑对的东西不要写成跑对了。

三、更新路径：为什么有时不维护索引

插入的代价很好理解：新行要进每一棵树。更新的代价就有点反直觉了——有时候更新 20 万行，一个索引条目都不用写。

这件事决定了一个很实际的设计取舍：一列该不该进索引。实测：三张结构相同的表，各更新 20 万行，只改两个变量（更新的列在不在索引里、页内有没有空位）。

（HOT = heap-only tuple，"只留在堆里的新版本"。它表示这一行的更新没有动到任何索引。）

三条结论，都是从上表直接读出来的：

① 更新的列在索引里，就必然要维护索引（C：0.0%）。 即使页内有空位、新版本能放在同一页，索引条目也必须新增一条——因为键变了。这条最实用：把一列放进索引，不是"只在查询时付成本"，而是在每一次更新它时都付。

② 页内没有空位，HOT 就完全失效（B：0.0%，只剩 19 次）。 新版本放不进原来的页，就只能去新页，行引用变了，所有索引都得更新。

③ fillfactor 留出的空位只买到有限的几次 HOT（A：54.0%，不是 100%）。 页内空位会在反复更新中被吃掉，吃满之后就退回到 B 的行为。所以"我设了 fillfactor 就应该全走 HOT"是错的期待——它只是把 HOT 的窗口往后推了一段。

再看 WAL 那一列：48 / 66 / 74 MB。把两件事拆开就清楚了：

48 MB  A：新版本同页 + 无索引列变化     → 两件都满足
66 MB  C：新版本同页 + 索引列变化       → 不满足索引那一条
74 MB  B：新版本换页 + 无索引列变化     → 不满足页内空间那一条

换到另一个引擎：为什么有些库根本没有 HOT

这不是"谁的实现更好"，而是原理章那张"行引用存什么"的表的直接后果：

所以："一级索引存的是键还是地址"这一个设计选择，同时决定了读路径（回表要不要多走一次）和写路径（更新要不要维护索引）。 同一件事在两边都成立，只是名字和观测手段不同。

四、连带影响：同一次变更，还有谁会受影响

写路径只是其中一个面。开工前应该把受影响面列出来，逐条问"我要怎么验证它没坏"：

其中"别的查询换计划"是唯一可能让读也变慢的一项。 加索引一般让读变快，但它改变了优化器的选择空间——原本走索引 A 的查询，现在可能改走索引 B，而那条路径对它是更差的。验证办法很朴素：把该表上的高频 SQL 列出来，前后各跑一遍 EXPLAIN，逐条比对计划有没有变。 别只测你优化的那一条。

五、上线前：采样验证能证明什么、不能证明什么

生产上通常不允许你拿全量数据预演，于是大家在测试库上用 10% 的采样数据验证。这件事能证明一半，另一半会骗你。

能证明的：这个索引能不能被规划器用上（计划里出现预期的访问方式，不再是全表扫描）、扫描页数与返回行数的比值是否有改善。这些是结构性的，10% 的数据上做出来的结论可以迁移。

不能证明的：上线后的绝对耗时。行数变了，优化器的估算就变了——在 10 万行上它选索引，在 1000 万行上它可能就选全表扫描（反过来也可能）。更别说缓存命中率和 IO 延迟完全不同。

所以采样的正确用法是：

采样数据上验证：能不能用上、比值有没有改善   → 可以下结论
采样数据上断言：上线后会快 N 倍              → 不能
绝对耗时与计划的生产形状：只能在上线后实测，或用一个与生产同量级的影子环境

这条是真话，而且它是索引变更最常翻车的地方：测试环境结论漂亮，上线之后计划变了，性能没有改善甚至退化。所以上线的验收必须包含"计划有没有按预期变"这一项，而不是只有耗时。

六、上线后的验收单

跨过一个业务高峰（至少包含一次周期任务，比如对账或批处理），然后按这七项填：

"有没有副作用"和"有没有变快"是同一张单子上的两项。 只填第 2、3 项，就是开头那个故事。

七、回滚：两种方向，成本差很远

所以：

• 加索引的回滚路径，必须在变更单里写明"删掉它"这条语句——它很便宜，不要因为"应该没问题"就省略。
• 删索引的回滚成本远高于删之前建它，所以删之前要留下精确的重建语句（含所有选项）和预估耗时。识别冗余索引与未使用索引的方法、以及 gh-ost 之类的在线变更工具，在 04 章；本章只补一条纪律：观察窗口必须覆盖一个完整的业务周期。只在工作日观察，就会把月末对账专用的索引判成"未使用"。
• 删索引的最坏结果比加索引严重：加索引最坏是写慢一点，删索引最坏是某条查询退化到全表扫描、并且在高峰期才发现。

一次索引变更的完整回合

flowchart TD
    S["① 量出基线：扫描页数、返回行数、耗时、WAL"] --> NEED{"② 比值差<br/>且读写比支持加索引？"}
    NEED -- 否 --> STOP["不改：收益不抵写侧代价"]
    NEED -- 是 --> SAMPLE["③ 采样数据预演<br/>只验证「能不能用上」"]
    SAMPLE --> OK{"④ 计划里出现预期的索引访问？"}
    OK -- 否 --> FIX["先查条件写法与统计信息<br/>见 03 章"]
    FIX --> OK
    OK -- 是 --> PLAN["⑤ 写变更单：回滚语句 + 受影响面清单"]
    PLAN --> ONLINE["⑥ 上线（在线变更走 04 章）"]
    ONLINE --> WATCH{"⑦ 跨过一个业务高峰后验收<br/>七项全过？"}
    WATCH -- 是 --> DONE["收口：记录前后数据，归档变更单"]
    WATCH -- 否 --> RB{"哪一项没过？"}
    RB -- "读没改善 / 写超预期" --> ROLLBACK["回滚：删掉新索引<br/>（成本低，立刻执行）"]
    RB -- "别的 SQL 计划翻转" --> TUNE["定位受影响 SQL<br/>调整索引或改写查询"]
    style S fill:#e3f2fd,color:#0d47a1
    style DONE fill:#e8f5e9,color:#1b5e20
    style STOP fill:#fff3e0,color:#e65100
    style ROLLBACK fill:#ffebee,color:#b71c1c

生产边界

• 本章的实测出自 PostgreSQL 16.15，而这个环境里 checkpoint 后的整页写会污染 WAL 指标（第二节那张"第一趟 vs 第二趟"的表就是证据）。生产上 WAL 的增长还受检查点频率、wal_compression、复制方式影响，所以要读的是形状（写侧代价随索引数量与键宽上升），不要去照抄 MB 数。
• fillfactor 与 HOT 是 PostgreSQL 的机制名。 别的引擎有各自避免索引维护的手段（见第三节的对照表），但没有 HOT 这个概念也不需要它——讨论时不要跨引擎混用这个词。
• n_tup_hot_upd 这类计数器统计的是"多少次"，不是"省了多少时间"。 本章的实测里，54% 的 HOT 在耗时上并没有拉开明显差距（索引小、全在缓存里），差别要到 WAL 和索引体积上才看得见。当计数器和耗时给出不同信号时，先问"缓存状态是否掩盖了差异"，不要选一个自己顺眼的。
• 验收窗口必须覆盖一个完整业务周期。 月末对账、季度报表、数据归档这类任务不在工作日跑；窗口不够长，"未使用"和"没改善"两个结论都会判错。
• 绝对耗时不能从测试环境推生产。 采样数据能验证"结构上能不能用上"，不能验证"上线后快多少"。这一条没有捷径，只能用同量级环境或上线后实测。

动手：可观察结果

完成标志：给定一次已上线的索引变更，你能在不看内容的情况下说出"它应该提供了哪几份数据"，并指出缺哪一份会让这次验收失效。

故障注入

自测题

1. 为什么读侧验收要用"扫描页数 ÷ 返回行数"而不是耗时？这个比值为什么可以跨机器比较？
2. 实测里 0 → 1 个索引插入慢 51%，1 → 3 个索引又多慢 172%。为什么边际成本不是常数？
3. 什么时候更新一行可以完全不动索引？列出必须同时满足的条件，并说明 fillfactor 为什么只能推后而不能保证它。
4. 为什么 InnoDB 没有 HOT 也不需要 HOT？这个差别源自哪个设计选择？
5. 用 10% 采样数据预演索引变更，哪些结论可以迁移到生产、哪些一定不行？为什么？
6. 加索引和删索引，哪个的回滚更贵？各自应该在变更单里留下什么？
7. 一次索引变更的验收七项里，哪一项最容易被跳过而不被发现？跳过它的后果是什么形态？

现在能解释什么

• 为什么"加索引之后写入变慢"必须被量化，而且必须用 WAL 与耗时的组合来量；
• 为什么以 WAL 为指标的测量要跑两趟才可信（checkpoint 后的整页写）；
• 为什么把一列放进索引，等于对它的每一次更新都收费——以及哪两个条件同时满足时这笔费用会被免掉；
• 为什么同样"避免索引维护"，PostgreSQL 需要 HOT 而 InnoDB 不需要，这条差别一路追溯到二级索引里存的是地址还是主键；
• 为什么采样环境上的性能结论不能迁移到生产，而"能不能用上索引"的结论可以；
• 为什么删索引的回滚成本远高于加索引，以及为什么观察窗口必须覆盖一个完整业务周期。

上一章：08 热点、削峰与一致性收口。索引的原理部分在 《数据库与缓存调优》00 章。

进入 keel 阅读