MySQL在Linux系统中的性能调优技巧

Linux 上 MySQL 性能调优的实用清单

一 系统层优化

• 资源与存储：优先使用SSD、保证充足内存与多核 CPU；在更高负载下结合合适的 RAID 策略提升吞吐与冗余。
• 内存与交换：将 vm.swappiness 调低至10或0，减少换页抖动；将脏页回写阈值设置为 vm.dirty_background_ratio=5–10%、vm.dirty_ratio=15–20%，平滑 I/O 峰值。
• I/O 调度：SSD 优先使用 noop/deadline；HDD 可用 deadline/cfq。查看与临时调整示例：
  • 查看：cat /sys/block/sdX/queue/scheduler
  • 设置：echo noop > /sys/block/sdX/queue/scheduler
• 文件系统与挂载：选择 XFS/Ext4，挂载时启用 noatime 减少元数据更新；必要时增大 ulimit -n（打开文件句柄数）以支撑高并发连接与表数量。
• 监控基线：使用 top/htop、iostat、vmstat 观察 CPU、内存、I/O 与上下文切换，先定位瓶颈再调参。

二 MySQL 配置优化

• InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的50%–70%（专用库可更高，但需为系统与其他进程预留），减少磁盘读。
• 日志与写入：适度增大 innodb_log_file_size 与 innodb_log_buffer_size，提升批量写入与崩溃恢复后的重做效率。
• 连接与会话：合理设置 max_connections，避免过高导致内存膨胀与争用；提高 thread_cache_size 复用线程，降低创建/销毁开销。
• 临时表与排序：将 tmp_table_size 与 max_heap_table_size 设为一致，避免磁盘临时表；仅在确有需要时才增大会话级 sort_buffer_size/join_buffer_size/read_rnd_buffer_size（它们按连接分配，过大反而浪费）。
• 二进制日志与一致性：启用 log_bin 保障复制与恢复；权衡性能与安全设置 sync_binlog，如 sync_binlog=1 最安全但最慢，常见折中为 1000 或 0（需评估可接受的 RPO）。
• 查询缓存：在 MySQL 8.0 中已移除查询缓存（query cache），旧版本高并发下也建议禁用（query_cache_size=0）以避免锁争用。

三 SQL 与索引优化

• 索引策略：为 WHERE/JOIN/ORDER BY 常用列建立合适索引，优先使用覆盖索引减少回表；删除冗余/重复索引以降低写入开销。
• 语句写法：避免 **SELECT ***，仅返回必要列；减少大表间不必要的 JOIN；能用 JOIN 替代低效子查询；合理使用 LIMIT 控制结果集。
• 执行计划：用 EXPLAIN 检查是否走索引、是否出现 filesort/Using temporary，据此调整索引或改写 SQL。
• 高级手段：对超大数据量表按时间/地域等做分区表；必要时进行分表以分散热点与负载。

四 维护与监控

• 例行维护：定期执行 ANALYZE TABLE 更新统计信息；对高碎片表执行 OPTIMIZE TABLE（或采用在线工具如 pt-online-schema-change）以回收空间与提升扫描效率；按需清理 二进制日志与慢查询日志，防止磁盘被占满。
• 慢查询治理：启用并定期分析慢查询日志，结合 pt-query-digest 找出 TOP SQL，优先优化执行次数多或影响大的语句。
• 内置与第三方监控：利用 Performance Schema 与 SHOW ENGINE INNODB STATUS 洞察锁、事务与缓冲池命中；使用 MySQLTuner/Percona Toolkit 获取配置与负载建议并持续迭代。

五 安全调优与常见误区

• DNS 解析：在 my.cnf 中设置 skip-name-resolve，以 IP 授权，避免 DNS 反向解析带来的连接延迟（注意授权需改用 IP）。
• 连接队列：根据短连接突发情况适当增大 back_log（如不超过系统限制），缓解瞬时连接堆积。
• 参数边界：避免把会话级缓冲（如 sort_buffer_size）设得过大，防止并发时内存爆炸；谨慎提高 max_connections，配合连接池与合理的超时回收策略。
• 变更流程：任何参数调整都应先备份、在测试环境验证，并灰度/逐步上线，同时持续监控错误日志、连接数、I/O 与复制延迟等关键指标。