PostgreSQL 索引设计完全指南:从一次"加 30 个索引写入腰斩查询还是 5 秒"看懂为什么 CREATE INDEX 远远不够

2020 年我接手一个 SaaS 客户关系管理系统 PostgreSQL 15 单库 800 张表 最大表 customer_events 8000 万行 用户反馈系统越来越慢 一个查询从 50ms 涨到 8 秒 老板天天追着问。我以为加索引嘛 简单 在 user_id 加个 B-tree 在 created_at 加个 B-tree 在 status 加个 B-tree 一共加了 30 多个索引 重启 应用查询确实快了 但写入性能从 5000 TPS 降到 800 TPS 业务高峰期 write lock 排队 用户下单都卡。然后我们陆续踩了一堆坑。第一种最让我傻眼 我在 user_id 加了单列 B-tree 在 status 加了单列 B-tree 但用户查询是 WHERE user_id=? AND status=? PostgreSQL 优化器只用了 user_id 一个索引 status 还是 filter 扫 30 万行 慢 5 秒。第二种最难缠 我建索引一律 B-tree 实测 like %关键字% 模糊搜索 B-tree 完全用不上 全表扫 8 秒 改成 GIN 加 pg_trgm 50ms 搞定 之前根本不知道有这个东西。第三种最离谱 我建索引时没考虑顺序 INDEX (user_id, created_at) 查 WHERE created_at > ? 不带 user_id 索引完全没用 改成 INDEX (created_at, user_id) 才有效 索引列顺序学问大了去。第四种最致命 我们的统计查询 SELECT count(*) WHERE status=active 慢 status 只有 3 个值 cardinality 极低 B-tree 索引没用 应该用 partial index WHERE status=active 才解决。第五种最莫名其妙 我们 JSONB 字段 user_profile 经常查 user_profile->>'city'='shanghai' 没建索引 全表扫 PostgreSQL 有 GIN 索引专门支持 JSONB 但要正确建。我盯着这一连串问题想了很久才彻底想明白第一版错在一个根本的认知上我以为索引就是 哪个字段查得多就加 多多益善 可这个认知是错的真正能用的 PostgreSQL 索引设计是一个 索引类型选择 加 复合索引顺序 加 partial 与 expression 索引 加 索引维护成本 加 EXPLAIN 分析 加 监控索引使用率 的整套工程方法论 任何一环没做都可能让你的索引白建 写入变慢 查询还是 5 秒本文从头梳理 PostgreSQL 索引设计的工程要点 索引类型怎么选 复合索引怎么排顺序 partial 索引何时用 GIN BRIN 适用什么场景 索引怎么监控 以及一些把索引设计做扎实要避开的工程坑

问题背景:为什么索引不是 CREATE INDEX 就完事

很多人对索引的认知是 哪个字段查得多就加上 加得越多越好 实际上 PostgreSQL 索引有 6 种类型 B-tree GIN GiST BRIN Hash SP-GiST 每种适用不同场景 复合索引的列顺序决定能不能用 partial index 能让 cardinality 低的字段也能优化 expression index 能优化函数查询 索引建错了不仅查询不快还拖累写入 因为每次 INSERT UPDATE DELETE 都要维护所有相关索引。问题的根源在于:

• 索引类型决定能优化什么场景:B-tree 通用 但 LIKE %x% 用不上 必须 GIN+pg_trgm。
• 复合索引列顺序决定能不能用:INDEX(a,b) 能用于 WHERE a=? 与 WHERE a=? AND b=? 但用不了 WHERE b=?。
• partial index 让低基数字段可优化:status 只有 3 个值 B-tree 没用 partial WHERE status=active 解决。
• expression index 能优化函数查询:WHERE lower(email)=? 普通索引没用 INDEX(lower(email)) 才行。
• 索引有写入代价:每个索引让 INSERT 慢 10-20% 加 30 个索引写入腰斩。
• 必须监控使用率:pg_stat_user_indexes 看哪些索引从来没被用过 删掉腾空间。

一 索引类型选择:6 种各有所长

PostgreSQL 提供 6 种索引类型 B-tree GIN GiST BRIN Hash SP-GiST 大多数人只知道 B-tree 这是浪费。每种索引有自己的强项 选对了一行 SQL 就快 100 倍 选错了索引白建还拖累写入。

