CentOS中MySQL性能如何优化

CentOS 上 MySQL 性能优化实操指南

一 系统层优化

• 使用高速存储（优先 SSD/NVMe），并尽量将 OS、数据、日志 分别放置在不同磁盘，降低 I/O 争用；阵列建议 RAID10（读写与可靠性兼顾）。
• 合理规划内存，尽量避免 swap 使用；文件系统建议 XFS/ext4，挂载时加上 noatime 减少元数据写入。
• 适度放宽文件句柄限制（如 systemd 服务或 limits.conf），避免连接高峰时 “Too many open files”。
• 监控资源使用（如 htop、iostat、vmstat），先定位瓶颈在 CPU、内存、磁盘还是网络，再决定优化方向。

二 InnoDB 与基础配置优化

• 关键内存参数：将 innodb_buffer_pool_size 设为物理内存的 50%–80%（专用库可更高）；innodb_log_file_size 设为 128M–512M，在崩溃恢复时间与写放大之间折中。
• 刷新策略：在允许一定数据丢失风险的场景，可将 innodb_flush_log_at_trx_commit=2（每秒落盘，宕机可能丢 1 秒事务）；强一致场景保持 =1。
• I/O 能力：设置 innodb_io_capacity（如 SSD≈5000），开启 innodb_adaptive_flushing；写密集可适当提高 innodb_write_io_threads。
• 绕过双重缓冲：使用 innodb_flush_method=O_DIRECT 减少 OS page cache 与 InnoDB buffer 的双缓冲。
• 连接与会话：根据负载调 max_connections，避免过大导致内存膨胀；合理设置 thread_cache_size 复用线程，降低创建销毁开销。
• 示例（需结合实际调整）：
  [mysqld]
  innodb_buffer_pool_size=8G
  innodb_log_file_size=256M
  innodb_flush_log_at_trx_commit=2
  innodb_io_capacity=5000
  innodb_flush_method=O_DIRECT
  innodb_adaptive_flushing=ON
  max_connections=500
  thread_cache_size=100
  注：修改前务必备份，变更后逐步观察性能与错误日志。

三 索引与 SQL 优化

• 为高频出现在 WHERE/JOIN/ORDER BY 的列建立索引；优先使用 整型 做主键/外键；尽量让列定义为 NOT NULL，减少索引与比较开销。
• 控制索引数量，避免“过度索引”（写入放大、维护成本高）；优先设计 覆盖索引，减少回表。
• 避免索引失效：不要在索引列上做函数/表达式运算（如 age+1=7）；LIKE 只有前导通配（如 LIKE ‘abc%’）才能有效利用索引。
• 对大文本/VARCHAR 使用 前缀索引 或哈希技巧，权衡选择性与长度；多列条件使用 联合索引 并遵循最左前缀原则。
• 查询编写：避免 **SELECT ***，只取需要的列；减少大表 JOIN 与笛卡尔积；用 EXPLAIN 检查执行计划，关注 type、key、rows、Extra。

四 查询缓存与连接优化

• 查询缓存（Query Cache）：仅在“读多写少、相同查询占比高”的场景考虑；若启用，设置 query_cache_type=1/2 与 query_cache_size（如 64M–128M），并关注 Qcache_hits/Qcache_lowmem_prunes 等指标。注意：在 MySQL 8.0 中查询缓存已被移除，若使用新版本请勿配置此项。
• 连接与解析：开启 skip-name-resolve 避免 DNS 反向解析带来的连接延迟（授权需用 IP）；根据并发与内存调 max_connections 与 back_log。

五 维护与监控

• 例行维护：定期执行 ANALYZE TABLE 更新统计信息，必要时对高碎片表执行 OPTIMIZE TABLE；周期性使用 mysqlcheck 检查与修复表。
• 监控与调优：利用 MySQLTuner、Percona Toolkit 获取配置与状态建议；结合 SHOW STATUS/VARIABLES 与慢查询日志持续迭代参数。
• 变更流程：任何参数调整遵循“备份—灰度—验证—回滚”，并在低峰期实施，观察错误日志与性能指标（QPS、TPS、延迟、I/O）。