如何高效查询多对多关系中仅关联单个数据集的聚合项？

我有两张存在多对多关系的表：dataset表存储数据集元数据，aggregates表记录预聚合数据（因dataset数据量庞大，UI展示逻辑复杂，需提前完成特定聚合操作）。aggregates可关联多个dataset，单个dataset也可属于多个aggregates，二者为多对多关系。

我经常需要针对单个dataset进行聚合操作，因此aggregates_dataset关联表中仅对应单个dataset的aggregates条目是高频访问对象，其核心特征为唯一性。请问如何高效筛选出这类仅关联单个数据集的aggregates？

以下是模拟示例表：

模拟表结构 #

products表 #

|    id    |   name    |
------------------------
| 1        | a         |
| 2        | b         |
| 3        | c         |
| 4        | d         |

categories表 #

| id | name     |
-----------------
| 1  | ab       |
| 2  | abc      |
| 3  | b        |
| 4  | d        |
| 5  | b        |

product_categories关联表 #

|  product_id  |  category_id  |
--------------------------------
| 1            | 1             |
| 2            | 1             |
| 1            | 2             |
| 2            | 2             |
| 3            | 2             |
| 2            | 3             |
| 4            | 4             |
| 2            | 5             |

给定产品id=2，需要获取类别id=3和5，因为这两个类别仅包含产品id=2。目前可用CTE按product_categories.category_id分组并COUNT每个类别下的产品数量实现需求，但GROUP BY + COUNT操作成本较高，想找更直接高效的方式获取该产品的「特殊类别」。

针对这类仅关联单个目标记录的关联条目筛选，有几种比GROUP BY + COUNT更高效的实现方式：

方法1：使用EXISTS子查询排除多关联的类别 #

通过判断当前类别是否存在其他关联产品，直接筛选出仅关联目标产品的类别：

SELECT pc.category_id
FROM product_categories pc
WHERE pc.product_id = 2
AND NOT EXISTS (
    SELECT 1
    FROM product_categories pc2
    WHERE pc2.category_id = pc.category_id
    AND pc2.product_id != 2
);

逻辑是：先找出产品2关联的所有类别，再排除掉那些还关联其他产品的类别，剩下的就是仅关联产品2的类别。相比分组统计，EXISTS子查询可利用索引快速定位，避免全表分组计算。

方法2：LEFT JOIN + NULL判断 #

通过自连接关联表，查找没有其他产品关联的类别：

SELECT pc1.category_id
FROM product_categories pc1
LEFT JOIN product_categories pc2
ON pc1.category_id = pc2.category_id
AND pc2.product_id != 2
WHERE pc1.product_id = 2
AND pc2.product_id IS NULL;

思路和方法1类似，通过左连接寻找是否存在其他关联产品，若左连接结果为NULL，说明该类别仅关联目标产品。

关键优化：索引支持 #

无论用哪种方法，都需要给关联表product_categories（对应你的aggregates_dataset）建立复合索引，才能让查询高效执行：

CREATE INDEX idx_pc_category_product ON product_categories (category_id, product_id);

这个复合索引可以让数据库快速定位每个类别下的所有产品，以及每个产品关联的所有类别，大幅降低查询耗时。

对比GROUP BY的优势 #

上述两种方法避免了对整个关联表进行分组和计数操作，而是针对目标产品的关联类别做精准排查，在数据量较大时，执行效率会明显高于GROUP BY + COUNT，尤其是当关联表存在大量多对多关联记录时。

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