如何在MySQL存储函数中避免IN(SELECT...)子查询以提升性能

你碰到的这个问题太典型了——MySQL里的某些IN (SELECT ...)子查询确实容易出现性能瓶颈，尤其是当子查询返回结果较多或者关联的表数据量大时，优化器可能没法生成最优的执行计划，导致重复扫描表。

先说说你原来的尝试：单个变量只能存单行值，所以直接用SET skuAsin = (SELECT ...)只能处理单个ASIN，碰到多个值就会报错，这确实没法满足需求。下面给你几个可行的优化方案，按性能优先级排序：

1. 用JOIN替代IN子查询（最推荐） #

这是优化这类问题最有效的方式，把IN里的子查询转换成JOIN，让MySQL优化器能更好地利用索引，避免子查询的重复执行。

修改后的存储函数代码如下：

BEGIN
 DECLARE average DECIMAL(10,4);
 SET average = (
 SELECT avg(unitsByDay) FROM (
     SELECT i.date, sum(units_ordered) as unitsByDay 
     from inventory i
     JOIN (
         SELECT DISTINCT asin FROM inventory WHERE sku = aSku
     ) as sub_asin ON i.asin = sub_asin.asin
     WHERE i.marketplace_id = mid && i.date between d1 and d2
     GROUP BY date
 ) as vel
 );
 RETURN average;
END;

原理是先把需要的ASIN列表通过子查询生成一个临时数据集，再和主表做JOIN关联，这样比IN子查询的执行效率高很多，尤其是当inventory表在sku、asin、marketplace_id、date这些字段上有合适索引的时候。

2. 使用临时表存储子查询结果 #

如果业务场景确实需要先存储ASIN列表，你可以用临时表来存放多行结果，然后在主查询中关联这个临时表：

BEGIN
 DECLARE average DECIMAL(10,4);
 -- 创建临时表存储ASIN列表
 CREATE TEMPORARY TABLE temp_asin (asin VARCHAR(30) PRIMARY KEY);
 -- 插入子查询结果
 INSERT INTO temp_asin
 SELECT DISTINCT asin FROM inventory WHERE sku = aSku;
 -- 主查询关联临时表
 SET average = (
 SELECT avg(unitsByDay) FROM (
     SELECT i.date, sum(units_ordered) as unitsByDay 
     from inventory i
     JOIN temp_asin ON i.asin = temp_asin.asin
     WHERE i.marketplace_id = mid && i.date between d1 and d2
     GROUP BY date
 ) as vel
 );
 -- 销毁临时表（可选，会话结束会自动清理）
 DROP TEMPORARY TABLE IF EXISTS temp_asin;
 RETURN average;
END;

临时表的好处是可以复用结果，适合需要多次使用这个ASIN列表的场景，不过要注意临时表在同一会话内是可见的，不要和其他逻辑冲突。

3. 用GROUP_CONCAT生成字符串配合FIND_IN_SET（适合小数据量） #

如果你的ASIN数量不多（注意MySQL的group_concat_max_len默认限制是1024字节，需要的话可以调整），可以把多行ASIN拼接成逗号分隔的字符串，然后用FIND_IN_SET来匹配：

BEGIN
 DECLARE average DECIMAL(10,4);
 DECLARE skuAsinList VARCHAR(10000); -- 根据实际情况调整长度
 -- 把多行ASIN拼接成字符串
 SET skuAsinList = (SELECT GROUP_CONCAT(DISTINCT asin SEPARATOR ',') FROM inventory WHERE sku = aSku);
 SET average = (
 SELECT avg(unitsByDay) FROM (
     SELECT i.date, sum(units_ordered) as unitsByDay 
     from inventory i
     WHERE FIND_IN_SET(i.asin, skuAsinList) && i.marketplace_id = mid && i.date between d1 and d2
     GROUP BY date
 ) as vel
 );
 RETURN average;
END;

这种方法的缺点是FIND_IN_SET没法利用索引，所以当inventory表数据量大时，性能不如前两种方案，只适合小数据量的场景。

补充一下：你原来的慢实现里，子查询SELECT DISTINCT asin FROM inventory WHERE sku = aSku如果没有索引的话，会全表扫描，建议给inventory表建一个(sku, asin)的联合索引，这能大幅提升子查询的速度，不管用哪种方案都能受益。

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