云服务器
在 2核4G 的 Linux 服务器（典型于轻量级生产或高负载测试环境）上优化 MySQL，需平衡性能、稳定性与内存安全，避免因配置不当导致 OOM Killer 杀进程或频繁 swap。以下是经过实践验证的、分层次的优化建议：
✅ 一、核心原则（先决条件）
- 确认 MySQL 版本：优先使用 MySQL 8.0+（InnoDB 性能更好、内存管理更智能）或 Percona Server（对小内存更友好）。
- 禁用不必要的服务：关闭 Apache/Nginx/Redis 等非必需服务，确保 MySQL 可稳定使用 ≥3GB 内存。
- 监控基线：用
mysqltuner.pl（https://github.com/major/MySQLTuner-perl）跑一次基准诊断（勿直接按其建议全改！ 它常过度推荐大内存值）。
⚙️ 二、关键配置优化（/etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）
| 参数 | 推荐值（2C4G） | 说明 |
|---|---|---|
innodb_buffer_pool_size |
2G ~ 2.5G（占物理内存 50%~65%，绝对不要 >3G） | InnoDB 最重要参数；设过大易触发 OOM；设过小则缓存命中率低。✅ 建议 2200M（2.15G），留足系统+连接内存。 |
innodb_log_file_size |
256M（单个日志文件） | 日志太小 → 频繁 checkpoint 影响写入；太大 → 崩溃恢复慢。搭配 innodb_log_files_in_group=2（默认）。⚠️ 修改前必须 mysqld --innodb-fast-shutdown=0 + 停库删旧 log 文件。 |
max_connections |
100~150（默认151可接受，但建议调至 120） |
每连接约占用 2~3MB 内存（含排序/临时表缓冲），150 连接 ≈ 300MB+；过高易耗尽内存。用 show status like 'Threads_connected'; 观察峰值。 |
sort_buffer_size |
512K（全局）或 256K | 切勿设为几 MB！ 此值 per-connection 分配，100 连接 × 2M = 200MB 冗余开销。默认 256K 足够多数场景。 |
read_buffer_size / read_rnd_buffer_size |
256K / 512K | 同上，避免 per-connection 内存爆炸。 |
tmp_table_size & max_heap_table_size |
64M（两者必须相等） | 控制内存临时表上限，超限自动转磁盘临时表（慢）。64M 平衡内存使用与性能。 |
innodb_flush_method |
O_DIRECT（Linux） |
绕过 OS cache，避免双重缓存，减少 swap 压力（尤其小内存环境）。✅ 必开！ |
innodb_io_capacity |
200（HDD）或 1000（SSD/NVMe） |
匹配存储性能，提升刷脏页效率。用 lsblk -d -o NAME,ROTA 查是否 SSD（ROTA=0 是 SSD）。 |
innodb_thread_concurrency |
0（MySQL 5.7+ 默认，表示不限制） | 小核数下设固定值反而降低吞吐，保持 0 即可。 |
query_cache_type |
0（彻底禁用） | MySQL 8.0 已移除；5.7 中若开启，高并发下锁争用严重，务必关闭！ |
🔧 配置后必做：
sudo systemctl restart mysql mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
📊 三、数据库层面优化（无需重启）
| 类别 | 操作 | 说明 |
|---|---|---|
| 索引优化 | EXPLAIN 分析慢查询；为 WHERE/JOIN/ORDER BY 字段建复合索引；删除重复/未使用索引 |
使用 sys.schema_unused_indexes（MySQL 5.7+）或 pt-index-usage 检测冗余索引 |
| 慢查询治理 | 开启慢日志：slow_query_log = ONlong_query_time = 1log_queries_not_using_indexes = OFF（仅调试时开） |
分析 mysqldumpslow -s t /var/log/mysql/mysql-slow.log 找 TOP SQL |
| 表结构优化 | OPTIMIZE TABLE tbl;（仅对频繁 DELETE/UPDATE 的 InnoDB 表，且有明显碎片）；使用 TINYINT 替代 INT，VARCHAR(50) 替代 TEXT |
减少 I/O 和内存占用 |
| 连接池化 | 应用层启用连接池（如 HikariCP、Druid），设置 minIdle=5, maxActive=50 |
避免频繁创建/销毁连接开销 |
🛡 四、系统级加固（防 OOM）
# 1. 确保 swappiness 极低（避免 MySQL 内存被 swap）
echo 'vm.swappiness = 1' | sudo tee -a /etc/sysctl.conf
sudo sysctl -p
# 2. 限制 MySQL 内存上限（systemd 方式，更安全）
sudo systemctl edit mysql
# 添加：
[Service]
MemoryLimit=3.2G # 略高于 buffer_pool，留余量
RestartSec=10
# 3. 监控内存压力
watch -n 1 'free -h && echo "---" && ps aux --sort=-%mem | head -10'🚫 五、务必避免的“伪优化”
| ❌ 错误做法 | ✅ 正确做法 |
|---|---|
innodb_buffer_pool_size = 3500M |
→ OOM 高风险！留至少 1G 给 OS + 其他进程 |
key_buffer_size = 512M（MyISAM） |
→ 若不用 MyISAM，设为 16M 或 0；InnoDB 不用此参数 |
innodb_buffer_pool_instances = 8 |
→ 2C 机器设 2 或 4 即可（实例数 ≤ CPU 核数），过多增加 mutex 开销 |
开启 performance_schema 全量采集 |
→ 生产环境建议关闭或仅开启必要 instruments：performance_schema=ON + 动态关闭无用项 |
📈 六、效果验证（优化后必做）
-- 1. 缓存命中率（目标 >95%）
SELECT
(1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100 AS hit_rate
FROM information_schema.GLOBAL_STATUS
WHERE variable_name IN ('innodb_buffer_pool_reads', 'innodb_buffer_pool_read_requests');
-- 2. 检查是否有大量临时表落盘
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables / Created_tmp_tables < 10% 为佳
-- 3. 检查连接数是否健康
SHOW STATUS LIKE 'Threads_%';
-- Threads_connected < max_connections * 0.8，且 Threads_created 增长缓慢💡 补充建议（进阶）
- 考虑 ProxySQL 或 Vitess：若读多写少，可加只读从库 + 读写分离，分担主库压力。
- 定期归档冷数据：用
pt-archiver将历史表分区归档，减小主表体积。 - 备份策略：用
mydumper（多线程）替代mysqldump，减少锁表时间。
如需进一步诊断，请提供：
mysql --versioncat /proc/meminfo | grep -i "mem"mysqltuner.pl输出摘要（脱敏后）- 慢查询日志中 1 条典型 SQL 及其
EXPLAIN结果
我可以为你定制化调优方案 👇
需要我帮你生成一份 即拷即用的 my.cnf 模板（适配 2C4G + SSD） 吗？