MySQL DBA 實戰：緩存參數優化全攻略（query_cache、InnoDB Buffer Pool、Binlog Cache 等）

數據庫屬 IO 密集型，內存訪問微秒級、磁盤毫秒級，差 3 個數量級。優化首要任務：把磁盤 IO 轉為內存 IO。本文系統梳理 9 大關鍵緩存參數。

1. Query Cache：query_cache_size / query_cache_type (Global)

• 緩存 SELECT 結果集，命中則跳過解析/優化/引擎層，極快。
• 致命缺陷：表任意寫入即失效所有相關緩存，寫多場景得不償失。
• 建議：query_cache_type=2 (DEMAND)，SQL 顯式 SQL_CACHE 才緩存；query_cache_size=256M 起步，按命中率 Qcache_hits/(Qcache_hits+Qcache_inserts) 調整。

2. Binlog Cache：binlog_cache_size (Global)

• 事務提交前暫存 Binlog，避免頻繁 fsync。
• 無大事務：2~4 MB；大事務頻寫：適當調大，監控 Binlog_cache_disk_use（溢寫臨時文件計數）決定是否再增。

3. MyISAM Key Buffer：key_buffer_size (Global)

• 僅緩存 MyISAM 索引塊，不緩存數據塊。
• 純 InnoDB 環境：16 MB 夠繫統計表；MyISAM 主力：內存允許下「越大越好」，目標裝下所有索引。
• SQL 必須讓過濾條件走索引，否則仍需讀數據塊觸發磁盤 IO。

4. 批量插入緩存：bulk_insert_buffer_size (Thread)

• 僅 MyISAM，服務於 INSERT ... SELECT、INSERT ... VALUES(...),(...)、LOAD DATA INFILE（非空表）。
• 常規 8 MB；大批量導入場景可調 16~32 MB，不建議更大。

5. InnoDB Buffer Pool：innodb_buffer_pool_size (Global) — 核心中的核心

• 緩存 InnoDB 索引+數據頁，類比 Oracle db_cache_size。
• 專用實例：內存允許下越大越好，目標裝下全部熱數據。
• 命中率公式：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%，目標 > 99%。

6. InnoDB 額外內存池：innodb_additional_mem_pool_size (Global)

• 存數據字典、內部結構。對象極多時調大（8~32 MB），error.log 會報 Warning 提示不足。

7. InnoDB Log Buffer：innodb_log_buffer_size (Global)

• 事務日志先寫緩存，滿足 innodb_flush_log_at_trx_commit 條件再刷盤。
• 默認 1 MB；高併發寫入可調 4~8 MB，一般不超 32 MB。
• 配合 innodb_flush_log_at_trx_commit：
  0=秒刷盤（高性能、風險 1 秒數據）
  1=提交即刷盤+同步（最安全、性能最低）
  2=提交刷盤、秒同步（折中）

8. 髒頁比例：innodb_max_dirty_pages_pct (Global)

• 控制 Buffer Pool 中髒頁上限，避免檢查點風暴。
• 建議：1GB / innodb_buffer_pool_size(GB) * 100 以內，確保崩潰恢復 < 1GB 日志回放。可接受長啟動則可調至 90，不建議超 90。

9. 經驗值速查表

以上數值基於典型場景經驗，實際上線必須基準測試+持續觀測動態調整。歡迎技術交流。