PostgreSQL中关联过滤数据的主键与索引最优使用方案

1. 两张表的主键关联设计 #

应该将唯一id设为主键并以此关联，原因如下：

• 唯一id无重复，完全符合主键的唯一性、非空约束要求；
• 主键默认会被数据库创建聚簇索引（以InnoDB为例），基于主键的INNER JOIN操作会利用聚簇索引的有序性大幅提升关联效率，避免全表扫描；
• 你的关联SQL SELECT * FROM tableA INNER JOIN tableB ON tableA.pk = tableB.pk是合理的，但要确保两张表的主键字段数据类型完全一致，避免隐式类型转换导致索引失效。

2. 级联筛选的索引最佳用法 #

针对按column1→column2→column3的级联筛选逻辑，最佳方案是创建联合索引：

CREATE INDEX idx_cascade_filter ON tableA (column1, column2, column3);

• 该索引完全匹配你的筛选顺序，利用左前缀匹配规则，第一步筛选column1时可以直接定位到对应数据区间，第二步基于column1的结果筛选column2、第三步筛选column3都能继续利用索引，大幅减少扫描行数；
• 虽然每个筛选维度的不同值仅5-25个（选择性较低），但对于20-100百万行的大表，联合索引仍能将筛选操作的IO成本降到最低。

3. Column3的多维度选择处理：是否需要新增列？ #

针对column3的三种选择方式（精确值、年份、季度），建议根据数据库版本选择方案：

方案一：使用函数索引（MySQL 8.0+/PostgreSQL 11+等支持函数索引的数据库） #

无需新增列，直接基于函数结果创建索引，匹配不同筛选场景：

• 针对年份查询，创建索引：

  CREATE INDEX idx_cascade_year ON tableA (column1, column2, YEAR(column3));
  

• 针对季度查询，创建索引：

  CREATE INDEX idx_cascade_quarter ON tableA (column1, column2, QUARTER(column3));
  

这样，当执行WHERE column1 = ? AND column2 = ? AND YEAR(column3) = ?或WHERE column1 = ? AND column2 = ? AND QUARTER(column3) = ?时，能直接利用索引加速查询。

方案二：新增计算列（兼容低版本数据库） #

如果数据库不支持函数索引，建议新增两个计算列：

• year_col：存储column3对应的年份（如从2022-01提取2022）；
• quarter_col：存储column3对应的季度（如从2022-01提取1，对应Q1）；

然后创建对应联合索引：

ALTER TABLE tableA ADD COLUMN year_col INT GENERATED ALWAYS AS (YEAR(column3)) STORED;
ALTER TABLE tableA ADD COLUMN quarter_col INT GENERATED ALWAYS AS (QUARTER(column3)) STORED;

CREATE INDEX idx_cascade_year ON tableA (column1, column2, year_col);
CREATE INDEX idx_cascade_quarter ON tableA (column1, column2, quarter_col);

• 新增列采用存储型计算列，数据库会自动维护值的更新，无需手动同步；
• 这两个列的存储成本极低（INT类型仅占4字节），对于大表来说额外存储可以忽略，但能彻底避免函数调用导致的索引失效问题。

补充说明 #

• 精确值筛选可以直接复用之前的idx_cascade_filter联合索引；
• 所有索引的设计都围绕你的级联筛选顺序，确保每一步筛选都能利用索引的左前缀匹配，避免全表扫描。

内容的提问来源于stack exchange，提问作者fstr