-- 1 B-tree 通用 默认选择
-- 适合 = != < > BETWEEN ORDER BY 范围查询
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- B-tree 不适合 LIKE %x% 模糊搜索 JSONB 查询 数组包含

-- 2 GIN 倒排索引
-- 适合 全文搜索 数组 JSONB tsvector 大文档字段
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- LIKE %关键字% 用 GIN+pg_trgm
CREATE INDEX idx_products_name_trgm
ON products USING GIN (name gin_trgm_ops);
-- 现在 WHERE name LIKE '%关键字%' 从全表扫变成索引扫

-- JSONB 字段
CREATE INDEX idx_user_profile_gin
ON users USING GIN (profile);
-- 现在 WHERE profile @> '{"city":"shanghai"}' 能用索引

-- JSONB 特定路径用 jsonb_path_ops 更省空间
CREATE INDEX idx_user_profile_path
ON users USING GIN (profile jsonb_path_ops);

-- 数组字段
CREATE INDEX idx_tags_gin ON articles USING GIN (tags);
-- 现在 WHERE tags @> ARRAY['postgresql'] 能用索引

-- 全文搜索
CREATE INDEX idx_content_fts
ON articles USING GIN (to_tsvector('chinese', content));
-- WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'postgres & 索引')

-- 3 GiST 通用搜索树
-- 适合 几何 地理 范围 全文搜索 KNN
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE INDEX idx_shops_location ON shops USING GIST (location);
-- WHERE ST_DWithin(location, point, 5000) 用 GIST 索引

-- 范围类型
CREATE INDEX idx_reservation_period
ON reservations USING GIST (period);
-- WHERE period && tsrange('2024-01-01', '2024-12-31') 重叠查询

-- 4 BRIN 块范围索引
-- 适合 物理顺序与值顺序相关的大表 比如时序日志
CREATE INDEX idx_events_created_brin
ON events USING BRIN (created_at) WITH (pages_per_range = 32);
-- 对 10 亿行日志表 B-tree 索引要 50G BRIN 只要 100M
-- 但查询效率比 B-tree 慢 适合 archive 数据

-- 5 Hash 等值查询专用
-- 适合 仅 = 查询 不支持范围
CREATE INDEX idx_users_id_hash ON users USING HASH (user_id);
-- 比 B-tree 略快但限制多 一般不用

-- 6 SP-GiST 空间分区树
-- 适合 IP 地址 phone 这种非平衡数据
CREATE INDEX idx_ips_spgist ON access_log USING SPGIST (ip_address inet_ops);

选索引类型有几个简单原则 通用查询用 B-tree 模糊搜索与 JSONB 数组用 GIN 地理位置用 GiST 时序大表 archive 用 BRIN 这四种覆盖 95% 场景。Hash 与 SP-GiST 是特殊场景不常用。

-- 索引类型选型对照表 查询场景 -> 推荐索引

-- 场景 1 用户登录查邮箱
-- WHERE email = ? B-tree
CREATE INDEX idx_users_email ON users (email);

-- 场景 2 商品名模糊搜索
-- WHERE name LIKE '%关键字%' GIN+pg_trgm
CREATE INDEX idx_products_name_trgm
ON products USING GIN (name gin_trgm_ops);

-- 场景 3 文章标签筛选
-- WHERE tags @> ARRAY['postgres'] GIN on array
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 场景 4 用户画像 JSONB 查询
-- WHERE profile @> '{"vip":true}' GIN on jsonb
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 场景 5 附近 5km 餐厅
-- WHERE ST_DWithin(location, point, 5000) GiST
CREATE INDEX idx_restaurants_loc ON restaurants USING GIST (location);

-- 场景 6 时序日志范围查询
-- WHERE created_at BETWEEN ? AND ? BRIN 适合 archive 表
CREATE INDEX idx_events_brin ON events USING BRIN (created_at);
-- 注意 BRIN 要求物理顺序与值顺序相关 即按时间插入

-- 场景 7 大小写不敏感查询
-- WHERE lower(email) = ? expression B-tree
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 场景 8 仅活跃用户查询
-- WHERE active = true partial B-tree
CREATE INDEX idx_users_active ON users (created_at) WHERE active = true;

索引类型选择的工程经验 B-tree 处理通用等值与范围 GIN 处理模糊搜索 JSONB 数组 GiST 处理地理与范围类型 BRIN 处理时序大表 这四种覆盖 95% 场景 不要默认 B-tree 而错过更好的选择 上线前必须用 EXPLAIN ANALYZE 验证索引被用上 不要看 CREATE INDEX 成功就以为完事。我们公司之前所有索引都 B-tree 切换到合适类型后 模糊搜索从 8 秒降到 50ms 全文搜索从 5 秒降到 30ms 性能提升肉眼可见。

