MySQL中多产品ID关联用户匹配的慢查询优化方法

原写法的性能瓶颈 #

你当前使用的逐次关联IN子查询写法，会随着校验产品ID数量的增长线性增加查询开销：每追加一个需要校验的产品ID，就会针对users表的每一行执行一次独立的products表子查询扫描，当校验的产品数量变多后，重复的扫描开销会被不断放大，最终导致查询耗时显著上升。

关于你提到的元组IN写法的说明 #

where (888, 999) in (select p.product from products p where p.user=u.id) 这类写法无法实现需求
行值IN的匹配规则要求：IN关键字前的元组列数，必须和子查询返回的结果列数完全一致。当前子查询仅返回单列的product值，根本无法和包含两个值的元组完成匹配，这类写法会直接触发语法错误。

可落地的高效实现方案 #

• 单扫描聚合匹配法（性能最优，适配任意数量的待校验产品ID）
  核心思路是只遍历一次products表，过滤出和目标产品相关的记录后按用户分组，只要分组内去重后的产品数量等于目标产品的总数，就说明该用户同时持有所有需要校验的产品。
  以查询同时持有999、888号产品的用户为例，可改写为如下语句：

  SELECT u.*
  FROM users u
  INNER JOIN (
      SELECT user
      FROM products
      WHERE product IN (999, 888)
      GROUP BY user
      HAVING COUNT(DISTINCT product) = 2
  ) AS matched_user ON u.id = matched_user.user
  

  这种写法的查询开销不会随待校验产品的数量增长出现明显上升，只要在products表上建立(product, user)的联合索引，整个过滤、分组、统计环节都可以通过索引覆盖完成，无需额外回表扫描。
• 多EXISTS写法（小幅优化原逻辑，适配少量产品校验场景）
  如果待校验的产品ID数量不多，也可以将原来的IN子查询替换为EXISTS判断，相比原写法的执行效率会有一定提升：

  SELECT * FROM users u
  WHERE EXISTS (SELECT 1 FROM products p WHERE p.user = u.id AND p.product = 999)
    AND EXISTS (SELECT 1 FROM products p WHERE p.user = u.id AND p.product = 888)
  

  但这种写法本质上还是每一个产品校验对应一次独立的索引查找，当待校验产品数量增多时，整体开销还是会线性上涨，性能上限不如聚合匹配法。

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