MySQL 8执行相同SQL比MySQL 5.6慢的配置优化问题咨询

核心原因 #

MySQL 8.0对IN子查询默认启用了半连接（Semi-join）优化和子查询物化策略，替代了5.6默认的DEPENDENT SUBQUERY执行路径。当前场景下优化器成本计算出现偏差，选择了子查询物化后全表扫描主表关联的执行计划，导致性能劣化。

调整方案 #

你可以通过修改MySQL 8.0的优化器开关参数，强制回退到5.6的子查询执行逻辑：

• 优先尝试关闭子查询物化优化
  会话级验证执行：

  set session optimizer_switch='materialization=off';
  

  验证问题SQL执行计划恢复为DEPENDENT SUBQUERY、性能达标后，可在RDS参数组中全局配置该参数。
• 如关闭物化后无效果，追加关闭半连接优化
  会话级验证执行：

  set session optimizer_switch='materialization=off,semijoin=off';
  

  该配置会完全对齐5.6的子查询处理逻辑，适配绝大多数存量业务SQL的执行计划。
• （可选）微调优化器成本模型
  若不想完全关闭8.0的新优化特性，可调整优化器成本权重：调低io_block_read_cost、memory_block_read_cost的取值，让优化器更倾向于走索引查找而非全表扫描关联，避免成本计算偏差。

生效流程 #

1. 先在测试会话中修改参数验证业务SQL性能和执行计划符合预期
2. 确认无其他业务SQL性能劣化后，在RDS控制台的参数组中修改对应配置
3. 重启RDS实例让全局配置生效

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