Aurora MySQL 5.7中IN子句值数量不同时主键索引失效问题咨询

这不是Bug，而是MySQL 5.7优化器针对IN()子句的决策逻辑相比5.6发生了明显变化。

核心原因：优化器的成本估算逻辑调整 #

MySQL优化器会基于成本模型选择执行计划，对比索引查找和全表扫描的预期代价：

• 当IN()中的ID数量较少（如2000个）时，优化器判断索引查找的IO、CPU成本远低于全表扫描，因此会选择主键索引。
• 当ID数量增加到20000个时，优化器可能认为多次索引定位的累计成本超过全表扫描，因此默认选择全表扫描；但实际场景中索引查找效率更高，说明此时优化器的成本估算存在偏差，USE INDEX(PRIMARY)可以强制纠正这一选择。
• 当ID数量达到200000个时，MySQL 5.7优化器的成本阈值触发了更激进的判断——即使添加FORCE INDEX，也会忽略索引选择全表扫描。这是因为5.7对大IN()集合的处理逻辑做了调整，优化器会认为遍历大量离散ID的索引查找代价过高，哪怕实际返回行数仅6000行（占表总量的0.006%）。

5.6与5.7的关键差异 #

MySQL 5.6的优化器对大IN()集合的索引查找成本估算偏低，因此即使ID数量很大，仍会优先选择索引；而5.7优化器更新了成本模型，对离散索引查找的代价评估更严格，当IN()集合超过一定规模时，会倾向于选择全表扫描。

可行的解决方案 #

1. 更新统计信息：执行ANALYZE TABLE A;让优化器获取更准确的表数据分布，可能修正成本估算偏差。
2. 拆分大IN()集合：将200000个ID拆分为多个小批次（如每个批次2000个），分别执行查询后合并结果，每个小查询会自动走主键索引。
3. 改用临时表关联：将ID存入临时表，通过JOIN替代IN()子句，示例代码：

   CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY);
   INSERT INTO temp_ids VALUES (1),(2),...; -- 批量插入目标ID
   SELECT A.col1, A.col2 FROM A JOIN temp_ids ON A.id = temp_ids.id;
   

   这种方式优化器会更倾向于使用主键索引关联，效率远高于全表扫描。

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