二 复合索引:列顺序决定生死

复合索引比单列索引强大 但列顺序写错就完全失效。INDEX(a,b,c) 能加速 WHERE a=? WHERE a=? AND b=? WHERE a=? AND b=? AND c=? 但加速不了 WHERE b=? WHERE c=? WHERE b=? AND c=? 这个 leftmost prefix 规则不懂就建废索引。

-- 1 复合索引的 leftmost prefix 规则
CREATE INDEX idx_orders_compound
ON orders (user_id, status, created_at);

-- 能用上索引的查询
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;  -- 用上
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';  -- 用上
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid'
AND created_at > '2024-01-01';  -- 用上

-- 用不上索引的查询 即使字段都在索引里
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';  -- 用不上
EXPLAIN ANALYZE SELECT * FROM orders
WHERE created_at > '2024-01-01';  -- 用不上
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2024-01-01';  -- 用不上

-- 部分用上 PostgreSQL 优化器会做 skip scan 但效率不如全 leftmost
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 1 AND created_at > '2024-01-01';
-- user_id 用上 created_at filter 在结果里再过

-- 2 列顺序的设计原则
-- 原则 a 选择性高的放前面 排除掉大部分行
-- 原则 b 等值条件放前面 范围条件放后面
-- 原则 c 经常在 ORDER BY 出现的放后面

-- 错误顺序
CREATE INDEX bad_idx ON orders (created_at, user_id, status);
-- 查询 WHERE user_id=1 AND status='paid' 用不上 created_at 是范围条件不在前

-- 正确顺序
CREATE INDEX good_idx ON orders (user_id, status, created_at);
-- 等值条件 user_id status 在前 范围 created_at 在后
-- 还能加速 ORDER BY created_at

复合索引列顺序错了的代价就是索引白建 PostgreSQL 优化器有时候会 bitmap scan 多个单列索引代替复合索引 但效率不如直接的复合索引。一个表上索引太多 写入又慢 一定要规划清楚。

-- 3 覆盖索引 covering index
-- 把 SELECT 的字段也放进索引 避免回表
CREATE INDEX idx_orders_cover
ON orders (user_id, status)
INCLUDE (total_amount, created_at);

-- 现在这个查询不需要回表 index only scan
EXPLAIN ANALYZE
SELECT total_amount, created_at FROM orders
WHERE user_id = 1 AND status = 'paid';

-- 4 索引列顺序的实战分析
-- 表 customer_events 8000 万行 字段 user_id status event_type created_at

-- 最常见查询
-- Q1: WHERE user_id=? ORDER BY created_at DESC LIMIT 20
-- Q2: WHERE user_id=? AND event_type=? AND created_at BETWEEN ? AND ?
-- Q3: WHERE status='pending' AND created_at > ?

-- 设计索引方案
-- 方案 A 覆盖 Q1 Q2
CREATE INDEX idx_events_user_type_time
ON customer_events (user_id, event_type, created_at DESC);

-- 方案 B 覆盖 Q3 用 partial 减小索引
CREATE INDEX idx_events_pending
ON customer_events (created_at)
WHERE status = 'pending';

-- 不要建 3 个单列索引 写入代价大且 PostgreSQL 难选最优
-- 建 2-3 个精心设计的复合 + partial 索引覆盖 80% 查询

复合索引的工程经验 leftmost prefix 规则必须懂 否则索引白建 列顺序选择性高的放前面 等值条件放前面 范围条件放后面 ORDER BY 字段放后面 用 covering index 避免回表 不要建 3 个单列索引 建 2-3 个精心设计的复合索引 这套组合能让查询性能提升 10 倍 同时减少写入开销。我们公司 customer_events 表原本 12 个索引 重构成 4 个复合索引 写入性能从 800 TPS 提到 4000 TPS 查询性能不降反升。

三 Partial Index 与 Expression Index:精准优化

Partial index 只索引满足某个 WHERE 条件的行 expression index 索引表达式结果 这两种高级索引能解决 B-tree 解决不了的场景。比如 status 只有 3 个值 全表 B-tree 索引没用 partial WHERE status=active 就有效 因为只索引活跃数据。

