LNMP下MySQL性能调优策略

一、硬件资源优化

硬件是数据库性能的基础，需根据业务负载选择合适的配置：

操作系统配置直接影响MySQL的资源利用率：

内核参数调整：
- 关闭NUMA特性（numactl --interleave=all），避免内存访问不均衡导致的性能下降；
- 调整vm.swappiness（设为10-30），减少系统使用交换分区（Swap）的概率；
- 优化vm.dirty_background_ratio（设为5-10）和vm.dirty_ratio（设为10-20），控制脏页刷新频率，平衡性能与数据安全性。
文件系统选择：使用XFS文件系统（支持并行I/O，性能优于ext4），挂载时添加noatime选项（避免频繁更新访问时间）。
网络优化：启用TCP校验和卸载（TOE）、调整TCP窗口大小（如net.core.rmem_max=16777216），提升网络传输效率。

MySQL配置文件（my.cnf/my.ini）的调整是性能调优的核心，需根据服务器资源动态设置：

缓冲池配置：
- innodb_buffer_pool_size：设置为系统内存的50%-80%（如64GB内存设为32-51.2GB），用于缓存数据和索引，减少磁盘I/O。
- innodb_buffer_pool_instances：设为4-8（大内存服务器），减少缓冲池争用。
连接数配置：
- max_connections：根据并发用户数设置（如1000-2000），避免连接数耗尽导致拒绝服务；
- thread_cache_size：设为64-256，缓存空闲线程，减少线程创建/销毁的开销。
日志配置：
- innodb_log_file_size：设为256M-512M（大内存服务器可设为1G），平衡性能与数据恢复速度；
- innodb_log_buffer_size：设为8M-64M，缓存事务日志，减少磁盘写入次数。
其他关键参数：
- query_cache_type=0（MySQL 8.0+已移除查询缓存）、innodb_flush_log_at_trx_commit=2（牺牲部分数据安全性换取性能，适用于高并发写入场景）、tmp_table_size=64M（增大临时表内存大小，减少磁盘临时表的使用）。

SQL语句的执行效率直接影响数据库性能，需重点优化：

索引优化：
- 为高频查询字段（如WHERE、JOIN、ORDER BY子句中的字段）创建索引，避免全表扫描；
- 使用复合索引（如(user_id, order_date)）优化多条件查询，注意索引顺序（最左前缀原则）；
- 避免在索引列上使用函数或计算（如WHERE YEAR(create_time)=2025），否则会导致索引失效。
查询优化：
- 使用EXPLAIN分析查询计划，识别全表扫描、临时表、文件排序等问题；
- 避免SELECT *，仅查询所需字段，减少数据传输量；
- 减少子查询，优先使用JOIN（如INNER JOIN、LEFT JOIN）替代子查询，提升查询效率；
- 优化分页查询，避免使用LIMIT offset, size（如LIMIT 10000, 10），改用WHERE id > last_id LIMIT size（基于主键的分页）。

合理的表结构设计能提升数据存储和查询效率：

数据类型选择：使用最小的合适数据类型（如INT替代BIGINT、VARCHAR(50)替代TEXT），减少存储空间和I/O开销；
表分区：对于超大型表（如超过1000万行），可使用分区表（如按时间范围RANGE、哈希HASH分区），将数据分散到多个物理文件中，提升查询性能；
定期维护：使用OPTIMIZE TABLE命令消除表碎片（针对InnoDB表，可重组数据页，提升I/O效率）；定期清理无用数据（如过期日志、临时表）。

持续的监控与维护是保持数据库高性能的关键：

性能监控：使用工具（如Prometheus+Grafana、Percona Monitoring and Management（PMM））实时监控MySQL指标（如QPS、TPS、连接数、缓冲池命中率、慢查询数量），及时发现性能瓶颈；
慢查询分析：开启慢查询日志（slow_query_log=1、long_query_time=1），使用pt-query-digest或mysqldumpslow工具分析慢查询，定位性能问题；
备份与恢复：定期备份数据库（如使用mysqldump、Percona XtraBackup），确保数据安全；测试恢复流程，缩短故障恢复时间。

当单台服务器无法满足性能需求时，需考虑扩展：

垂直扩展：增加单台服务器的硬件资源（如升级CPU、内存、SSD），提升单机性能；
水平扩展：
- 主从复制：配置一主多从架构，将读请求分发到从库，减轻主库压力；
- 分片（Sharding）：将数据分散到多个数据库实例（如按用户ID哈希分片），提升整体并发处理能力；
- 读写分离：使用中间件（如ProxySQL、MyCat）实现读写分离，自动将读请求路由到从库，写请求路由到主库。