MySQL 深度性能优化配置实战指南

I/O 策略优化

innodb_flush_method = O_DIRECT # 绕过 OS 缓存，避免双重缓冲
innodb_io_capacity = 5000 # SSD 建议值
innodb_file_per_table = ON # 独立表空间便于管理

四、查询与索引优化：根治慢查询痼疾

索引设计黄金法则

• 联合索引：遵循最左前缀原则（INDEX (a,b,c) 支持 WHERE a=1 AND b=2，不支持 b=2）。
• 覆盖索引：SELECT 字段尽量包含在索引中，避免回表（EXPLAIN 的 Extra 列显示 Using index）。
• 禁忌：
  • ❌ 对低选择性字段建索引（如性别）
  • ❌ LIKE '%xxx%' 前导通配符

查询优化实战

• 分页优化：
  • 低效：SELECT * FROM logs LIMIT 1000000, 20;
  • 高效：SELECT * FROM logs WHERE id > 1000000 ORDER BY id LIMIT 20;
• 临时表控制：

  tmp_table_size = 64M
  max_heap_table_size = 64M # 避免内存临时表转磁盘
  

五、连接与缓存管理：高并发下的生存法则

连接池配置

max_connections = 1000 # 根据业务压力调整
thread_cache_size = 64 # 复用线程，减少创建开销
wait_timeout = 600 # 自动回收空闲连接（秒）

• 连接池工具：应用层用 HikariCP，中间件用 ProxySQL。

缓存策略

• 查询缓存：MySQL 8.0 已移除！旧版本建议关闭（query_cache_type = 0），高并发写入下易成瓶颈。
• 表缓存：table_open_cache = 2000，避免频繁开表。

六、监控与持续优化：数据驱动的调优闭环

监控三板斧

• 慢查询日志：

  slow_query_log = ON
  long_query_time = 1 # 捕获>1 秒的查询
  

• 性能视图：
  • SHOW ENGINE INNODB STATUS; – 查看锁、缓冲池状态
  • SHOW GLOBAL STATUS LIKE 'Threads_connected';
• 专业工具：Percona PMM + Prometheus + Grafana（监控大盘示例）。

维护动作

• 碎片整理：每月对核心表执行 OPTIMIZE TABLE orders;。
• 备份策略：XtraBackup 全量+Binlog 增量，恢复时验证 RTO（恢复时间目标）。

终极配置模板（my.cnf）

[mysqld]

## 内存优化
innodb_buffer_pool_size = 48G # 64GB 内存的 75%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 128M

## 写入与 I/O
innodb_flush_method = O_DIRECT
innodb_io_capacity = 5000
innodb_flush_log_at_trx_commit = 1

## 连接管理
max_connections = 1200
thread_cache_size = 100
wait_timeout = 300

## 查询优化
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = 1
# query_cache_type = 0 # MySQL 8.0+ 已移除，旧版本慎用
# query_cache_size = 96M
# query_cache_limit = 2M

注：调整后需重启 MySQL，并使用 sysbench 或 tpcc-mysql 进行压力测试。

结语：性能优化是持续旅程

核心原则

• 渐进调整：一次只改一个参数，用监控数据验证效果。
• 权衡之道：在数据一致性（ACID）、查询速度、硬件成本间找平衡点。
• 场景为王：电商库侧重并发连接，日志分析库侧重磁盘 I/O 吞吐。

最后告诫

任何优化都需基准测试支撑！盲目套用参数可能导致性能倒退。

推荐工具链

• 压测工具：sysbench、tpcc-mysql
• 分析工具：pt-query-digest、Percona Toolkit
• 监控：PMM + Grafana