mysql内存配置与优化参数调整_技术教程

InnoDB缓冲池大小应设为物理内存的50%–75%（专用服务器）或40%–50%（共存服务），并合理配置实例数与临时表参数，确保缓存命中率超99%。

这是 MySQL 内存调优里最核心的参数，它决定了 InnoDB 能用多少内存缓存数据和索引。设小了，磁盘 IO 爆增；设大了，可能挤占系统其他进程内存，触发 OOM Killer 杀掉 mysqld。

实操建议：

专用数据库服务器：设为物理内存的 50%–75%，但 绝对不要超过 80%，得给 OS、连接线程、排序缓冲等留足空间
与 Web 服务共存（如 Nginx + PHP）：建议压到 40%–50%，观察 free -h 中 available 值是否持续低于 1G
动态调整可在线执行：
```
SET GLOBAL innodb_buffer_pool_size = 4294967296;
```
（单位字节，此处为 4G），但需确保 innodb_buffer_pool_instances 同时匹配（见下条）
MySQL 5.7+ 支持在线扩容/缩容，但缩容会触发脏页刷盘，期间写入延迟上升，避开业务高峰

这个参数把 buffer pool 拆成多个独立实例，减少并发访问时的互斥锁争用。设成 1 是很多老配置的默认值，但在多核机器上等于自废武功。

关键规则：

每个 instance 最小约 1GB，所以 innodb_buffer_pool_size / innodb_buffer_pool_instances ≥ 1073741824
常见安全值：buffer pool 总量 ≤ 8G → 设 4；8–16G → 设 8；≥16G → 设 16（上限通常不超 64）
不能在线修改，必须写进 my.cnf 并重启生效，否则启动时会报错：
```
Invalid value for innodb_buffer_pool_instances
```

这两个参数共同控制内存临时表上限。如果只调大 tmp_table_size 而忽略 max_heap_table_size，MySQL 仍按后者限制建表，导致本该走内存的 GROUP BY 或 ORDER BY 被迫落地磁盘，产生 Created_tmp_disk_tables 飙升。

检查与调优：

查当前值：

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

线上建议设为 64M–256M（取决于单条记录大小和并发数），但注意：每个连接独享一份，100 个连接就可能吃掉 25G 内存
配合监控 SHOW GLOBAL STATUS LIKE 'Created_tmp%';，若 Created_tmp_disk_tables / Created_tmp_tables > 0.1，说明内存临时表经常撑爆

MySQL 8.0 已彻底移除查询缓存（Query Cache），5.7 虽保留但默认关闭。很多运维还在 my.cnf 里写 query_cache_type = 1，这在 5.7 实际无效（因 query_cache_size = 0），在 8.0 启动直接报错：

Unknown system variable 'query_cache_type'

替代思路更实际：

内存优化不是堆参数，而是看 innodb_buffer_pool_read_requests 和 innodb_buffer_pool_reads 的比值——理想情况后者应低于前者的 1%。其余参数调得再细，buffer pool 命中率不上 99%，都是隔靴搔痒。

mysql内存配置与优化参数调整