CentOS LNMP 环境下 MySQL 优化实操指南
一 基线评估与监控
- 明确负载特征：读多写少/写密集、事务大小、峰值 QPS/TPS、慢查询比例、连接数使用率。
- 建立监控与基线：启用 Performance Schema，打开 slow_query_log，用 pt-query-digest 分析慢日志；系统侧用 top/htop、iostat、vmstat、netstat 观察 CPU、I/O、内存、网络与连接状态，形成对比基线后再做调优变更。
二 操作系统与存储优化
- 硬件与文件系统：优先使用 SSD，阵列建议 RAID10；数据库分区使用 XFS 更利于大表与高并发元数据操作。
- 内核与 VM 调优：将 vm.swappiness=0–10、vm.dirty_background_ratio=5–10，并将 vm.dirty_ratio≈2×dirty_background_ratio，减少 swap 与抖动；必要时开启 透明大页（THP）关闭/延迟分配以减轻内存管理抖动。
- NUMA 与调度：在 BIOS/内核启动参数中关闭 NUMA 或绑定 MySQL 进程到单一 NUMA 节点，降低跨节点访问延迟；I/O 调度器优先 noop/deadline（SSD）。
- 文件描述符与网络：提升 ulimit -n（如 65536+），优化 TCP 参数（如 rmem/wmem、somaxconn、tcp_tw_reuse）以缓解高并发短连接场景。
三 MySQL 配置优化
- 内存与缓冲
- InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的 50%–70%（专用库可到 60%–80%）；启用 innodb_buffer_pool_instances=4/8（按内存与 CPU 核数分片，减少锁争用）；开启 innodb_file_per_table。
- 临时表与排序：将 tmp_table_size 与 max_heap_table_size 设为 128M–256M 并保持一致，避免磁盘临时表；适度提高 table_open_cache（如 2048–4096）减少元数据争用。
- 连接与会话
- 将 max_connections 设为业务峰值所需并保留余量（避免盲目上调，过高会放大内存与上下文切换开销）；wait_timeout/interactive_timeout=600 回收空闲连接；必要时提高 back_log（如 128–512）。
- InnoDB 日志与持久化
- 提升 innodb_log_file_size=1G–2G（需停机调整并先备份），innodb_log_buffer_size=64M–256M；根据一致性要求设置 innodb_flush_log_at_trx_commit：强一致用 1；可接受秒级丢失风险可设 2 提升吞吐；高写入场景可设 sync_binlog=1 配合 1 保证持久性，或权衡为 N>1 提升性能。
- 并发与 I/O
- 保持 innodb_thread_concurrency=0（让 InnoDB 自适应），或按经验设为 CPU 核心数×2 上限；开启 innodb_use_native_aio；SSD 建议 innodb_flush_neighbors=0；按磁盘能力设置 innodb_io_capacity=20000、innodb_io_capacity_max=40000（普通 SATA SSD 可下调）；根据脏页压力调节 innodb_lru_scan_depth、innodb_page_cleaners。
- 查询与缓存
- 若使用 MySQL 5.7，查询缓存已废弃，建议关闭 query_cache_type=0；如仍用 5.6 且读多写少，可小量开启并严格评估。
- 示例片段（/etc/my.cnf 的 [mysqld] 段，按实际内存与负载调整）
- innodb_buffer_pool_size=24G
- innodb_buffer_pool_instances=8
- innodb_log_file_size=1G
- innodb_log_buffer_size=128M
- innodb_flush_log_at_trx_commit=1
- innodb_flush_method=O_DIRECT
- innodb_use_native_aio=1
- innodb_io_capacity=20000
- innodb_io_capacity_max=40000
- innodb_flush_neighbors=0
- innodb_thread_concurrency=0
- max_connections=800
- back_log=256
- wait_timeout=600
- interactive_timeout=600
- tmp_table_size=128M
- max_heap_table_size=128M
- table_open_cache=4096
- slow_query_log=1
- slow_query_log_file=/var/log/mysql/mysql-slow.log
- long_query_time=1
- log_error=/var/log/mysql/error.log
- performance_schema=ON
四 索引与 SQL 优化
- 索引策略：为高频 WHERE/JOIN/ORDER BY 字段建立索引，优先 复合索引 与 覆盖索引；避免冗余与过度索引（写放大）。
- 语句与执行计划：用 EXPLAIN 检查是否走索引、是否出现 filesort/Using temporary；避免 **SELECT ***、隐式转换、函数包索引列；大数据量分页避免大 OFFSET，改用 游标/键集分页。
- 连接与子查询：减少不必要的 JOIN 层级，能用 JOIN 替代低效子查询；合理使用 LIMIT 与聚合。
- 维护与分析：定期用 pt-query-digest 分析慢查询，针对 Top SQL 建立/改写索引与语句；必要时对高碎片大表执行 OPTIMIZE TABLE（InnoDB 多为在线 DDL，仍应在低峰期评估）。
五 架构扩展与安全运维
- 扩展与高可用：读多写少场景部署 主从复制 做读写分离；跨机房/大规模可用 组复制/InnoDB Cluster；超大规模考虑 分库分片；定期 备份与演练恢复。
- 应用与中间件：启用 连接池（减少握手开销），使用 Redis/Memcached 做热点数据缓存；PHP 侧开启 OPcache 降低解析成本。
- 安全加固：最小权限、强口令、限制来源 IP、开启 SSL/TLS 加密传输、及时打补丁与版本升级。