索引

单列索引索引和聚合索引

本文以 mv_scene_code 表为例，围绕以下几类查询，说明 PostgreSQL 对索引的使用方式，以及如何设计更合理的索引。

SELECT * FROM mv_scene_code WHERE group_id = 602948105429491712;
SELECT * FROM mv_scene_code WHERE group_id = 602948105429491712 AND scene = 2;

一、仅创建 group_id 单列索引的情况

索引定义

CREATE INDEX idx_mv_scene_code_group_id
ON mv_scene_code (group_id);

查询行为分析

1. 查询仅包含 group_id

SELECT * FROM mv_scene_code
WHERE group_id = 602948105429491712
LIMIT 1;

• PostgreSQL 会使用该单列索引
• 可以通过索引快速定位满足条件的行
• 对于等值查询非常高效

2. 查询同时包含 group_id 和 scene

SELECT * FROM mv_scene_code
WHERE group_id = 602948105429491712
  AND scene = 2;

• PostgreSQL 仍可能使用 group_id 索引

• 执行方式通常是：

  1. 通过 group_id 索引找到候选行
  2. 再在结果集中过滤 scene = 2

• 如果某个 group_id 对应的数据量较大，性能会明显下降

• 是否使用索引取决于查询计划器的成本估算

二、创建 (group_id, scene) 复合索引的情况（推荐）

索引定义

CREATE INDEX idx_mv_scene_code_group_scene
ON mv_scene_code (group_id, scene);

三、复合索引的使用规则（最左前缀原则）

PostgreSQL 的 B-tree 复合索引遵循 最左前缀原则（Leftmost Prefix Rule）：

只要查询条件中包含索引的最左列，索引就可以被使用。

对于索引 (group_id, scene)，可用情况如下：

四、仅创建复合索引时的查询表现

场景：只创建 (group_id, scene)，不创建单列索引

CREATE INDEX idx_mv_scene_code_group_scene
ON mv_scene_code (group_id, scene);

查询：

SELECT * FROM mv_scene_code
WHERE group_id = 602948105429491712
LIMIT 1;

结论：

• PostgreSQL 会使用复合索引

• 原因是：

  • 复合索引首先按 group_id 排序
  • scene 只是次级排序列

• 因此，该查询等价于使用一个以 group_id 为前导列的索引

不需要额外再创建 group_id 的单列索引

五、索引设计建议总结

1. 推荐索引方案

如果你的查询模式主要是以下两类：

WHERE group_id = ?
WHERE group_id = ? AND scene = ?

推荐只创建一个复合索引：

(group_id, scene)

2. 为什么不建议同时建两个索引？

• 复合索引已经覆盖单列 group_id 的使用场景

• 多余的单列索引会：

  • 增加写入成本（INSERT / UPDATE / DELETE）
  • 占用更多磁盘空间
  • 增加维护复杂度

六、最佳实践结论

• 复合索引 (group_id, scene)：

  • 可以同时优化单条件和双条件查询
  • 遵循最左前缀原则
  • 是该场景下的最优解

• 单列索引仅适合查询条件单一、且不会扩展的场景

如果后续还有 ORDER BY、范围查询（>、<）、或只按 scene 查询的需求，索引设计可以进一步调整。