Linux MySQL如何配置优化

Linux 上 MySQL 配置优化实操指南

一 基线评估与配置入口

• 明确硬件与负载：内存容量、存储类型（优先 SSD/NVMe）、CPU 核数、业务类型（OLTP 还是 OLAP）、峰值并发与读写比例。
• 配置文件位置与生效：常见路径为 /etc/mysql/my.cnf 或 /etc/my.cnf；修改后通过 systemctl restart mysqld 重启，并用 SHOW VARIABLES LIKE 'xxx'; 验证。
• 基线监控与诊断：开启并分析慢查询日志，使用 Performance Schema 与 sys 库定位瓶颈；配合 MySQL Tuner、Percona Toolkit 定期体检。

二 核心参数优化清单

• 内存与缓存
  • InnoDB 缓冲池：专用库建议设为物理内存的 50%-70%，内存充足可到 80%；大内存（>16GB）建议拆分实例：innodb_buffer_pool_instances=4-8。监控缓冲池命中率目标 ≥99%。
    示例：innodb_buffer_pool_size=12G
  • MyISAM 键缓存：如仍使用 MyISAM，设为 MyISAM 数据总量的 25%-50%（如 key_buffer_size=256M）；MySQL 8 默认以 InnoDB 为主，可保守设置。
• 连接与会话
  • 最大连接数：结合连接池与业务峰值设置，常见 200-500；同时调 thread_cache_size=50-100 减少线程创建开销。
    示例：max_connections=500
  • 每连接内存：谨慎设置 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size，避免“连接数 × 每连接缓冲”造成内存膨胀。
• InnoDB 日志与刷盘
  • 重做日志：单个日志文件 256MB-2GB，通常 2 个文件；总容量建议覆盖 ~1 小时峰值写入量。
    示例：innodb_log_file_size=1G，innodb_log_files_in_group=2
  • 提交策略：innodb_flush_log_at_trx_commit=1（最安全）；读多写少且可接受秒级丢失时可权衡 2。
• I/O 子系统
  • 刷新方式：innodb_flush_method=O_DIRECT（绕过 OS 页缓存，减少双重缓存）。
  • I/O 能力：SSD 建议 innodb_io_capacity=2000+，高端 NVMe 可更高；必要时提高 innodb_io_capacity_max。
  • 双写缓冲：innodb_doublewrite=1 为默认且更安全；在具备 电池保护/超级电容 的高端 SSD/NVMe 上，为追求吞吐可关闭，但需充分评估风险。
• 查询与临时表
  • 查询缓存：MySQL 8.0 已移除查询缓存；5.7 及以下建议关闭（query_cache_type=0; query_cache_size=0）。
  • 临时表：设置 tmp_table_size 与 max_heap_table_size（如各 64M-256M），避免磁盘临时表激增。
• 版本与特性
  • MySQL 8 特性：可结合负载启用 并行查询（innodb_parallel_read_threads=2-4）、合理调整 自适应哈希索引 与 变更缓冲 策略。

三 不同场景的推荐配置

• OLTP（高并发短事务）
  • 示例：

    [mysqld]
    innodb_buffer_pool_size=8G
    innodb_buffer_pool_instances=8
    innodb_log_file_size=1G
    innodb_log_files_in_group=2
    innodb_flush_method=O_DIRECT
    innodb_io_capacity=2000
    innodb_io_capacity_max=4000
    innodb_flush_log_at_trx_commit=1
    max_connections=500
    thread_cache_size=100
    

• OLAP/报表（大扫描、少写入）
  • 示例：

    [mysqld]
    innodb_buffer_pool_size=16G
    innodb_change_buffering=all
    innodb_change_buffer_max_size=50
    innodb_read_io_threads=8
    innodb_write_io_threads=4
    innodb_parallel_read_threads=4
    tmp_table_size=256M
    max_heap_table_size=256M
    innodb_read_only=1   # 只读副本
    

• 通用保守起步（便于逐步调优）
  • 示例：

    [mysqld]
    innodb_buffer_pool_size=物理内存的50%-70%
    innodb_log_file_size=512M-1G
    innodb_flush_method=O_DIRECT
    innodb_io_capacity=2000
    innodb_flush_log_at_trx_commit=1
    max_connections=200-500
    thread_cache_size=50-100
    slow_query_log=1
    long_query_time=1
    

  注：以上为起点值，需结合监控与压测迭代。

四 Linux 系统层优化

• I/O 调度：SSD/NVMe 优先 noop/noop/kyber；HDD 可用 deadline。可写入内核启动参数或在线调整：echo deadline > /sys/block/sda/queue/scheduler。
• 文件描述符：提升 ulimit -n（如 65536），并在 /etc/security/limits.conf 与 PAM 中持久化。
• NUMA：多路 CPU 服务器建议开启 interleave（numactl --interleave=all 启动 mysqld）或在 mysqld_safe 中启用 innodb_numa_interleave=1，缓解跨 NUMA 访问不均。
• 内存回收：vm.swappiness 适当降低（如 10-30），减少不必要的 swap。

五 监控验证与常见误区

• 关键监控
  • 缓冲池命中率：目标 ≥99%；计算公式：

    SELECT (1 - SUM(IF(VARIABLE_NAME='Innodb_buffer_pool_reads',VARIABLE_VALUE,0)) /
                 SUM(IF(VARIABLE_NAME='Innodb_buffer_pool_read_requests',VARIABLE_VALUE,0))
           ) * 100 AS hit_rate
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');
    

  • 连接与线程：Threads_connected、Threads_cached、Max_used_connections。
  • 临时表与磁盘：Created_tmp_disk_tables、Created_tmp_tables。
  • InnoDB 锁与 I/O：SHOW ENGINE INNODB STATUS\G、SHOW STATUS LIKE 'Innodb_row_lock%';。
• 慢查询与 SQL 优化
  • 开启慢查询：slow_query_log=1、long_query_time=0.5-1；用 mysqldumpslow 或 pt-query-digest 分析并优化执行计划、索引与语句写法。
• 动态调参与重启
  • 多数 InnoDB 参数支持在线调整（如 SET GLOBAL innodb_buffer_pool_size=...;）；但 innodb_log_file_size 等需按流程重启变更。
• 常见误区
  • 盲目增大 max_connections 导致内存与上下文切换开销激增。
  • 过度分配 innodb_buffer_pool_size 引发 OOM/swap。
  • 在 SSD 上忽视 innodb_flush_method=O_DIRECT 导致双重缓存。
  • 依赖已移除/弃用的查询缓存（MySQL 8 已移除）。