MySQL 8.x的性能优化文档整理
一、内存与缓冲优化
# InnoDB缓冲池（内存的60%-80%）
innodb_buffer_pool_size = 12G # 核心参数
innodb_buffer_pool_instances = 8 # 8核CPU建议分8个实例# 日志缓冲区与Redo日志
innodb_log_buffer_size = 256M # 事务日志缓冲区
innodb_log_file_size = 4G # Redo日志文件大小
innodb_log_files_in_group = 3 # Redo日志组文件数量# 内存临时表
tmp_table_size = 512M # 内存临时表上限
max_heap_table_size = 512M # 内存表最大值
二、连接与线程优化
# 连接控制
max_connections = 1000 # 最大连接数
thread_cache_size = 100 # 线程缓存数（建议max_connections的10%）
table_open_cache = 2000 # 表缓存数量# InnoDB线程优化
innodb_thread_concurrency = 16 # 并发线程数（建议CPU核心数*2）
innodb_read_io_threads = 16 # 读线程数（8核CPU建议16）
innodb_write_io_threads = 16 # 写线程数（8核CPU建议16）
三、事务与日志优化
# 事务持久化策略
innodb_flush_log_at_trx_commit = 1 # 生产环境建议1（安全优先）/测试环境可设2
sync_binlog = 1 # Binlog同步策略# Binlog配置
binlog_expire_logs_seconds = 2592000 # Binlog保留30天（替代expire_logs_days）
binlog_cache_size = 8M # 事务Binlog缓存
四、查询与锁优化
# 查询缓冲区
sort_buffer_size = 8M # 排序缓冲区（避免过大）
read_buffer_size = 8M # 全表扫描缓冲区
join_buffer_size = 8M # JOIN操作缓冲区# 锁与超时
innodb_lock_wait_timeout = 50 # 行锁等待超时（秒）
lock_wait_timeout = 120 # 元数据锁超时
五、网络与安全
max_allowed_packet = 64M # 最大数据包限制
skip_name_resolve = ON # 禁用DNS解析加速连接
lower_case_table_names = 1 # 表名不区分大小写
六、监控与诊断
# 慢查询日志
slow_query_log = ON # 启用慢查询日志
long_query_time = 1 # 记录超过1秒的查询
log_queries_not_using_indexes = ON # 记录无索引查询# Performance Schema
performance_schema = ON # 启用性能监控
调优验证步骤
-
压力测试工具
使用sysbench模拟业务负载（OLTP读写比例推荐8:2）：sysbench oltp_read_write --threads=16 --time=300 --mysql-host=localhost --mysql-user=root run -
监控指标
- 通过
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'检查缓冲池命中率（目标>95%） - 通过
SHOW STATUS LIKE 'Threads_%'监控线程状态 - 使用
pt-query-digest分析慢查询日志
- 通过
-
动态调整
SET GLOBAL innodb_flush_log_at_trx_commit=2; -- 临时调整事务提交策略 SET GLOBAL tmp_table_size=1024*1024*1024; -- 调整临时表大小