centos mysql如何优化

CentOS 上 MySQL 性能优化实操指南

一 硬件与存储优化

• 优先选用SSD/NVMe、多核CPU、充足内存，并尽量减少swap使用；数据库盘建议使用RAID10（写多/随机写场景更稳）。
• 操作系统与数据分盘/分区，避免与系统盘争抢 I/O。
• 文件系统建议用XFS（或 ext4），挂载选项加上noatime、nodiratime；如使用带电池保护的 RAID 卡可酌情使用nobarrier。
• 在 BIOS/UEFI 或内核启动参数中关闭NUMA或设置为interleave，降低跨 NUMA 访问带来的延迟波动。

二 操作系统与内核参数

• I/O 调度器：SSD/数据库负载建议使用deadline或noop（不建议用 cfq）。
• 虚拟内存：降低vm.swappiness（如 5–10），设置vm.dirty_background_ratio（如 5–10）与vm.dirty_ratio（如 20），让脏页更平滑地落盘，避免突发 I/O 尖峰。
• 网络栈（高并发连接场景）：适当增大net.core.somaxconn、net.core.netdev_max_backlog、net.ipv4.tcp_max_syn_backlog；开启tcp_tw_reuse，将tcp_fin_timeout调小（如30s）；按需调整tcp_keepalive_time（如600s）。

三 InnoDB 关键配置建议

• 缓冲池：单实例且以 InnoDB 为主时，设置innodb_buffer_pool_size≈物理内存的 50%–70%（专用库可上探至80%，需留足 OS 与连接栈内存）。
• 日志：设置innodb_log_file_size=256M–512M、innodb_log_files_in_group=2；根据业务在数据安全与吞吐间权衡innodb_flush_log_at_trx_commit（1 最安全，0/2 吞吐更高）与sync_binlog（1 最安全）。
• 文件与空间：innodb_file_per_table=1；如有需要可设置innodb_data_file_path=ibdata1:1G:autoextend以避免默认10M带来的高并发扩展抖动。
• 刷新与 I/O：启用innodb_flush_method=O_DIRECT减少双缓冲；依据磁盘能力设置innodb_io_capacity（如 SSD 可尝试5000）；开启innodb_adaptive_flushing；将innodb_max_dirty_pages_pct调到75–80以更积极写回。
• 并发与缓存：合理设置max_connections（避免过高导致上下文切换与内存膨胀）；将table_open_cache、table_definition_cache、open_files_limit提升到接近max_connections 的 10 倍量级；将tmp_table_size、max_heap_table_size控制在128M左右；线程缓存thread_cache_size按需设置（如50）；建议关闭查询缓存（MySQL 8 默认已移除）。

四 查询与索引优化

• SQL 编写：避免SELECT ，只查需要的列；减少不必要的JOIN与子查询；分页使用LIMIT*；对大结果集使用流式/游标或分批处理。
• 执行计划：用EXPLAIN检查是否走索引、是否出现filesort/Using temporary，据此增加或改写索引。
• 索引策略：为高频WHERE/JOIN/ORDER BY列建立合适索引；优先覆盖索引减少回表；避免对低基数列建索引；定期清理重复/低效索引。
• 维护与扩展：对大表按时间/范围做分区表；定期执行ANALYZE TABLE更新统计信息，碎片较多时再考虑OPTIMIZE TABLE；对超长文本/大对象考虑冷热分离或外部存储。

五 监控与维护流程

• 慢查询治理：设置slow_query_log=ON、long_query_time=0.05–2s，用pt-query-digest分析并优化 Top SQL；必要时配合索引调整或SQL 改写。
• 例行体检：使用MySQLTuner、Percona Toolkit定期评估配置与对象健康度，关注连接数、缓冲池命中率、临时表/排序、复制延迟等关键指标。
• 变更流程：任何参数调整前先备份与在测试环境验证；变更后灰度/回滚预案到位，观察错误日志、性能曲线与主从状态再扩大范围。