PHP小码农

Appearance

索引设计最佳实践 ​

前面几节分别讲了索引的类型、特殊形式与维护手段。真正落到工程上，问题会聚成一句话：面对一张表、一堆查询，到底该建哪些索引？

索引设计没有「标准答案」，它是在三种成本之间做权衡：查询变快的收益、写入变慢的代价、磁盘与缓存的占用。多建一个索引可能让某条查询快 100 倍，也可能让批量插入慢 30%——两者都真实存在。

本节把这些权衡整理成一套可操作的规则：从读懂查询模式起步，到列顺序、索引类型、数量控制、命名、上线流程与生命周期复审，最后给出常见反模式清单。

前置知识

阅读本节前，建议先了解：CREATE INDEX 基础、多列索引、索引使用监控、索引膨胀检测

基础概念 ​

索引的三重成本 ​

评估任何索引，都要同时问三个问题：

成本具体表现衡量方式
查询收益目标查询是否真的走该索引、耗时下降多少EXPLAIN (ANALYZE, BUFFERS)、pg_stat_user_indexes.idx_scan
写入代价每次 INSERT / UPDATE / DELETE 都要同步维护索引写入 TPS 对比、pg_stat_user_tables
空间与缓存索引占用磁盘，并与表争抢 shared_bufferspg_relation_size()、pg_statio_user_indexes
text
一条 INSERT 的成本（表上有 5 个索引）：

  写入堆表 1 次
  + 更新 5 个索引（每个索引至少 1 次页修改）
  + 可能的页分裂
  = 写放大 ≈ 6 倍

索引不是免费的。每多一个，写入路径就多一份开销。

索引设计与查询模式的关系 ​

索引的服务对象是查询，不是列。这决定了设计顺序：

text
错误顺序：先看表结构 → 觉得某列可能被查 → 建索引

正确顺序：
  ① 采集真实查询（pg_stat_statements 的 top SQL）
  ② 找出慢查询与高频查询
  ③ 分析它们的 WHERE / JOIN / ORDER BY / 聚合维度
  ④ 针对这些模式设计索引
  ⑤ 上线后用 EXPLAIN 与 idx_scan 验证
sql
-- 第①步：找出最值得优化的查询（需先启用 pg_stat_statements）
SELECT calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms,
       rows,
       left(query, 60) AS query_head
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
text
 calls  | total_ms | mean_ms |   rows   |                 query_head
--------+----------+---------+----------+--------------------------------------------
 184203 |  4821900 |   26.18 |        1 | SELECT id, amount FROM orders WHERE user_id = $1
   1204 |  1802400 | 1497.01 |  1000000 | SELECT ... FROM events WHERE status = $1

索引设计的六条主线 ​

整套实践可以收敛成六条主线，后面逐一展开：

text
① 工作负载驱动   —— 从真实查询出发，不从列出发
② 列顺序        —— 等值在前、范围在后，最左前缀要能用上
③ 数量克制      —— 索引越多，写入越慢；每张表控制在 5～6 个内
④ 类型匹配      —— B-Tree 是默认，特殊数据用 GIN / GiST / BRIN
⑤ 让索引更小    —— 部分索引、表达式索引、覆盖索引按需选用
⑥ 生命周期管理  —— 上线走 CONCURRENTLY，定期复审与清理

语法与用法 ​

设计索引最终都落回 CREATE INDEX。常用形态速查：

text
-- 单列 / 多列
CREATE INDEX 名 ON 表 (列1 [, 列2, ...]);

-- 唯一索引
CREATE UNIQUE INDEX 名 ON 表 (列1 [, 列2]);

-- 指定排序方向与 NULL 位置（仅 B-Tree）
CREATE INDEX 名 ON 表 (列1 DESC NULLS LAST, 列2 ASC);

-- 部分索引：只索引满足谓词的行
CREATE INDEX 名 ON 表 (列1) WHERE 谓词;

