如何让MySQL生产环境执行计划与测试环境保持一致？

1. 强制生成精准的表统计信息 #

ANALYZE TABLE默认采样率可能无法覆盖生产环境的数据分布特征，可调整采样参数后重新收集统计信息（建议低峰期操作）：

• 设置全局采样比例（数值根据表大小调整，越大采样越精准）：

  SET GLOBAL innodb_stats_persistent_sample_pages = 10000;
  SET GLOBAL stats_sample_pages = 10000;
  

• 对目标表重新执行分析：

  ANALYZE TABLE your_table_name;
  -- 批量处理可通过存储过程或脚本遍历所有表
  

• 完成后可将采样参数调回默认值，避免长期资源消耗。

2. 彻底重建InnoDB表消除碎片 #

OPTIMIZE TABLE对InnoDB的效果受外键、主从架构等限制，可采用和测试环境一致的表重建逻辑：

• 单表重建流程：

  CREATE TABLE new_table LIKE your_table_name;
  INSERT INTO new_table SELECT * FROM your_table_name;
  RENAME TABLE your_table_name TO old_table, new_table TO your_table_name;
  DROP TABLE old_table;
  

  该方式能完全消除生产环境长期增删改导致的表空间碎片化、索引结构差异。

3. 清理执行计划缓存 #

MySQL会缓存历史执行计划，即使统计信息更新，旧缓存仍可能生效：

• 清除单表相关缓存：

  FLUSH TABLES your_table_name;
  

• 若开启了查询缓存（MySQL 8.0默认关闭），可全局清理：

  FLUSH QUERY CACHE;
  

• 执行ANALYZE TABLE ... PERSISTENT FOR ALL确保统计信息持久化，避免重启后丢失。

4. 对齐优化器参数细节 #

即使全局配置一致，部分隐式或会话级参数可能存在差异：

• 对比生产与测试环境的optimizer_switch设置：

  SHOW VARIABLES LIKE 'optimizer_switch';
  

• 将生产环境参数调整为测试环境一致：

  SET GLOBAL optimizer_switch = 'xxx=on,yyy=off,...'; -- 替换为测试环境的参数值
  

• 同步join_buffer_size、sort_buffer_size等会话级参数的默认值。

5. 直接导入测试环境的统计信息 #

若上述方法无效，可将测试环境的统计数据直接导入生产，确保两者统计基准完全一致：

• 在测试环境导出统计信息：

  SELECT * FROM mysql.innodb_table_stats WHERE database_name = 'your_db';
  SELECT * FROM mysql.innodb_index_stats WHERE database_name = 'your_db';
  

• 将查询结果转为SQL插入语句，在生产环境先删除对应库的旧统计数据，再插入测试环境的统计信息，最后执行FLUSH TABLES刷新。

6. 验证执行计划一致性 #

调整完成后，用EXPLAIN ANALYZE对比两个环境的执行计划：

EXPLAIN ANALYZE SELECT ...; -- 替换为你的目标查询语句

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