mysql升级后如何优化新版本的性能_mysql优化方案

mysql 8.0 升级后需手动调优 innodb_buffer_pool_size（关闭 innodb_dedicated_server，设为内存50%–75%并预留2GB）、慎用新默认排序规则、精简 performance_schema 开销、确保 sys schema 依赖项启用，避免多配置叠加导致隐性性能衰减。

MySQL 8.0 升级后 innodb_buffer_pool_size 必须重调

MySQL 8.0 默认启用 innodb_dedicated_server，会自动根据物理内存设 innodb_buffer_pool_size，但这个值常偏保守——尤其当服务器混跑其他服务（如 redis、nginx）时，会导致 Buffer Pool 实际可用内存不足，大量磁盘随机读出现。

实操建议：

• 关闭 innodb_dedicated_server（在 my.cnf 中显式设为 OFF），手动控制更稳妥
• innodb_buffer_pool_size 建议设为物理内存的 50%–75%，但需预留至少 2GB 给 OS 和其他进程
• 若使用 NUMA 架构（常见于多路 CPU 服务器），添加 innodb_buffer_pool_instances = 8 或更高，避免单实例锁争用
• 升级后首次启动，观察 SHOW ENGINE INNODB STATUS 中的 Buffer pool hit rate，低于 99.5% 就说明不够用

MySQL 8.0 的默认排序规则（utf8mb4_0900_as_cs）拖慢 ORDER BY 和 GROUP BY

新默认 collation 比旧版 utf8mb4_unicode_ci 更严格、更耗 CPU，尤其在无索引字段上做排序时，性能下降可达 3–5 倍。

实操建议：

• 对已存在的业务表，不强制改 collation；只需在 CREATE table 或 ALTER TABLE 时显式指定 COLLATE utf8mb4_unicode_ci（或更轻量的 utf8mb4_general_ci，仅限 MySQL 8.0.31+ 支持）
• 检查慢查询日志中含 using filesort 的语句，优先给 ORDER BY 字段加联合索引，而非依赖 collation 优化
• 应用层字符串比较逻辑若不区分大小写和重音，可改用 BINARY 转换后比对，绕过 collation 开销

performance_schema 在 8.0 中默认全开，但多数监控项实际无用且吃 CPU

MySQL 8.0 启动即加载全部 instruments 和 consumers，即使没开任何监控工具，也会带来 3%–8% 的额外 CPU 开销，尤其在高并发短连接场景下明显。

实操建议：

• 编辑 my.cnf，在 [mysqld] 下添加：

  performance_schema = ON performance_schema_instrument = 'memory/%=OFF' performance_schema_consumer_events_stages_current = OFF performance_schema_consumer_events_statements_history = OFF performance_schema_consumer_events_transactions_current = OFF

• 保留 events_statements_summary_by_digest 和 table_io_waits_summary_by_table，这两项对定位慢查和热点表最关键
• 避免在生产库长期开启 stage/sql/ 类 instruments，它们会显著拉低 QPS

升级后 sys schema 视图可能返回空或卡住

MySQL 8.0 的 sys schema 依赖 performance_schema 的实时采集，若上一步关了太多 instruments，或 performance_schema 内存不足（performance_schema_max_table_instances 太小），sys 视图就会查不到数据甚至超时。

实操建议：

• 确认 performance_schema_max_table_instances ≥ 5000（默认 5000，但大库建议设为 10000）
• 运行 CALL sys.ps_setup_enable_consumer('global_instrumentation'); 确保基础 consumer 已启用
• 不要直接依赖 sys.schema_tables_with_full_table_scans 等视图做告警，改用 performance_schema.table_io_waits_summary_by_table + 自定义阈值更稳定
• 如果只是临时诊断，可临时执行 UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_current';，用完即关

真正影响性能的往往不是某条 SQL 或某个参数，而是多个“合理但叠加”的配置共同导致的隐性衰减。比如 innodb_buffer_pool_size 看似够用，但加上 performance_schema 的内存占用、NUMA 不均衡、以及新 collation 的 CPU 消耗，QPS 就可能掉 20%。上线后盯住 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free' 和 Threads_created 这两个指标，比看平均响应时间更能暴露真实瓶颈。