CentOS 上 MySQL 性能调优实战指南

一 基线评估与监控

• 明确优化目标：以业务核心指标为导向，优先保障P95/P99 延迟、QPS/TPS、错误率以及连接可用性，这是开展 MySQL 性能优化的前提。
• 建立监控体系：在 CentOS 服务器上部署htop、iostat -x 1、vmstat 1、sar等系统监控工具；在 MySQL 层持续采集SHOW GLOBAL STATUS/LONG STATUS、SHOW ENGINE INNODB STATUS、错误日志和慢查询日志，形成完整监控闭环。
• 基线数据采集：记录当前硬件与系统状态，包括 CPU 型号和核心数、内存容量、磁盘类型与阵列方式、文件系统、I/O 调度器、网络带宽，以及 MySQL 版本、关键参数、峰值 QPS/TPS、慢查询数量、连接数、InnoDB 缓冲池命中率、磁盘 await/rrqm/s、%util 等关键指标。
• 压测与效果验证：通过代表性业务负载进行压测，例如 sysbench 或业务脚本回放，在优化前后对比各项性能指标，确保调优收益可复现，同时不影响数据正确性与业务稳定性。
• 变更流程规范：遵循“备份—小步变更—灰度/回滚预案—复核监控”的原则，每次只调整少量配置项，并保留足够观察时间窗口，降低 MySQL 调优带来的风险。

二 操作系统与存储层优化

• 存储与磁盘阵列：MySQL 数据库服务器优先选用SSD/NVMe；磁盘阵列通常建议使用RAID10，更适合高并发与高可靠场景，避免或谨慎使用RAID5，因为其写放大和重建压力较大。主库写入压力较高，从库则可结合业务特点在RAID10/RAID5之间做平衡。
• 文件系统与挂载参数：高并发、大数据表场景优先选择XFS，普通业务可使用ext4。挂载时建议增加noatime、nodiratime以减少额外 I/O；若 RAID 卡具备电池或超级电容保护，可考虑启用nobarrier。如使用 deadline 调度器，可根据业务读写特征设置read_expire约为write_expire 的 1/2，例如 read_expire=500ms、write_expire=1000ms。
• I/O 调度器选择：对于 SSD/NVMe 设备，推荐使用none/mq-deadline；机械硬盘更适合deadline。示例：echo deadline > /sys/block/sdX/queue/scheduler。
• 内核与网络参数优化：适度降低vm.swappiness（例如10），设置vm.dirty_background_ratio=5–10、vm.dirty_ratio≈其 2 倍，以平滑脏页刷盘过程；网络层可适当调大net.core.somaxconn、net.ipv4.tcp_max_syn_backlog，开启tcp_tw_reuse/tcp_tw_recycle（需注意内核版本和云环境兼容性），缩短tcp_fin_timeout，并适度增大rmem/wmem与tcp_rmem/tcp_wmem缓冲区，从而提升高并发连接和短连接场景下的 MySQL 访问性能。

三 InnoDB 与 my.cnf 关键参数建议

