3. 复合索引设计：最左前缀原则、索引列顺序选择、覆盖索引
复合索引（Composite Index / Multi-Column Index）是 PostgreSQL 中应对多条件查询的核心手段。与单列索引不同，复合索引的列顺序、查询条件的使用方式，直接决定了索引能否被有效利用。本章围绕三个核心概念展开：最左前缀原则、索引列顺序选择、覆盖索引。
3.1 最左前缀原则
PostgreSQL 的 B-Tree 复合索引按照索引定义的列顺序，从左到右构建一棵排序树。查询条件必须从索引的最左列开始匹配，跳过最左列或打乱顺序会导致索引无法被完整使用。
核心规则： 复合索引 (a, b, c) 可以匹配的条件组合：
- ✅
WHERE a = 1—— 使用索引 - ✅
WHERE a = 1 AND b = 2—— 使用索引 - ✅
WHERE a = 1 AND b = 2 AND c = 3—— 使用索引 - ✅
WHERE a = 1 AND c = 3—— 仅使用索引列 a，c 条件无法利用索引 - ❌
WHERE b = 2—— 无法使用索引 - ❌
WHERE c = 3—— 无法使用索引
设计建议： 将等值查询条件中最常出现的列放在复合索引的最左侧。范围查询（
>、<、BETWEEN）的列应放在等值列之后，因为范围条件会中断后续列的索引匹配。
3.2 索引列顺序选择
复合索引的列顺序不仅影响最左前缀匹配，还影响索引的选择性（Selectivity）和排序能力。选择正确的列顺序，可以大幅减少扫描的索引页数量。
| 场景 | 推荐顺序 | 原因 |
|---|---|---|
| 等值条件 + 等值条件 | 选择性高的列在前 | 快速过滤更多行，减少后续比较 |
| 等值条件 + 范围条件 | 等值列在前，范围列在后 | 等值列精确匹配后，范围列只需扫描连续区间 |
| 等值条件 + ORDER BY | 等值列在前，排序列在后 | 索引天然有序，避免额外排序操作 |
| 高基数列 + 低基数列 | 高基数列在前 | 高基数列（如用户ID）能更快缩小数据范围 |
实战案例： 订单表查询
WHERE status = 'paid' AND created_at > '2024-01-01' ORDER BY created_at
- ✅ 推荐索引：
(status, created_at)—— 等值列 status 在前，范围+排序列 created_at 在后 - ❌ 不推荐：
(created_at, status)—— 范围条件在前，无法利用索引排序，且 status 等值过滤效果降低
3.3 覆盖索引
覆盖索引（Covering Index / Index-Only Scan）是指索引中包含了查询所需的所有列，PostgreSQL 可以直接从索引页获取数据，无需回表访问堆表（Heap）。这能显著减少 I/O，尤其适合高频只读查询。
关键条件：
- 查询的 SELECT 列、WHERE 条件列、ORDER BY 列全部包含在同一个复合索引中
- 索引的可见性映射（Visibility Map）标记为全可见，否则仍需回表检查事务可见性
| 查询类型 | 索引设计 | 是否覆盖 | 执行计划 |
|---|---|---|---|
SELECT id, name FROM users WHERE status = 1 |
(status, id, name) |
✅ 是 | Index Only Scan |
SELECT id, name, email FROM users WHERE status = 1 |
(status, id, name) |
❌ 否（缺少 email） | Index Scan + 回表 |
SELECT count(*) FROM orders WHERE status = 'paid' |
(status) |
✅ 是（仅索引列） | Index Only Scan |
性能收益： 覆盖索引可减少 50%~90% 的 I/O 开销，因为索引页通常比堆表页更紧凑，且 B-Tree 的缓存命中率更高。在报表导出、热点数据查询等场景中效果尤为明显。
-- 创建覆盖索引示例
CREATE INDEX idx_orders_status_date_amount
ON orders (status, created_at, amount);
-- 查询：所有列都在索引中，触发 Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, created_at, amount
FROM orders
WHERE status = 'shipped'
AND created_at > '2024-06-01';
注意事项：
- 覆盖索引会增加索引体积，因为需要存储更多列数据
- 写操作（INSERT/UPDATE/DELETE）的维护成本会上升
- 仅对高频只读查询设计覆盖索引，避免过度索引
- PostgreSQL 的 Index Only Scan 依赖可见性映射，频繁更新的表可能无法完全避免回表
3.4 综合对比：三种设计策略
| 策略 | 核心目标 | 适用场景 | 代价 |
|---|---|---|---|
| 最左前缀原则 | 确保索引能被查询条件匹配 | 多条件组合查询 | 列顺序固定，灵活性受限 |
| 索引列顺序选择 | 最大化过滤效率与排序能力 | 等值+范围+排序混合查询 | 需要分析查询模式 |
| 覆盖索引 | 避免回表，减少 I/O | 高频只读查询、报表导出 | 索引体积增大，写性能下降 |