PostgreSQL 索引与性能优化详解 —— 从原理到实战

✅ PostgreSQL 索引与性能优化详解 —— 从原理到实战

索引是数据库性能优化的核心手段。本篇将带你深入理解 PostgreSQL 索引原理、类型选择、创建策略、监控维护及性能调优实战。

---

🧭 一、索引基础概念

✅ 什么是索引？

索引是数据库中用于加速数据检索的数据结构，类似于书籍的“目录”。

✅ 为什么需要索引？

• 避免全表扫描（Seq Scan）
• 加速 WHERE、JOIN、ORDER BY、DISTINCT、GROUP BY
• 提高并发查询效率

⚠️ 索引的代价：

优点	缺点
查询速度大幅提升	占用磁盘空间
减少 I/O 操作	降低 INSERT/UPDATE/DELETE 速度（需维护索引）
提高系统并发能力	增加查询计划复杂度

💡 原则：读多写少 → 多建索引；写多读少 → 谨慎建索引

---

一、索引类型详解（PostgreSQL 支持 6 种）

---

✅ 1. B-Tree 索引（默认 & 最常用）

• 适用场景：等值查询、范围查询、排序、模糊查询（前缀匹配）
• 支持操作符：=, <, <=, >, >=, BETWEEN, IN, LIKE 'abc%'
• 内部结构：平衡多路搜索树（高度通常 3~4 层，千万级数据毫秒定位）

-- 创建 B-Tree 索引
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

✅ 90% 场景首选 B-Tree！

---

✅ 2. Hash 索引

• 适用场景：仅等值查询（=）
• 优点：等值查询极快（O(1) 时间复杂度）
• 缺点：
  • 不支持范围查询
  • PostgreSQL 10 之前不支持 WAL（崩溃后需重建）
  • 不能用于唯一约束

CREATE INDEX idx_users_id_hash ON users USING HASH (user_id);

💡 生产环境谨慎使用，B-Tree 通常更优。

---

✅ 3. GIN 索引（Generalized Inverted Index）

• 适用场景：JSONB、数组、全文搜索、范围（多值）
• 核心能力：支持“包含”查询（@>）、“存在”查询（?）、“重叠”查询（&&）

-- JSONB
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 数组
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 全文搜索
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', content));

✅ JSONB 查询必建 GIN 索引！

---

✅ 4. GiST 索引（Generalized Search Tree）

• 适用场景：范围类型、几何数据、全文搜索、KNN 查询
• 支持操作符：&&（重叠）、@>（包含）、<@（被包含）、<<（左）、>>（右）

-- 范围类型
CREATE INDEX idx_bookings_during ON room_bookings USING GIST (during);

-- 几何数据
CREATE INDEX idx_locations_point ON locations USING GIST (point);

-- 全文搜索（也可用 GIN）
CREATE INDEX idx_articles_gist ON articles USING GIST (to_tsvector('english', content));

💡 GIN 更适合“包含”查询，GiST 更适合“重叠”和“邻近”查询。

---

✅ 5. SP-GiST 索引（Space-Partitioned GiST）

• 适用场景：非平衡数据结构（如 IP 地址、树形结构、不规则几何）
• 优点：对稀疏、非均匀分布数据更高效

-- IP 地址范围
CREATE INDEX idx_ip_ranges ON ip_table USING SPGIST (ip_range inet_ops);

-- 树形路径（如文件系统路径）
CREATE INDEX idx_fs_path ON filesystem USING SPGIST (path text_ops);

---

✅ 6. BRIN 索引（Block Range Index）

• 适用场景：超大表 + 物理存储有序（如时间序列、自增 ID）
• 原理：为每个数据块（Block Range）存储最小/最大值，跳过不匹配块
• 优点：索引极小（1% 甚至 0.1% 表大小），维护成本低
• 缺点：数据无序时效果差

-- 时间序列表
CREATE INDEX idx_sensor_readings_time ON sensor_readings USING BRIN (reading_time);

-- 自增 ID 表
CREATE INDEX idx_logs_id ON logs USING BRIN (id);

✅ TB 级日志表、监控数据首选 BRIN！

---

🆚 索引类型对比速查表

索引类型	适用场景	查询类型	空间占用	写入开销
B-Tree	通用（90%场景）	=, <, >, LIKE ‘x%’	中	中
Hash	等值查询	=	小	低
GIN	JSONB、数组、全文搜索	@>, ?, &&	大	高
GiST	范围、几何、全文搜索	&&, @>, <<, >>	中	中
SP-GiST	IP、树形、稀疏数据	同 GiST	小	低
BRIN	超大表 + 物理有序（时间序列）	=, <, >	极小	极低

---

二、索引创建策略与最佳实践

---

✅ 1. 单列索引 vs 多列索引

-- 单列索引
CREATE INDEX idx_users_email ON users (email);

-- 多列索引（复合索引）
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

⚠️ 多列索引列顺序至关重要！

原则：

• 最常用、选择性最高（唯一值多）的列放前面
• 范围查询列放最后（B-Tree 中范围列之后的列无法使用索引）

-- ✅ 正确：等值 + 范围
CREATE INDEX idx_good ON orders (status, created_at); -- WHERE status='paid' AND created_at > '2025-01-01'

-- ❌ 低效：范围 + 等值
CREATE INDEX idx_bad ON orders (created_at, status);  -- created_at 范围查询后，status 无法用索引

---

✅ 2. 部分索引（Partial Index）

只为表的子集创建索引 → 节省空间，提高效率

