MySQL如何查询订阅了所有指定tag的productID

要实现匹配全部指定tag的product检索，最通用的实现方式是分组后统计匹配tag的数量：

SELECT productID
FROM productTagSubscription
WHERE tagID IN ('id1', 'id2', 'id3', 'id4')
GROUP BY productID
-- 等号右侧数值为你传入的tag总数量
HAVING COUNT(DISTINCT tagID) = 4;

如果你的表中不存在同一个productID重复绑定同一个tagID的冗余记录，可以去掉DISTINCT减少计算开销，改写为：

SELECT productID
FROM productTagSubscription
WHERE tagID IN ('id1', 'id2', 'id3', 'id4')
GROUP BY productID
HAVING COUNT(tagID) = 4;

如果传入的tag数量较少，也可以用多表关联的方式实现，部分场景下性能更优：

SELECT DISTINCT p1.productID
FROM productTagSubscription p1
INNER JOIN productTagSubscription p2 
  ON p1.productID = p2.productID AND p2.tagID = 'id2'
INNER JOIN productTagSubscription p3 
  ON p1.productID = p3.productID AND p3.tagID = 'id3'
INNER JOIN productTagSubscription p4 
  ON p1.productID = p4.productID AND p4.tagID = 'id4'
WHERE p1.tagID = 'id1';

• 新增联合索引：为productTagSubscription表创建(tagID, productID)的联合索引，WHERE条件过滤tag、分组取productID的操作都可以直接通过索引完成，无需回表查询，性能可以提升数倍。
• 移除冗余去重逻辑：如果业务侧已经保证product和tag的关联关系唯一，直接删除COUNT函数中的DISTINCT关键字，降低分组统计的计算成本。
• 参数化适配tag数量：业务代码中传入tag列表时同步计算列表长度，作为参数传入查询语句中HAVING的判断条件，无需硬编码固定数值，适配不同数量的tag查询需求。
• 高频率查询场景预聚合：如果该类查询的QPS很高、表数据量极大，可以新增字段存储每个product绑定的所有tag集合（可使用Set、JSON类型存储），查询时直接做集合包含判断，查询效率更高，仅需额外维护预聚合字段和原始关联表的一致性即可。

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