PostgreSQL 部分索引详解

概述

部分索引（Partial Index） 是 PostgreSQL 中一种高级索引功能，它只对表中满足特定条件的行创建索引条目。与传统的全表索引不同，部分索引通过谓词条件（WHERE 子句）来过滤需要索引的数据，从而实现更精准、更高效的索引策略。

INFO

核心概念部分索引 = 索引列 + 谓词条件（WHERE 子句）

只有满足谓词条件的行才会被包含在索引中，这使得索引更小、更快、更有针对性。

部分索引的工作原理

核心优势

1. 存储空间优化

• 减小索引大小：只索引需要的数据
• 降低存储成本：特别适用于大表中的小数据子集

2. 性能提升

• 查询加速：索引更小，B-tree 层级更少
• 更新优化：不满足谓词的行更新时无需维护索引

3. 维护效率

• 减少索引维护开销：只有符合条件的数据变更才需要更新索引
• 降低锁竞争：减少索引页面的并发访问冲突

实战应用场景

场景一：Web 访问日志分析 - 排除常用值

业务背景

假设您运营一个企业网站，需要分析访问日志。大部分访问来自内部员工（192.168.100.x 网段），但您主要关心外部访问的分析。

问题分析

• 内部访问占总访问量的 80%
• 查询主要针对外部 IP 地址
• 为内部 IP 创建索引是资源浪费

解决方案

创建表结构

-- 创建访问日志表
CREATE TABLE access_log (
    id SERIAL PRIMARY KEY,
    url VARCHAR(500),
    client_ip INET,
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_agent TEXT,
    response_code INTEGER
);

插入测试数据

-- 插入测试数据
INSERT INTO access_log (url, client_ip, user_agent, response_code) VALUES
-- 内部访问（不会被索引）
('/home', '192.168.100.10', 'Mozilla/5.0', 200),
('/admin', '192.168.100.15', 'Mozilla/5.0', 200),
('/reports', '192.168.100.20', 'Mozilla/5.0', 200),
-- 外部访问（会被索引）
('/index.html', '212.78.10.32', 'Mozilla/5.0', 200),
('/products', '203.45.67.89', 'Mozilla/5.0', 200),
('/contact', '185.92.134.45', 'Mozilla/5.0', 404);

创建部分索引

-- 创建部分索引：只索引外部 IP 访问
CREATE INDEX access_log_external_ip_idx ON access_log (client_ip)
WHERE NOT (client_ip >= inet '192.168.100.0' AND
           client_ip <= inet '192.168.100.255');

查询示例与分析

有效查询 - 使用索引

-- ✅ 这个查询会使用部分索引
SELECT url, access_time, response_code
FROM access_log
WHERE client_ip = inet '212.78.10.32'
  AND access_time > CURRENT_DATE - INTERVAL '7 days';

无效查询 - 不使用索引

-- ❌ 这个查询不会使用部分索引
SELECT url, access_time, response_code
FROM access_log
WHERE client_ip = inet '192.168.100.15'
  AND access_time > CURRENT_DATE - INTERVAL '7 days';

性能对比查询

-- 查看查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM access_log
WHERE client_ip = inet '212.78.10.32';

执行计划分析

执行计划解读使用部分索引的查询会显示：

• Index Scan using access_log_external_ip_idx
• 扫描的行数明显减少
• 执行时间和缓冲区读取次数都有所改善

场景二：订单管理系统 - 关注活跃数据

业务背景

电商平台的订单表包含大量历史订单，但日常查询主要集中在未结算订单上。

数据特征分析

• 未结算订单：占总订单的 5%
• 已结算订单：占总订单的 95%
• 查询热点：90% 的查询针对未结算订单

实现方案

表结构设计

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_nr VARCHAR(20) UNIQUE NOT NULL,
    customer_id INTEGER,
    amount DECIMAL(10,2),
    order_date DATE DEFAULT CURRENT_DATE,
    billed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

数据准备

-- 插入示例数据
INSERT INTO orders (order_nr, customer_id, amount, billed) VALUES
('ORD001', 1001, 299.99, FALSE),  -- 未结算
('ORD002', 1002, 159.50, TRUE),   -- 已结算
('ORD003', 1003, 89.99, FALSE),   -- 未结算
('ORD004', 1004, 449.99, TRUE),   -- 已结算
('ORD005', 1005, 199.99, FALSE);  -- 未结算