-- 表达式索引：索引计算结果
CREATE INDEX 名 ON 表 (lower(列1));

-- 覆盖索引：非键列随行携带
CREATE INDEX 名 ON 表 (列1) INCLUDE (列2, 列3);

-- 指定索引方法
CREATE INDEX 名 ON 表 USING gin (列1);

-- 指定存储参数
CREATE INDEX 名 ON 表 (列1) WITH (fillfactor = 90);

-- 在线创建（不阻塞读写）
CREATE INDEX CONCURRENTLY 名 ON 表 (列1);

一个综合示例（表结构见「实战示例」）：

sql
-- 多租户 + 用户维度的等值查询，附带常用返回列
CREATE INDEX idx_orders_tenant_user_cover
  ON orders (tenant_id, user_id)
  INCLUDE (status, total, created_at);

-- 只索引未删除的行（软删除场景的经典用法）
CREATE INDEX idx_orders_active_created
  ON orders (tenant_id, created_at DESC)
  WHERE deleted_at IS NULL;
text
CREATE INDEX
CREATE INDEX

详细说明 ​

从查询模式反推索引：四类子句 ​

一条查询里，只有四类子句能「用上」索引：

子句能否用索引关键要求
WHERE 等值条件能列顺序靠前，选择性好
WHERE 范围条件（> < BETWEEN）能放在等值列之后，后续列不再可用于过滤
JOIN ... ON能连接列应有索引（尤其被驱动侧）
ORDER BY / GROUP BY能，但有条件排序方向与列顺序要和索引一致
sql
-- 典型查询：租户 + 用户 + 最近的订单
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE tenant_id = 100 AND user_id = 42
ORDER BY created_at DESC
LIMIT 20;
text
 Limit  (actual time=0.041..0.188 rows=20 loops=1)
   ->  Index Only Scan using idx_orders_tenant_user_cover on orders
         Index Cond: ((tenant_id = 100) AND (user_id = 42))
         Heap Fetches: 0
         Buffers: shared hit=6
text
逐个子句对应：
  WHERE tenant_id = 100        → 索引第 1 列（等值）
  WHERE user_id = 42           → 索引第 2 列（等值）
  ORDER BY created_at DESC     → 由 INCLUDE 列无法排序 → 见下方提示
  SELECT id, total, created_at → id 是主键（天然在索引里）；total/created_at 在 INCLUDE

INCLUDE 列不能用于排序

上例中 created_at 是 INCLUDE 列，不参与排序。若排序是大结果集上的关键瓶颈，应把排序列放进键列（如 (tenant_id, user_id, created_at DESC)）而不是 INCLUDE。

设计动作可以形式化为：

text
① 把 WHERE 里的等值列排在最前，按选择性从高到低
② 紧跟范围条件列（它之后无法再用于索引过滤）
③ 若有 ORDER BY，且需索引直接提供顺序，把排序列接在后面
④ 高频返回的小列放进 INCLUDE
⑤ 若只需服务部分行，加 WHERE 做成部分索引

列顺序：最左前缀与等值优先 ​

多列索引的「最左前缀」规则决定了它能服务哪些查询：

text
索引 (a, b, c) 能服务：
  WHERE a = ?                        ✅
  WHERE a = ? AND b = ?              ✅
  WHERE a = ? AND b = ? AND c = ?    ✅
  WHERE a = ? AND b > ?              ✅（a 等值 + b 范围）
  WHERE b = ?                        ❌（跳过最左列 a）
  WHERE a = ? AND c = ?              ⚠️（c 无法用于过滤，只能靠 a）
sql
-- 三个索引候选，看哪个能覆盖更多查询
CREATE INDEX idx_a    ON orders (tenant_id);
CREATE INDEX idx_ab   ON orders (tenant_id, user_id);
CREATE INDEX idx_ac   ON orders (tenant_id, created_at);

