LNMP在Ubuntu上如何优化MySQL

Ubuntu LNMP 环境下 MySQL 优化实操指南

一 基线评估与监控

• 明确硬件与负载：记录内存、CPU、磁盘类型（优先 SSD）、业务 QPS/TPS、读写比例与峰值连接数，作为后续参数与架构调整依据。
• 建立监控与诊断：开启并定期分析慢查询日志，使用 EXPLAIN 检查执行计划，配合 Performance Schema 与状态变量（如缓冲池命中率）定位瓶颈。
• 配置与版本：Ubuntu 上配置文件常见路径为 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf；修改后执行 sudo systemctl restart mysql 使配置生效。
• 安全与维护：运行 mysql_secure_installation 完成基础加固；定期执行 ANALYZE/OPTIMIZE TABLE 维护统计信息与碎片。

二 InnoDB 与基础配置优化

• 关键参数建议（示例为 16GB 内存，需按实际调整）：
  • innodb_buffer_pool_size：建议为物理内存的 50%–70%（16GB 可用 8–12G），用于缓存数据与索引，是 InnoDB 最重要的性能项。
  • innodb_log_file_size：建议 256M，增大可提升批量写入与恢复性能（变更需按步骤替换日志文件）。
  • innodb_flush_log_at_trx_commit：安全性优先设为 1；在可容忍秒级丢失的场景可设为 2 提升吞吐。
  • max_connections：结合业务峰值与连接占用内存谨慎设置，避免过大导致内存争用。
  • 字符集：统一使用 utf8mb4/utf8mb4_unicode_ci 以支持完整 UTF-8。
• 示例片段（置于 [mysqld] 段）：

  [mysqld]
  innodb_buffer_pool_size = 8G
  innodb_log_file_size   = 256M
  innodb_flush_log_at_trx_commit = 1
  max_connections        = 300
  character-set-server  = utf8mb4
  collation-server      = utf8mb4_unicode_ci
  

• 变更流程要点：调整 innodb_log_file_size 需先停库、备份、移走旧日志、改配置、启动；其他参数多数动态生效或需重启。

三 查询与索引优化

• 索引策略：为高频 WHERE/JOIN/ORDER BY 列建立索引；多列条件使用复合索引并遵循最左前缀原则；避免过多索引以免写放大。
• SQL 编写：避免 **SELECT ***，仅返回必要列；减少不必要的 JOIN 与子查询；合理使用 LIMIT 分页；避免在 WHERE 中对列做函数计算导致索引失效。
• 执行计划与诊断：用 EXPLAIN 检查是否走索引、是否出现全表扫描或临时表/文件排序；对慢查询建立索引与语句改写的闭环优化。
• 维护与缓存：定期 ANALYZE/OPTIMIZE TABLE；在应用侧引入 Redis/Memcached 做热点数据缓存，降低数据库读压。

四 系统层与存储优化

• 存储与文件系统：优先 SSD；生产常用 XFS/ext4，挂载选项可用 noatime,nodiratime（权衡可靠性与性能）。
• I/O 调度：机械盘可用 deadline，SSD 通常默认 none/mq-deadline 更合适（视内核与驱动而定）。
• 内存与脏页：适度降低 vm.swappiness（如 0–10），设置 vm.dirty_background_ratio 与 vm.dirty_ratio 以平滑刷盘，避免抖动。
• InnoDB I/O：如底层设备与阵列支持，可设置 innodb_flush_method=O_DIRECT 减少双缓冲。
• 说明：不同云厂商与内核版本默认值差异较大，变更前请在测试环境验证。

五 维护与扩展策略

• 配置与容量评估：使用 MySQLTuner/Percona Toolkit 定期评估配置与索引，结合监控数据逐步调优。
• 读写分离与扩展：读多写少场景引入主从复制与读写分离；超大规模考虑分库分表/分区。
• 变更流程：任何参数或结构变更前做好备份与回滚方案；先在测试环境验证，再灰度上线并持续观测。