MySQL 5.7升级至8.0后大量ID的IN预编译查询变慢

这个问题是MySQL 8.0优化器成本模型调整+大参数列表估算偏差共同导致的决策错误，具体原因如下：

• 成本计算逻辑变更：MySQL 8.0重构了InnoDB的查询成本模型，提高了随机IO的成本权重，且在计算大IN列表的主键范围扫描成本时，错误将聚簇主键的顺序扫描成本，按照二级索引回表的随机IO成本计算，大幅高估了走主键索引的实际开销。而InnoDB主键是聚簇索引，叶子节点直接存储行数据，走主键范围扫描不需要回表，本身是顺序IO操作，实际成本极低。
• 索引探底阈值触发：默认配置下eq_range_index_dive_limit参数值为200，当IN列表的参数数量超过这个阈值时，优化器会跳过精确的索引探底（index dive）统计流程，直接用预存的模糊索引统计值估算符合条件的行数。从你的执行计划可以看到，优化器错误预估过滤率为50%（仅3万行符合条件），进一步拉低了全表扫描的估算成本，最终错选全表扫描。
• 场景规则错配：8.0优化器内置了"查询覆盖表20%~30%以上数据时优先选择全表扫描"的规则，这个规则原本是为了避免二级索引查大量数据时频繁回表的性能开销，但直接套用到聚簇主键场景时就会出现决策失误——聚簇主键查询不存在回表开销，即使查询全表数据，走主键扫描的性能也不弱于全表扫描。

MySQL 5.7版本的成本模型没有上述聚簇索引成本计算的逻辑错误，即使触发同样的索引探底阈值，也会优先选择主键索引，因此不会出现性能骤降。

按照改造成本从低到高排序：

• 强制索引绕过优化器决策：在查询中添加FORCE INDEX(PRIMARY)提示，直接指定走主键索引，不需要调整数据库参数、不需要拆分查询，改完即可恢复到MySQL 5.7下的性能。JOOQ原生支持表级强制索引的语法，可以直接在代码层拼接生成对应SQL。
• 会话级调整优化器参数：在连接池初始化连接时，执行set session eq_range_index_dive_limit = 100000，将索引探底的阈值调高到覆盖你业务中IN列表的最大长度，让优化器对大IN列表也做精确的行数统计，得到准确的成本估算结果。这个调整只在当前业务连接生效，不会影响其他业务查询。如果你的数据库存储是SSD，也可以同时把random_page_cost从默认的4.0调低到1.0，匹配SSD下随机IO和顺序IO性能差距极小的实际情况，避免优化器高估索引扫描成本。
• 拆分大IN查询：将6万ID的大查询拆分为每批500~1000个ID的小查询串行执行，每个小查询IN列表长度远低于优化器阈值，会稳定走主键范围扫描，总耗时远低于错走全表扫描的几十秒，同时也能避免超长SQL解析、传输的额外开销。
• 临时表关联替代大IN：先创建一张只有ID字段、带主键索引的临时表，把需要查询的6万ID批量插入临时表，再通过JOIN关联业务表查询，示例SQL：

SELECT t.id, t.name, t.description 
FROM ex_table t 
INNER JOIN temp_id_list tmp ON t.id = tmp.id

这种写法不受IN列表长度限制，优化器会稳定选择主键关联，性能比大IN查询更稳定，适合ID数量可能继续增长的场景。

• 升级MySQL小版本：MySQL 8.0.30及之后的小版本修复了多个大IN列表场景下聚簇索引成本估算错误的bug，升级后不需要调整参数、修改代码，优化器即可自动选择正确的执行计划。

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