PostgreSQL索引策略之从慢查询到毫秒响应的工程实践指南_MsSqlserver

一、索引是解决慢查询的手段，但不是没有代价的手段

给 postgresql 加索引，可能是数据库优化里最容易看到效果的操作——一条 create index 命令，就能让一个原本需要几秒钟的查询变成几毫秒。但索引的代价，在写入密集的场景里会成倍放大。每一次 insert、update、delete，数据库不仅要修改数据，还要更新所有相关索引。索引越多，写入越慢；索引设计不合理，查询也未必变快，甚至可能变慢。

一个工程上负责任的索引策略，必须在「查询性能」和「写入性能」之间做权衡，而这个权衡没有通用答案，只有针对具体业务的判断。电商平台的订单表，查询密集但写入也密集，索引设计需要非常克制；日志系统的日志表，写入密集但查询相对简单，索引可以更少；数据分析系统的宽表，查询复杂但数据不要求实时写入，索引可以更激进。

但无论什么业务，索引策略的第一步永远是「用数据说话」：哪一两条查询最慢？它们访问了哪些表和哪些列？过滤条件是什么？排序条件是什么？如果没有 pg_stat_statements 的数据，任何索引建议都是猜的。

二、索引类型选择：b-tree 不是唯一答案，但通常是第一个答案

flowchart td
    a[查询性能问题] --> b{查询模式?}
    b -- 精确匹配/范围查询 --> c[b-tree 索引]
    b -- 全文搜索 --> d[gin 索引 + tsvector]
    b -- 地理空间查询 --> e[gist / sp-gist 索引]
    b -- 数组包含关系 --> f[gin 索引]
    b -- 模糊前缀匹配 --> g[gist 索引 + pg_trgm]
    b -- 布隆过滤 --> h[bloom 索引]
    c --> i[适用大部分场景]
    d --> j[适用搜索场景]
    e --> k[适用 postgis]
    f --> l[适用标签/分类]
    g --> m[适用模糊搜索]

b-tree 索引是 postgresql 的默认索引类型，适用于等值查询、范围查询、order by 和 group by。如果你不确定该用什么索引，先从 b-tree 开始。但 b-tree 不是万能的：like '%keyword%' 这种前后模糊的查询，b-tree 索引无法加速；数组字段的「包含」查询，b-tree 也无法加速；全文搜索更不能用 b-tree。

gin（generalized inverted index）索引是 postgresql 里第二常用的索引类型，特别适合多值类型（jsonb、数组、tsvector）。比如一个文章表有 tags 数组字段，查询「包含所有这些标签的文章」，gin 索引可以高效支持。但 gin 索引的写入成本比 b-tree 高，更新操作会导致索引的大量随机 i/o。

对于 jsonb 字段，postgresql 支持两种索引：gin 索引可以加速 jsonb 的「包含」查询（@>、?、?&），而 b-tree 索引只能加速 jsonb 字段的整体比较。如果你的查询是「找到所有 meta 字段里包含 {"premium": true} 的记录」，gin 索引是正确的选择。

三、复合索引设计：列顺序决定索引能否被使用

复合索引（多列索引）的列顺序，是索引设计里最容易被忽视、但影响最大的细节。postgresql 的 b-tree 索引可以支持从左到右的前缀查询，但不能跳过前面的列。比如一个 (user_id, created_at) 的复合索引，可以加速 where user_id = 1 的查询，也可以加速 where user_id = 1 and created_at > '2024-01-01' 的查询，但不能加速 where created_at > '2024-01-01' 的查询——因为 created_at 不是索引的最左前缀。

-- 假设有索引：create index idx_orders_user_created on orders(user_id, created_at);
-- 能用到索引
select * from orders where user_id = 1 order by created_at desc;
-- 能用到索引（user_id 是前缀）
select * from orders where user_id = 1 and created_at > '2024-01-01';
-- 用不到索引（跳过了 user_id）
select * from orders where created_at > '2024-01-01';
-- 能用到索引，但效率较低（索引扫描后过滤）
select * from orders where user_id in (1, 2, 3) and created_at > '2024-01-01';

列顺序的选择原则：= 条件的列放前面，范围 条件的列放后面。因为范围条件（>、<、between、like 'prefix%'）会终止索引的连续使用，范围条件后面的列就无法利用索引了。所以如果 user_id 是等值查询、created_at 是范围查询，(user_id, created_at) 是正确的顺序；反过来就错了。

另一个要考虑的因素是「选择性」：选择性高的列（唯一值多的列）放前面，可以让索引在查询早期过滤掉更多行。但这条规则和「= 条件放前面」的规则有时会冲突，需要具体分析查询模式。

四、生产环境索引管理：创建、监控与清理

在生产环境的数据库上创建索引，最危险的操作是「阻塞写入」。postgresql 的 create index 会锁表，阻止写入，直到索引创建完成。对于大表，这可能意味着几分钟甚至几小时的写入不可用。生产环境中必须使用 create index concurrently（并发创建索引），它不会阻塞写入，但创建时间更长，而且有可能失败（失败后留下一个无效的索引，需要手动清理）。

索引创建后，需要持续监控两个指标：索引是否被使用，以及索引的维护成本。pg_stat_user_indexes 视图提供了每个索引的扫描次数和使用情况。如果一个索引从来没有被扫描过，它就在白白消耗写入性能和存储空间。

-- 找出从未被使用的索引
select
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
from pg_stat_user_indexes
join pg_index on pg_stat_user_indexes.indexrelid = pg_index.indexrelid
where idx_scan = 0
  and not indisprimary
  and not indisunique;

但需要注意的是，idx_scan = 0 不一定意味着索引没用——可能是这个索引是为不常执行的查询准备的，或者是为灾难恢复场景准备的。删除索引前，最好先记录下它的定义，并在低流量时段观察一段时间。

另一个生产环境中容易被忽略的问题是「索引膨胀」。postgresql 的 mvcc 机制会导致索引产生死页，随着时间推移，索引文件会变得比实际需要的更大，查询性能也会下降。reindex 或 reindex concurrently 可以重建索引，回收空间并提升性能。对于大表，可以考虑使用 pg_repack 工具，它可以在不持锁的情况下重建表和索引。