创建部分索引

-- 只对未结算订单创建索引
CREATE INDEX orders_unbilled_idx ON orders (order_nr)
WHERE billed IS NOT TRUE;

-- 为金额查询创建复合部分索引
CREATE INDEX orders_unbilled_amount_idx ON orders (amount, order_id)
WHERE billed IS NOT TRUE;

多种查询模式

按订单号查询

-- ✅ 高效：使用 orders_unbilled_idx
SELECT * FROM orders
WHERE billed IS NOT TRUE
  AND order_nr = 'ORD001';

按金额范围查询

-- ✅ 高效：使用 orders_unbilled_amount_idx
SELECT * FROM orders
WHERE billed IS NOT TRUE
  AND amount > 200.00;

只按状态查询

-- ✅ 高效：索引扫描整个部分索引
SELECT COUNT(*) FROM orders
WHERE billed IS NOT TRUE;

无法使用索引的查询

-- ❌ 无法使用部分索引
SELECT * FROM orders
WHERE order_nr = 'ORD002';  -- 可能是已结算订单

性能对比

性能提升分析

传统全表索引 vs 部分索引

指标	全表索引	部分索引	提升比例
索引大小	100MB	5MB	95% ↓
查询时间	15ms	3ms	80% ↓
插入性能	基准	15% ↑	-
更新性能	基准	20% ↑	-

场景三：测试系统 - 部分唯一约束

业务需求

测试管理系统中，每个测试对象在特定目标上只能有一个成功记录，但可以有多个失败记录。

传统方案的问题

使用普通唯一约束会阻止记录多个失败测试结果。

部分唯一索引方案

表结构

CREATE TABLE test_results (
    test_id SERIAL PRIMARY KEY,
    subject VARCHAR(100) NOT NULL,
    target VARCHAR(100) NOT NULL,
    success BOOLEAN NOT NULL,
    test_date DATE DEFAULT CURRENT_DATE,
    details TEXT,
    score INTEGER
);

创建部分唯一索引

-- 只对成功的测试结果创建唯一约束
CREATE UNIQUE INDEX tests_success_constraint
ON test_results (subject, target)
WHERE success = TRUE;

测试数据插入

-- ✅ 允许：多个失败结果
INSERT INTO test_results (subject, target, success, score) VALUES
('数学', '期中考试', FALSE, 45),
('数学', '期中考试', FALSE, 52),
('数学', '期中考试', FALSE, 48);

-- ✅ 允许：一个成功结果
INSERT INTO test_results (subject, target, success, score) VALUES
('数学', '期中考试', TRUE, 85);

-- ❌ 违反约束：重复的成功结果
-- 这个插入会失败
INSERT INTO test_results (subject, target, success, score) VALUES
('数学', '期中考试', TRUE, 88);

约束验证

-- 验证约束效果
SELECT subject, target, success, COUNT(*) as count
FROM test_results
WHERE subject = '数学' AND target = '期中考试'
GROUP BY subject, target, success
ORDER BY success;

预期输出：

 subject | target   | success | count
---------|----------|---------|-------
 数学    | 期中考试  | f       | 3
 数学    | 期中考试  | t       | 1

高级使用技巧

1. 复杂谓词条件

-- 多条件组合的部分索引
CREATE INDEX user_activity_recent_idx ON user_logs (user_id, action_time)
WHERE action_time > CURRENT_DATE - INTERVAL '30 days'
  AND action_type IN ('login', 'purchase', 'view_product')
  AND is_bot = FALSE;

2. 函数表达式部分索引

-- 基于计算字段的部分索引
CREATE INDEX products_discounted_idx ON products (category, price)
WHERE (original_price - current_price) / original_price > 0.1;

3. 空值处理

-- 只索引非空值
CREATE INDEX customer_phone_idx ON customers (phone)
WHERE phone IS NOT NULL;

-- 只允许一个空值的唯一索引
CREATE UNIQUE INDEX customer_unique_email_idx ON customers (email)
WHERE email IS NOT NULL;

查询优化器的谓词匹配

匹配规则

PostgreSQL 查询优化器在决定是否使用部分索引时，会检查查询的 WHERE 条件是否在数学上蕴含索引的谓词条件。

