这是个典型的MySQL查询执行计划退化问题，我在日常运维中遇到过好几次类似的场景，咱们一步步拆解原因和解决办法：

一、为什么执行计划会突然变化？ #

核心原因是MySQL查询优化器的成本估算依赖过时的表统计信息：

• 当你的大表数据量从少量激增到1300万行时，InnoDB自动收集的统计信息（比如行数、索引基数、数据分布）可能没有及时更新。此时优化器计算成本时，误以为全表扫描（ALL类型）的成本更低，所以选择了低效的执行路径。
• 当你逐步移除关联再重新启用时，相当于强制优化器重新解析查询并评估执行成本。这时候要么统计信息已经自动触发了更新，要么优化器重新计算时使用了更准确的临时数据，最终选择了基于索引的高效执行计划（range/eq_ref这类索引关联类型）。

二、为什么同一查询的处理方式会改变？ #

MySQL的优化器是基于成本的优化器（CBO），它不会固定选择某一种执行路径，而是根据当前表的统计信息、系统资源（比如内存、IO）来计算不同执行计划的成本，选成本最低的那个：

• 数据量较小时，全表扫描的成本确实很低（30ms就能完成），优化器的选择没问题；
• 数据量剧增后，统计信息过时，优化器误判了全表扫描的成本（比如它以为表只有几万行，实际已经1300万），所以选了错误的执行计划；
• 当你调整查询结构（移除关联再恢复），相当于打破了之前缓存的执行计划，优化器不得不重新计算，这时候如果统计信息已经更新，或者临时采样到了更准确的数据，就会选择更优的索引关联路径。

三、如何让生产服务器实现同样的快速执行效果？ #

你可以按以下优先级尝试：

1. 手动更新表统计信息（最推荐）
   执行这条命令，让InnoDB重新收集大表的统计数据：

   ANALYZE TABLE 你的大表名;
   

   这个操作是轻量级的，不会锁表（InnoDB下是在线操作），执行后优化器就能基于准确的统计信息选择最优执行计划。

2. 强制指定索引
   如果知道关联用的foreign_ID索引名称，可以在查询中强制优化器使用该索引：

   SELECT ... 
   FROM 大表 FORCE INDEX(foreign_id索引名)
   JOIN 小表 ON 大表.foreign_ID = 小表.ID
   ...;
   

   这种方式适合临时应急，但长期来看还是要保证统计信息准确。

3. 清理查询计划缓存
   如果生产环境开启了查询缓存（MySQL 5.7默认开启，但不建议长期使用），可以执行：

   FLUSH QUERY CACHE;
   

   不过MySQL 8.0已经移除了查询缓存，所以这个方法优先级较低。

四、如何避免未来再次出现这类问题？ #

1. 定期更新统计信息
   给大表设置定时任务（比如每周一次），执行ANALYZE TABLE。尤其是在批量导入数据、大规模更新后，一定要手动执行一次，确保统计信息及时更新。

2. 调整InnoDB统计信息收集参数

   • 确保innodb_stats_auto_recalc参数是开启的（MySQL 5.7默认开启），它会在表数据变化超过10%时自动重新计算统计信息；
   • 如果表的数据分布很不均匀，可以调大innodb_stats_sample_pages参数（默认是20），让统计信息更准确（比如设置为100）：

     SET GLOBAL innodb_stats_sample_pages = 100;
     

3. 监控慢查询日志
   开启MySQL的慢查询日志，设置合理的阈值（比如1秒），及时发现执行计划退化的查询。一旦发现某条查询突然变慢，第一时间用EXPLAIN分析执行计划。

4. 确保索引最优
   检查foreign_ID的索引是否是独立的单列索引，避免用复合索引的前缀（除非复合索引的第一个字段就是foreign_ID）。另外，定期清理冗余索引，避免优化器在选择索引时混淆。

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