一、内存与缓冲优化

# 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            # 启用性能监控

---

调优验证步骤

1. 压力测试工具
   使用sysbench模拟业务负载（OLTP读写比例推荐8:2）：

   sysbench oltp_read_write --threads=16 --time=300 --mysql-host=localhost --mysql-user=root run
   

2. 监控指标

   • 通过SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'检查缓冲池命中率（目标>95%）
   • 通过SHOW STATUS LIKE 'Threads_%'监控线程状态
   • 使用pt-query-digest分析慢查询日志

3. 动态调整

   SET GLOBAL innodb_flush_log_at_trx_commit=2;  -- 临时调整事务提交策略
   SET GLOBAL tmp_table_size=1024*1024*1024;     -- 调整临时表大小