3. 复合索引设计：最左前缀原则、索引列顺序对查询性能的影响、冗余索引的识别与清理

复合索引（多列索引）是 PostgreSQL 中应对复杂查询条件最有力的工具，但设计不当反而会拖慢写入、浪费存储。本章从最左前缀原则出发，深入分析索引列顺序对查询性能的影响，并给出冗余索引的识别与清理方法。

3.1 最左前缀原则

PostgreSQL 的 B-Tree 复合索引按照索引定义中列的顺序构建一棵排序树。查询条件只有从索引的最左列开始匹配，才能有效利用该索引，这就是最左前缀原则。

3.2 索引列顺序对查询性能的影响

复合索引中列的顺序不仅决定哪些查询能走索引，还直接影响索引的过滤效率和排序能力。

3.2.1 等值条件优先原则

将等值条件（=）的列放在范围条件（>、<、BETWEEN）之前，可以最大化索引的过滤粒度。

3.2.2 排序与分组优化

如果查询包含 ORDER BY 或 GROUP BY，将排序列放在复合索引的末尾，且与 WHERE 条件中的列顺序一致，可以避免额外的排序操作。

-- 高效：索引 (department_id, hire_date) 同时满足 WHERE 和 ORDER BY
CREATE INDEX idx_emp_dept_hiredate ON employees (department_id, hire_date);
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department_id = 10
ORDER BY hire_date DESC;
-- 输出：Backward Index Scan using idx_emp_dept_hiredate（无需 Sort 节点）

-- 低效：索引 (hire_date, department_id) 无法直接用于排序
CREATE INDEX idx_emp_hiredate_dept ON employees (hire_date, department_id);
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department_id = 10
ORDER BY hire_date DESC;
-- 输出：Index Scan + Sort（额外排序开销）

3.3 冗余索引的识别与清理

冗余索引不仅浪费磁盘空间，还会拖慢 INSERT、UPDATE、DELETE 操作的性能，因为每次数据变更都需要同步维护所有索引。

3.3.1 冗余索引的常见类型

3.3.2 使用系统视图识别冗余索引

-- 1. 查找未被使用的索引（idx_scan = 0 表示从未被扫描）
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 2. 查找前缀冗余索引（使用 pg_index 系统表）
SELECT
    a.indrelid::regclass AS table_name,
    a.indexrelid::regclass AS redundant_index,
    b.indexrelid::regclass AS covering_index,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS redundant_size
FROM pg_index a
JOIN pg_index b
    ON a.indrelid = b.indrelid
    AND a.indkey < b.indkey
    AND a.indkey @> b.indkey  -- a 的前缀包含 b 的所有列
WHERE a.indisprimary = false
  AND b.indisprimary = false
  AND a.indisunique = false
  AND b.indisunique = false;

-- 3. 结合 pg_stat_user_indexes 确认冗余索引是否真的被覆盖
-- 如果 covering_index 的 idx_scan 远高于 redundant_index，则冗余索引可安全删除

3.3.3 冗余索引清理的注意事项

• 唯一约束索引不可随意删除：即使功能上被覆盖，唯一索引还承担着数据完整性约束。
• 外键关联索引：PostgreSQL 不会自动为外键创建索引，但外键列上的索引对 ON DELETE CASCADE 等操作至关重要，不要误删。
• 部分索引（Partial Index）：如果冗余索引是部分索引，其过滤条件可能无法被普通复合索引替代，需单独评估。