centos lnmp如何进行数据库优化

CentOS LNMP 数据库优化实操指南

一 硬件与操作系统优化

• 存储优先使用SSD，数据库盘建议做RAID10；CPU与内存按并发与数据规模匹配，优先保证足够的内存以减少磁盘 I/O。
• 文件系统优先XFS；在具备电池保护或超级电容的存储上，可启用写缓存 WCE=1、禁用读缓存RCD=0以提升写吞吐。
• 在 BIOS/内核层面禁用 NUMA或绑定 MySQL 进程到指定 NUMA 节点，减少跨节点访问延迟。
• 适度调优内核参数：将vm.swappiness=0–10、vm.dirty_background_ratio=5–10、vm.dirty_ratio≈2×dirty_background_ratio，降低 swap 倾向并平滑刷脏。
• 提升文件描述符限制（如 systemd 服务设置LimitNOFILE），避免连接、打开表过多受限。

二 MySQL/MariaDB 配置优化

• 内存与连接
  • 将innodb_buffer_pool_size设为物理内存的50%–70%（写多可上探至 80%）；单实例常见32–64GB，多实例按实例数均分。
  • max_connections按并发峰值配置，避免过大导致上下文切换与内存开销激增。
• InnoDB 关键参数
  • 事务持久性与性能权衡：innodb_flush_log_at_trx_commit=1（最安全）、2（折中）、0（更高吞吐）；
  • 提升日志吞吐：innodb_log_file_size适度增大（如256M–1G），并合理设置innodb_log_files_in_group；
  • 按磁盘能力设置innodb_io_capacity（SSD 可显著提高）；
  • 缓冲与排序：innodb_log_buffer_size适度增大；必要时调大会话级sort_buffer_size/read_buffer_size（避免全局过大）。
• 查询与缓存
  • MySQL 5.7 及更早版本可按需配置query_cache（写密集场景建议关闭或减小）；
  • 持续开启并分析慢查询日志，配合pt-query-digest定位问题 SQL。

三 SQL 与索引优化

• 使用EXPLAIN检查执行计划，确保关键查询走索引，避免全表扫描；尽量使用覆盖索引减少回表。
• 设计复合索引匹配多条件查询，避免过多单列索引（写放大、占用空间）。
• 查询写法优化：避免SELECT ，减少JOIN数量与复杂度，分页使用LIMIT*；避免**LIKE ‘%value%’**这类无法有效利用索引的模式。
• 事务与锁：控制事务粒度，避免长事务与不必要的锁等待；批量操作合并提交。

四 架构与缓存优化

• 引入Redis/Memcached做热点数据缓存，应用层结合APCu；对列表/报表类数据设置合理TTL与失效策略。
• 读写分离：主库写、从库读，结合应用或中间件分发读流量，降低主库压力。
• 数据规模治理：对大表按时间或业务键做分区表；必要时进行分库分表。
• 连接治理：在应用侧使用连接池或持久连接，减少频繁建连/断连开销。

五 监控维护与安全

• 监控与诊断：系统侧用top/htop、iostat、vmstat、netstat观察资源；数据库侧用SHOW STATUS/SHOW PROCESSLIST、Performance Schema与慢查询日志定位瓶颈。
• 表维护：对高碎片表执行OPTIMIZE TABLE/ANALYZE TABLE；定期重建或整理索引。
• 备份与安全：制定定期备份与校验流程；及时更新系统与数据库补丁；通过防火墙限制访问，必要时启用SSL/TLS加密传输。

六 快速落地清单与示例配置

• 快速清单
  • 基线：收集负载、QPS、慢查询、I/O 与连接数；建立回滚与变更窗口。
  • 配置：按内存与负载设置innodb_buffer_pool_size、max_connections、innodb_flush_log_at_trx_commit、innodb_log_file_size、innodb_io_capacity；开启慢查询日志与采样。
  • SQL：用pt-query-digest定位 Top SQL，按索引与写法逐条优化。
  • 缓存与架构：接入Redis/Memcached，评估读写分离/分区。
  • 监控：部署可视化监控（如Prometheus + Grafana），设置告警。
  • 维护与安全：例行OPTIMIZE/ANALYZE、备份校验、补丁与加固。
• 示例配置片段（/etc/my.cnf.d/optim.cnf，按实际调整）
  • [mysqld]
    • innodb_buffer_pool_size=24G
    • innodb_log_file_size=512M
    • innodb_log_files_in_group=2
    • innodb_flush_log_at_trx_commit=1
    • innodb_io_capacity=2000
    • max_connections=500
    • slow_query_log=ON
    • slow_query_log_file=/var/log/mysql/slow.log
    • long_query_time=1
  • 风险提示：修改前务必备份 my.cnf与数据；变更后逐步观察错误日志与性能指标，必要时回滚。