MySQL 8.1使用索引查询时返回重复/缺失行问题求助

核心现象 #

• 基于recipeid关联recipe与recipe_ingredient表，使用WHERE IN (...)批量查询时，结果总行数正确，但存在预期食材行缺失、recipe_ingredient.id重复的异常
• 移除recipe_ingredient.recipeid索引后结果恢复正常，但查询性能下降
• 添加ORDER BY子句或减少IN列表中的recipeid数量时，查询结果正常
• 执行OPTIMIZE TABLE优化表后，问题未解决

可能根因 #

1. 查询优化器执行计划偏差 #

当recipeid索引存在时，MySQL优化器可能选择了索引驱动的嵌套循环连接等执行路径，在批量IN查询的场景下，出现了行重复读取或遗漏的逻辑错误。

• 添加ORDER BY会强制优化器调整执行计划（如先排序再关联），避开了异常路径
• 减少IN列表值时，优化器会切换到更简单的执行逻辑，也能规避问题

2. 索引统计信息失真 #

尽管执行了OPTIMIZE TABLE，但recipeid索引的统计数据（如基数、数据分布）可能仍未正确更新。优化器依赖统计信息选择执行计划，失真的统计数据会导致其选择错误的连接策略，进而引发数据读取异常。

3. MySQL 8.1版本特定Bug #

MySQL 8.1作为较新版本，可能存在与IN子句+索引关联查询相关的未修复Bug，这类问题通常在特定数据分布、查询条件组合下触发，表现为结果集异常。

排查验证步骤 #

• 更新统计信息：执行ANALYZE TABLE recipe_ingredient;强制更新表统计数据，重新执行查询验证结果
• 查看详细执行计划：使用EXPLAIN ANALYZE（MySQL 8.0+支持）获取执行过程细节，对比正常/异常场景的执行计划差异
• 强制指定执行计划：用STRAIGHT_JOIN强制按表书写顺序连接，或用FORCE INDEX/IGNORE INDEX指定索引使用策略，观察结果是否恢复正常
• 版本验证：尝试降级到MySQL 8.0稳定版，或升级到8.1最新小版本，验证是否为版本Bug

临时解决方案 #

• 添加ORDER BY子句（如ORDER BY recipe_ingredient.id），强制优化器调整执行计划，同时保留索引保障性能
• 使用STRAIGHT_JOIN指定连接顺序，避免优化器选择异常执行路径

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