mysql版本升级后的性能调优与配置优化

mysql 8.0 升级后需重估 innodb_buffer_pool_size，建议设为物理内存的50%–75%并预留≥2GB给系统；同时注意 sql_mode 严格模式、performance_schema 开销、字符集 collation 变更及 mysql_upgrade 遗留问题。

MySQL 8.0 升级后 innodb_buffer_pool_size 必须重估

升级到 MySQL 8.0 后，InnoDB 的内存管理机制更激进，默认启用 innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup，但若 innodb_buffer_pool_size 沿用旧值（比如 512MB），可能引发频繁刷盘、冷启动慢、甚至 OOM。8.0 对 Buffer Pool 分区和预热更敏感，原配置在高并发下容易成为瓶颈。

• 建议值 = 物理内存的 50%–75%，但需预留至少 2GB 给 OS + 其他进程（如 Percona Toolkit、备份工具）
• 若启用了 innodb_buffer_pool_instances > 1（默认为 8），确保 innodb_buffer_pool_size 是 innodb_buffer_pool_instances 的整数倍，否则会自动向下取整并报 warning
• 验证方式：

  select @@innodb_buffer_pool_size / 1024 / 1024 AS mb;

  并对比 SHOW ENGINE INNODB STATUSG 中的 “BUFFER POOL AND MEMORY” 部分是否接近设定值

MySQL 8.0 默认启用 sql_mode 严格模式，间接拖慢批量写入

升级后未显式修改 sql_mode，MySQL 8.0 默认启用 STRICT_TRANS_tableS,NO_ENGINE_SUBSTITUTION 等，导致隐式类型转换失败、零日期拒绝插入——表面看是应用报错，实则因事务回滚+重试引发锁等待和 QPS 下降。

• 排查方法：检查慢日志中是否有大量 Error 1292 或 ERROR 1366，再查对应 SQL 是否含 INSERT ... VALUES ('2023-00-00', ...) 类写法
• 临时缓解（仅测试环境）：

  SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_date,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

  ——注意移除 NO_ZERO_DATE 前确认业务真能容忍 ‘0000-00-00’

• 长期方案：应用层清洗数据，或用 ALTER TABLE ... MODIFY column dt DATE default '1970-01-01' 显式定义兜底值

performance_schema 在 8.0 中默认全开，开销不可忽略

MySQL 8.0 默认启用全部 performance_schema instruments 和 consumers（包括 events_statements_history_long），尤其在高 QPS 场景下，单实例 CPU 使用率可能无故升高 5–15%，且 setup_actors 默认监控所有用户，加剧 mutex 竞争。

• 关键关闭项（生产推荐）：

  UPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO' WHERE NAME LIKE 'statement/%' AND NAME NOT IN ('statement/sql/select', 'statement/sql/insert', 'statement/sql/update', 'statement/sql/delete');

• 限制采集范围：

  DELETE FROM performance_schema.setup_actors WHERE HOST != 'localhost'; INSERT INTO performance_schema.setup_actors VALUES ('%', 'root', '%', 'YES', 'YES');

• 验证效果：SELECT count(*) FROM performance_schema.events_statements_current; 应明显下降（通常从数万降至数百）

字符集与排序规则变更引发索引失效和连接慢

MySQL 8.0 默认字符集从 latin1 变为 utf8mb4，默认 collation 从 latin1_swedish_ci 变为 utf8mb4_0900_ai_ci。若表未显式指定 collation，升级后 JOIN 或 WHERE 中跨字段比较（如 t1.name = t2.name）可能因 collation 不一致触发隐式转换，导致索引失效。

• 快速检查：

  SELECT table_name, column_name, collation_name FROM information_schema.columns WHERE table_schema = 'your_db' AND collation_name NOT LIKE 'utf8mb4_0900_%';

• 安全迁移命令（逐表执行，避免锁表）：

  ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

• 特别注意：utf8mb4_0900_ai_ci 区分大小写（binary-aware），旧应用若依赖 _ci 的“不区分大小写”语义，需同步调整查询逻辑或改用 utf8mb4_0900_as_cs

实际调优时最容易被跳过的，是升级后未重跑 mysql_upgrade（8.0.16+ 已废弃，但部分系统仍残留旧脚本），以及忽略 information_schema 视图结构变化对监控脚本的影响。这些不会立刻报错，但会让慢日志解析、空间统计、连接分析等后台任务逐渐失准。