索引与性能优化

5.1 索引类型概览

PostgreSQL 提供多种索引类型，每种针对不同数据结构和查询模式优化。选错索引类型，性能可能毫无改善甚至更差。

索引类型	适用数据	支持操作	典型场景
B-Tree	可排序的标量值	=、<、>、BETWEEN、LIKE 'prefix%'	主键、外键、时间范围查询
Hash	任何值	仅 = 操作	等值查询，PG 10+ WAL 安全
GIN	复合值（数组、JSONB、tsvector）	包含、相交、全文匹配	数组 @>、JSONB 查询、全文搜索
GiST	几何/范围类型	相交、包含、最近邻	PostGIS 地理坐标、范围类型、pg_trgm
SP-GiST	非均匀分布数据	前缀匹配、最近邻	IP 地址、电话号码、树形路径
BRIN	物理顺序与值顺序相关的大表	范围扫描	时序日志表（按时间写入）

5.2 B-Tree 索引详解

SQL-- 创建普通 B-Tree 索引
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created ON orders (created_at DESC);

-- 多列（复合）索引：列顺序非常重要！
-- 查询 WHERE status = 'pending' AND created_at > '2024-01-01'
-- 选择性高的列（status 枚举值少）放前面
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 部分索引（Partial Index）：只索引满足条件的行
-- 场景：只查询 is_active=TRUE 的商品，这是多数查询的条件
CREATE INDEX idx_products_active ON products (price)
WHERE is_active = TRUE;
-- 效果：索引只包含活跃商品的行，体积更小，更新更少

-- 表达式索引：对表达式结果建索引
-- 场景：经常查询 LOWER(email)（不区分大小写）
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- 查询时必须用相同表达式才能命中：WHERE LOWER(email) = 'alice@example.com'

-- UNIQUE 索引（同时保证数据唯一性）
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

-- CONCURRENTLY：不锁表地在线创建索引（生产环境必备）
-- 需要更多时间，且不在事务块内执行
CREATE INDEX CONCURRENTLY idx_orders_total ON orders (total);

-- 删除索引
DROP INDEX CONCURRENTLY idx_orders_total;

5.3 GIN 索引：数组、JSONB 与全文搜索

SQL-- GIN 索引加速数组查询
CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);

-- 查询时 GIN 索引会生效：
SELECT * FROM products WHERE tags @> ARRAY['laptop'];  -- 使用 GIN

-- GIN 索引加速 JSONB 查询（jsonb_ops，最通用）
CREATE INDEX idx_profiles_jsonb ON user_profiles USING GIN (profile);

-- jsonb_path_ops：只支持 @> 但体积更小、查询更快
CREATE INDEX idx_profiles_path ON user_profiles
  USING GIN (profile jsonb_path_ops);

-- GIN 索引加速全文搜索
ALTER TABLE products ADD COLUMN search_vector TSVECTOR
  GENERATED ALWAYS AS (to_tsvector('english', COALESCE(name,'') || ' ' || COALESCE(description,''))) STORED;

CREATE INDEX idx_products_fts ON products USING GIN (search_vector);

5.4 EXPLAIN ANALYZE：读懂执行计划

EXPLAIN ANALYZE 是性能诊断的核心工具。EXPLAIN 显示计划（不执行），EXPLAIN ANALYZE 实际执行并显示真实时间。

SQL-- 基础用法
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- 带详细 Buffer 信息（生产诊断推荐）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

EXPLAIN OUTPUT — 示例解读Gather  (cost=1000.00..12345.67 rows=500 width=48)
        (actual time=5.432..38.891 rows=500 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Hash Join  (cost=... rows=... width=...)
                          (actual time=... rows=... loops=3)
        Hash Cond: (o.user_id = u.id)
        ->  Parallel Seq Scan on orders o
                          (cost=0.00..8234.50 rows=182350 width=32)
                          (actual time=0.041..25.312 rows=182350 loops=3)
        ->  Hash  (cost=321.00..321.00 rows=10000 width=16)
                  (actual time=2.198..2.198 rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 512kB
              ->  Seq Scan on users u
                  (cost=0.00..321.00 rows=10000 width=16)

Planning Time: 1.234 ms
Execution Time: 45.678 ms

字段	含义
cost=1000..12345	启动代价..总代价（优化器估算的抽象单位，非毫秒）
rows=500	优化器估算的输出行数
actual time=5.4..38.9	实际执行时间（毫秒）：第一行返回时间..最后一行返回时间
actual rows=500	实际返回行数（与估算差距大时说明统计信息陈旧）
loops=3	该节点执行次数（Parallel 时等于 Worker 数量）
Seq Scan	全表扫描（无索引或优化器认为全扫更快）
Index Scan	索引扫描（回表读取堆数据）
Index Only Scan	仅索引扫描（覆盖索引，不回表，最优）
Bitmap Index Scan	位图索引扫描（多条件 OR 时合并位图）

5.5 慢查询定位

SQL-- 1. 启用 pg_stat_statements（需在 postgresql.conf 添加 shared_preload_libraries）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查询最耗时的 SQL（按总执行时间降序）
SELECT
  LEFT(query, 100)       AS query_preview,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric,  2) AS avg_ms,
  ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 2. 记录慢查询到日志（在 postgresql.conf 中设置）
-- log_min_duration_statement = 1000  # 记录超过 1 秒的查询
-- log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

-- 或会话级临时启用：
SET log_min_duration_statement = 500;  -- 500ms

-- 3. 查看当前活跃查询（>5秒的）
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 强制终止慢查询（谨慎使用）
SELECT pg_cancel_backend(pid);   -- 发送 SIGINT，允许查询清理
SELECT pg_terminate_backend(pid); -- 发送 SIGTERM，立即终止

5.6 统计信息与 ANALYZE

SQL-- 手动更新统计信息（通常由 autovacuum 自动完成）
ANALYZE orders;          -- 分析单表
ANALYZE VERBOSE;         -- 分析所有表并显示进度

-- 查看列的统计信息（优化器用这些决定索引选择）
SELECT
  attname AS column_name,
  n_distinct,           -- 不同值数量（负数表示占比）
  correlation,          -- 物理顺序与值顺序的相关性（1=完全相关，适合索引扫描）
  most_common_vals,     -- 最常见的值列表
  most_common_freqs     -- 对应频率
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY attname;

-- 增加统计精度（默认100，范围1-10000）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

5.7 VACUUM 与表膨胀

PostgreSQL 的 MVCC 机制会产生"死元组"（dead tuples）：UPDATE 和 DELETE 不立即物理删除旧版本行，而是标记为"死亡"。VACUUM 负责回收这些死元组占用的空间。

SQL-- 手动 VACUUM
VACUUM orders;                -- 清理死元组，不锁表，不释放系统空间（重用给 PG）
VACUUM FULL orders;           -- 完全重写表，释放空间给 OS，但会锁表！生产慎用
VACUUM ANALYZE orders;        -- 清理 + 更新统计信息
VACUUM VERBOSE ANALYZE;       -- 详细输出

-- 查看各表的死元组情况
SELECT
  schemaname,
  relname AS table_name,
  n_dead_tup AS dead_tuples,
  n_live_tup AS live_tuples,
  ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

-- 调整 autovacuum 积极性（高更新频率表单独配置）
ALTER TABLE orders
  SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 默认 0.2，改为 1% 死元组即触发
    autovacuum_analyze_scale_factor = 0.005
  );