Ubuntu LNMP 环境下 MySQL 优化路线图
一 基线评估与监控
- 明确硬件与负载：记录内存容量、磁盘类型（优先 SSD）、CPU 核数、QPS/TPS、连接数、读写比例与慢查询数量，作为调优基线。
- 开启并分析慢查询：在 my.cnf 中启用慢查询日志，定位耗时 SQL 并用 EXPLAIN 检查执行计划，优先优化高成本语句。
- 建立持续监控与建议机制：使用 MySQLTuner、Percona Toolkit 定期体检，配合 Prometheus + Grafana 监控关键指标（连接、缓冲池命中、锁等待、I/O 等），形成闭环优化。
二 InnoDB 与关键配置优化
- 内存与缓存
- 将 innodb_buffer_pool_size 设为物理内存的约 50%–75%（写密集或专用库可更高），减少磁盘随机 IO。
- 如仍使用 MyISAM，适当设置 key_buffer_size；以 InnoDB 为主的实例可保持较小值。
- 日志与刷写策略
- 将 innodb_log_file_size 提升到 128M–512M，降低日志切换频率；
- 根据一致性要求选择 innodb_flush_log_at_trx_commit：强一致设为 1；可接受更高性能可设为 2；极致性能测试可设为 0（风险高，生产慎用）。
- 并发与连接
- 合理设置 max_connections，避免过大导致上下文切换与内存压力；
- 提升 thread_cache_size 复用线程，降低连接开销。
- 临时表与排序
- 适度增大 tmp_table_size 与 sort_buffer_size，减少磁盘临时表与文件排序。
- 版本差异
- MySQL 8.0 已移除查询缓存（Query Cache），请勿再配置 query_cache_size；早期版本如启用需评估高并发下的锁争用。
三 SQL 与索引优化
- 索引策略
- 为高频 WHERE/JOIN/ORDER BY/GROUP BY 列建立索引；优先使用复合索引并遵循最左前缀；尽量设计覆盖索引减少回表。
- 控制索引数量，避免写放大与优化器选择困难。
- 查询写法
- 避免 **SELECT ***，只取必要列；减少不必要的 JOIN 与子查询，必要时用 JOIN 替代；
- 分页使用 LIMIT；避免在索引列上使用函数或计算，以免索引失效。
- 执行计划与诊断
- 使用 EXPLAIN 检查是否走索引、是否产生文件排序/临时表、扫描行数是否合理，针对性加索引或改写 SQL。
- 维护与结构
- 定期执行 ANALYZE TABLE 更新统计信息；对高碎片表执行 OPTIMIZE TABLE；
- 大表按业务时间或范围进行分区/分表，并建立合适生命周期的数据归档策略。
四 架构与系统层优化
- 架构层
- 引入 Redis/Memcached 做应用层缓存，热点数据走缓存，数据库专注事务与一致性；
- 合理使用持久连接或连接池，降低握手与销毁开销（注意连接泄漏与超时设置）。
- 存储与文件系统
- 优先 SSD/NVMe，阵列建议 RAID10；文件系统选 XFS/ext4，挂载选项建议使用 noatime（必要时配合 async），降低元数据写入开销。
- 操作系统与内核
- 适度降低 vm.swappiness（如 1–10），减少换页；
- 调整脏页刷写阈值（如 vm.dirty_background_ratio 与 vm.dirty_ratio）以平滑写峰；
- 存储 I/O 调度器在 SSD 场景可选 deadline/noop，减少调度开销。
五 Ubuntu 落地与变更流程
- 配置路径与备份
- 配置文件通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/mysql/my.cnf；变更前先备份并保留回滚方案。
- 动态与静态参数
- 动态参数可用 SET GLOBAL 在线调整（如 max_connections、thread_cache_size、innodb_flush_log_at_trx_commit 等）；
- 静态参数（如 innodb_buffer_pool_size、innodb_log_file_size）需修改配置并重启实例。
- 安全重启与验证
- 重启命令示例：sudo systemctl restart mysql；重启后核对 SHOW VARIABLES LIKE ‘xxx’ 与 SHOW ENGINE INNODB STATUS，并通过监控与慢查询日志验证效果与稳定性。