innodb_buffer_pool_size必须调但别盲目设80%，应据内存70%初设并留足OS等内存；query_cache_size已淘汰需禁用；SQL低效会污染缓冲池；OS page cache与buffer pool需协同配置。
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 = 0query_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 或检查是否有大量小事务。
