MySQL性能优化综合指南

一、高并发场景优化

1. 连接池配置优化

• 合理设置连接数：根据服务器配置调整max_connections(默认151)
• 使用连接池：推荐HikariCP(Spring Boot默认)、Druid等高性能连接池
• 连接复用：减少连接创建销毁开销

-- 查看当前连接状态
SHOW STATUS LIKE 'Threads_%';

2. 锁优化策略

• 减少锁粒度：使用行锁而非表锁(InnoDB默认行锁)
• 避免长事务：长事务会持有锁过长时间
• 合理使用隔离级别：根据业务需求选择适当隔离级别

3. 缓存层设计

• 查询缓存：MySQL 8.0已移除，建议使用Redis等外部缓存
• 结果缓存：应用层缓存常用查询结果
• 缓冲池优化：调整innodb_buffer_pool_size(通常设为物理内存的50-70%)

二、复杂查询优化

1. 索引策略

• 合理创建索引：覆盖常用WHERE、JOIN、ORDER BY条件
• 复合索引顺序：遵循最左前缀原则
• 避免过度索引：每个索引增加写入开销

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

2. 查询重写

• **避免SELECT ***：只查询必要字段
• 优化JOIN操作：确保JOIN字段有索引
• 使用EXISTS代替IN：大数据集时性能更好

3. 分区表与分表策略

• 水平分区：按范围/哈希/列表分区大表
• 垂直分表：将不常用字段拆分到单独表

三、大数据量处理

1. 批量操作优化

• 批量插入：使用INSERT INTO ... VALUES (...), (...), ...
• 批量更新：使用CASE WHEN或临时表方式
• 分批处理：大数据集分页处理

2. 存储引擎选择

• InnoDB：事务型应用默认选择(MySQL 5.5+)
• MyISAM：只读或读多写少场景(注意表锁问题)
• TokuDB：高压缩比，适合归档数据

3. 归档策略

• 历史数据归档：将不活跃数据迁移到归档表
• 时间序列数据：按时间分表(日表/月表)

四、事务处理优化

1. 事务配置

• 合理设置隔离级别：通常使用READ COMMITTED或REPEATABLE READ
• 自动提交：批量操作时临时关闭SET autocommit=0

-- 查看当前事务隔离级别
SELECT @@transaction_isolation;

2. 死锁预防

• 事务顺序：按固定顺序访问多表
• 锁超时：设置innodb_lock_wait_timeout(默认50秒)
• 死锁检测：innodb_deadlock_detect=ON(默认)

3. 性能监控

• 慢查询日志：开启并分析慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒记录

• 性能模式：使用Performance Schema监控

-- 查看锁等待情况
SELECT * FROM performance_schema.events_waits_current;

五、服务器配置优化

1. 关键参数调整

# InnoDB缓冲池(通常设为物理内存的50-70%)
innodb_buffer_pool_size = 4G

# 日志文件大小(通常设为缓冲池的25%)
innodb_log_file_size = 1G

# 并发线程数
innodb_thread_concurrency = 0 # (0表示无限制)

2. 硬件优化

• 内存：增加内存可显著提升性能
• SSD存储：使用NVMe SSD替代传统硬盘
• CPU：MySQL能有效利用多核CPU

六、架构层面优化

1. 读写分离

• 主从复制：写主库，读从库
• 中间件：使用ProxySQL、MySQL Router等

2. 分库分表

• 水平分片：按特定规则分散数据
• 垂直分库：按业务拆分不同数据库

3. 高可用方案

• 主从切换：MHA、Orchestrator
• 集群方案：InnoDB Cluster、Galera Cluster

总结

MySQL性能优化是一个系统工程，需要从查询优化、索引设计、配置调优、架构设计等多个维度综合考虑。建议采用渐进式优化策略：

1. 先识别瓶颈(慢查询、锁等待、IO压力等)
2. 针对性优化(索引、查询重写、配置调整)
3. 监控优化效果
4. 持续迭代改进

记住：没有放之四海皆准的最优配置，需要根据实际业务负载特点进行调优。