-- 查询 1：WHERE tenant_id = 1 AND user_id = 2   → idx_ab 最佳
-- 查询 2：WHERE tenant_id = 1                   → 三者皆可，idx_a 最小
-- 查询 3：WHERE user_id = 2                     → 都不行，需要 (user_id) 索引

等值列应该排在范围列之前，否则范围列后面的列无法参与索引过滤：

sql
-- 好：等值在前，范围在后
CREATE INDEX idx_good ON orders (tenant_id, created_at);

-- 差：范围列在前，created_at 之后的条件就用不上索引了
CREATE INDEX idx_bad  ON orders (created_at, tenant_id);
text
(a, b) 索引上的查询能力：

  a = 1 AND b = 2     → 两列都用于定位，效率最高
  a = 1 AND b > 2     → a 定位，b 走范围扫描
  a > 1 AND b = 2     → 只有 a 走索引，b 需回表过滤

索引列的「选择性」怎么衡量

选择性 = 不同值个数 / 总行数，接近 1 说明选择性好（如唯一 id），接近 0 说明差（如布尔值）。用 pg_stats 的 n_distinct 查看，选择性高的列更适合放在索引前列。

sql
SELECT attname,
       n_distinct,
       correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('tenant_id', 'user_id', 'status');
text
  attname  | n_distinct | correlation
-----------+------------+-------------
 tenant_id |         50 |       0.002
 user_id   |      19983 |       0.001
 status    |          4 |       0.003

status 只有 4 个不同值，单独为它建普通 B-Tree 索引收益极低，应改用部分索引。

索引数量：写放大与收益的平衡 ​

每张表的索引数量应受控。经验值：

表类型建议索引数说明
只读/报表表不限（按查询建）写入少，可放开
常规 OLTP 表3 ～ 6主键 + 外键 + 少量业务索引
高写吞吐表2 ～ 4每多一个索引都是写入负担
日志/时序表1 ～ 3优先考虑 BRIN、分区、部分索引
sql
-- 统计每张表的索引数量，找出「索引过多」的表
SELECT c.relname                        AS table_name,
       count(i.indexrelid)              AS index_count,
       pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS index_size
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
GROUP BY c.relname
HAVING count(i.indexrelid) > 6
ORDER BY count(i.indexrelid) DESC;
text
 table_name | index_count | index_size
------------+-------------+------------
 orders     |           9 | 412 MB

设计时的三个务实原则：

text
① 新索引必须有明确的「服务对象」——能说出它服务于哪条/哪类查询
② 能合并就合并：单列 (a) 与 (a, b) 若查询都从 a 起，前者往往是冗余的
③ 重复前缀要警惕：(a) 与 (a, b) 同时存在时，(a) 常可删除
sql
-- 找出「前缀重复」的候选：判断某索引的列序列是否是另一索引的前缀
SELECT a.indexrelid::regclass AS candidate_redundant,
       b.indexrelid::regclass AS covers_it
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
WHERE a.indkey::int2[] @> ARRAY[b.indkey[0]]::int2[]
  AND (b.indnkeyatts > 1)
ORDER BY 1;
text
 candidate_redundant | covers_it
---------------------+------------
 idx_orders_tenant   | idx_orders_tenant_user_cover

索引类型怎么选 ​

B-Tree 是「默认答案」，但特殊数据形态有更合适的方法：

场景首选方法理由
等值、范围、排序、唯一性B-Tree通用性最强
数组、jsonb 包含、全文检索GIN多值列的高效倒排
几何、范围类型、最近邻（KNN）GiST支持距离排序
极度稀疏的几何/文本前缀SP-GiST空间划分
超大规模、范围过滤、物理有序BRIN体积极小，适合追加型数据
纯等值、键很长Hash无排序能力但体积小
sql
-- 同一列的三种索引方案，按数据特征选择
CREATE INDEX idx_tags_gin   ON articles USING gin (tags);        -- text[] 数组
CREATE INDEX idx_geom_gist  ON places  USING gist (location);    -- 几何
CREATE INDEX idx_ts_brin    ON events  USING brin (created_at);  -- 时序，体积小
text
体积对比（同一份 created_at 数据，1 亿行）：
  B-Tree → ~2.1 GB     BRIN → ~200 KB     ← 相差上万倍

