centos lnmp中MySQL数据库优化

CentOS LNMP 环境下 MySQL 性能优化实操指南

一 硬件与操作系统优化

• 硬件选型
  • 使用 64 位 CPU，核心数按并发与查询复杂度配置（建议 2–16 核起步）。
  • 内存优先分配给 InnoDB 缓冲池，多实例时按实例数拆分内存（如 96–128GB 内存可支撑 3–4 个实例）。
  • 存储采用 SSD，阵列优先 RAID10 兼顾性能与可靠性；必要时使用 网卡 bonding 提升吞吐。
• 操作系统与文件系统
  • 选择 x86_64 CentOS，必要时 禁用 NUMA 以避免跨 NUMA 访问抖动。
  • 使用 XFS，挂载选项建议 noatime；如为电池供电 RAID 控制器可启用 写缓存，否则不建议在裸设备上开启写回缓存。
• 内核与网络
  • 降低 vm.swappiness（0–10），适度设置 vm.dirty_background_ratio（5–10） 与 vm.dirty_ratio（约为前者的 2 倍），减少抖动与刷盘峰值。
  • 优化 TCP 参数，减少 TIME_WAIT 堆积，提升短连接与高并发网络表现。

二 MySQL 配置优化

• 内存与缓冲
  • 将 innodb_buffer_pool_size 设为物理内存的 60%–80%（专用数据库可上探至 70%–80%），并按内存大小设置 innodb_buffer_pool_instances（如 4 或 8） 降低锁争用。
  • 仅在使用 MyISAM 时调 key_buffer_size；InnoDB 场景以缓冲池为主。
• 日志与持久化
  • 适度增大 innodb_log_file_size 与 innodb_log_files_in_group，缩短检查点间隔、提升批量写入吞吐；注意需按停机流程调整。
  • innodb_flush_log_at_trx_commit：主库建议 1（强一致），从库可 2（更高吞吐、容忍秒级数据丢失）。
• 连接与会话
  • 合理设置 max_connections，并结合应用连接池（如 HikariCP/PHP-FPM 连接池）避免“连接风暴”；监控 Threads_connected / Max_used_connections，经验上使峰值使用率约 ≤85%。
  • 适度 wait_timeout / interactive_timeout，回收空闲连接，降低资源占用。
• 临时表与排序
  • 提高 tmp_table_size / max_heap_table_size（如 128M–256M），减少磁盘临时表；结合 sort_buffer_size / join_buffer_size 按会话按需分配，避免全局过大。
• 其他常用
  • 开启 slow_query_log = 1、设置 long_query_time = 1–2 秒，定位慢 SQL。
  • 启用 performance_schema = ON 做细粒度诊断；如为 MySQL 5.7 且非特殊场景，建议 query_cache_size = 0（避免并发失效抖动）。

三 SQL 与索引优化

• 索引策略
  • 为高频 WHERE / JOIN / ORDER BY / GROUP BY 字段建立 B+Tree 索引，遵循 最左前缀；优先 覆盖索引 减少回表。
  • 控制索引数量，避免写入放大与维护成本上升；定期清理 冗余/重复索引。
• 查询优化
  • 避免 **SELECT ***，仅取必要列；用 EXPLAIN 检查是否走索引、是否出现 filesort / temporary。
  • 减少 OFFSET 大分页，改用 游标分页（WHERE id > ? LIMIT N） 或 键集分页。
  • 谨慎使用 LIKE ‘%value%’（难以利用索引），必要时采用 全文索引 或搜索引擎替代。
• 表结构
  • 选择最小可用数据类型（如 INT vs BIGINT、合适 VARCHAR 长度），减少行宽与 I/O。
  • 大表可按时间/业务键做 分区表，提升扫描与维护效率。

四 架构与运维优化

• 读写分离与连接池
  • 引入 ProxySQL/MaxScale 做读写分离与查询规则管理；应用侧使用 连接池，降低握手与线程创建开销。
• 缓存与外置加速
  • 以 Redis/Memcached 缓存热点数据与计算结果，配合 异步写入/消息队列 削峰填谷。
• 监控与维护
  • 持续采集 Performance Schema / sys schema 指标与 慢查询日志，用 pt-query-digest 聚合分析；对高成本 SQL 建立优化迭代闭环。
  • 周期性 OPTIMIZE TABLE / 重建索引（对高碎片表），并评估 统计信息 更新策略（如 innodb_stats_auto_recalc）。
• 备份与恢复
  • 制定 全量 + 增量 备份策略（如 mysqldump / xtrabackup），定期演练恢复 RTO/RPO；变更前务必 备份与回滚预案。

五 安全与配置落地

• 安全基线
  • 仅开放必要端口与来源 IP，使用 SSL/TLS 加密复制与客户端连接；限制 SUPER / FILE 等高权限账户。
  • 定期审计 用户权限 与 密码策略，避免明文/弱口令与过度授权。
• 配置落地与验证
  • 配置文件通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf；可用命令查看加载顺序：/usr/sbin/mysqld --verbose --help | grep -A 1 'default options'。
  • 修改前 备份 my.cnf 与数据；变更后逐步滚动重启实例，观察 错误日志 / 性能指标 再继续调优。