-- 1 Partial Index 部分索引
-- 场景 status 字段只有 active inactive pending 三个值
-- 全表 B-tree 索引 cardinality 太低 优化器不愿意用

-- 不好的方案
CREATE INDEX idx_users_status ON users (status);
-- 优化器看到 status='active' 估算 1/3 数据 不如全表扫

-- 好的方案 用 partial
CREATE INDEX idx_users_active
ON users (created_at, email)
WHERE status = 'active';
-- 只索引 active 用户 数据量大幅减少 而且包含常用字段

-- 查询用法
EXPLAIN ANALYZE
SELECT * FROM users
WHERE status = 'active' AND created_at > '2024-01-01'
ORDER BY email;
-- 优化器识别到 status='active' 匹配 partial index 直接用

-- 2 Partial Index 的进阶用法
-- 场景 soft delete 软删除 大部分查询不要已删除数据
CREATE INDEX idx_orders_active_user
ON orders (user_id, created_at)
WHERE deleted_at IS NULL;

-- 场景 异常订单监控
CREATE INDEX idx_orders_failed
ON orders (created_at, error_msg)
WHERE status = 'failed';

-- 场景 高价值客户索引
CREATE INDEX idx_users_vip
ON users (last_purchase_at, total_spent)
WHERE total_spent > 10000;

Partial index 的设计要点是 WHERE 条件要稳定 不要建一个 WHERE created_at > '2024-01-01' 这种 一年后就过期了。条件要选数据集中的特征 比如 active soft delete 异常状态 VIP 这些业务上长期稳定的属性。

-- 3 Expression Index 表达式索引
-- 场景 大小写不敏感登录
SELECT * FROM users WHERE lower(email) = 'admin@test.com';
-- 普通 INDEX(email) 用不上 因为查询是表达式

CREATE INDEX idx_users_email_lower ON users (lower(email));
-- 现在能用上 但 INSERT/UPDATE 时会计算表达式略慢

-- 4 Expression Index 的其他用法
-- 场景 日期截断查询
CREATE INDEX idx_orders_date
ON orders (date_trunc('day', created_at));

SELECT count(*) FROM orders
WHERE date_trunc('day', created_at) = '2024-01-01';

-- 场景 拼接字段查询
CREATE INDEX idx_users_fullname
ON users ((first_name || ' ' || last_name));

SELECT * FROM users
WHERE (first_name || ' ' || last_name) = '张 三';

-- 场景 hash 索引避免长字段
CREATE INDEX idx_articles_url_hash
ON articles USING HASH (md5(url));

SELECT * FROM articles WHERE md5(url) = md5('https://...');

-- 5 Partial + Expression 组合 极致优化
CREATE INDEX idx_users_active_email_lower
ON users (lower(email))
WHERE status = 'active' AND deleted_at IS NULL;

-- 这种索引非常小且精准
-- 查询 WHERE lower(email)=? AND status='active' AND deleted_at IS NULL 飞速

Partial 与 Expression Index 的工程经验 低基数字段如 status type 用 partial 不要用普通 B-tree 表达式查询如 lower() date_trunc() 必须用 expression index 否则索引白建 soft delete deleted_at IS NULL 用 partial 排除大量已删除数据 partial 条件要选业务长期稳定的属性 不要选时间这种会过期的 这套组合能让特定查询性能提升 100 倍 索引体积减少 80%。我们公司 users 表 1000 万行 200 万活跃 partial active 索引大小是全量 B-tree 的 20% 查询还更快。

四 索引维护与监控:发现废索引

建索引是工程的开始 不是结束。每个索引都有写入代价 必须监控哪些索引在被使用 哪些索引从来没被用过 删掉废索引能让 INSERT 提速 30%。PostgreSQL 提供 pg_stat_user_indexes 系统视图 但很多人不知道用。

-- 1 找出从未使用的索引
SELECT
    schemaname, relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indisunique = false
  AND indisprimary = false
ORDER BY pg_relation_size(indexrelid) DESC;
-- 这些索引从未被查询用到 占空间还拖慢写入 可以删除

-- 2 找出几乎不用的索引
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans,
    n_tup_ins + n_tup_upd + n_tup_del AS writes,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_stat_user_tables USING (relid)
WHERE idx_scan < 100
  AND n_tup_ins + n_tup_upd + n_tup_del > 10000
ORDER BY writes DESC;
-- 写入很多但读取很少的索引 性价比极低