选择要点：
  查询里有 @> / && / @@ / ~ 等操作符，且列为多值类型 → 考虑 GIN
  查询里有 ORDER BY 距离 <->                        → 考虑 GiST
  表极大、按时间写入、查询按时间范围过滤             → 考虑 BRIN
  不确定？先用 B-Tree，用 EXPLAIN 验证后再换

BRIN 只适合物理有序的数据

BRIN 的有效性依赖「列值与物理存储位置强相关」。如果数据是随机写入的，BRIN 的块范围摘要会退化成「每块都包含全部值」，完全失去过滤能力。用 pg_stats.correlation 检查：接近 ±1 才适合 BRIN。

让索引更小：三种「瘦身」手段 ​

索引越小，写入越快、缓存命中率越高。在保证功能的前提下，优先选择更小的形态。

手段一：部分索引（Partial Index）。 只索引经常被查询的那部分行。

sql
-- 订单表 95% 是历史数据，应用只查未完成订单
CREATE INDEX idx_orders_pending
  ON orders (tenant_id, created_at)
  WHERE status = 'pending';
text
全量索引：3000 万行 → 1.2 GB
部分索引：  30 万行 →  12 MB      ← 小 100 倍

手段二：表达式索引（Expression Index）。 索引计算结果，避免函数调用阻断索引。

sql
-- 查询 WHERE lower(email) = ? 时，普通 email 索引用不上
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 查询 WHERE amount * 1.1 > 100 时，索引表达式
CREATE INDEX idx_orders_total_with_tax ON orders ((total * 1.1));

手段三：覆盖索引（INCLUDE）。 消除回表，把 Index Scan 提升为 Index Only Scan。

sql
CREATE INDEX idx_orders_user_cover
  ON orders (tenant_id, user_id) INCLUDE (total, created_at);

三者可以叠加，但要注意叠加代价：

text
部分索引 + 表达式索引 + 覆盖索引 = 索引很小、查询很快
但每个索引项要精确匹配查询写法，应用侧的 SQL 必须稳定

⚠️ 表达式索引对查询写法敏感：
   WHERE lower(email) = 'a@b.com'   ✅ 命中
   WHERE email = 'A@B.COM'          ❌ 可能不命中（取决于优化器的常量折叠）

外键与连接列必须有索引 ​

这是最容易被漏掉、后果又最严重的一类。PostgreSQL 不会自动为外键列建索引，而外键约束在父表删除/更新时需要在子表上查找引用行：

sql
CREATE TABLE order_items (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku      text   NOT NULL,
  qty      int    NOT NULL
);

-- 必须手工建：否则删除父行时会对 order_items 做全表扫描
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
text
没有外键列索引时，DELETE FROM orders WHERE id = 1 的代价：
  父表删除 1 行
  + 子表 order_items 全表扫描（找 order_id = 1 的引用行）
  = 子表越大越慢，大表上可能演变成「删除一条订单卡死数据库」
sql
-- 检查外键列是否都建了索引
SELECT conrelid::regclass  AS child_table,
       conname             AS fk_constraint,
       a.attname           AS fk_column,
       CASE WHEN idx.indexrelid IS NULL THEN 'MISSING' ELSE 'ok' END AS index_status
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
JOIN LATERAL unnest(c.conkey) AS k(attnum) ON true
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.attnum
LEFT JOIN pg_index idx
       ON idx.indrelid = c.conrelid
      AND idx.indkey[0] = k.attnum
WHERE c.contype = 'f' AND n.nspname = 'public'
ORDER BY index_status, 1;
text
  child_table  |   fk_constraint    | fk_column | index_status
