如何让MySQL InnoDB表常驻内存以提升低频查询速度

针对你需要将300GB的InnoDB表常驻内存、同时预留700GB给其他数据的需求，以下是直接对应的实现方案：

一、缓冲池分区配置（需重启MySQL） #

将总1TB的InnoDB缓冲池拆分为专属分区和通用分区，确保目标表的内存配额不被挤占：

1. 计算并设置缓冲池核心参数：
   • 总缓冲池大小：innodb_buffer_pool_size = 1073741824000（1TB，单位字节）
   • 缓冲池块大小：设置为能整除300GB的数值，例如innodb_buffer_pool_chunk_size = 1610612736（1.5GB），确保300GB是该值的整数倍（300GB ÷ 1.5GB = 200个块）
   • 缓冲池实例数：总实例数设为667（1.5GB × 667 ≈ 1TB），其中前200个实例对应300GB专属分区，剩余467个实例对应700GB通用分区
   • 在MySQL配置文件中添加：

     innodb_buffer_pool_size = 1073741824000
     innodb_buffer_pool_chunk_size = 1610612736
     innodb_buffer_pool_instances = 667
     

2. 重启MySQL使配置生效。

二、锁定目标表到专属缓冲池 #

针对MySQL 8.0.28+版本（推荐） #

使用官方支持的PINNED选项，直接将表固定在缓冲池中，避免被LRU算法淘汰：

LOAD TABLE your_target_table INTO CACHE PINNED;

该操作会将整个表的数据页加载到缓冲池，并标记为"不可淘汰"，即使其他高频数据也无法挤出这些页。

针对低于8.0.28的版本 #

通过全表加载+定期触发热度的方式模拟锁定：

1. 在业务低峰期执行全表扫描，将表加载到缓冲池：

   SELECT * FROM your_target_table;
   

   （若表数据量极大，可分批扫描避免单次查询超时）
2. 添加定时任务，定期执行轻量查询维持表的热度：

   SELECT COUNT(*) FROM your_target_table;
   

   注：这种方式无法100%保证不被挤出，仅适合对稳定性要求稍低的场景。

三、验证表是否成功常驻内存 #

查询缓冲池中的表页数量，确认与表实际占用页数一致：

-- 查询缓冲池中目标表的页数量
SELECT TABLE_NAME, COUNT(*) AS buffer_pages
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%your_target_table%'
GROUP BY TABLE_NAME;

-- 查询表实际占用的总页数
SHOW TABLE STATUS LIKE 'your_target_table';
-- 计算方式：Data_length / 16384（默认InnoDB页大小为16KB）

若两个数值接近或一致，说明表已全部加载到内存。

关键注意事项 #

• 缓冲池配置修改需重启MySQL，操作前务必做好数据备份
• 300GB表加载到内存需要一定时间，建议在业务低峰期执行LOAD TABLE操作
• MySQL重启后需重新执行LOAD TABLE ... INTO CACHE PINNED，可将该命令加入启动脚本自动执行
• 确保服务器物理内存充足，避免因内存不足触发swap交换导致性能下降

内容的提问来源于stack exchange，提问作者Chris