centos lamp如何进行数据库优化

CentOS LAMP 数据库优化实战指南

一 基线评估与监控

• 明确目标：以应用延迟、QPS/TPS、连接利用率、InnoDB 缓冲池命中率为核心指标，先采集基线再调优。
• 开启与解读监控：
  • 慢查询日志：开启并设置阈值（如 long_query_time=1s），定期用 pt-query-digest 分析 Top SQL。
  • 性能架构：利用 performance_schema 观察锁等待、I/O 延迟、语句成本等，定位瓶颈。
  • 系统层：用 top/vmstat/iostat/netstat 观察 CPU、内存、磁盘、网络是否成为制约因素。
• 硬件与存储：优先 SSD/NVMe，随机 I/O 场景 NVMe 可比 HDD 降低约 90% 延迟，SSD 随机读写性能通常较 HDD 提升一个数量级；高并发写入建议 RAID 10，并通过 innodb_io_capacity 匹配设备 IOPS。

二 InnoDB 与连接层关键参数

• 内存与缓存：将 innodb_buffer_pool_size 设为物理内存的 50%-80%（常见做法是 70% 左右），以覆盖工作集；大内存机型可适度上调，避免 swap。
• 日志与持久性：
  • innodb_log_file_size：写入密集型可适当增大（如 256M 起步），在崩溃恢复时间与写放大之间取平衡。
  • innodb_flush_log_at_trx_commit：1 为最安全（ACID），约 2000-3000 TPS；2 可提升到 3-5 倍 吞吐，但有最多 1 秒 数据丢失风险。
  • sync_binlog：0 性能最高；结合备份策略可设为 1/N 降低风险。
• 并发与线程：
  • max_connections：结合业务峰值设置（常见 500-2000），避免过大导致上下文切换与内存压力。
  • thread_cache_size：建议约为 max_connections 的 25%，减少线程创建/销毁开销。
• 查询缓存：
  • MySQL 5.7：如非强依赖建议关闭（query_cache_type=0），因为 >64MB 易引发锁竞争。
  • MySQL 8.0：已移除查询缓存，无需配置。
• 其他常用项：
  • innodb_thread_concurrency：按 CPU 核心数与负载调节，避免线程过度争用。
  • innodb_io_capacity / innodb_io_capacity_max：依据磁盘（如 SSD IOPS）设置后台刷盘速率上限。

三 索引与 SQL 优化

• 索引设计原则：
  • 单表索引不超过 5 个，复合索引列数不超过 3 列；优先高选择性列，遵循最左前缀。
  • 覆盖索引减少回表；定期 ANALYZE TABLE 更新统计信息，避免执行计划退化。
• 执行计划与改写：
  • 用 EXPLAIN / EXPLAIN ANALYZE 检查是否出现 type=ALL（全表扫描）、Using temporary/filesort 等风险信号。
  • 避免 **SELECT ***、在索引列上做函数计算；将复杂查询拆分为多步、应用侧聚合，减少服务器压力。
• 变更与维护：
  • 大表结构变更使用在线 DDL 工具（如 pt-online-schema-change），降低锁表时间。
  • 历史数据按时间做 分区表，便于快速裁剪与查询裁剪。

四 架构与高可用扩展

• 读写分离：主库写、从库读，读流量按 read_only=1 约束；对一致性要求高的场景引入 半同步复制（rpl_semi_sync_master_enabled=1），并监控 Rpl_semi_sync_master_wait_sessions 避免主库阻塞。
• 复制与切换：启用 GTID 简化故障切换与定位（gtid_mode=ON）。
• 分片与中间件：按 用户 ID 哈希 或时间等路由键分片；跨分片查询用应用层路由或 ProxySQL 等中间件治理。
• 扩展策略：高峰期按需增加从库副本数，结合监控自动扩缩容。

五 系统层与维护实践

• 资源与内核：
  • 文件句柄与进程限制：在 /etc/security/limits.conf 提升 nofile/nproc（如 65535），并在 /etc/sysctl.conf 提升 fs.file-max；服务进程（如 mysql）单独设置更稳妥。
  • 网络与连接：适度增大 net.core.somaxconn、net.ipv4.ip_local_port_range，开启 tcp_tw_reuse，在 NAT 环境下将 tcp_tw_recycle=0，合理设置 tcp_fin_timeout/keepalive 等以减少连接开销与 TIME_WAIT 堆积。
  • I/O 调度：SSD 优先 noop/deadline 调度器，降低调度开销。
• 存储与文件系统：使用 XFS/ext4，挂载选项加 noatime 减少元数据写入；定期检查与优化文件系统。
• 备份与安全：
  • 全量+增量备份结合二进制日志（binlog）点位恢复；定期演练恢复流程。
  • 最小权限账户、限制来源 IP、禁用不必要服务与端口，保持系统与数据库组件 及时更新。
• 压测与验证：用 ab/wrk 或业务脚本进行压测，对比 QPS/TPS、P95/P99 延迟、错误率 与基线，按指标逐步回放参数与索引变更。