-- 3 索引膨胀检测
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    round(100 * pg_relation_size(indexrelid)::numeric
          / pg_relation_size(relid), 2) AS index_to_table_ratio
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100*1024*1024
ORDER BY pg_relation_size(indexrelid) DESC;
-- 如果索引大于表 30% 考虑重建或调整

-- 4 重建膨胀的索引
REINDEX INDEX CONCURRENTLY idx_orders_user_status;
-- CONCURRENTLY 在线重建 不阻塞读写 但速度慢

-- 5 找出可能需要的新索引
-- pg_stat_statements 看慢查询
SELECT
    query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
  AND query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 20;

-- 然后对每个慢查询 EXPLAIN ANALYZE 看是否缺索引

监控索引使用率是日常运维 不是上线时做一次。我们公司每周跑一次脚本 列出所有 idx_scan=0 的索引 review 后删除 半年清理掉 40% 的废索引 写入性能提升 30%。

#!/bin/bash
# 每周索引健康检查脚本

PSQL='psql -h localhost -U postgres -d production'

echo '=== 完全未使用的索引 ==='
$PSQL -c "
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND indisunique = false
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;"

echo '=== 写多读少的索引 ==='
$PSQL -c "
SELECT relname AS table, indexrelname AS index,
       idx_scan, n_tup_ins + n_tup_upd + n_tup_del AS writes
FROM pg_stat_user_indexes
JOIN pg_stat_user_tables USING (relid)
WHERE idx_scan < 100 AND n_tup_ins + n_tup_upd + n_tup_del > 100000
ORDER BY writes DESC LIMIT 20;"

echo '=== 索引膨胀超过 30% ==='
$PSQL -c "
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       round(100*pg_relation_size(indexrelid)::numeric
             / pg_relation_size(relid),2) AS ratio
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100*1024*1024
  AND pg_relation_size(indexrelid)::numeric / pg_relation_size(relid) > 0.3
ORDER BY ratio DESC LIMIT 20;"

echo '=== Top 20 慢查询 待加索引 ==='
$PSQL -c "
SELECT substring(query, 1, 80) AS query,
       calls, round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE mean_exec_time > 100 AND query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC LIMIT 20;"

索引维护的工程经验 必须每周跑监控找未使用的索引 idx_scan=0 且非 unique/primary 的删掉 写入多读取少的索引性价比低考虑删除 索引膨胀超过表 30% 用 REINDEX CONCURRENTLY 重建 上 pg_stat_statements 找慢查询补索引 不要凭感觉加 这套监控流程能让数据库性能持续优化 而不是越用越慢。我们公司一次清理 删了 80 个废索引 写入 TPS 翻倍 磁盘空间省 200G。

[mermaid]flowchart TD
A[查询慢] --> B[EXPLAIN ANALYZE]
B --> C{Seq Scan?}
C -->|是| D[缺索引]
C -->|否| E{Index Scan 但慢?}
D --> F{字段类型?}
F -->|等值范围| G[B-tree]
F -->|LIKE %x%| H[GIN+pg_trgm]
F -->|JSONB 数组| I[GIN]
F -->|地理| J[GiST]
F -->|时序大表| K[BRIN]
E --> L{cardinality 低?}
L -->|是| M[partial index]
L -->|否| N{表达式查询?}
N -->|是| O[expression index]
N -->|否| P[复合索引列顺序]
G --> Q[CREATE INDEX]
H --> Q
I --> Q
J --> Q
K --> Q
M --> Q
O --> Q
P --> Q
Q --> R[周期监控 idx_scan]
R -->|0 次使用| S[删除废索引]

五 EXPLAIN ANALYZE 实战:看穿优化器

建完索引必须验证 EXPLAIN ANALYZE 是看穿优化器的窗口 不会读 EXPLAIN 的 DBA 就是瞎建索引。重点看 Seq Scan 还是 Index Scan rows 估算与实际差距 buffers 命中率 这些数字告诉你索引是否真的被用上 用得好不好。

-- 1 基础 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
--                          QUERY PLAN
-- Seq Scan on orders  (cost=0.00..15000.00 rows=100 width=200)
--   Filter: ((user_id = 1) AND (status = 'paid'))
-- Seq Scan 全表扫 没用上索引

