如何优化MySQL在2核2G服务器上的内存使用？

在 2 核 2G（约 1.8GB 可用内存）的服务器上优化 MySQL，核心原则是严格控制缓冲池大小、减少额外内存开销、避免临时表溢出到磁盘。以下是具体可操作的优化方案：

🔧 一、关键配置项调整（my.cnf / my.ini）

[mysqld]
# 基础安全设置
max_connections = 50          # 限制并发连接数（默认 151 过高）
thread_cache_size = 16        # 缓存线程，减少创建开销

# ⭐ 核心：缓冲池大小（占物理内存 40%~50%）
innodb_buffer_pool_size = 768M   # 推荐值：总内存 2G × 35%~40% ≈ 700–800M
# 注意：必须小于 total_memory - OS overhead - other_services
# 若运行其他服务（如 Nginx/PHP），建议设为 512M~640M

# InnoDB 日志与刷新
innodb_log_file_size = 64M      # 小文件利于崩溃恢复和内存管理
innodb_flush_log_at_trx_commit = 1  # 保证事务安全（生产环境通常保留）
innodb_flush_method = O_DIRECT  # 避免双重缓冲，减少 OS 页缓存干扰

# 临时表控制（防止 spill to disk）
tmp_table_size = 64M
max_heap_table_size = 64M

# 查询缓存（MySQL 8.0+ 已移除；5.7 及以下谨慎使用）
# query_cache_type = 1
# query_cache_size = 32M        # 仅当读多写少且无高并发时启用

# 其他内存敏感参数
sort_buffer_size = 128K         # 每连接独立分配，设小！
read_buffer_size = 128K
read_rnd_buffer_size = 128K
join_buffer_size = 128K
key_buffer_size = 0             # 若只用 InnoDB，设为 0 或极小（如 4M）

# 禁止过度分配
skip-name-resolve               # 禁用 DNS 解析，加快连接建立
local-infile = 0                # 关闭本地文件导入，提升安全

✅ 验证公式：
预估最大内存 ≈ innodb_buffer_pool_size + (max_connections × (sort_buffer + read_buffer + join_buffer))
目标：< 1.6GB（留 200MB 给 OS + 应用进程）

📊 二、监控与诊断工具

1. 查看实际内存占用

   mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_pages_%';
   mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
   mysql> SELECT * FROM performance_schema.memory_summary_by_thread_by_event_name 
         WHERE THREAD_ID IS NOT NULL ORDER BY SUM_NUMBER_OF_BYTES_USED DESC LIMIT 10;

2. 检查是否频繁 spill to disk

   SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
   SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
   -- 若 Created_tmp_disk_tables > 0，说明 tmp_table_size 太小或 SQL 未走索引

3. 慢查询分析

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   SET GLOBAL log_queries_not_using_indexes = 'ON';

🚀 三、SQL 与架构优化建议

🛡️ 四、系统级配合

• OS 层面：

  # 限制 swap 使用（避免 thrashing）
  echo "vm.swappiness = 10" >> /etc/sysctl.conf
  sysctl -p
  
  # 禁用透明大页（THP）——显著降低 MySQL 抖动
  echo never > /sys/kernel/mm/transparent_hugepage/enabled

• 容器化场景（Docker）：

  # 显式限制内存 & 设置 cgroup
  docker run -d 
  --memory="1.8g" 
  --memory-swap="1.8g" 
  --cpus="2" 
  -e MYSQL_ROOT_PASSWORD=xxx 
  mysql:8.0

  ⚠️ 务必同步调低 innodb_buffer_pool_size，否则 OOM Killer 会终止进程。

📉 五、降级策略（极端资源紧张时）

若仍无法满足需求：

1. 切换为 MariaDB 10.6+（更轻量，默认配置更保守）
2. 启用 Percona Server 的 innodb_adaptive_hash_index=OFF + 自定义 buffer pool
3. 考虑 读写分离：主库只写，从库承担大部分读（即使单实例也可分库）
4. 引入 Redis/Memcached 缓存热点数据，减少 DB 压力

需要我根据你的具体业务场景（如：电商订单系统？博客 CMS？高频写入日志？）提供定制化配置模板吗？