Debian 上降低 MySQL 资源占用的实用方案
一 监控与定位
- 系统层：用 top/htop 观察 mysqld 的 CPU、RES、MEM%；用 free -m、vmstat 1 看整体内存与换页；检查 /var/log/mysql/error.log 是否有异常重启、InnoDB 报错等线索。
- MySQL 层：开启并查询 performance_schema，用视图定位内存大户与热点会话：
- 总内存：SELECT * FROM sys.memory_global_total;
- 会话内存：SELECT * FROM sys.session ORDER BY current_memory DESC LIMIT 10;
- 按线程：SELECT * FROM sys.memory_by_thread_by_current_bytes;
- 按分配类型：SELECT * FROM sys.memory_global_by_current_bytes;
- 慢查询：开启 slow_query_log，用 pt-query-digest 或 mysqldumpslow 分析并优先优化耗时 SQL。
二 配置参数优化
- 全局缓冲（InnoDB 为主）：将 innodb_buffer_pool_size 设为可用内存的 50%–70%（写多或内存充足可上探至 60%–80%）；写密集场景可适度增大 innodb_log_buffer_size（如 256M）。
- 连接与会话：合理控制 max_connections，避免过高导致线程与内存膨胀；启用 thread_cache_size 复用线程；适度降低 wait_timeout / interactive_timeout 回收空闲连接。
- 临时表与排序/连接：将 tmp_table_size 与 max_heap_table_size 设为 64M 左右以抑制内存临时表；仅在确有需要时才提高 sort_buffer_size / join_buffer_size（二者为“每个连接”分配，过高会放大总内存）。
- 存储引擎：若基本不用 MyISAM，将 key_buffer_size 降到 64M 以下。
- 查询缓存：在 MySQL 8.0+ 已移除查询缓存，旧版本如启用需评估并谨慎设置。
- 示例（仅示意，需按实例内存与负载调优）：
[mysqld]
innodb_buffer_pool_size = 4G # 8GB 内存示例
innodb_log_buffer_size = 256M
max_connections = 150
thread_cache_size = 50
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
key_buffer_size = 16M
slow_query_log = 1
long_query_time = 1
三 查询与索引优化
- 只查需要的列，避免 **SELECT ***；用 EXPLAIN 检查是否走索引、是否出现 Using filesort / Using temporary。
- 优化 WHERE 条件，避免在列上使用函数导致索引失效；尽量用 JOIN 替代子查询；分页加 LIMIT。
- 为高频条件建立合适的 单列/复合索引，定期清理无用或重复索引；大表可考虑 分区表。
- 批量写入使用批量提交，减少事务/语句往返。
四 架构与系统层措施
- 连接治理：在应用侧使用连接池（如 HikariCP、ProxySQL），避免短连接风暴；必要时用 cgroup/systemd 给 mysqld 设置内存上限，防止 OOM。
- 存储与 I/O：优先 SSD；阵列建议 RAID10（写多）或 RAID5（读多）；合理设置 innodb_flush_log_at_trx_commit=2 可提升写性能（存在少量事务丢失风险，需结合业务权衡）。
- 维护与清理：定期归档/清理历史数据，减少扫描与锁竞争；必要时对高碎片表执行 OPTIMIZE TABLE 或在线改表工具（如 pt-online-schema-change）。
五 安全变更流程
- 任何参数调整前先备份（物理/逻辑皆可）；在测试环境验证；采用“小步快跑、逐项评估”的方式上线；变更后持续观察 performance_schema 与慢查询指标，确认资源占用下降且性能未劣化。