• 内存与缓冲池：若业务以 InnoDB 为主，建议将innodb_buffer_pool_size设置为物理内存的50%–70%；专用数据库服务器可适当提高，但要避免与操作系统争抢内存。开启innodb_buffer_pool_instances（例如4/8/16）可以降低高并发场景下的缓冲池锁竞争。
• 日志与持久化策略：将innodb_log_file_size设置为256M通常能满足大多数场景，若写入非常密集可进一步调大；innodb_log_files_in_group=2是常见配置。innodb_flush_log_at_trx_commit=1提供最强事务持久性，即每次提交都刷盘；若业务对延迟极度敏感且能接受秒级数据丢失风险，可谨慎选择2。innodb_flush_method=O_DIRECT有助于减少双缓冲；innodb_io_capacity/innodb_io_capacity_max应根据磁盘介质性能配置，例如 SSD 常见可设为2000–5000，高性能设备还可上调，并配合innodb_max_dirty_pages_pct（例如75–80）和innodb_adaptive_flushing=ON实现更平稳的刷脏策略。
• 表空间与数据文件：建议启用innodb_file_per_table=1，便于单表管理和空间回收。若默认ibdata1过小，可按需设置更大的innodb_data_file_path=ibdata1:1G:autoextend，但此类调整涉及底层表空间，必须谨慎操作并遵循官方流程。
• 并发与连接参数：根据业务高峰连接需求设置max_connections，并预留适当余量；同时关注thread_cache_size、back_log等参数，避免频繁创建线程和连接排队。启用skip-name-resolve可以减少 DNS 解析带来的连接延迟，但账户授权需要改用 IP 方式。
• 缓存与临时表优化：MySQL 8.0 已移除查询缓存（QC）；对于5.7及更早版本，如果启用 QC，建议仅用于读多写少且查询重复度极高的场景，生产环境通常建议关闭，或控制在≤512M以内。合理设置tmp_table_size与max_heap_table_size，可减少磁盘临时表带来的额外开销。
• 存储引擎与索引缓存：默认存储引擎建议使用InnoDB；如果历史系统中仍存在MyISAM表，则key_buffer_size控制在几十 MB级别通常即可，避免无谓占用服务器内存。

示例my.cnf片段（仅作参考，实际需结合业务场景与硬件资源调整）： [mysqld] innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 8 innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 5000 innodb_io_capacity_max = 20000 innodb_max_dirty_pages_pct = 78 innodb_adaptive_flushing = ON innodb_file_per_table = 1 innodb_data_file_path = ibdata1:1G:autoextend max_connections = 1500 thread_cache_size = 256 back_log = 512 skip-name-resolve default_storage_engine = InnoDB

四 SQL 与索引优化

• 定位慢 SQL：在 my.cnf 中开启slow_query_log，并设置long_query_time=1–2（根据业务可接受延迟调整），必要时启用log_queries_not_using_indexes；借助pt-query-digest或 mysqlsla分析慢查询日志，优先处理 Top SQL。
• 分析执行计划：对慢 SQL 使用EXPLAIN查看扫描类型（ALL/ref/range/index_merge）、是否命中索引、扫描行数以及 Extra 中的Using filesort/Using temporary等信息，优先通过建立合适索引或改写 SQL 来消除文件排序和临时表。
• 索引设计策略：针对高频WHERE/JOIN/ORDER BY/GROUP BY字段建立合理索引；避免在低基数列上盲目建索引；同时控制索引数量和索引宽度，降低写入成本与缓存压力。可定期使用pt-duplicate-key-checker清理重复或冗余索引，并借助pt-index-usage识别低利用率索引。
• SQL 语句与分页优化：避免SELECT ，仅查询必要字段；减少大表OFFSET深分页，建议改为游标分页或键集分页；批量写入、更新、删除操作应分批提交；对JOIN与子查询进行合理设计，必要时结合拆分与预计算提升查询效率。

五 维护、压测与回滚预案

• 日常维护：定期执行ANALYZE TABLE更新统计信息；对碎片较高的数据表执行OPTIMIZE TABLE，虽然 InnoDB 多数情况下属于在线 DDL，但仍需评估锁等待与磁盘空间影响；同时周期性整理或重建分区表，并校验主从一致性，例如使用 pt-table-checksum。
• 在线变更工具：对于大表结构调整，优先使用pt-online-schema-change或gh-ost，以降低锁表风险和业务抖动，保证线上变更更加平滑。
• 压测验证闭环：通过sysbench oltp_read_write/point_select或业务流量回放进行回归压测，重点观察TPS/QPS、P95/P99、InnoDB 行锁等待、磁盘 I/O、错误率等核心指标，在确认优化有效后再逐步推广到生产环境。
• 备份与回滚机制：任何 MySQL 配置调整前都应先完成全量/增量备份；如果变更失败或性能指标恶化，应按照预案快速回滚到上一个稳定版本及配置，并保留完整的变更记录与流量回放数据，便于后续复盘与持续优化。