-- 只为活跃用户建索引
CREATE INDEX idx_active_users_email ON users (email) WHERE is_active = true;

-- 只为未处理订单建索引
CREATE INDEX idx_pending_orders ON orders (user_id) WHERE status = 'pending';

---

✅ 3. 表达式索引（函数索引）

对列的表达式或函数结果建索引

-- 大小写不敏感查询
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- JSONB 路径索引
CREATE INDEX idx_users_profile_name ON users ((profile->>'name'));

-- 日期截断索引
CREATE INDEX idx_orders_month ON orders (DATE_TRUNC('month', created_at));

💡 使用时查询条件必须与索引表达式完全一致！

-- ✅ 能用索引
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- ❌ 不能用索引
SELECT * FROM users WHERE email ILIKE 'alice%';

---

✅ 4. 覆盖索引（Covering Index / Index-Only Scan）

PostgreSQL 11+ 支持 INCLUDE 子句，将非键列包含在索引中 → 避免回表

-- 查询 SELECT name, email WHERE user_id = ? 可完全走索引
CREATE INDEX idx_users_covering ON users (user_id) INCLUDE (name, email);

-- 旧版本可用多列索引模拟
CREATE INDEX idx_users_covering_old ON users (user_id, name, email);

✅ 大幅提升查询性能，尤其适合宽表！

---

✅ 5. 并发创建索引（不阻塞写入）

大表建索引会锁表 → 使用 CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_big_table_id ON big_table (id);

⚠️ 注意：

• 不能在事务中执行
• 如果失败会留下 INVALID 索引，需手动删除
• 执行时间更长（需两次表扫描）

---

三、索引监控与维护

---

✅ 1. 查看索引定义

-- psql 中
\d table_name

-- SQL 查询
SELECT 
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

---

✅ 2. 监控索引使用情况

-- 查看索引扫描次数（需开启 track_counts）
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used,          -- 被扫描次数
    idx_tup_read as tuples_read,     -- 通过索引读取的行数
    idx_tup_fetch as tuples_fetched  -- 通过索引获取的行数
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

📊 分析：

• times_used = 0 → 可能是无用索引，考虑删除
• tuples_fetched << tuples_read → 索引选择性差

---

✅ 3. 查看查询执行计划

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'alice@example.com';

关注：

• Index Scan vs Seq Scan
• Rows Removed by Filter
• Buffers: shared hit/read

---

✅ 4. 重建索引（碎片整理）

长期 UPDATE/DELETE 会导致索引膨胀 → 定期重建

-- 重建单个索引
REINDEX INDEX index_name;

-- 重建表的所有索引
REINDEX TABLE table_name;

-- 并发重建（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY index_name;

---

✅ 5. 删除无用索引

-- 删除索引
DROP INDEX IF EXISTS index_name;

-- 批量查找低使用率索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan < 50  -- 使用次数少于50次
ORDER BY pg_relation_size(indexrelid) DESC;

---

四、性能优化实战案例

---

🎯 案例1：慢查询优化（用户订单查询）

❌ 优化前：

SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at > '2025-01-01'
ORDER BY created_at DESC
LIMIT 10;

→ 全表扫描，耗时 2.5 秒

✅ 优化后：

-- 创建复合索引
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- 查询计划变为 Index Scan + Limit，耗时 0.002 秒！

---

🎯 案例2：JSONB 查询优化

❌ 优化前：

SELECT * FROM users 
WHERE profile->>'city' = '北京' 
  AND profile->'tags' ? 'vip';

→ Seq Scan，耗时 1.8 秒

✅ 优化后：

-- 创建 GIN 索引
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 或针对特定路径创建表达式索引（更高效）
CREATE INDEX idx_users_city ON users ((profile->>'city'));
CREATE INDEX idx_users_tags ON users USING GIN ((profile->'tags'));

-- 查询耗时 0.005 秒！

---

🎯 案例3：大表分区 + BRIN 索引

-- 创建按月分区的表
CREATE TABLE sensor_data (
    id BIGSERIAL,
    sensor_id INT,
    reading_time TIMESTAMPTZ,
    value NUMERIC
) PARTITION BY RANGE (reading_time);

-- 为每个分区创建 BRIN 索引
CREATE INDEX idx_sensor_data_brin ON sensor_data_2025_04 USING BRIN (reading_time);

-- 查询最近数据极快！
SELECT AVG(value) FROM sensor_data 
WHERE reading_time >= '2025-04-01' AND reading_time < '2025-05-01';

---

五、索引设计黄金法则

1. WHERE 条件列优先建索引
2. JOIN 关联列必须建索引
3. ORDER BY / GROUP BY 列考虑建索引
4. 多列索引注意列顺序（等值→范围）
5. 大文本/JSONB 用 GIN
6. 超大表+有序数据用 BRIN
7. 避免过度索引（写多读少的表）
8. 定期监控索引使用率，删除无用索引
9. 使用 EXPLAIN ANALYZE 验证优化效果
10. 大表建索引用 CONCURRENTLY

---

🎯 实践任务

请完成以下操作：

1. 创建一个包含 100 万行数据的 logs 表（id, user_id, action, created_at, metadata JSONB）
2. 执行慢查询：SELECT * FROM logs WHERE user_id = 100 AND created_at > '2025-01-01'
3. 使用 EXPLAIN ANALYZE 查看执行计划
4. 为 (user_id, created_at) 创建复合索引
5. 再次执行查询，对比性能差异
6. 为 metadata 创建 GIN 索引，测试 JSONB 查询性能
7. 使用 pg_stat_user_indexes 监控索引使用情况