2核2G的服务器运行MySQL时最关键的几个性能优化参数是什么？

在2核2G的低配服务器上运行MySQL时，由于资源有限（CPU和内存都较小），必须合理配置关键参数以避免OOM（内存溢出）或性能瓶颈。以下是最关键的几个性能优化参数及其推荐设置：

🔑 1. innodb_buffer_pool_size

• 作用：InnoDB 缓冲池，用于缓存数据和索引，是 MySQL 最重要的性能参数。
• 建议值：

  innodb_buffer_pool_size = 512M ~ 1G

  • 对于 2G 内存的服务器，建议设置为 512MB 到 1GB。
  • 如果还有其他服务（如Web服务器、PHP等），应留足内存给系统和其他进程。

⚠️ 不要超过物理内存的 70%，否则容易导致 swap 或 OOM。

🔑 2. max_connections

• 作用：最大并发连接数。
• 默认值：通常为 151。
• 建议值：

  max_connections = 50 ~ 100

  • 每个连接会消耗一定内存（thread_stack、sort_buffer等），连接过多会导致内存耗尽。
  • 根据应用实际需求调整，避免过高。

🔑 3. innodb_log_file_size 和 innodb_log_files_in_group

• 作用：InnoDB 重做日志文件大小，影响写入性能和恢复时间。
• 建议值：

  innodb_log_file_size = 128M
  # 总日志大小 = log_file_size × files_in_group（默认2）

  • 合理的日志大小可减少 checkpoint 频率，提升写入性能。
  • 注意：修改此参数需先停库，删除旧日志文件再重启。

🔑 4. query_cache_type 和 query_cache_size（仅适用于 MySQL < 8.0）

• 说明：MySQL 8.0 已移除查询缓存。
• 建议（MySQL 5.7 及以下）：

  query_cache_type = 0
  query_cache_size = 0

  • 在小内存环境下，查询缓存可能带来锁争用和内存碎片，反而降低性能。
  • 建议关闭，改用应用层缓存（如 Redis）。

🔑 5. tmp_table_size 和 max_heap_table_size

• 作用：控制内存中临时表的最大大小。
• 建议值：

  tmp_table_size = 64M
  max_heap_table_size = 64M

  • 过大会浪费内存，过小会导致频繁磁盘临时表（慢）。
  • 保持两者相等，避免混淆。

🔑 6. table_open_cache 和 table_definition_cache

• 作用：缓存打开的表和表定义。
• 建议值：

  table_open_cache = 400
  table_definition_cache = 400

  • 默认值可能偏高，可根据 Open_tables / Opened_tables 监控调整。
  • 避免过高导致文件描述符耗尽。

🔑 7. key_buffer_size（仅 MyISAM 引擎需要）

• 作用：MyISAM 索引缓存。
• 建议值：

  key_buffer_size = 32M

  • 如果只使用 InnoDB，可设为 16~32M 即可。

🔑 8. 其他系统级优化建议

• 禁用不必要的插件/功能：

  skip_name_resolve = ON          # 禁止DNS反查，加快连接
  performance_schema = OFF        # 小内存下可关闭（或设为ON但限制内存）
  innodb_flush_log_at_trx_commit = 2  # 提升写入性能（牺牲一点持久性）
  sync_binlog = 0                 # 生产环境慎用，测试可用

✅ 总结：2核2G 推荐配置片段（my.cnf）

[mysqld]
innodb_buffer_pool_size = 768M
max_connections = 80
innodb_log_file_size = 128M
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 400
table_definition_cache = 400
key_buffer_size = 32M

# 性能与安全
skip_name_resolve = ON
performance_schema = OFF
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

# 关闭查询缓存（MySQL 5.7）
query_cache_type = 0
query_cache_size = 0

📊 建议监控指标

• 使用 SHOW STATUS LIKE 'Threads_connected';
• SHOW ENGINE INNODB STATUS;
• free -h 查看内存使用
• top 或 htop 观察 mysqld 内存占用

✅ 最终原则：在资源受限环境下，稳定 > 性能，优先保证不崩溃，再逐步调优。建议结合慢查询日志（slow_query_log）优化 SQL，往往比参数调优更有效。