MySQL服务在CentOS/Ubuntu服务器上吃掉80%内存，怎样排查和调优？

MySQL 占用大量内存是常见但可优化的问题。以下是系统化的排查和调优步骤：

一、快速诊断

1. 确认 MySQL 实际内存占用

# 查看进程内存使用
ps aux | grep mysql

# 或使用 top/htop 观察 mysqld 的 RES（常驻内存）
top -p $(pgrep mysqld)

2. 检查 MySQL 内部内存分配

-- 关键变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'query_cache_size';      -- MySQL 5.7+ 已移除
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'read_buffer_size';
SHOW VARIABLES LIKE 'read_rnd_buffer_size';
SHOW VARIABLES LIKE 'thread_cache_size';
SHOW VARIABLES LIKE 'table_open_cache';

-- 查看 InnoDB 缓冲池状态
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 查看当前会话使用的临时表
SHOW STATUS LIKE 'Created_tmp%';

3. 分析慢查询和频繁查询

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询

-- 分析最耗资源的查询
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.statements_with_full_table_scans;

二、核心优化策略

1. InnoDB Buffer Pool（最大影响因素）

# my.cnf / my.ini
[mysqld]
# 设置为物理内存的 50%-70%，不要超过可用内存
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8   # 大内存时增加实例数提升并发

⚠️ 注意：Buffer Pool 不是"吃掉"内存，而是缓存数据和索引。如果负载高，这是正常且必要的。

2. 调整连接相关缓冲区（每连接分配）

[mysqld]
# 这些是每个连接独占的，乘以 max_connections 就是总开销
max_connections = 200          # 根据实际需求降低，避免过高
thread_cache_size = 64

# 每个连接的最大内存消耗估算：
# (join_buffer + sort_buffer + read_buffer + read_rnd_buffer + tmp_table_size) * max_connections
# 建议总和不超过 1-2GB
join_buffer_size = 256K       # 默认可能过大
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 128K
tmp_table_size = 64M
max_heap_table_size = 64M

3. 其他关键参数

[mysqld]
# 键缓冲（仅对 MyISAM 有效，现代系统多用 InnoDB）
key_buffer_size = 16M

# 表描述符缓存
table_open_cache = 4000
table_open_cache_instances = 16

# 日志相关
log_error_verbosity = 2
slow_query_log_file = /var/log/mysql/slow.log

三、系统化排查流程

Step 1: 确定内存瓶颈类型

Step 2: 使用监控工具深入分析

# Percona Monitoring and Management (PMM)
# 或 Prometheus + Grafana + mysqld_exporter

# 简单脚本监控内存趋势
watch -n 5 "free -m && echo '---' && ps aux | grep mysqld"

Step 3: 检查是否有内存泄漏或异常

-- 检查是否有大事务或未释放资源
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%memory%' AND TIMER_WAIT > 1000000000000;

-- 检查长时间运行的事务
SELECT * FROM information_schema.innodb_trx 
WHERE trx_started < NOW() - INTERVAL 1 HOUR;

四、实战调优示例

假设服务器有 16GB RAM：

[mysqld]
# ==================== 核心缓存 ====================
innodb_buffer_pool_size = 10G           # ~60% 内存
innodb_buffer_pool_instances = 8

# ==================== 连接参数 ====================
max_connections = 150                    # 合理限制
thread_cache_size = 32

# ==================== 每连接缓冲区 ====================
join_buffer_size = 256K
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 128K
tmp_table_size = 64M
max_heap_table_size = 64M

# ==================== 其他 ====================
table_open_cache = 4000
key_buffer_size = 16M
query_cache_type = 0                   # 禁用（5.7+ 已移除）

# ==================== 日志 ====================
slow_query_log = 1
long_query_time = 2

验证总内存估算：

固定内存 ≈ innodb_buffer_pool(10G) + key_buffer(16M) + table_cache + log 等 ≈ 10.5G
每连接额外 ≈ (0.256+0.256+0.128+0.128+0.064) GB × 150 ≈ 0.96G
总计 ≈ 11.5G（留有余量给 OS 和其他进程）

五、注意事项

1. 不要盲目减小 Buffer Pool：如果查询响应快，高 Buffer Pool 是正常的
2. 优先优化 SQL：低效查询会导致大量临时表和排序，比配置影响更大
3. 使用连接池：应用层使用 HikariCP、DBCP 等，避免直连
4. 定期维护：OPTIMIZE TABLE、重建索引
5. 考虑升级硬件或分库分表：如果业务确实需要更多内存

六、一键诊断脚本

#!/bin/bash
echo "=== MySQL 内存诊断 ==="
echo ""
echo "--- 系统内存 ---"
free -h
echo ""
echo "--- MySQL 进程 ---"
ps aux | grep mysqld | grep -v grep
echo ""
echo "--- MySQL 关键变量 ---"
mysql -u root -e "
SHOW VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size', 'max_connections',
    'join_buffer_size', 'sort_buffer_size', 'read_buffer_size',
    'tmp_table_size', 'max_heap_table_size', 'key_buffer_size',
    'table_open_cache', 'thread_cache_size'
);
"
echo ""
echo "--- 当前连接数 ---"
mysql -u root -e "SHOW PROCESSLIST;" | wc -l

通过以上步骤，通常可以将 MySQL 内存使用控制在合理范围内，同时保证性能。