---------------+--------------------+-----------+--------------
 order_items   | order_items_order_id_fkey | order_id | ok

命名规范：让索引可维护 ​

索引名混乱会让巡检、删除、重建全部变困难。一套稳定可用的规范：

text
{表名}_{列名缩写}[_{后缀}]

前缀：
  idx_     普通索引
  uq_      唯一索引
  pk_      主键索引（一般由约束生成，无需手工命名）
  fk_      外键索引
  gin_ / gist_ / brin_   特殊方法（可选）

后缀：
  _cover   覆盖索引（含 INCLUDE）
  _partial 部分索引
  _expr    表达式索引
sql
-- 推荐命名
CREATE INDEX idx_orders_tenant_user_cover ON orders (tenant_id, user_id) INCLUDE (total);
CREATE UNIQUE INDEX uq_users_email_lower ON users (lower(email));
CREATE INDEX idx_orders_pending_partial ON orders (tenant_id, created_at) WHERE status = 'pending';

-- 不推荐：含义不明的名字
CREATE INDEX index1 ON orders (tenant_id);
CREATE INDEX tmp_idx ON users (email);

索引名长度上限 63 字符

标识符超过 63 个字符会被静默截断，可能导致「以为不同名的两个索引实际重名」的困惑。长表名 + 多列的场景要主动缩写。

上线流程：CONCURRENTLY 与 ANALYZE ​

索引从「设计」走到「生效」，有几个必须遵守的步骤。

第一步：用 CONCURRENTLY 创建，避免阻塞写入。

sql
-- 普通 CREATE INDEX 会对表加 SHARE 锁，阻塞写入直到建完
-- CREATE INDEX CONCURRENTLY 只加 SHARE UPDATE EXCLUSIVE，不阻塞读写（代价是更慢）
CREATE INDEX CONCURRENTLY idx_orders_tenant_user_cover
  ON orders (tenant_id, user_id) INCLUDE (total, created_at);

第二步：用真实查询验证它真的有效，然后更新统计信息。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE tenant_id = 100 AND user_id = 42;

ANALYZE orders;

第三步：上线后一段时间检查使用情况。

sql
SELECT indexrelname, idx_scan, last_idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_tenant_user_cover';
text
         indexrelname           | idx_scan |        last_idx_scan
--------------------------------+----------+----------------------------
 idx_orders_tenant_user_cover   |    18204 | 2026-09-28 10:12:04.881+08
text
完整上线流程：

  设计 → CREATE INDEX CONCURRENTLY → EXPLAIN 验证
       → ANALYZE → 观察 idx_scan → 保留或清理
       失败会留下 INVALID 索引，用 pg_index.indisvalid 找出后 DROP 或重建

索引的生命周期管理 ​

索引不是「建好就永久有效」的资产——查询模式会变，业务会重构，昨天必需的索引今天可能完全没人用。

text
        设计           上线            使用期           复审           处置
  ────────────→  ────────────→  ────────────→  ────────────→  ────────────→
  按查询设计      CONCURRENTLY    idx_scan 增长   定期采样       保留 / 重建
  EXPLAIN 验证    ANALYZE        监控体积        对比窗口        / 删除

复审的固定动作：

sql
-- 动作一：找出零使用的大索引
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024
ORDER BY pg_relation_size(indexrelid) DESC;
text
 schemaname | relname |     indexrelname     | idx_scan |  size
------------+---------+----------------------+----------+--------
 public     | logs    | idx_logs_created_at  |        0 |  96 MB

动作二：找出前缀冗余的索引（查询见「索引数量」一节）。 动作三：检查外键列索引是否齐全（查询见「外键与连接列必须有索引」一节）。

text
复审节奏建议：

  每周：新增索引的 idx_scan 检查（确认上线后确实被使用）
  每月：零使用索引与大索引体积巡检
  每季度：结合业务变化做一次全面复审与清理

