LNMP架构中CentOS数据库优化策略（以MySQL为例）

一、硬件资源优化：奠定性能基础

• 处理器（CPU）：选择64位多核心处理器（建议2-16核，L2缓存越大越好），满足数据库并发处理需求。
• 内存（RAM）：根据实例数量分配内存，单个MySQL实例建议3-4GB（如96GB内存可支持3-4个实例）；InnoDB缓冲池（innodb_buffer_pool_size）需占用物理内存的70%-80%，用于缓存数据和索引，减少磁盘I/O。
• 存储（磁盘）：优先使用SSD固态硬盘（随机IO性能远优于SAS机械硬盘），高并发场景下推荐RAID10配置（兼顾性能与冗余，从库可采用RAID5/RAID0）。
• 网络（网卡）：使用多网卡绑定（bonding）提升吞吐量，优化TCP参数（如增大缓冲区、调整超时时间），确保网络传输效率。

二、操作系统层面优化：提升系统效率

• 操作系统选择：使用64位CentOS系统（如CentOS 7/8），禁用NUMA特性（避免内存访问延迟）。
• 文件系统：采用XFS文件系统（支持大文件和高并发），调整挂载参数：noatime（不记录文件访问时间，减少磁盘写入）、async（异步写入，提升IO性能）。
• 内核参数调整：
  • vm.swappiness：设置为0-10（减少内存交换，避免磁盘IO瓶颈）；
  • vm.dirty_background_ratio：5-10（后台脏页刷新阈值）；
  • vm.dirty_ratio：设置为dirty_background_ratio的两倍（强制刷新脏页的上限）；
  • 优化TCP协议栈：增大net.core.somaxconn（连接队列长度）、调整net.ipv4.tcp_tw_reuse（复用TIME_WAIT连接），减少连接建立开销。

三、MySQL数据库核心优化：聚焦查询与资源

1. 配置参数调整

• 缓冲池优化：innodb_buffer_pool_size是MySQL性能的关键参数，建议设置为物理内存的70%-80%（如16GB内存可设为12GB），用于缓存数据和索引，显著减少磁盘IO。
• 连接数管理：max_connections根据并发用户数调整（如500-1000），避免连接数耗尽；配合thread_cache_size（线程缓存，建议50-100）减少线程创建开销。
• 日志文件优化：innodb_log_file_size（建议256MB-1GB）和innodb_log_files_in_group（建议2-4个）平衡性能与数据安全性（更大的日志文件减少刷新频率，提升写入性能）。
• 刷新策略：innodb_flush_log_at_trx_commit：主库设为1（保证数据一致性，每次事务提交都刷新日志到磁盘），从库设为2（每秒刷新一次，提升复制性能）。

2. 索引优化：提升查询效率

• 合理创建索引：为高频查询的WHERE、JOIN、ORDER BY字段创建索引（如user_id、order_date）；优先使用复合索引（覆盖多个查询字段，遵循最左前缀原则，如(user_id, order_status)）。
• 避免冗余索引：定期检查并删除未使用的索引（通过SHOW INDEX FROM table_name查看），减少写操作的开销（索引越多，INSERT/UPDATE/DELETE越慢）。
• 优化索引使用：避免在索引字段上使用函数（如WHERE YEAR(create_time) = 2025），这会导致索引失效；使用EXPLAIN分析查询计划，确保查询使用了索引（type列显示ref或range为佳）。

3. SQL语句优化：减少资源消耗

• 避免全表扫描：使用SELECT column1, column2代替SELECT *，减少数据传输量；确保查询条件能命中索引（如WHERE id = 1比WHERE name LIKE '%张三%'高效）。
• 优化JOIN操作：用JOIN代替子查询（子查询会导致临时表创建，影响性能），确保JOIN字段有索引；避免SELECT *在JOIN中，只选择必要的字段。
• 分页优化：避免LIMIT offset, size的大偏移量分页（如LIMIT 10000, 10），改用WHERE id > last_id LIMIT size（记录上一页最后一条记录的ID，减少扫描行数）。
• 批量操作：使用INSERT INTO ... VALUES (...), (...), ...代替多次单条插入，减少客户端与服务器的通信次数（提升写入性能）。

4. 表结构优化：减少冗余与碎片

• 选择合适数据类型：用最小的数据类型满足需求（如TINYINT代替INT、VARCHAR(50)代替TEXT），减少磁盘占用和内存消耗；避免使用TEXT、BLOB等大字段（如需存储，可分离到单独表中）。
• 避免过度规范化：适当冗余（如将category_name存储在订单表中，而非每次查询都关联category表），减少表连接操作（连接越多，性能越差）。
• 定期维护：使用OPTIMIZE TABLE整理表碎片（针对频繁更新的表，如日志表、订单表），恢复索引效率；定期执行ANALYZE TABLE更新统计信息（帮助优化器选择更优的执行计划）。

四、数据库架构扩展：应对高并发

• 读写分离：使用ProxySQL、Atlas等中间件，将读请求分发到从库，写请求发送到主库，提升整体吞吐量（适用于读多写少的场景）。
• 分库分表：对于大型表（如订单表、用户表），按时间（如按月分表）、ID范围（如每100万条一个表）或哈希值拆分，减少单表数据量，提升查询性能。
• 缓存策略：使用Redis、Memcached缓存热点数据（如商品详情、用户信息），减少对数据库的直接访问（降低数据库负载，提升响应速度）。

五、监控与维护：持续优化保障

• 慢查询分析：开启慢查询日志（slow_query_log = 1，long_query_time = 2），使用pt-query-digest、mysqldumpslow工具分析慢查询，定位性能瓶颈（如未使用索引、全表扫描）。
• 性能监控：使用Prometheus+Grafana、Zabbix等工具监控MySQL性能指标（如QPS、TPS、连接数、缓冲池命中率、磁盘IO），及时发现异常（如缓冲池命中率低于90%，需扩容缓冲池）。
• 定期备份：使用mysqldump、xtrabackup等工具定期备份数据库（每日全备+增量备份），确保数据安全；开启压缩功能（如--single-transaction --quick --master-data=2 | gzip > backup.sql.gz），减小备份文件大小。

以上优化策略需根据实际业务负载（如并发量、数据量）和环境（如服务器配置）进行调整，持续监控和迭代优化是保持数据库高性能的关键。