CentOS LAMP 环境下 MySQL 性能调优实战
一 基线评估与监控
- 明确硬件与负载特征：可用内存、CPU 核数、存储类型（优先 SSD）、数据总量与增长、读写比例、峰值 QPS/并发连接。
- 建立监控与慢查询基线：开启并定期分析慢查询日志，结合 performance_schema 与系统监控（如 top、vmstat、iostat）定位瓶颈。慢查询日志建议设置阈值 long_query_time=2，并配置日志轮转，便于长期分析。
二 InnoDB 与关键内存参数
- 合理设置 InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的约 60%–70%（专用数据库可更高），并启用多个实例（如 innodb_buffer_pool_instances=4 起）以降低锁争用。示例：
- [mysqld]
- innodb_buffer_pool_size = 4G
- innodb_buffer_pool_instances = 4
- 日志与事务权衡：增大 innodb_log_file_size（常取缓冲池的 25%–50%，如 1G）可提升批量写入吞吐；innodb_log_buffer_size=256M 适合大事务场景。事务持久性由 innodb_flush_log_at_trx_commit 控制：1 为最安全（每次提交落盘），0/2 性能更高但存在数据丢失窗口（崩溃可能丢失最近 1 秒事务）。
- 连接与会话：避免盲目拉高 max_connections，结合业务峰值与内存预算设置（如 500–1000 起步），并配合 back_log（如 512）、thread_cache_size（如 120）减少线程创建开销；合理设置超时 wait_timeout/interactive_timeout=600 回收空闲连接。
三 查询与索引优化
- 索引策略：为高频过滤/排序/分组列建立索引，优先使用覆盖索引；多列条件使用复合索引并遵循最左前缀；避免在更新频繁的列上过度建索引。
- SQL 编写规范：避免 **SELECT ***，仅返回必要列；尽量使用参数化查询与预处理；分页使用 LIMIT 与合适的索引；谨慎使用 LIKE ‘%value%’（无法有效利用索引），可改为前缀匹配或全文检索；减少不必要的 JOIN 与子查询，必要时用临时表/物化中间结果。
- 执行计划与诊断：用 EXPLAIN 检查是否走索引、是否出现文件排序/临时表；对慢查询逐一优化执行计划与索引。
- 缓存策略：若使用 MySQL 8.0+，查询缓存已被移除；在 5.7 及以下版本可按需配置，但需评估写多场景的收益与开销。
四 表设计与维护
- 存储引擎选择：默认使用 InnoDB（事务、行锁、外键、崩溃恢复）；读多写少且不需事务的场景再考虑 MyISAM。
- 表结构优化：选择合适数据类型（如能用 INT 不用 VARCHAR），适度反规范化以减少复杂 JOIN；大字段拆分与冷热数据分离。
- 碎片整理与统计：对高碎片表执行 OPTIMIZE TABLE；定期 ANALYZE TABLE 更新统计信息，提升执行计划质量。
五 系统与 LAMP 协同优化
- 操作系统与网络：提升文件描述符与进程限制（如 nofile 65535），优化内核网络参数（如 net.core.somaxconn、net.ipv4.tcp_tw_reuse、合理设置 tcp_keepalive 等），使用 SSD 并优化 I/O 调度（如 noop/deadline）。
- Apache 与 PHP：优先采用 Event MPM 并合理设置 MaxRequestWorkers/KeepAlive；启用 mod_deflate 压缩；PHP 启用 OPcache 并连接 Redis/Memcached 做应用层缓存，降低数据库读压。
- 安全与变更流程：保持系统与软件包更新，按最小权限配置数据库账户与防火墙；任何参数变更先备份配置并在低峰期灰度验证，持续监控并回滚异常。
