LNMP环境中MySQL调优指南

基础配置调优

1. 内存相关参数

# 缓冲池大小，建议为可用内存的50-70%
innodb_buffer_pool_size = 4G

# 日志缓冲区大小
innodb_log_buffer_size = 16M

# 查询缓存(MySQL 8.0已移除)
# query_cache_size = 0

# 排序缓冲区
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

2. 连接相关参数

# 最大连接数
max_connections = 200

# 线程缓存
thread_cache_size = 20

# 连接超时
wait_timeout = 300
interactive_timeout = 300

3. InnoDB引擎优化

# 日志文件大小
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# 刷新方式
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1  # 1为最安全，2为性能折中

# IO线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 并发控制
innodb_thread_concurrency = 0
innodb_io_capacity = 200
innodb_io_capacity_max = 2000

高级调优策略

1. 索引优化

• 使用EXPLAIN分析查询执行计划
• 为常用查询条件添加合适索引
• 避免过度索引，定期检查冗余索引
• 考虑使用覆盖索引减少回表操作

2. 查询优化

• 避免SELECT *，只查询需要的列
• 优化复杂JOIN操作
• 合理使用子查询和临时表
• 使用LIMIT限制结果集大小

3. 表结构优化

• 选择合适的数据类型
• 规范化与反规范化平衡
• 对大表考虑分区(Partitioning)
• 定期执行OPTIMIZE TABLE或ALTER TABLE ... ENGINE=InnoDB

监控与维护

1. 性能监控工具

• SHOW STATUS和SHOW VARIABLES
• SHOW ENGINE INNODB STATUS
• mysqldumpslow分析慢查询日志
• pt-query-digest(Percona工具)
• MySQL Enterprise Monitor或Percona PMM

2. 定期维护

# 分析表
ANALYZE TABLE table_name;

# 检查表
CHECK TABLE table_name;

# 优化表
OPTIMIZE TABLE table_name;

3. 慢查询日志配置

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

LNMP环境特定优化

1. PHP连接池配置

• 调整PHP-FPM的pm.max_children与MySQL的max_connections匹配
• 使用持久连接(考虑pconnect)

2. Nginx缓存策略

• 对静态内容设置适当缓存
• 考虑使用FastCGI缓存减轻数据库压力

3. 整体架构优化

• 对高读取场景考虑主从复制
• 对写入密集场景考虑分片(Sharding)
• 引入Redis等缓存层减少数据库访问

注意事项

1. 调优前做好备份
2. 每次只调整少量参数并观察效果
3. 生产环境先在测试环境验证
4. 不同MySQL版本参数可能有差异
5. 根据实际负载特点调整参数

通过以上调优措施，可以显著提升LNMP环境中MySQL的性能表现。