-- 2 EXPLAIN ANALYZE 带实际执行
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
--                          QUERY PLAN
-- Index Scan using idx_orders_user_status on orders
--   (cost=0.42..8.45 rows=100 width=200)
--   (actual time=0.05..0.50 rows=98 loops=1)
--   Index Cond: ((user_id = 1) AND (status = 'paid'))
--   Buffers: shared hit=5
-- Planning Time: 0.15 ms
-- Execution Time: 0.65 ms
-- 看到 Index Scan rows 估算 100 实际 98 差距小 优化器准确
-- Buffers hit=5 全部命中缓存
-- 这是理想情况

-- 3 EXPLAIN 看出问题
EXPLAIN ANALYZE SELECT * FROM orders
WHERE created_at > '2024-01-01' ORDER BY total DESC LIMIT 10;
-- Limit  (actual time=2500..2500 rows=10 loops=1)
--   -> Sort  (actual time=2500..2500 rows=10000000 loops=1)
--         Sort Key: total DESC
--         Sort Method: external merge  Disk: 500MB
--         -> Seq Scan on orders  (actual time=0.05..1500 rows=10000000)
--               Filter: created_at > '2024-01-01'
-- Sort Method: external merge Disk 500MB 用磁盘排序极慢
-- 解决 加索引 (created_at, total DESC)
CREATE INDEX idx_orders_time_total ON orders (created_at, total DESC);

-- 4 EXPLAIN 看 rows 估算偏差
-- 如果 estimate rows = 100 但 actual rows = 1000000
-- 优化器统计数据过期 需要 ANALYZE
ANALYZE orders;  -- 更新统计信息

-- 设置自动 analyze 阈值
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.05,  -- 5% 行变化触发
    autovacuum_analyze_threshold = 1000
);

看 EXPLAIN 要养成几个习惯 第一关注 Seq Scan vs Index Scan vs Bitmap Index Scan 第二关注 rows 估算与实际差距大于 10 倍说明统计过期 第三关注 Sort Method 是否用磁盘 第四关注 Buffers 命中率 命中低说明数据冷或缓存小。

-- 5 EXPLAIN ANALYZE 的高级选项
EXPLAIN (
    ANALYZE,         -- 实际执行
    BUFFERS,         -- buffer 统计
    SETTINGS,        -- 显示影响计划的配置
    WAL,             -- WAL 量统计
    TIMING,          -- 时间统计 默认开
    SUMMARY,         -- 总结
    VERBOSE,         -- 详细
    FORMAT JSON      -- JSON 格式便于工具解析
) SELECT * FROM orders WHERE user_id = 1;

-- 6 让 EXPLAIN 不执行修改 SQL 但又能 ANALYZE
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status='shipped' WHERE id = 1;
ROLLBACK;  -- 撤销修改 但能看到执行计划

-- 7 比较两种索引的优劣
-- 删除一个索引看 EXPLAIN 变化
BEGIN;
DROP INDEX idx_orders_user_status;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
-- 看是否退化到 Seq Scan 时间差多少
ROLLBACK;  -- 恢复索引

-- 8 用 auto_explain 自动记录慢查询计划
-- postgresql.conf
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '500ms'
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on
-- 现在所有 > 500ms 的查询自动记录 EXPLAIN ANALYZE 到日志

EXPLAIN 分析的工程经验 必须养成建完索引就 EXPLAIN ANALYZE 的习惯 关注 Seq Scan vs Index Scan rows 估算偏差大于 10 倍要 ANALYZE 更新统计 Sort Method 用磁盘要建排序索引 用 auto_explain 自动记录慢查询计划 用 (BUFFERS, SETTINGS) 看更多细节 这套分析能让索引优化精准而不是凭感觉。我们公司 DBA review 慢查询都会带上 EXPLAIN ANALYZE 截图 没有这个的 ticket 直接打回。

六 索引的工程坑:那些教科书没写的

