在2核2G的Linux服务器上优化MySQL有哪些建议？

在 2核2G（2 vCPU, 2GB RAM）的服务器配置下，MySQL 的资源非常紧张。这个配置属于入门级/轻量级场景，核心优化思路是：严格控制内存使用、减少并发压力、优化查询效率。

以下是针对该配置的详细优化建议：

一、核心原则

1. 不要运行其他重型服务：如 Nginx/Apache + PHP-FPM + MySQL 同时运行在 2G 内存上会非常吃力。建议将 Web 服务和数据库分离，或仅部署静态网站+MySQL。
2. 关闭所有不必要的功能：禁用二进制日志、慢查询日志（除非调试）、性能模式等。
3. 优先使用 InnoDB：MyISAM 已淘汰，InnoDB 更稳定且支持事务，但需合理配置缓冲池。

二、关键参数优化（my.cnf / my.cnf.d/*.cnf）

⚠️ 注意：以下参数为起点，需根据实际业务调整。修改后重启 MySQL。

[mysqld]
# ============================================
# 基础设置
# ============================================
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp

# 字符集
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci

# ============================================
# 内存优化（最关键部分）
# ============================================
# InnoDB 缓冲池：占物理内存的 50%-60%
# 2G 系统保留约 1G 给 OS 和其他进程，因此 innodb_buffer_pool_size 设为 1G 左右
innodb_buffer_pool_size = 1G

# 如果数据量小，可略低；如果数据量大，尽量接近 1G
# 注意：单实例最大不超过 1.5G，避免 OOM

# InnoDB 日志文件：每个 50M-100M，共2个
innodb_log_file_size    = 50M
innodb_log_buffer_size  = 8M

# 临时表内存限制
tmp_table_size          = 32M
max_heap_table_size     = 32M

# 排序和连接缓冲区（默认较小，适合低并发）
sort_buffer_size        = 2M
join_buffer_size        = 2M
read_buffer_size        = 1M
read_rnd_buffer_size    = 1M

# 线程缓存（2核服务器不需要太高）
thread_cache_size       = 8

# ============================================
# 安全与稳定性
# ============================================
# 最大连接数：2G 内存不建议设太高，避免耗尽内存
max_connections         = 100

# 等待超时时间（缩短空闲连接占用）
wait_timeout            = 10
interactive_timeout     = 10

# 禁止本地文件加载（安全）
local_infile            = OFF

# ============================================
# 日志管理（生产环境建议关闭或精简）
# ============================================
# 关闭二进制日志（如果不需要主从复制和点恢复）
# binlog_format         = ROW
# log_bin               = /var/log/mysql/mysql-bin.log
# expire_logs_days      = 7

# 关闭慢查询日志（除非调试）
# slow_query_log        = 1
# long_query_time       = 2
# slow_query_log_file   = /var/log/mysql/mysql-slow.log

# 关闭性能模式
performance_schema      = OFF

# ============================================
# 其他优化
# ============================================
# 启用 AHI（自适应哈希索引），提升随机读性能
innodb_adaptive_hash_index = ON

# 刷新频率控制
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 2  # 提高写入性能，牺牲少量崩溃风险（可选）

三、操作系统层面优化

1. Swap 设置

• 不要完全禁用 Swap，但应降低其优先级，避免 MySQL 被换出到磁盘。

  
  # 查看当前 swappiness
  sysctl vm.swappiness

设置为 10（推荐）

echo "vm.swappiness=10" >> /etc/sysctl.conf
sysctl -p

#### 2. I/O Scheduler
- 如果使用 SSD，建议使用 `none` 或 `noop`；如果是 HDD，使用 `deadline` 或 `bfq`。
```bash
# 查看当前调度器
cat /sys/block/sda/queue/scheduler

# 临时修改（假设磁盘为 sda）
echo none > /sys/block/sda/queue/scheduler

3. 文件系统挂载选项

• 挂载 /var/lib/mysql 时添加 noatime 和 nodiratime，减少元数据写入开销。

  # 编辑 /etc/fstab
  /dev/sdb1 /var/lib/mysql ext4 defaults,noatime,nodiratime 0 2

4. 关闭防火墙中的非必要端口

• 仅开放 3306（如需远程访问）或仅限本地访问（更安全）。

四、SQL 与数据库设计优化

1. 索引优化

• 只加必要索引：每个索引都会增加写入开销和内存占用。
• 覆盖索引：尽量让查询通过索引完成，避免回表。
• 避免前导模糊查询：LIKE '%abc' 无法利用索引。

2. 查询优化

• **避免 SELECT ***：只查询需要的字段。
• 分页优化：大表分页使用 WHERE id > last_id LIMIT 100 替代 OFFSET。
• 避免子查询：改用 JOIN。
• 使用 EXPLAIN：分析执行计划，确保走索引。

3. 表结构优化

• 使用合适的引擎：全部使用 InnoDB。
• 分区表：如果单表超过千万行，考虑按时间分区。
• 归档历史数据：定期将旧数据迁移到冷存储或归档表。

五、应用层优化

1. 引入缓存

• Redis/Memcached：将热点数据缓存到内存，减少 MySQL 查询压力。
• 页面缓存：前端使用 CDN 或静态化页面。

2. 连接池

• 应用程序中使用连接池（如 HikariCP、DBPool），避免频繁创建/销毁连接。

3. 读写分离（进阶）

• 如果读多写少，可考虑搭建主从复制，将读请求分发到从库（但 2G 服务器可能无力承担额外节点）。

六、监控与维护

1. 监控指标

• 使用 top, htop, free -m 观察内存和 CPU。
• 使用 mysqladmin extended-status 或 Percona Monitoring Tools 监控 QPS、TPS、连接数、缓冲池命中率。

2. 定期维护

• OPTIMIZE TABLE：对大量删除/更新的表定期整理碎片（建议在低峰期进行）。
• 备份策略：即使配置低，也要定期备份（mysqldump 或 xtrabackup）。

3. 清理日志

• 如果启用了二进制日志，定期清理过期日志。
• 检查 /var/log/mysql/ 是否有异常增长。

七、常见陷阱提醒

八、总结建议

对于 2核2G 服务器：

1. 最优先：设置 innodb_buffer_pool_size = 1G，max_connections = 100。
2. 其次：关闭二进制日志和慢查询日志。
3. 再次：优化 SQL 查询，添加必要索引。
4. 最后：引入 Redis 缓存热点数据。

如果业务增长，升级硬件是最根本的解决方案。2G 内存仅适合小型个人项目、测试环境或极低流量的企业官网。