多对多关联多过滤条件的高效SQL查询方案咨询

背景与需求 #

现有三张业务表：

• Product：id（主键）、state（状态：active/archived）
• Category：id（主键）、name
• ProductCategory：pid（关联Product.id）、cid（关联Category.id）

核心需求：获取属于指定分类ID列表且state='active'的产品列表

前提条件：

• 产品数量远大于分类数量
• 产品数量 < 产品分类关联记录数（单个产品可归属多个分类）

现有方案分析 #

方案1：分别过滤后关联 #

SELECT DISTINCT p.*
FROM (SELECT id FROM Product WHERE state='active') p
INNER JOIN (SELECT pid FROM ProductCategory WHERE cid IN (/* 指定分类ID */)) pc
ON p.id = pc.pid

• 逻辑：先分别筛选活跃产品和目标分类的关联记录，再执行内联
• 问题：同一产品若属于多个指定分类，关联后会产生重复数据，必须加DISTINCT去重，额外增加计算开销。

方案2：先筛活跃产品再联分类过滤 #

SELECT DISTINCT p.*
FROM Product p
LEFT JOIN ProductCategory pc ON p.id = pc.pid
WHERE p.state='active' AND pc.cid IN (/* 指定分类ID */)

• 逻辑：先获取所有活跃产品，左联分类关联表后再过滤目标分类
• 问题：LEFT JOIN后加pc.cid条件等价于INNER JOIN，但如果活跃产品数量庞大（远大于目标分类的关联记录数），会先扫描大量数据，中间结果集过大，效率低下。

方案3：先筛分类关联再联产品过滤状态 #

SELECT DISTINCT p.*
FROM (SELECT pid FROM ProductCategory WHERE cid IN (/* 指定分类ID */)) pc
LEFT JOIN Product p ON pc.pid = p.id
WHERE p.state='active'

• 逻辑：先提取目标分类的所有产品关联记录，再联产品表筛选活跃状态
• 优势：利用cid索引快速定位目标分类的关联记录（数据量远小于全量活跃产品），后续联表和过滤的开销更小；但仍需DISTINCT去重。

补充方案：EXISTS半连接查询 #

这是适配当前场景的更高效方案：

SELECT p.*
FROM Product p
WHERE p.state = 'active'
  AND EXISTS (
    SELECT 1
    FROM ProductCategory pc
    WHERE pc.pid = p.id
      AND pc.cid IN (/* 指定分类ID */)
  )

• 逻辑：对每个活跃产品，检查是否存在目标分类的关联记录
• 核心优势：
  1. 无需DISTINCT：数据库优化器会将其处理为半连接，仅返回存在关联的产品一次，避免重复数据。
  2. 索引利用率拉满：若Product有state索引（或(id, state)联合索引）、ProductCategory有(pid, cid)联合索引，查询会直接走索引，几乎无全表扫描。
  3. 数据量可控：只针对活跃产品做存在性检查，避免处理大量无关的关联记录。

推荐方案及原因 #

在给定前提条件下，优先选择EXISTS半连接方案，其次选择方案3，原因如下：

1. 数据量优先级匹配：指定分类数量少，目标分类的关联记录数远小于全量活跃产品数，先通过分类筛选缩小范围（或通过EXISTS直接关联索引），减少中间数据处理量。
2. 索引高效利用：两种方案都能最大化利用ProductCategory.cid、Product.state及主键索引，避免不必要的全表扫描。
3. 减少额外开销：EXISTS方案无需去重，比方案3少了DISTINCT的计算步骤，性能更优。

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