innodb_buffer_pool_size 必须调，但别盲目设到 80%
这是 MySQL 性能的“命门”参数——它决定 InnoDB 能把多少表数据和索引缓存在内存里。命中率低于 99% 时，磁盘 I/O 就成了瓶颈，哪怕用 NVMe SSD，内存访问也比它快百倍以上。
实操建议：
• 先查当前使用情况：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';
如果
Innodb_buffer_pool_pages_free长期为 0，且
Innodb_buffer_pool_pages_data接近
Innodb_buffer_pool_pages_total，说明缓冲池基本吃满，可考虑扩容；
• 初始设置可参考：专用数据库服务器上设为物理内存的 70%，但**不要无脑设 80%**——留足空间给 OS、binlog cache、sort buffer 等其他内存需求，否则可能触发系统级 swap，性能断崖式下跌；
• 动态调整（MySQL 5.7+）：
SET GLOBAL innodb_buffer_pool_size = 12884901888;（即 12G），但注意该值必须是 1MB 的整数倍，且重启后失效，需同步写入
my.cnf的
[mysqld]段。
query_cache_size 已淘汰，别再配了
MySQL 8.0 已彻底移除查询缓存（Query Cache），而 5.7 中默认关闭，且在高并发更新场景下，它反而成性能杀手：只要任意一行被 UPDATE/DELETE/INSERT，所有关联该表的 SELECT 缓存全失效，导致大量锁争用和无效刷新。
常见错误现象：
•
Qcache_hits极低，
Qcache_lowmem_prunes持续飙升；
• 启用后 QPS 不升反降，CPU 使用率异常偏高；
• 主从延迟加剧，因 query cache 清理逻辑加重主库负担。
实操建议：
• MySQL 5.7 及以后版本，直接在配置中显式禁用：
query_cache_type = 0
query_cache_size = 0
• 把省下来的内存，加给
innodb_buffer_pool_size或
tmp_table_size更实在。
缓存行为不只靠参数，还要看 SQL 写法
再大的 buffer pool，也救不了反复全表扫描、没走索引、或频繁
SELECT *的语句——这些操作会把大量冷数据页拖进内存，挤走热点数据，导致缓冲池“污染”。
使用场景与影响：
• 大字段（如 TEXT/BLOB）或宽表查询，即使只读一行，也可能加载整页（16KB）甚至多页；
•
ORDER BY RAND()或未加 LIMIT 的分页（
OFFSET 100000），会强制扫描大量无关数据页；
• JOIN 多张大表且没合适索引，buffer pool 会被中间结果集反复刷写，脏页刷新压力陡增。
实操建议：
• 用
EXPLAIN确认是否走了索引，尤其注意
type是否为
ALL或
index；
• 查询只取必要字段，避免
SELECT *；
• 分页改用游标式（
WHERE id > ? ORDER BY id LIMIT 20），减少缓冲池无效占用。
别忽略操作系统和文件系统层面的缓存协同
MySQL 的
innodb_buffer_pool是应用层缓存，而 Linux 还有 page cache ——它会自动缓存 InnoDB 的 ibd 文件读取。两者不是互斥，而是叠加生效。但若配置不当，反而浪费内存。
容易踩的坑：
• 开启
innodb_flush_method = O_DIRECT后，InnoDB 绕过 OS page cache 直写磁盘，此时 OS 层缓存失效，但 buffer pool 必须更大才能扛住压力；
• 若误设
innodb_flush_method = fsync（默认值），则同一份数据可能既在 buffer pool 又在 page cache，造成双重缓存，白白消耗内存；
•
innodb_doublewrite默认开启，它写 doublewrite buffer 时也会触发额外 I/O，虽保障崩溃恢复安全，但在高写入负载下需关注其日志写入延迟。
实操建议：
• SSD 环境推荐
innodb_flush_method = O_DIRECT，避免 double cache；
• 检查
Innodb_os_log_written和
Innodb_buffer_pool_write_requests比值，若远低于 1:10，说明写放大严重，可能需调优
innodb_log_file_size或检查是否有大量小事务。 缓冲池不是越大越好，SQL 不是越短越快，缓存策略也不是开个开关就完事——真正卡住性能的，往往藏在参数、SQL、OS 三层缓存的交界处。
