PostgreSQL / 系统课时

设计索引变更验收

设计索引变更验收

本课把索引新增、删除和重建的验收从“SQL 执行成功”扩展成一套可复核的门禁:先验证功能,再验证执行计划与性能,最后确认索引数量、体量和整体资源没有出现新的风险。

学习目标

  • 能为新增、删除和重建索引分别定义可执行的验收条件。
  • 能用 EXPLAIN (ANALYZE, BUFFERS) 对关键查询做前后对比。
  • 能用系统目录和关系大小检查索引状态、容量变化与残留对象。

把验收拆成四道门

索引变更至少要经过四层验收。功能验收确认 SQL 结果和约束语义没有被破坏;计划验收确认优化器能够合理使用或放弃该索引;性能验收比较真实查询的耗时与缓冲访问;容量验收确认新增空间和删除后的释放符合预期。

四层门禁彼此不能互相替代。例如查询更快,不代表删除索引是安全的,因为可能还有唯一性约束、排序或另一类查询依赖它。相反,索引目录状态正常,也不代表核心查询计划一定改善。

用同一条核心查询做前后对照

为了让验收可重复,应固定一条业务核心查询、相同参数范围和相近的数据状态。下面是自包含的实验脚本:它建立临时数据集,创建索引并用 EXPLAIN (ANALYZE, BUFFERS) 观察索引对查询路径的影响。

sql
CREATE TEMP TABLE orders_acceptance (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    status text NOT NULL,
    amount numeric(12,2) NOT NULL
);

INSERT INTO orders_acceptance (customer_id, status, amount)
SELECT
    (g % 1000) + 1,
    CASE WHEN g % 10 = 0 THEN 'pending' ELSE 'paid' END,
    (g % 500) + 10
FROM generate_series(1, 50000) AS g;

ANALYZE orders_acceptance;

CREATE INDEX orders_acceptance_customer_status_idx
    ON orders_acceptance (customer_id, status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM orders_acceptance
WHERE customer_id = 42
  AND status = 'paid';

预期结果

text
QUERY PLAN
------------------------------------------------------------------------------------------------
Index Scan using orders_acceptance_customer_status_idx on orders_acceptance ...
  Index Cond: ((customer_id = 42) AND (status = 'paid'::text))
  Buffers: shared hit=...
Planning Time: ... ms
Execution Time: ... ms

这里不要求固定毫秒数或固定缓冲命中数,因为它们受机器和执行环境影响。验收重点是查询结果正确、计划能合理利用索引,而且对比指标相较变更前达到预设门槛。

验收新增和删除

新增索引重点检查“需要它的查询是否真正受益”,同时确认索引本身有效。删除索引则方向相反:除了确认目标对象已经消失,还必须确认原来依赖它的核心查询没有退化。

可以用系统目录快速核对目标对象:

sql
SELECT
    c.oid::regclass AS index_name,
    i.indisvalid,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_class AS c
JOIN pg_index AS i ON i.indexrelid = c.oid
WHERE c.relkind = 'i'
  AND c.oid::regclass::text = '<INDEX_NAME>';

预期结果

text
 index_name       | indisvalid | index_size
------------------+------------+------------
 <INDEX_NAME>     | t          | <SIZE>

删除验收时,目标索引应在目录查询中不再出现;如果是唯一索引或约束相关对象,还要额外确认业务要求仍由正确的约束对象承担,不能把“查询结果没报错”当成完整证明。

验收重建而不是只验“新对象存在”

重建后的核心问题是:逻辑定义有没有改变、有效状态是否正常、关键查询计划是否稳定、空间是否达到治理目标。对于重建操作,应优先比较变更前后的同一索引定义与体量,并复跑核心 SQL。

可以用索引定义查询来确认对象语义没有被意外改变:

sql
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexname = '<INDEX_NAME>';

预期结果

text
 schemaname | tablename | indexname    | indexdef
------------+-----------+--------------+------------------------------
 <SCHEMA>   | <TABLE>   | <INDEX_NAME> | CREATE INDEX ...

容量验收也要结合目标来解释。如果本次重建的目的就是治理膨胀,不能简单要求“新索引必须比旧索引小”;应该比较同一数据集和同一索引定义下的体量变化,并结合磁盘剩余空间判断治理是否值得。若目标是消除异常空间占用,则应把索引大小变化作为正式门槛,而不是仅记录一次结果。

索引变更四层验收门禁

把验收标准写成可以判定的门槛

好的验收标准必须能够让执行人和复核人得到同样结论。与其写“性能正常”,不如写成“核心查询结果与基线一致,执行时间不超过既定阈值,缓冲访问未出现异常增长”;与其写“空间正常”,不如明确记录索引大小、数据库剩余空间以及是否存在无效残留对象。

对于线上环境,还应保留变更前快照和变更后结果,至少覆盖索引定义、索引有效状态、核心查询计划、关键性能指标和容量数据。这样一次验收不仅能判断本次变更,还能为后续索引膨胀治理提供可比较的基线。

最终应把“功能、计划、性能、容量”视为缺一不可的验收链。只有四类证据都满足门槛,索引变更才真正从一次数据库操作变成可审计、可回退、可复用的工程变更。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

设计索引变更验收

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通