WARNING

谓词匹配限制查询优化器的匹配能力有限：

✅ 可以识别的简单蕴含关系：

• x < 1 蕴含 x < 2
• x = 5 蕴含 x < 10

❌ 无法识别的复杂关系：

• 参数化查询：x < ? 不会匹配 x < 2
• 复杂表达式：x + y < 10 不会匹配 x < 5

最佳实践示例

正确的匹配模式

-- 索引谓词
CREATE INDEX idx_name ON table_name (col)
WHERE status = 'active';

-- ✅ 匹配的查询
SELECT * FROM table_name
WHERE status = 'active' AND other_col = 'value';

不匹配的查询模式

-- ❌ 参数化查询不匹配
PREPARE stmt AS
SELECT * FROM table_name
WHERE status = $1 AND other_col = $2;

-- 即使运行时 $1 = 'active'，也不会使用索引

性能监控与优化

索引使用情况监控

-- 查看索引使用统计
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE indexname LIKE '%partial%'
ORDER BY idx_scan DESC;

索引大小对比

-- 比较索引大小
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'your_table_name'
ORDER BY pg_relation_size(indexname::regclass) DESC;

常见陷阱与反模式

❌ 反模式：过度分割的部分索引

-- 错误做法：为每个类别创建单独的部分索引
CREATE INDEX products_cat_1_idx ON products (price) WHERE category_id = 1;
CREATE INDEX products_cat_2_idx ON products (price) WHERE category_id = 2;
CREATE INDEX products_cat_3_idx ON products (price) WHERE category_id = 3;
-- ... 继续创建更多索引

问题分析：

• 查询规划器需要测试每个索引的适用性
• 增加了规划时间
• 维护多个小索引的开销可能超过收益

✅ 正确做法：复合索引

-- 正确做法：使用复合索引
CREATE INDEX products_category_price_idx ON products (category_id, price);

何时考虑分区而非部分索引

分区 vs 部分索引选择指南

使用分区的情况：

• 表非常大（百万行以上）
• 数据有明显的时间或范围分割特征
• 需要并行查询优化
• 管理和维护的便利性更重要

使用部分索引的情况：

• 需要精细的条件控制
• 表大小适中
• 查询模式相对固定
• 索引选择性很高

维护和管理策略

定期评估和重建

-- 检查部分索引的有效性
WITH index_stats AS (
    SELECT
        schemaname,
        tablename,
        indexname,
        idx_scan,
        pg_size_pretty(pg_relation_size(indexname::regclass)) as size
    FROM pg_stat_user_indexes
    WHERE indexname LIKE '%partial%' OR indexname LIKE '%_idx'
)
SELECT * FROM index_stats
WHERE idx_scan < 100  -- 使用次数少的索引
ORDER BY pg_relation_size(indexname::regclass) DESC;

数据分布变化的应对

监控数据分布

-- 监控部分索引覆盖的数据比例
SELECT
    COUNT(*) as total_rows,
    COUNT(*) FILTER (WHERE billed IS NOT TRUE) as indexed_rows,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE billed IS NOT TRUE) / COUNT(*),
        2
    ) as coverage_percentage
FROM orders;

重建策略

-- 当数据分布显著变化时，考虑重建索引
DROP INDEX IF EXISTS orders_unbilled_idx;

-- 根据新的数据分布调整谓词条件
CREATE INDEX orders_unbilled_idx ON orders (order_nr)
WHERE billed IS NOT TRUE
  AND order_date > CURRENT_DATE - INTERVAL '1 year';

总结

部分索引是 PostgreSQL 中一个强大但需要谨慎使用的功能。它在以下场景中特别有价值：

1. 数据倾斜严重：大部分数据不需要索引
2. 查询模式固定：明确知道哪些数据会被频繁查询
3. 存储成本敏感：需要优化索引的存储空间
4. 特殊约束需求：需要条件性的唯一约束

使用原则

• 深入理解业务查询模式后再创建部分索引
• 监控索引使用情况，及时调整不合理的索引
• 考虑维护成本，避免过度复杂的索引策略
• 优先考虑简单方案，部分索引不是万能解决方案

通过合理使用部分索引，可以显著提升数据库查询性能，同时降低存储和维护成本，但需要在复杂性和收益之间找到平衡点。