PostgreSQL：多分类关联场景下IN与NOT IN的高效替代方案

问题背景 #

Category表与Product表为多对多关系，通过中间表product_category关联。需要检索满足以下规则的产品：

• 针对每个指定的category_type，产品要么属于该类型下的指定category_value，要么完全没有关联该类型的任何分类。

当前实现的性能问题 #

原查询使用多层嵌套的IN/NOT IN子句，虽然结果正确，但在大数据量表中效率极低——这类子查询容易触发全表扫描，嵌套层级越多，性能损耗越明显。原查询代码如下：

select product_id 
from product_category
where (
  (
  product_id IN 
    (
    select product_id 
    from product_category pc 
    join category c on pc.category_id = c.category_id 
      and category_type = 'type1' and category_value = 'v1'
    ) or product_id NOT IN (
    select product_id 
    from product_category pc 
    join category c on pc.category_id = c.category_id 
      and category_type = 'type1'
    )
  ) and (
  product_id IN (
    select product_id 
    from product_category pc 
    join category c on pc.category_id = c.category_id 
      and category_type = 'type2' and category_value = 'v2'
    ) or product_id NOT IN (
    select product_id 
    from product_category pc 
    join category c on pc.category_id = c.category_id 
    and category_type = 'type2'
    )
  )
)

高效替代方案 #

采用LEFT JOIN结合分组聚合的方式，避免嵌套子查询的性能问题，同时确保逻辑准确。核心思路是：通过左连接保留所有产品，用条件聚合判断每个分类类型的满足情况，最后通过HAVING子句过滤符合要求的产品。

优化后的查询语句 #

SELECT p.product_id
FROM Product p
LEFT JOIN product_category pc 
  ON p.product_id = pc.product_id
LEFT JOIN Category c 
  ON pc.category_id = c.category_id
GROUP BY p.product_id
HAVING 
  -- 处理type1的条件：要么匹配type1的v1，要么无type1关联
  (MAX(CASE WHEN c.category_type = 'type1' THEN c.category_value END) = 'v1' 
   OR MAX(CASE WHEN c.category_type = 'type1' THEN 1 ELSE 0 END) = 0)
  AND
  -- 处理type2的条件：要么匹配type2的v2，要么无type2关联
  (MAX(CASE WHEN c.category_type = 'type2' THEN c.category_value END) = 'v2' 
   OR MAX(CASE WHEN c.category_type = 'type2' THEN 1 ELSE 0 END) = 0);

方案优势 #

1. 避免嵌套子查询：用左连接和聚合操作替代IN/NOT IN，减少数据库的执行计划复杂度。
2. 全量覆盖产品：LEFT JOIN Product确保没有关联任何分类的产品也能被纳入判断。
3. 可扩展性强：新增分类类型条件时，只需在HAVING中添加对应的判断逻辑即可。

索引优化建议 #

为进一步提升查询效率，建议添加以下索引：

• 给product_category表创建联合索引：(product_id, category_id)，加速产品与分类关联的查询。
• 给Category表创建联合索引：(category_type, category_value, category_id)，快速定位指定类型和值的分类记录。
• 确保Product表的product_id为主键（默认已创建主键索引）。

示例验证 #

用提供的示例数据测试：

• 当查询条件为type1=v1、type2=c2时：
  • p1关联了type1的v1（满足type1条件），但type2关联的是c1（不满足type2条件），因此不会被返回。
  • p2没有关联type1（满足type1条件），且关联了type2的c2（满足type2条件），因此会被返回。

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