pgsql-25 索引优化策略

2025-09-25 约 2083 字预计阅读 5 分钟

1. 概述

上一篇索引原理与类型讲了"有哪些索引"，本篇讲"怎么用好索引"——这是日常调优和面试实战的核心。核心思路是三句话：

建对索引：在正确的列、正确的顺序上建，避免冗余。
让查询能用上索引：避免写出使索引失效的 SQL。
持续维护：删掉没用的、监控膨胀。

索引优化的很多原则（选择性、最左前缀、索引失效）PG 与 MySQL 是相通的，本篇会标注差异点。

2. 选择性与区分度

选择性（Selectivity）= 不同值的数量 / 总行数，越接近 1 越适合建索引。

-- 评估某列的选择性
SELECT count(DISTINCT status)::float / count(*) AS selectivity FROM orders;

高选择性（如 user_id、email、订单号）→ 建索引收益大。
低选择性（如 gender、is_deleted、status 只有几个值）→ 单独建普通索引几乎没用，优化器往往直接全表扫描。
- 但低选择性列适合放进复合索引或用作部分索引的条件（如 WHERE status='pending'）。

3. 复合索引的列顺序（重点）

复合索引 (a, b, c) 遵循最左前缀原则：只能被"从最左列开始连续"的条件利用。

CREATE INDEX idx ON orders (user_id, status, created_at);
-- ✅ 能用: WHERE user_id=1
-- ✅ 能用: WHERE user_id=1 AND status='paid'
-- ✅ 能用: WHERE user_id=1 AND status='paid' AND created_at>'2026-01-01'
-- ⚠️ 部分用: WHERE user_id=1 AND created_at>... （跳过status，只用到user_id）
-- ❌ 不能用: WHERE status='paid' （跳过最左列user_id）

列顺序设计原则：

等值查询列放前，范围查询列放后。范围条件（>、<、BETWEEN）之后的列无法再用于索引过滤。

-- 查询: WHERE user_id=1 AND created_at > X AND status='paid'
-- 好: (user_id, status, created_at)  等值(user_id,status)在前，范围(created_at)在后
-- 差: (user_id, created_at, status)  created_at范围后，status用不上索引过滤

高选择性列尽量靠前（能更快缩小范围）。
兼顾 ORDER BY：若索引顺序与排序一致，可省去排序步骤（见第6节）。

4. 索引失效的常见原因（面试高频）

即使建了索引，写法不当也会让优化器放弃它，退化为全表扫描：

-- ❌ 1) 列上使用函数/运算 → 失效
WHERE lower(email) = 'a@b.com'          -- 解决：建表达式索引 lower(email)
WHERE created_at::date = '2026-07-01'   -- 解决：改成范围 created_at >= ... AND < ...
WHERE amount + 10 > 100                 -- 解决：改成 amount > 90

-- ❌ 2) 前置通配的 LIKE → 失效
WHERE name LIKE '%abc%'                 -- 解决：用 pg_trgm 的 GIN 索引
WHERE name LIKE 'abc%'                  -- ✅ 后置通配可用 B-tree

-- ❌ 3) 隐式类型转换 → 失效
WHERE phone = 13800138000              -- phone是varchar，数字触发转换。加引号: '13800138000'

-- ❌ 4) 违反最左前缀（见第3节）

-- ❌ 5) 优化器认为全表更快（低选择性/小表）→ 主动放弃索引（这是"对"的）

差异点：MySQL 里 OR 常导致索引失效，PG 有 BitmapOr 能对多个索引做位图合并，col1=1 OR col2=2 在两列都有索引时仍可能各走一个索引再合并，比 MySQL 更灵活。

5. 用 EXPLAIN 验证索引是否生效

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

看到 Index Scan / Index Only Scan / Bitmap Index Scan → 用了索引。
看到 Seq Scan（全表扫描）→ 索引没生效，排查第4节原因。
关注 Rows Removed by Filter：若很大说明索引过滤不够精准。

执行计划详解见查询优化与执行计划。

6. 覆盖索引与消除排序

6.1 覆盖索引（Index Only Scan）

查询用到的列全在索引里，就不必回堆表，性能最佳。

-- 查询只要 status，把它 INCLUDE 进索引
CREATE INDEX idx_cover ON orders (user_id) INCLUDE (status, amount);
-- SELECT status, amount FROM orders WHERE user_id=1  → Index Only Scan

注意 PG 的 Index Only Scan 还依赖 Visibility Map（VACUUM 维护），见 VACUUM与表膨胀。

6.2 用索引消除 ORDER BY 排序

-- 索引本身有序，若排序方向匹配可直接返回，省掉 Sort 步骤
CREATE INDEX idx_created ON orders (created_at DESC);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;   -- 无需额外排序

7. 找出并清理无效索引

索引不是越多越好——每个索引都会拖慢 INSERT/UPDATE/DELETE 并占空间。

-- 1) 从未被使用的索引（idx_scan=0），可考虑删除（排除主键/唯一约束）
SELECT s.relname AS 表, s.indexrelname AS 索引, s.idx_scan AS 使用次数,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS 大小
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisprimary AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- 2) 重复/冗余索引：如已有 (a,b) 再建 (a) 就是冗余（被最左前缀覆盖）
-- 3) 索引膨胀检查后用 REINDEX 重建
REINDEX INDEX CONCURRENTLY idx_orders_user;   -- PG 12+ 支持并发重建

8. 索引维护实践

大表建索引一律 CREATE INDEX CONCURRENTLY（不锁写）。
定期用 pg_stat_user_indexes 审计未用索引并清理。
避免冗余索引（复合索引已覆盖的前缀不必单建）。
索引膨胀严重时 REINDEX CONCURRENTLY 重建。
外键列建议手动建索引——PG 不像 MySQL 会为外键自动建索引，忘记建会导致父表删除时对子表全表扫描。

9. 面试高频问答

Q1：什么样的列适合建索引？ 高选择性（不同值多）、频繁出现在 WHERE/JOIN/ORDER BY 中的列。低选择性列（如性别、状态）单独建索引意义不大，但可作复合索引后缀或部分索引条件。

Q2：复合索引的列顺序怎么定？ 遵循最左前缀；等值条件列放前、范围条件列放后（范围之后的列用不上索引过滤）；高选择性列靠前；并尽量兼顾 ORDER BY 顺序。

Q3：哪些写法会导致索引失效？ 列上加函数/运算、前置通配 LIKE ‘%x%’、隐式类型转换、违反最左前缀。解决办法分别是：表达式索引、pg_trgm 索引、显式匹配类型、调整索引/查询。

Q4：PG 的外键会自动建索引吗？ 不会（这点和 MySQL 不同，MySQL InnoDB 外键自动建索引）。需手动为外键列建索引，否则父表删除/更新会引发子表全表扫描。

Q5：怎么判断一个索引该不该删？ 查 pg_stat_user_indexes 的 idx_scan，长期为 0 且非主键/唯一约束的索引通常可删；同时排查被更长复合索引最左前缀覆盖的冗余索引。

10. 下一步

学习查询优化与执行计划，学会读懂 EXPLAIN。

目录