MySQL 5.7升级8.0后性能下降排查方法

MySQL 8.0 出现性能下滑，问题通常并不在于“版本升级”本身，而是旧配置、新默认行为以及更严格的优化器规则叠加在一起，把过去被掩盖的问题集中暴露出来。排查时，应重点关注执行计划、持久化配置和隐式转换这几个关键点：如果统计信息已经过期，需要使用 ANALYZE TABLE WITH SYNC 修复；如果 sort_buffer_size 偏小，容易触发 filesort，这时既要调整参数，也要补充合适的复合索引；此外，PERSIST 配置的优先级高于 my.cnf，必须先确认实际生效的是哪一项；还有一种常被忽视的情况是隐式类型转换，在 STRICT 模式下会直接影响索引命中，因此字段类型和参数类型应尽量保持一致，或者使用虚拟列索引处理。

性能下降并不是 MySQL 8.0 本身变慢，而是旧配置、新默认行为和优化器更严格三者叠加后，暴露了历史遗留问题。因此，排查时需要直接从执行计划、持久化配置和隐式转换三个方向切入。

EXPLAIN 显示 rows 暴涨或 type 退化为 ALL 时如何处理

这是非常明确的信号，说明优化器可能误判了索引选择性，根源通常在于 INNODB_TABLESTATS.last_update 仍停留在升级之前。

• 先检查统计信息更新时间：SELECT last_update FROM INFORMATION_SCHEMA.INNODB_TABLESTATS WHERE TABLE_NAME = 'your_table'。如果时间早于升级时间，说明统计信息已经失效。
• 不要仅依赖 innodb_stats_auto_recalc=ON，因为它只会在单次变更超过 10% 行数时触发，对冷表或配置表往往不会生效。
• 执行 ANALYZE TABLE your_table WITH SYNC，并使用 WITH SYNC 避免后台异步刷新带来的延迟。
• 同时验证基数是否失真：查看 SHOW INDEX FROM your_table 中的 Cardinality，再对比 SELECT COUNT(DISTINCT your_col) FROM your_table。如果相差超过 10 倍，通常可以确认统计信息失效。

sort_buffer_size 不足导致 Using filesort 明显增多

MySQL 8.0.20 及以上版本已彻底废弃 max_length_for_sort_data，改为全字段内存排序模型。如果 sort_buffer_size 不足，就更容易直接写入磁盘临时文件。

• 先检查 EXPLAIN 输出中 Extra 列是否包含 Using filesort；只有不包含时，才能说明真正走了索引排序。
• 临时修复方式是为 WHERE + ORDER BY 涉及的字段建立复合索引。例如 WHERE a=1 ORDER BY b,c，可建立 INDEX(a,b,c)。
• 尽量避免 SELECT * 进一步加重问题。查询字段越多，filesort 占用的内存压力越大，可以先尝试改为 SELECT a,b,c，观察是否恢复到毫秒级。
• sort_buffer_size 按连接分配，设置过大容易造成内存占用激增。通常建议从 512K 开始逐步调整，并结合 EXPLAIN ANALYZE 观察实际排序内存使用情况。

SET PERSIST 写入的配置覆盖 my.cnf

MySQL 8.0 的 PERSIST 机制会将变量写入 mysqld-auto.cnf，而该文件的优先级高于 my.cnf，因此经常出现“看起来改了配置，但实际没有生效”的情况。

• 查看当前实际生效的持久化变量：SELECT * FROM performance_schema.persisted_variables。
• 查看变量加载来源和顺序：SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH FROM performance_schema.variables_info WHERE VARIABLE_SOURCE IN ('PERSISTED', 'CONFIG')。
• 如果发现关键变量，例如 sort_buffer_size，来源显示为 PERSISTED，而只修改了 my.cnf，说明配置实际上并未按预期生效。
• 必要时可临时清空持久化配置：RESET PERSIST，之后重启再验证，但操作时需要谨慎。

WHERE 条件中字符串与 INT 字段比较后突然变慢

MySQL 8.0 默认开启 STRICT_TRANS_TABLES，对隐式类型转换的容忍度更低，优化器可能直接放弃索引访问路径，使 type 退化为 ALL。

• 可先通过 SELECT @@sql_mode 确认是否包含 STRICT_TRANS_TABLES，再使用 SHOW CREATE TABLE 检查字段类型与传入参数类型是否严格一致。
• 典型情况是：WHERE user_id = '123'，当 user_id 为 INT 时，在 MySQL 5.7 中可能还能走索引，而在 8.0 中则可能直接退化为全表扫描。
• JSON 字段的情况更需要注意。例如 WHERE JSON_CONTAINS(meta, '"admin"')，如果 JSON 中存储的是数字 123，或者是不带引号的字符串 admin，在不匹配时就可能导致逐行解析整个 blob。
• 更彻底的处理方式是建立 STORED 虚拟列并添加索引，例如 role_flag TINYINT AS (JSON_CONTAINS(meta, '"admin"')) STORED，之后改为查询 WHERE role_flag = 1。

很多情况下，最容易被忽略的恰恰是这一层：MySQL 8.0 的“默认行为”并不是固定不变的静态设置，而是一套会动态适配的结果。例如在 8.0 中，innodb_buffer_pool_size 往往会有 15% 到 20% 被元数据占用；再如，performance_schema.data_locks 记录的是所有事务锁，而不仅仅是阻塞锁，稍有不慎就可能把全局互斥量推到高压状态。归根结底，问题不在版本本身，而在于是否真正看清了这些变化，以及它们是如何直接作用到数据和查询上的。