PostgreSQL / 系统课时
设计索引变更验收
设计索引变更验收
本课把索引新增、删除和重建的验收从“SQL 执行成功”扩展成一套可复核的门禁:先验证功能,再验证执行计划与性能,最后确认索引数量、体量和整体资源没有出现新的风险。
学习目标
- 能为新增、删除和重建索引分别定义可执行的验收条件。
- 能用
EXPLAIN (ANALYZE, BUFFERS)对关键查询做前后对比。 - 能用系统目录和关系大小检查索引状态、容量变化与残留对象。
把验收拆成四道门
索引变更至少要经过四层验收。功能验收确认 SQL 结果和约束语义没有被破坏;计划验收确认优化器能够合理使用或放弃该索引;性能验收比较真实查询的耗时与缓冲访问;容量验收确认新增空间和删除后的释放符合预期。
四层门禁彼此不能互相替代。例如查询更快,不代表删除索引是安全的,因为可能还有唯一性约束、排序或另一类查询依赖它。相反,索引目录状态正常,也不代表核心查询计划一定改善。
用同一条核心查询做前后对照
为了让验收可重复,应固定一条业务核心查询、相同参数范围和相近的数据状态。下面是自包含的实验脚本:它建立临时数据集,创建索引并用 EXPLAIN (ANALYZE, BUFFERS) 观察索引对查询路径的影响。
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';预期结果
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这里不要求固定毫秒数或固定缓冲命中数,因为它们受机器和执行环境影响。验收重点是查询结果正确、计划能合理利用索引,而且对比指标相较变更前达到预设门槛。
验收新增和删除
新增索引重点检查“需要它的查询是否真正受益”,同时确认索引本身有效。删除索引则方向相反:除了确认目标对象已经消失,还必须确认原来依赖它的核心查询没有退化。
可以用系统目录快速核对目标对象:
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>';预期结果
index_name | indisvalid | index_size
------------------+------------+------------
<INDEX_NAME> | t | <SIZE>删除验收时,目标索引应在目录查询中不再出现;如果是唯一索引或约束相关对象,还要额外确认业务要求仍由正确的约束对象承担,不能把“查询结果没报错”当成完整证明。
验收重建而不是只验“新对象存在”
重建后的核心问题是:逻辑定义有没有改变、有效状态是否正常、关键查询计划是否稳定、空间是否达到治理目标。对于重建操作,应优先比较变更前后的同一索引定义与体量,并复跑核心 SQL。
可以用索引定义查询来确认对象语义没有被意外改变:
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE indexname = '<INDEX_NAME>';预期结果
schemaname | tablename | indexname | indexdef
------------+-----------+--------------+------------------------------
<SCHEMA> | <TABLE> | <INDEX_NAME> | CREATE INDEX ...容量验收也要结合目标来解释。如果本次重建的目的就是治理膨胀,不能简单要求“新索引必须比旧索引小”;应该比较同一数据集和同一索引定义下的体量变化,并结合磁盘剩余空间判断治理是否值得。若目标是消除异常空间占用,则应把索引大小变化作为正式门槛,而不是仅记录一次结果。

把验收标准写成可以判定的门槛
好的验收标准必须能够让执行人和复核人得到同样结论。与其写“性能正常”,不如写成“核心查询结果与基线一致,执行时间不超过既定阈值,缓冲访问未出现异常增长”;与其写“空间正常”,不如明确记录索引大小、数据库剩余空间以及是否存在无效残留对象。
对于线上环境,还应保留变更前快照和变更后结果,至少覆盖索引定义、索引有效状态、核心查询计划、关键性能指标和容量数据。这样一次验收不仅能判断本次变更,还能为后续索引膨胀治理提供可比较的基线。
最终应把“功能、计划、性能、容量”视为缺一不可的验收链。只有四类证据都满足门槛,索引变更才真正从一次数据库操作变成可审计、可回退、可复用的工程变更。