一、核心内存参数调优

1.1 InnoDB缓冲池(Buffer Pool)优化

缓冲池是MySQL性能调优的重中之重，其大小直接影响磁盘I/O压力。建议按以下规则配置：

-- 查看当前缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_%';

• 配置建议：物理内存的50-70%（专用数据库服务器）
• 动态调整：MySQL8支持动态修改（无需重启）

  SET GLOBAL innodb_buffer_pool_size=8589934592; -- 8GB示例

• 优化技巧：
  • 启用多实例缓冲池（innodb_buffer_pool_instances=8）
  • 监控缓冲池命中率（目标>99%）

    SELECT (1-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests))*100 
    AS hit_ratio FROM performance_schema.global_status;

1.2 关键内存区域配置

• 排序缓冲区(sort_buffer_size)：
  • 默认256KB，复杂查询可增至2-4MB
  • 警告：过大可能导致内存碎片
• 连接内存(thread_stack/tmp_table_size)：

  -- 典型连接内存配置
  SET GLOBAL thread_stack=256K;
  SET GLOBAL tmp_table_size=64M;
  SET GLOBAL max_heap_table_size=64M;

• 查询缓存(query_cache_type)：
  • MySQL8已移除查询缓存，建议使用Redis等外部缓存

二、I/O性能优化策略

2.1 日志文件配置

• 重做日志(Redo Log)：
  • 默认128MB×2文件，建议增至1-2GB×3-4文件

    -- 优化配置示例
    SET GLOBAL innodb_log_file_size=1G;
    SET GLOBAL innodb_log_files_in_group=3;

• 双写缓冲(Doublewrite Buffer)：
  • 默认启用，保障数据页写入完整性
  • SSD环境下可考虑禁用（innodb_doublewrite=0）

2.2 存储引擎优化

• 页大小选择：
  • 默认16KB，大数据场景可尝试32KB

    -- 修改需重建表空间
    [mysqld]
    innodb_page_size=32K

• 自适应哈希索引：
  • 默认启用，监控使用率

    SHOW ENGINE INNODB STATUS\G
    -- 查找"HASH INDEX USE"部分

三、并发控制优化

3.1 连接管理

• 最大连接数：
  • 计算公式：max_connections = (可用内存-系统保留)/每个连接内存
  • 典型值：500-2000（根据硬件调整）

    SET GLOBAL max_connections=1000;

• 线程缓存：

  SET GLOBAL thread_cache_size=100; -- 典型值

3.2 锁优化

• 全局锁超时：

  SET GLOBAL lock_wait_timeout=300; -- 默认50秒

• 死锁检测：
  • 启用InnoDB死锁日志（innodb_print_all_deadlocks=ON）
  • 分析SHOW ENGINE INNODB STATUS中的死锁信息

四、高级性能参数

4.1 变更缓冲区(Change Buffer)

• 优化非唯一二级索引的修改操作
• 配置建议：

  SET GLOBAL innodb_change_buffer_max_size=25; -- 默认25%

4.2 并行查询

• MySQL8.0.18+支持并行查询
• 典型配置：

  SET GLOBAL innodb_parallel_read_threads=4;

4.3 资源组管理

• 将不同查询分配到不同CPU核心
  ```sql
  — 创建资源组示例
  CREATE RESOURCE GROUP cpu_intensive TYPE=USER
  VCPU=0-3 THREAD_PRIORITY=10;

— 将查询绑定到资源组
SET RESOURCE GROUP cpu_intensive FOR SELECT * FROM large_table;

# 五、监控与持续优化
## 5.1 性能模式(Performance Schema)
- 启用关键监控项：
  ```sql
  UPDATE performance_schema.setup_instruments 
  SET ENABLED='YES', TIMED='YES' 
  WHERE NAME LIKE 'wait/%';

• 关键查询：

  -- 等待事件分析
  SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT 
  FROM performance_schema.events_waits_summary_global_by_event_name 
  ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

5.2 慢查询日志优化

• 配置建议：

  SET GLOBAL slow_query_log='ON';
  SET GLOBAL long_query_time=2; -- 秒
  SET GLOBAL log_queries_not_using_indexes='ON';

5.3 定期维护脚本

#!/bin/bash
# 定期维护脚本示例
mysql -e "ANALYZE TABLE large_tables;"
mysql -e "FLUSH QUERY CACHE;"  # MySQL5.7及之前
mysql -e "FLUSH LOGS;"  # 轮换日志

六、典型场景优化方案

6.1 OLTP系统优化

• 配置重点：

  SET GLOBAL innodb_flush_log_at_trx_commit=1;
  SET GLOBAL sync_binlog=1;
  SET GLOBAL innodb_buffer_pool_size=12G; -- 32GB内存服务器

6.2 OLAP系统优化

• 配置重点：

  SET GLOBAL innodb_buffer_pool_size=24G; -- 64GB内存服务器
  SET GLOBAL tmp_table_size=256M;
  SET GLOBAL max_heap_table_size=256M;
  SET GLOBAL innodb_change_buffering=none; -- 减少写缓冲

6.3 高并发写入优化

• 配置重点：

  SET GLOBAL innodb_autoinc_lock_mode=2; -- 交错模式
  SET GLOBAL binlog_group_commit_sync_delay=50; -- 微秒级延迟提交

七、避坑指南

1. 避免过度调优：每次只修改1-2个参数，观察效果后再继续
2. 参数依赖检查：某些参数需要配套修改（如增大缓冲池时需调整innodb_buffer_pool_instances）
3. 版本差异注意：MySQL8.0.26+对部分参数默认值做了优化
4. 云数据库限制：云服务商可能限制部分参数修改范围

八、验证优化效果

使用以下方法验证优化效果：

1. 基准测试：

   mysqlslap --concurrency=50 --iterations=10 --query="SELECT * FROM large_table" --user=test --password=test

2. 监控指标对比：
   • QPS/TPS变化
   • 等待事件分布
   • 缓存命中率
3. 业务指标：
   • 关键页面加载时间
   • 事务完成率

通过系统性的参数优化，典型生产环境可实现30-50%的性能提升。建议每季度进行一次全面性能评估，根据业务发展动态调整配置。