PostgreSQL / 系统课时

使用部分索引

使用部分索引

部分索引(Partial Index)只为满足指定谓词的行建立索引,适合把查询最常访问的一小部分数据单独组织起来。本课重点解决两个问题:什么时候值得把索引限定在一部分行,以及为什么 SQL 明明写了同样的过滤条件,优化器仍可能不使用这个索引。

学习目标

  • 能根据数据分布判断部分索引是否值得建立。
  • 能理解查询谓词与部分索引谓词之间的匹配关系。
  • 能用 EXPLAIN 验证部分索引是否命中,并识别常见失配原因。

先看数据分布,再决定是否缩小索引

假设订单表有大量历史数据,但业务后台最常查询的是仍处于待处理状态的订单。此时为全表建立 status 索引,意味着历史已完成订单也要占用索引空间;若目标查询长期只关心少数状态,部分索引更合适。

sql
CREATE TABLE orders (
    id bigint PRIMARY KEY,
    tenant_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    total_amount numeric(12, 2) NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders (tenant_id, created_at DESC)
    WHERE status IN ('PENDING', 'PROCESSING');

EXPLAIN (COSTS OFF)
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;

预期结果

text
Limit
  -> Index Scan using idx_orders_pending on orders
       Index Cond: (tenant_id = 42)

实际计划会受统计信息、数据规模和其他索引影响,Index Cond 的展示细节也可能不同。关键验证点是计划是否选择 idx_orders_pending,以及是否仍需要对大量不满足部分索引谓词的行做额外扫描。

部分索引真正省下的是什么

部分索引的收益来自两处:索引条目更少、查询时要维护和访问的数据结构更小。若 PENDING 和 PROCESSING 只占总表的一小部分,而且这类查询频率很高,收益通常更明显。

但部分索引不是“给某个值单独建索引”的语法糖。它表达的是一个稳定的集合条件,例如 WHERE status IN (...)、WHERE deleted_at IS NULL。如果被索引集合随着业务演进迅速扩大,索引体积和维护成本也会同步增加,此时就需要重新评估。

部分索引的覆盖范围与查询命中关系

谓词匹配决定能不能用

优化器必须能够判断查询条件保证目标行满足部分索引谓词。最稳妥的做法是让业务 SQL 直接包含与索引定义语义一致的条件,例如索引谓词是 status IN ('PENDING', 'PROCESSING'),查询明确限定其中一个状态。

常见失配来自应用层把条件写得过于动态,或者查询只保留了一个无法证明集合包含关系的表达式。比如应用只写 WHERE tenant_id = 42,虽然返回的数据中可能碰巧有待处理订单,但它没有保证结果行一定满足部分索引谓词,因此不能简单假设这个查询可以安全使用该索引。

用数据分布决定是否长期保留

部分索引特别依赖业务分布。可以用聚合先估计目标集合占比,再决定它是否值得单独维护。

sql
SELECT
    status,
    count(*) AS rows,
    round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status
ORDER BY rows DESC;

预期结果

text
   status    | rows |  pct
-------------+------+------
 DONE        | 9500 | 95.00
 PENDING     |  300 |  3.00
 PROCESSING  |  200 |  2.00

这里的数字只是示例,实际结果取决于你的数据。重点是判断部分索引覆盖集合是否足够小,以及该集合上的查询是否足够频繁。

还要考虑更新成本。状态从 PENDING 变成 DONE 时,相关行会从部分索引覆盖范围中退出;反向变化则会重新进入索引。因此如果状态迁移非常频繁,部分索引虽然较小,却可能增加索引维护活动。设计时要同时看读收益和写入成本。

建立后如何确认它仍然值得用

上线后不要只检查“有没有这个索引”,而要持续观察目标查询是否命中、扫描量是否下降,以及索引覆盖集合是否发生结构性变化。若某个状态从 5% 扩大到 60%,原本“只索引热点集合”的假设可能已经不成立。

部分索引最适合满足一个稳定、明确且规模明显小于全表的业务子集。先验证谓词可匹配,再结合数据占比、查询频率和状态更新成本评估;只有这三点同时成立,Partial Index 才真正有治理价值。

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

目录与阅读设置

记录一个想法

使用部分索引

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

登录哪都通