mysql升级后查询性能下降50%怎么办_对比optimizer_switch参数

MySQL 5.7 升级 8.0 后，优化器开关的“静默陷阱”与性能调优实战

从 MySQL 5.7 升级到 8.0，数据库性能不升反降？这事儿其实挺常见。很多时候，问题就藏在那个不起眼的 optimizer_switch 参数里。升级后，这个参数里至少有12个子开关的默认值悄悄发生了变化。调优的关键，不是盲目地开开关关，而是要导出基准、逐项比对、并结合统计信息与索引设计进行综合验证。

对比 optimizer_switch 前后值，别只看“on/off”列表

升级后，直接执行 SHOW VARIABLES LIKE 'optimizer_switch';，看到那一长串用逗号分隔的 key=value，是不是觉得眼花？这里有个大坑：很多变化是“静默”发生的。比如，mrr（多范围读）在5.7默认是on，到了8.0却变成了off；而像hash_join这种8.0才引入的新特性，旧版本里压根没有。光看列表，很容易忽略某个关键开关被默默关闭了。

所以，正确的做法得讲究点章法：

• 升级前先留“底片”：在生产库执行 SELECT @@optimizer_switch;，把结果完整保存为 baseline.txt。
• 升级后立刻“找不同”：在测试环境或新生产库执行同样命令，用diff工具逐行比对。要特别关注那些变成 =off 的项，尤其是 mrr、use_index_extensions、derived_merge 这几个“惯犯”。
• 警惕“差不多”先生：index_merge=on,index_merge_union=on 和 index_merge=on,index_merge_union=off 看起来只差一点，但可能导致联合索引完全不被优化器选用，性能天差地别。

临时开启可疑开关验证执行计划是否恢复

当你发现某条在5.7跑得飞快的查询，到了8.0后EXPLAIN显示type=ALL（全表扫描），预估行数rows暴涨，先别急着改SQL或加索引。更高效的办法是，先锁定几个高概率的“嫌疑开关”，在会话级别临时开启验证。

具体操作时，有几个细节必须注意：

• 务必使用SESSION级别：用 SET SESSION optimizer_switch='mrr=on,use_index_extensions=on'; 这样的语句，只影响当前连接，测试完断开或重置即可，避免影响其他业务。
• 遵循“单一变量”原则：每次只开启1到2个开关。比如先试mrr=on，如果执行计划没改善，再试use_index_extensions=on。一次开一堆，出了问题你都不知道该归因于谁。
• 辩证看待新特性：像hash_join=on这类8.0的新能力，开启后可能让某些JOIN查询大幅提速，这说明旧版本是靠嵌套循环硬扛过来的。但别忘了同时观察内存消耗是否同步上涨，避免拆东墙补西墙。

禁用 index_merge 可能反而让查询变快

这听起来有点反直觉：index_merge（索引合并）在8.0默认开启，本意是让优化器能同时利用多个单列索引。但理想很丰满，现实很骨感。实际操作中，它经常导致优化器把两个单列索引的结果做交集或并集，中间产生庞大的临时结果集，最终速度反而比不上直接使用一个设计良好的复合索引。

怎么判断是不是这个问题？看EXPLAIN输出里Extra列，如果出现了 Using intersect(...) 或 Using union(...)，那就是索引合并的信号。

验证和解决的步骤很清晰：

• 会话级关闭验证：执行 SET SESSION optimizer_switch='index_merge=off';。
• 观察执行计划变化：再次运行EXPLAIN，看key字段是否切换回了你预设的复合索引，预估rows是否显著下降。
• 制定长期方案：如果关闭后确实有效，长期解决方案绝不是简单地全局关闭index_merge。而是要去审视表结构，删除那些引发合并的冗余单列索引，并补充上真正匹配查询条件的复合索引。例如，对于 WHERE a=1 AND b>10 ORDER BY c 这样的查询，建立一个 INDEX(a,b,c) 往往是最优解。

sql_mode 改变会间接干扰 optimizer_switch 生效逻辑

这一点容易被忽略：sql_mode本身不直接控制执行计划，但它会改变SQL语句的语义和执行行为，尤其是类型转换规则，从而间接让优化器“失效”。

8.0默认包含了 STRICT_TRANS_TABLES 等更严格的模式。举个例子，假设有个status字段是VARCHAR类型，但查询写成了 WHERE status = 1。在5.7里，数据库可能默默地把数字1转换成字符串‘1’去利用索引。但在8.0的严格模式下，它可能直接放弃使用索引，转而进行全表扫描。这时候，你折腾optimizer_switch是没用的，因为问题在优化器“工作”之前就发生了。

排查时需要顺着这个思路：

• 确认当前模式：执行 SELECT @@sql_mode;，看看是否包含 STRICT_TRANS_TABLES。
• 检查类型一致性：针对慢查询中的等值条件，逐一核对字段类型和传入值的数据类型是否严格一致。字符串型的ID、状态码是重灾区。
• 临时测试：可以在会话级别临时移除严格模式进行测试：SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'STRICT_TRANS_TABLES',''));，然后再观察EXPLAIN是否恢复了索引访问。

说到底，性能问题的根源往往是多方面的。真正卡住脖子的，通常不是optimizer_switch里某一个开关，而是优化器行为、统计信息准确性、索引设计合理性以及数据类型定义这四者之间的错位与不匹配。即便你把所有开关都手动调回5.7的默认值，但如果表的统计信息过时（没跑ANALYZE TABLE）、持久化统计没开（innodb_stats_persistent），或者缺少关键的复合索引，执行计划照样会一塌糊涂。因此，系统化的视角和综合性的调优，才是解决问题的关键所在。