常见反模式清单 ​

以下是最容易踩的设计误区，逐条给出症状与正解：

反模式症状正解
为每列各建一个单列索引索引多、写慢，多列查询仍需回表建组合索引，删除冗余单列索引
在低基数列建 B-Treeidx_tup_read/idx_scan 极高，几乎全表扫改部分索引或直接不建
高写表上堆索引写入 TPS 低，pg_stat_user_tables 写入耗时长数量控制在 2～4 个
漏建外键列索引父表删除极慢外键列必须建索引
给从不被查的列建索引idx_scan = 0删除
盲目 fillfactor = 100写热点页反复分裂、膨胀快写密集表设 75～85
用 CREATE INDEX 上大表长时间阻塞写入一律用 CONCURRENTLY
忽视表达式索引的写法匹配索引建了但查询不命中保证 SQL 写法与索引表达式一致
从不复审索引冗余索引逐年累积建立周期性复审机制
只建 B-Tree 处理所有类型数组/JSON 查询走不上索引按数据类型选索引方法
sql
-- 反模式示例：为每列各建索引
CREATE INDEX idx_o_a ON orders (tenant_id);
CREATE INDEX idx_o_b ON orders (user_id);
CREATE INDEX idx_o_c ON orders (created_at);
-- 查询 WHERE tenant_id = 1 AND user_id = 2 只能用一个索引，另一个靠回表过滤

-- 正解：组合索引，一箭双雕
DROP INDEX idx_o_a;
CREATE INDEX idx_o_ab ON orders (tenant_id, user_id);

实战示例 ​

下面用一张真实感较强的电商订单表，走完「采集查询 → 设计索引 → 验证 → 精简」的完整流程。

步骤一：确定查询模式 ​

从应用代码与慢查询日志中归纳出的四条核心查询：

text
Q1: 用户查看自己的订单列表
    SELECT id, total, status, created_at FROM orders
    WHERE tenant_id = $1 AND user_id = $2
    ORDER BY created_at DESC LIMIT 20;

Q2: 运营统计各状态订单数
    SELECT status, count(*) FROM orders
    WHERE tenant_id = $1 AND created_at >= $2
    GROUP BY status;

Q3: 客服按订单号精确查询
    SELECT * FROM orders WHERE id = $1;

Q4: 后台任务扫描待处理订单
    SELECT id FROM orders
    WHERE status = 'pending' AND created_at < $1;

步骤二：造表与数据 ​

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint        NOT NULL,
  user_id     bigint        NOT NULL,
  status      text          NOT NULL,
  total       numeric(12,2) NOT NULL,
  created_at  timestamptz   NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);

INSERT INTO orders (tenant_id, user_id, status, total, created_at)
SELECT (random() * 200)::bigint,
       (random() * 100000)::bigint,
       (ARRAY['pending','paid','shipped','done','refunded'])[1 + (random() * 4)::int],
       random() * 2000,
       now() - (random() * interval '365 days')
FROM generate_series(1, 2000000);

ANALYZE orders;

步骤三：按查询设计索引 ​

sql
-- Q1：等值 × 2 + 排序 + 覆盖
-- created_at 需参与 ORDER BY，因此放进键列（不能用 INCLUDE）
CREATE INDEX idx_orders_tenant_user_created
  ON orders (tenant_id, user_id, created_at DESC)
  INCLUDE (total, status);

-- Q2：租户 + 时间范围过滤，返回行数多，用部分索引缩小体积
CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at)
  WHERE status <> 'done';

-- Q3：主键已服务（orders_pkey 自动存在）

-- Q4：低基数 status + 时间范围的「任务队列」模式 → 部分索引
CREATE INDEX idx_orders_pending_created
  ON orders (created_at)
  WHERE status = 'pending';
text
CREATE INDEX
CREATE INDEX
CREATE INDEX

