MySQL 5.7中WHERE IN含大量ID时UPDATE不使用索引问题求助

我们有一张约1000万条记录的表，执行以下UPDATE语句：

UPDATE table_name SET column1=1, column2=0, column3='2022-10-30' WHERE id IN(1,2,3,...,N);

遇到的核心问题：

• 当N≤3000：执行计划正常走PRIMARY索引，速度极快；
• 当3000≤N≤30000：需用FORCE INDEX(PRIMARY)才能强制走索引；
• 当N>30000：即使加FORCE INDEX，执行仍极慢，推测优化器实际选择全表扫描。

以下是可引导优化器选择索引扫描的解决方案：

一、拆分大IN列表为小批量更新 #

这是最稳妥的方案，既规避优化器成本估算偏差，又降低大事务风险：

• 将超过3万的ID列表拆分为多个≤3000的子列表，分批执行UPDATE，每次追加FORCE INDEX(PRIMARY)：

-- 第1批
UPDATE table_name SET column1=1, column2=0, column3='2022-10-30' WHERE id IN(1,2,...,3000) FORCE INDEX(PRIMARY);
-- 第2批
UPDATE table_name SET column1=1, column2=0, column3='2022-10-30' WHERE id IN(3001,...,6000) FORCE INDEX(PRIMARY);
-- ... 后续批次

• 优势：每个小批次的执行计划稳定走主键索引，执行速度快，还能减少锁竞争和二进制日志写入压力。

二、改用临时表JOIN替代IN列表 #

通过临时表存储ID并建立主键，让优化器更易选择索引关联：

1. 创建带主键的临时表并插入目标ID：

CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1),(2),...,(N); -- 批量插入所有目标ID

2. 执行JOIN方式的UPDATE：

UPDATE table_name t
JOIN tmp_ids tmp ON t.id = tmp.id
SET t.column1=1, t.column2=0, t.column3='2022-10-30';

• 临时表的主键会让JOIN操作基于主键匹配，优化器会优先选择索引扫描而非全表扫描，即使ID数量很大也能保持高效。

三、调整优化器参数（谨慎测试后使用） #

针对MySQL 5.7的优化器行为，调整参数引导其选择索引扫描：

1. 调整eq_range_index_dive_limit #

MySQL 5.7默认值为200，当IN列表长度超过这个值时，优化器会用统计信息估算成本而非逐个探测索引，容易出现偏差。调大这个值让优化器对大IN列表做精确的索引成本计算：

-- 会话级生效，仅影响当前连接
SET SESSION optimizer_switch='eq_range_index_dive_limit=100000';

如果测试有效，可在my.cnf中配置全局生效：

optimizer_switch = eq_range_index_dive_limit=100000

2. 关闭index_merge_union #

当优化器尝试使用索引合并策略时，可能忽略单独的主键索引，关闭该选项可强制优化器优先考虑主键索引：

SET SESSION optimizer_switch='index_merge_union=off';

3. 调整全表扫描成本相关参数 #

• 调小read_rnd_buffer_size：增加全表扫描的成本，让索引扫描更具优势（注意：该参数影响所有查询，需测试后调整）：

SET SESSION read_rnd_buffer_size=8192; -- 默认可能是256K或更大，调小至8K

• 关闭索引剪枝：设置optimizer_prune_level=0，让优化器考虑所有可能的索引，避免剪枝掉主键索引（会增加优化时间，不建议全局长期开启）：

SET SESSION optimizer_prune_level=0;

四、验证执行计划 #

每次调整后，用EXPLAIN UPDATE ...确认执行计划：

• 查看type字段：应为range或ref（表示走索引扫描），而非ALL（全表扫描）；
• 查看key字段：确认是PRIMARY，确保主键索引被使用。

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