PostgreSQL 部分索引（Partial Index）详解

PostgreSQL 部分索引Partial Index详解一、什么是部分索引普通索引会对表中所有行建立索引条目而部分索引只对满足特定条件的行建立索引。-- 普通索引对全表 10000 万行建索引CREATEINDEXidx_normalONmage_op_order_full(version_number,svc_tag);-- 部分索引只对待处理的 ESS 行建索引可能只有几万行CREATEINDEXidx_partialONmage_op_order_full(version_number,svc_tag)WHEREess_noISNULLANDsource_parent_idISNOTNULL;一句话理解普通索引是给全班同学建花名册部分索引是只给还没交作业的同学建花名册。二、真实案例背景在 Magellan ST 数据集成链路中有一个flush2SQL 专门用于补填 ESS 产品编号-- flush2找出 ESS 行中 ess_no 为空的记录从父记录中回填 product_noUPDATEmage_op_order_full aSETess_nob.product_noFROMmage_op_order_full bWHEREa.version_number#{versionNumber} -- 当前批次ANDa.svc_tagESS-- 只处理 ESS 类型ANDa.ess_noISNULL-- 尚未填充ANDa.source_parent_idISNOTNULL-- 有父记录ANDa.source_parent_idb.sell_through_record_id表数据特征mage_op_order_full总数据量数千万行其中svc_tag ESS且ess_no IS NULL的行极少数只有新批次刚写入的数据随着flush2执行完毕这些行的ess_product_no被填充后自动退出部分索引范围三、普通索引 vs 部分索引3.1 索引大小对比表总行数5000 万行 其中 ess_no IS NULL 的行约 50 万行仅占 1% 普通索引大小~2GB覆盖 5000 万行 部分索引大小~20MB只覆盖 50 万行 索引体积缩小 100 倍 → 全部装入内存 → 查询速度极快3.2 写入维护成本对比操作普通索引部分索引INSERT 新行ess_no IS NULL必须写入索引必须写入索引INSERT 新行ess_no 有值必须写入索引✅ 不需要维护UPDATE 填充 ess_product_noIS NULL → 有值索引条目需要删除更新✅ 行退出索引范围自动失效UPDATE 与索引无关的字段所有索引都要检查✅ 不在条件范围内的行直接跳过核心优势只有 1% 的行满足部分索引条件但 99% 的 INSERT/UPDATE 操作都不需要维护这个索引写入成本降低 99%。3.3 查询命中条件对比-- ✅ 能命中部分索引查询条件包含部分索引的 WHERE 子句UPDATE...WHEREversion_number?ANDsvc_tagESSANDess_noISNULL-- ← 包含此条件ANDsource_parent_idISNOTNULL-- ← 包含此条件-- ❌ 不能命中部分索引查询条件不满足部分索引的 WHERE 子句SELECT*FROMmage_op_order_fullWHEREversion_number?-- 这个查询没有 ess_no IS NULL 条件走普通索引或全表扫描四、建立部分索引针对flush2的查询模式建议创建如下部分索引-- 针对 flush2 的部分索引CREATEINDEXidx_st_full_ess_flushONmage_op_order_full(version_number,svc_tag)WHEREess_noISNULLANDsource_parent_idISNOTNULL;索引字段解释version_numberWHERE 中的等值过滤放第一位选择性高svc_tagWHERE 中的等值过滤 ESS放第二位WHERE ess_no IS NULL AND source_parent_id IS NOT NULL部分索引条件只索引待处理的行五、部分索引的自动缩减特性这是部分索引最精妙的地方结合本案例理解时间线 T1: 新批次写入 50 万条 ESS 行ess_no NULL → 这 50 万行进入部分索引 → 索引大小50 万条目 T2: flush2 执行回填 ess_product_no → 每填充一行ess_no IS NULL 不再成立 → 该行自动退出部分索引范围 → 索引大小逐渐缩减至 0 T3: flush2 执行完毕 → 部分索引几乎为空只剩真正没有父记录的极少数行 → 索引几乎不占空间也不影响后续写入性能这就像待办清单完成一项划掉一项清单越来越短查找速度始终很快。六、验证部分索引是否生效-- 用 EXPLAIN ANALYZE 查看执行计划EXPLAINANALYZEUPDATEmage_op_order_full aSETess_nob.product_noFROMmage_op_order_full bWHEREa.version_number20260522000707011ANDa.svc_tagESSANDa.ess_noISNULLANDa.source_parent_idISNOTNULLANDa.source_parent_idb.record_id;执行计划中出现以下内容说明部分索引已命中Index Scan using idx_st_full_ess_flush on mage_op_order_full a Index Cond: ((version_number 20260522000707011) AND (svc_tag ESS)) Filter: (ess_no IS NULL AND source_parent_id IS NOT NULL)七、什么时候适合用部分索引适用场景说明示例只处理少数状态的数据大表中只有少数行处于待处理状态status PENDING处理完变为DONE软删除过滤绝大多数行都是有效数据WHERE deleted_at IS NULL特定类型数据加速只有某个枚举值的子集需要高频查询WHERE svc_tag ESSNULL 值过滤表中大量字段为 NULL只查非 NULLWHERE ess_no IS NULL时间窗口查询只查最近 N 天的数据WHERE created_at NOW() - INTERVAL 7 days八、部分索引 vs 普通索引 总结对比维度普通索引部分索引索引覆盖范围全表所有行只覆盖满足 WHERE 条件的行索引体积大与表成比例小只有目标行写入维护成本每次 INSERT/UPDATE 都要维护只有满足条件的行才维护查询命中条件只需查询字段匹配查询条件必须包含索引的 WHERE 子句适用场景通用查询特定状态/条件的高频查询MySQL 支持✅❌MySQL 不支持PostgreSQL 支持✅✅九、注意事项1. 查询条件必须覆盖部分索引条件-- ✅ 命中查询条件包含了部分索引的 WHERE 子句WHEREversion_number?ANDsvc_tagESSANDess_noISNULL-- ❌ 不命中缺少 ess_no IS NULL 条件WHEREversion_number?ANDsvc_tagESS2. 新建部分索引需要 REINDEX 或等待 autovacuum-- 建好后立即生效不需要重建-- 但如果表已有大量数据建索引过程会扫描全表一次只扫满足条件的行CREATEINDEXCONCURRENTLY idx_st_full_ess_flush-- 加 CONCURRENTLY 不锁表ONmage_op_order_full(version_number,svc_tag)WHEREess_noISNULLANDsource_parent_idISNOTNULL;3. 部分索引不适合频繁变化的条件列如果 WHERE 条件中的列频繁被 UPDATE会导致行不断进出索引反而增加维护开销。本案例中ess_no只会从 NULL → 有值单向变化非常适合部分索引。十、一句话总结部分索引是给少数特殊行开的快速通道它比普通索引体积更小、维护成本更低、查询更快——前提是你的查询条件能精确描述这批特殊行是谁。