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

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

innodb_buffer_pool_size 设多少才不翻车

这是 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+ 支持在线扩容/缩容，但缩容会触发脏页刷盘，期间写入延迟上升，避开业务高峰

innodb_buffer_pool_instances 别乱设成 1

这个参数把 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 必须相等

这两个参数共同控制内存临时表上限。如果只调大 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，说明内存临时表经常撑爆

query_cache_type 已废弃，别再碰它

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'

替代思路更实际：

• 应用层加 Redis 缓存热点结果，可控性远高于 QC
• 用 SELECT SQL_NO_CACHE ... 显式跳过（仅限 5.7 测试用）
• 真正影响响应的往往是慢查询本身，优先优化 EXPLAIN 显示的全表扫描、未命中索引等问题

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