MySQL IN子句含10+项时查询缓慢排查（附18秒耗时SQL）

首先，咱们先拆解下这个查询慢的核心原因：当IN子句包含10+值时，MySQL优化器可能会从"索引范围扫描"退化为"全表扫描"（尤其是当IN里的值分布较散时），再加上你的查询涉及JOIN和多个过滤条件，数据量一大就容易卡壳。下面是具体的优化方案：

1. 优先优化索引（最关键！） #

你的查询在weekly_data表用到的过滤字段有：week、itemgroup、days，还有JOIN用的hold_code；aol_reporting_hold_codes表用到chart和hold_code做JOIN过滤。建议创建以下索引：

• 对weekly_data创建联合覆盖索引：

  CREATE INDEX idx_weekly_data_filter ON weekly_data (week, itemgroup, days, hold_code);
  

  为什么这么建？week是范围查询（>=），放在最左；itemgroup是IN等值匹配，紧跟其后；days是范围过滤；最后hold_code是JOIN和聚合需要的字段——这个索引包含了查询需要的所有字段，能实现"覆盖索引扫描"，不用回表查主数据，速度会飙升。

• 对aol_reporting_hold_codes创建索引：

  CREATE INDEX idx_aol_hold_code_chart ON aol_reporting_hold_codes (chart, hold_code);
  

  这样JOIN时能快速定位到chart='GR'的hold_code数据，避免全表扫描。

2. 用JOIN替代IN子句（适合多值场景） #

虽然10个值不算特别多，但如果后续IN里的项还会增加，用临时表JOIN替代IN会更稳定：

-- 创建临时表存储需要的itemgroup值
CREATE TEMPORARY TABLE temp_itemgroups (
  itemgroup VARCHAR(20) NOT NULL PRIMARY KEY
);
INSERT INTO temp_itemgroups VALUES 
('BOTDTO'), ('BOTDWG'), ('C&FORG'), ('C&FOTO'), 
('MF-SUB'), ('MI-SUB'), ('PROPRI'), ('PROPTO'), 
('STRSTO'), ('STRSUB');

-- 优化后的查询
SELECT 
  wd.week AS start_week, 
  wd.hold_code, 
  COUNT(wd.hold_code) AS hold_code_count 
FROM weekly_data AS wd
JOIN temp_itemgroups tig ON wd.itemgroup = tig.itemgroup
JOIN aol_reporting_hold_codes hc ON hc.hold_code = wd.hold_code AND hc.chart = 'GR'
WHERE 
  wd.days <= 6 
  AND wd.hold_code IS NOT NULL 
  AND wd.hold_code != '' 
  AND wd.week >= '201717'
GROUP BY wd.week, wd.hold_code; -- 补全GROUP BY，避免隐式分组的问题

临时表的主键会自动创建索引，JOIN时的匹配效率比IN子句更高，尤其是当IN值超过20个时效果更明显。

3. 检查执行计划，确认索引生效 #

用EXPLAIN命令跑一遍原查询和优化后的查询，重点看这几个字段：

• type：最好是range或ref，如果是ALL说明全表扫描，索引没生效；
• key：应该显示咱们刚才创建的索引名称；
• Extra：如果出现Using index说明用到了覆盖索引，这是最优状态；
• rows：数值越小说明扫描的数据量越少。

4. 其他小优化 #

• 更新表统计信息：如果表数据量很大或经常变更，执行ANALYZE TABLE weekly_data; ANALYZE TABLE aol_reporting_hold_codes;，让MySQL优化器拿到最新的数据分布，生成更优的执行计划；
• 简化过滤条件：如果hold_code字段设置了NOT NULL，可以去掉wd.hold_code IS NOT NULL的判断；如果业务上不会有空串，也可以去掉wd.hold_code != ''；
• 确认GROUP BY的合法性：MySQL 5.7+默认开启ONLY_FULL_GROUP_BY，所以查询里的SELECT字段必须都在GROUP BY里，避免隐式分组导致的性能问题或错误。

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