讲完原理来说几个真实生产里踩过的坑。第一个坑是 索引建在生产 NEVER 直接 CREATE INDEX 会锁表 写入全堵 必须 CREATE INDEX CONCURRENTLY 在线建 但要注意 CONCURRENTLY 失败会留废索引 要 DROP 后重建 我们一次没用 CONCURRENTLY 主从同步延迟 30 分钟 业务投诉。第二个坑是 索引列顺序的常见误区 把高基数字段放前面是对的 但忘了考虑查询模式 我们建 INDEX(email, created_at) 因为 email 唯一性高 但实际查询都是 WHERE created_at > ? ORDER BY email 这个索引完全用不上 调整顺序才生效。第三个坑是 unique 约束自带索引 不要再建重复索引 PRIMARY KEY 和 UNIQUE 已经自带 B-tree 一些 DBA 不知道又手动建一个浪费空间。第四个坑是 大表加索引必须用 CONCURRENTLY 但占用 2 倍时间且需要锁短暂 best practice 是 1 选维护窗口或低峰 2 用 pg_repack 这样的工具更优雅 3 大表建议提前预备好分区表 加索引就是给分区加省事。第五个坑是 索引失效的隐藏场景 字段类型不匹配 SELECT * FROM users WHERE user_id = '123' 但 user_id 是 int 字符串比较索引失效 还有 WHERE id + 1 = 100 表达式两边都不是字段 索引失效 这些细节坑得多。

关键概念速查

避坑清单

1. 索引类型选对 模糊搜索用 GIN+pg_trgm JSONB 用 GIN 不要默认 B-tree。
2. 复合索引 leftmost prefix 规则必须懂 列顺序错索引白建。
3. 等值条件放前 范围条件放后 ORDER BY 字段放索引尾部。
4. 低基数字段如 status 用 partial index 不要用普通 B-tree。
5. 表达式查询如 lower() date_trunc() 必须用 expression index。
6. 用 covering index INCLUDE 字段避免回表 提升 50%。
7. 生产建索引必用 CONCURRENTLY 否则锁表业务停。
8. 每周监控 idx_scan=0 的废索引删除 写入提速 30%。
9. 索引膨胀超过表 30% 用 REINDEX CONCURRENTLY 重建。
10. 建完必 EXPLAIN ANALYZE 验证 rows 估算偏差大要 ANALYZE 更新统计。

总结

PostgreSQL 索引设计这事 很多人的直觉是 哪个字段查得多就加上 加得越多越好 这其实是把 我会写 CREATE INDEX 和 我能为生产 1 亿行表设计索引 不拖慢写入还能让查询飞快 混为一谈。前者是会建索引 后者是懂索引工程。中间隔着的是 索引类型选择 复合索引列顺序 partial 与 expression 索引 维护与监控 EXPLAIN 分析 整整一套工程方法论。

从能跑到能扛 你需要做的事远不止 CREATE INDEX。你要懂 6 种索引类型怎么选 复合索引列顺序怎么排 partial index 何时用 expression index 怎么写 EXPLAIN 怎么读 pg_stat_user_indexes 怎么监控 CONCURRENTLY 怎么用。每一项单独看都不复杂 但它们组合在一起 才是一个能撑住 1 亿行表 1000 QPS 写入还查询飞快的数据库。少任何一项 都可能让你的索引白建 写入腰斩 查询还是 5 秒 用户骂娘。

我经常用一个比喻来理解索引 它有点像一本书的目录。B-tree 是按拼音排序的常规目录 适合查 张三 王五 这种精确名字 也适合查 张 开头的所有名字 GIN 是按内容拆分的关键词目录 适合查包含 数据库 三个字的所有章节 GiST 是按主题分类的目录 适合查 经济学相关章节 BRIN 是粗粒度的页范围目录 适合 1000 页大书 知道 数据库 内容在 200-300 页就行。复合索引是按 章 节 段落 多级排序的目录 必须按顺序查 不能跳级。partial index 是只给重点章节做目录 普通章节不做 节省空间。expression index 是给 章节标题转小写 这种特殊形式做目录。你的书 1000 页 没目录读者翻死 目录建得好读者翻 5 秒找到 目录建得不好读者翻 30 秒还找不到 目录建得太多读者翻目录都晕了。

这套体系最难的地方在于 它的复杂度在小数据量阶段几乎完全暴露不了。你 100 万行表上建 30 个 B-tree 索引 查询都几十毫秒 看起来一切都好。但真正生产 1 亿行表 1000 QPS 写入 各种刁钻 SQL 你才发现 99% 的复杂度都在 那 1% 的工程细节里 类型选错 顺序排错 cardinality 太低 索引膨胀 统计过期 EXPLAIN 看不懂。建议任何想做严肃数据库优化的 DBA 一定要建立每周索引健康检查的习惯 找未使用的废索引 找慢查询补索引 找膨胀重建 任何指标不健康都要处理 千万别只看上线 那只是索引设计的冰山一角 真正生产的复杂度藏在水下 90%。

—— 别看了 · 2026