核心認知：IO 是性能殺手
內存讀取微秒級，機械盤 IO 毫秒級，相差 3 個數量級。優化首要目標：將磁盤 IO 轉化為內存 IO。本文系統梳理 9 大關鍵緩存參數。
九大核心緩存參數詳解
1. Query Cache：query_cache_size / query_cache_type (Global)
- 緩存
SELECT 結果集，命中則跳過解析/優化/引擎層，極大提升讀性能。
- 致命缺陷：表任何變更導致相關緩存全部失效，寫入頻繁時得不償失。
- 配置建議：
- 純讀/靜態數據庫：
query_cache_type=1，size=256M-1G。
- 寫入頻繁/InnoDB 為主：
query_cache_type=0（關閉）或 =2 (DEMAND) 配合 SQL_CACHE 顯式控制。
- 監控命中率：
Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%，低於 20% 建議關閉。
2. Binlog Cache：binlog_cache_size (Global)
- 事務提交前臨時緩存 Binlog，避免頻繁寫磁盤。
- 無大事務：
2-4 MB；大事務/高寫入：適當調大（建議 ≤ 32 MB）。
- 監控：
Binlog_cache_disk_use 增長快說明內存不夠，須調大。
3. MyISAM 索引緩存：key_buffer_size (Global)
- 僅緩存 MyISAM 索引塊，不緩存數據塊。
- 純 InnoDB 環境：
16-32 MB 留給系統表即可。
- 混用 MyISAM：內存允許下「越大越好」，目標覆蓋所有 .MYI 索引文件。
- SQL 必須走索引過濾，否則全表掃描仍讀數據塊（磁盤 IO）。
4. 批量插入緩存：bulk_insert_buffer_size (Thread)
- 僅 MyISAM，服務於
INSERT ... SELECT、INSERT ... VALUES(...),(...)、LOAD DATA (非空表)。
- 常規：
8 MB；頻繁大批量導入：16-32 MB，不建議再大。
5. InnoDB 核心緩存：innodb_buffer_pool_size (Global) ⭐
- InnoDB 性能靈魂：緩存索引 + 數據塊，類比 Oracle SGA。
- 專用機器：物理內存 60%-80%（如 64G 內存給 40-50G）。
- 監控命中率：
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%，目標 > 99%。
- 大內存機器啟用
innodb_buffer_pool_instances（建議 8-16）減少鎖競爭。
6. InnoDB 額外內存池：innodb_additional_mem_pool_size (Global)
- 存放數據字典、內部結構。對象極多時需調大。
- 建議：
8-16 MB，表超多可到 32 MB。error log 出現 Warning 即需調大。
- 注意：5.6.3+ 已廢棄，併入 buffer pool，新版本無需關注。
7. InnoDB Redo Log 緩存：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 | 每秒刷日誌文件 + 刷盤，commit 不觸發 | 最快，可能丟 1 秒數據 |
| 1 | 每次 commit 刷日誌 + 刷盤 | 最安全（ACID），最慢 |
| 2 | commit 刷日誌文件，每秒刷盤 | 折中，可能丟 1 秒數據（OS cache 未刷盤） |
8. 髒頁比例控制：innodb_max_dirty_pages_pct (Global)
- 控制 Buffer Pool 中髒頁上限比例，觸發後台刷盤。
- 值大 → 刷盤少、寫入快，但崩潰恢復慢（需重放大量 Redo）。
- 建議：
≤ 90，且使恢復數據量 ≤ 1 GB（經驗值），即 1 / buffer_pool(GB) * 100。啟動時間敏感則設 75 左右。
經驗值速查表（供參考，實測為準）
| 參數 | 建議值範圍 | 適用場景備註 |
|---|
| query_cache_type | 0 (InnoDB主) / 2 (MyISAM/靜態) | 寫多讀少關閉，靜態數據開啟 |
| query_cache_size | 256 MB 起步，視命中率調整 | 一般不超 1 GB |
| binlog_cache_size | 2-4 MB (常規) / ≤32 MB (大事務) | 監控 disk_use 調整 |
| key_buffer_size | 16 MB (純InnoDB) / 覆蓋所有MYI (混用) | MyISAM場景「越大越好」 |
| bulk_insert_buffer_size | 8 MB (常規) / 16-32 MB (頻繁批量導入) | 僅MyISAM生效 |
| innodb_buffer_pool_size | 物理內存 60-80% (專用DB機) | 核心參數，「越大越好」 |
| innodb_additional_mem_pool_size | 8-16 MB / 32 MB (超多表) | 5.6+已廢棄可忽略 |
| innodb_log_buffer_size | 1 MB (默認) / 4-8 MB (高寫入) | 一般≤32 MB |
| innodb_max_dirty_pages_pct | 75-90 | 平衡恢復時間與寫入吞吐 |
調優方法論
- 基線測量：收集運行 24h+ 的
SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS。
- 單參數變更：每次調整一個參數，觀察核心指標（QPS、延遲、命中率、IOPS）。
- 壓測驗證：用
sysbench/tpcc-mysql 模擬生產負載，對比調優前後。
- 持續迭代：業務增長、數據分布變化，參數需定期複盤。