步骤四：验证每条查询都命中目标索引 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, status, created_at FROM orders
WHERE tenant_id = 100 AND user_id = 42
ORDER BY created_at DESC LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE status = 'pending' AND created_at < now() - interval '1 day';
text
 Limit  (actual time=0.038..0.201 rows=20 loops=1)
   Buffers: shared hit=9
   ->  Index Only Scan using idx_orders_tenant_user_created on orders
         Index Cond: ((tenant_id = 100) AND (user_id = 42))
         Heap Fetches: 0

 Index Only Scan using idx_orders_pending_created on orders
   Index Cond: (created_at < (now() - '1 day'::interval))
   Heap Fetches: 0
text
验证清单：
  Q1 → Index Only Scan + Heap Fetches: 0         ✅
  Q2 → Index Only Scan + 覆盖 status             ✅
  Q3 → orders_pkey（Index Scan）                 ✅
  Q4 → 部分索引，只扫 pending 行                  ✅

步骤五：精简冗余索引 ​

sql
-- 检查索引数量与体积（假设有人后来又随手加了一个单列索引 idx_orders_tenant）
SELECT indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 检查前缀冗余：idx_orders_tenant 已被 idx_orders_tenant_user_created 覆盖
SELECT a.indexrelid::regclass AS redundant_index,
       b.indexrelid::regclass AS covering_index
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
 AND b.indnkeyatts > 1
 AND a.indkey::int2[] @> ARRAY[b.indkey[0]]::int2[]
WHERE a.indnkeyatts = 1;

-- 确认无使用后删除
DROP INDEX CONCURRENTLY idx_orders_tenant;
text
          indexrelname              | idx_scan |  size
------------------------------------+----------+--------
 orders_pkey                        |     1204 | 156 MB
 idx_orders_tenant_user_created     |    18403 |  64 MB
 idx_orders_tenant_created_partial  |     2044 |  38 MB
 idx_orders_pending_created         |      102 | 1.2 MB

   redundant_index    |       covering_index
----------------------+----------------------------
 idx_orders_tenant    | idx_orders_tenant_user_created

最终方案（3 个业务索引 + 主键）：

text
orders
  ├── orders_pkey                      主键，服务 Q3
  ├── idx_orders_tenant_user_created   等值×2 + 排序 + 覆盖，服务 Q1
  ├── idx_orders_tenant_created_partial 部分索引，服务 Q2
  └── idx_orders_pending_created        部分索引，服务 Q4（仅 1.2 MB）

2 亿行数据、四条查询、四个索引搞定——没有一个是多余的，这就是设计到位的样子。

注意事项 ​

  • 不要从表结构出发设计索引。 索引服务于查询模式，没有查询依据的索引大概率会变成 idx_scan = 0 的负担。

  • 不要为低基数列单独建 B-Tree 索引。 选择性差的列（布尔、状态枚举）应改用部分索引。

    sql
    -- 不推荐：status 只有 5 个取值
CREATE INDEX idx_orders_status ON orders (status);

-- 推荐：只索引真正需要的小集合
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

  • 别漏掉外键列索引。 PostgreSQL 不会自动创建，漏了会让父表删除变成全表扫描。

  • INCLUDE 列不能用于排序或过滤。 排序列、过滤列必须放进键列。

    sql
    -- 错误认知：以为 created_at 能参与 ORDER BY
CREATE INDEX idx_bad ON orders (tenant_id) INCLUDE (created_at);

  • 表达式索引对查询写法很敏感。 应用侧 SQL 一旦变化，索引可能瞬间失效（lower(email) 索引对 email = 'X@Y.COM' 可能不命中）。

  • BRIN 只对物理有序的列有效。 随机写入的数据上，BRIN 会退化为无用结构，建之前先查 pg_stats.correlation。

  • 大表建索引必须用 CONCURRENTLY。 否则 SHARE 锁会阻塞写入，几分钟的构建可能造成业务中断。

  • 索引数量不是越多越好，也别忽略 fillfactor。 高写吞吐表上每个索引都在为写入加税；默认 90% 对写热点常常过于激进，会加速膨胀。

  • 索引一旦建立就要纳入复审。 查询模式随业务变化，昨天的必需可能是今天的累赘。

  • 不要在事务里创建 CONCURRENTLY 索引。 会直接报错。

    sql
    -- 错误！
