一、硬件与系统层优化：夯实性能基石
硬件选型策略
- CPU：读密集型场景选择多核 CPU（如 32 核）；写密集型场景选择高主频 CPU（如 3.5GHz+）。
- 内存：建议≥64GB，缓冲池命中率≥99% 是性能关键指标。
- 存储：必用 NVMe SSD，IOPS≥5 万，避免 HDD 的 I/O 瓶颈。RAID10 配置兼顾性能与冗余。
操作系统级优化
- 内核参数调整（/etc/sysctl.conf）：
fs.file-max = 1000000
net.core.somaxconn = 65535
vm.swappiness = 1 # 减少 Swap 使用
- 文件系统：XFS 优于 EXT4（处理大文件更高效），挂载选项加 noatime 减少元数据写入。
- 关闭 NUMA：避免跨节点内存访问延迟，BIOS 中关闭或启动参数加 numa=off。
二、内存配置优化：拒绝无效资源浪费
核心参数配置
| 参数 | 推荐值 | 作用 |
|---|---|---|
| innodb_buffer_pool_size | 物理内存的 70%~80% | 缓存数据与索引，减少磁盘 I/O |
| innodb_log_buffer_size | 64M~128M | 加速 Redo 日志写入，写密集型应用必调 |
| key_buffer_size | 128M（仅 MyISAM 需调） | 缓存 MyISAM 索引 |
避坑指南
- 缓冲池分区：高并发场景启用多缓冲池实例（innodb_buffer_pool_instances = 8），减少锁竞争。
- OOM 预防：总内存分配 ≤ 物理内存的 90%，预留空间给操作系统和连接线程。
三、InnoDB 引擎调优：事务与 I/O 的平衡艺术
日志与写入优化
- 事务安全 vs 性能：
- innodb_flush_log_at_trx_commit = 1：主库必选（ACID 保障）
- innodb_flush_log_at_trx_commit = 2：从库或允许秒级数据丢失的场景
- 日志文件大小：innodb_log_file_size = 1~4GB，过大影响恢复速度，过小导致频繁 Checkpoint。
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 = 1 # MySQL 8.0 已移除，仅适用于旧版本
# query_cache_size = 96M # 占用总内存的 0.4%–0.8%
# query_cache_limit = 2M # 单条结果集上限 2MB
注：调整后需重启 MySQL，并使用 sysbench 或 tpcc-mysql 进行压力测试。
结语：性能优化是持续旅程
核心原则
- 渐进调整：一次只改一个参数，用监控数据验证效果。
- 权衡之道：在数据一致性（ACID）、查询速度、硬件成本间找平衡点。
- 场景为王：电商库侧重并发连接，日志分析库侧重磁盘 I/O 吞吐。
最后告诫
任何优化都需基准测试支撑！盲目套用参数可能导致性能倒退。
推荐工具链
- 压测工具：sysbench、tpcc-mysql
- 分析工具：pt-query-digest、Percona Toolkit
- 监控：PMM + Grafana