BEGIN;
CREATE INDEX CONCURRENTLY idx_x ON orders (tenant_id);
COMMIT;

  • 上线新索引后必须观察。 没有 idx_scan 增长就意味着设计假设不成立，要回头重新分析查询。

最佳实践 ​

  • 建立「先有查询，后有索引」的纪律。 每个索引都要能说出它服务哪条查询；说不出来的就不要建。

    sql
    -- 记录设计依据：索引 → 查询 → 预期收益
COMMENT ON INDEX idx_orders_tenant_user_created IS
  'serves: orders list by user, ORDER BY created_at DESC';

  • 等值列在前，范围列在后，排序维度接在最后（若需索引提供顺序）。 用键列承载排序与过滤语义，只把载荷放进 INCLUDE。

    sql
    CREATE INDEX idx_orders_tenant_user_created
  ON orders (tenant_id, user_id, created_at DESC) INCLUDE (total, status);

  • 优先用部分索引缩小体积。 只服务热数据，索引可以小一两个数量级（用法见「让索引更小」一节）。

  • 外键列一律建索引，并定期巡检补齐。

    sql
    SELECT c.conname, c.conrelid::regclass AS child_table
FROM pg_constraint c
WHERE c.contype = 'f' AND NOT EXISTS (
  SELECT 1 FROM pg_index i
  WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
);

  • 按数据类型选索引方法，不要全是 B-Tree。 数组/JSON 用 GIN，几何/KNN 用 GiST，时序用 BRIN。

    sql
    CREATE INDEX idx_articles_tags_gin ON articles USING gin (tags);
CREATE INDEX idx_events_created_brin ON events USING brin (created_at);

  • 高写表严格限制索引数量（2～4 个）。 每个额外索引都要用写入性能来换。

    sql
    SELECT c.relname, count(*) AS idx_count
FROM pg_class c JOIN pg_index i ON i.indrelid = c.oid
WHERE c.relkind = 'r'
GROUP BY c.relname HAVING count(*) > 6;

  • 统一命名规范。 idx_ / uq_ / fk_ 前缀 + 表名列名 + 场景后缀，让巡检脚本可规模化（如 idx_orders_tenant_user_cover）。

  • 建索引一律 CONCURRENTLY，建完 ANALYZE；写密集索引同时设好 fillfactor。 这是生产环境的硬性流程。

    sql
    CREATE INDEX CONCURRENTLY idx_hot_orders ON orders (tenant_id) WITH (fillfactor = 80);
ANALYZE orders;

  • 建立周期性索引复审机制。 每周看新增索引的 idx_scan，每月清理零使用与冗余索引。

    sql
    SELECT indexrelname, idx_scan, last_idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

  • 删索引也要 CONCURRENTLY，并在低峰执行。 删除前确认不是约束底层索引。

    sql
    DROP INDEX CONCURRENTLY idx_orders_tenant;

  • 把索引总体积纳入容量规划。 索引与表争抢 shared_buffers，体积失控会拖慢所有查询。

    sql
    SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_index_size
FROM pg_stat_user_indexes;

  • 用真实数据与真实负载验收。 造几条假数据验证不了什么，必须在生产规模 + EXPLAIN (ANALYZE, BUFFERS) 下确认。

    sql
    EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders WHERE tenant_id = 100 AND user_id = 42;

  • 保持索引设计与查询代码同步演进。 查询改了、索引没跟上，是最常见的技术债。

下一节

继续学习：范式与反范式

参考链接 ​