MySQL key_buffer_size 科學調優指南：從指標解讀到實戰案例

MySQL key_buffer_size 科學調優指南：從指標解讀到實戰案例

參數定位

key_buffer_size 控制 **MyISAM** 索引塊緩衝區大小，直接決定索引讀取速度。**僅對 MyISAM 生效** (內部臨時表亦為 MyISAM 受影響)。

核心判斷指標

mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| Key_read_requests | 3916880184 |
| Key_reads         | 1014261    |
+-------------------+------------+

1. 未命中率

miss_rate = Key_reads / Key_read_requests
-- 範例: 1014261 / 3916880184 ≈ 1:4000 (0.025%)

• ≤ 1:1000 (0.1%) 優秀
• 1:100 ~ 1:1000 良好
• > 1:100 需調大

2. 緩衝區塊使用率

mysql> SHOW STATUS LIKE 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 0     |
| Key_blocks_used   | 50000 |
+-------------------+-------+

• Key_blocks_unused = 0 → 緩衝區滿載，必須調大
• 理想：used / (used + unused) ≈ 80%

調優建議速查表

實戰案例：某 MyISAM 主庫調優記錄

環境

• 資料庫以 MyISAM 為主
• 伺服器記憶體 8GB
• 原配置 key_buffer_size = 512M

觀測數據

Key_read_requests: 2,454,354,135,490
Key_reads: 23,490
miss_rate ≈ 0.001% (極低)
Key_blocks_unused: 0  (緩衝區已滿)

分析與決策

雖未命中率極低，但 Key_blocks_unused = 0 表示緩衝區 100% 滿載，新索引無法緩存，長期會導致抖動。決策調大至 2048M (2GB)，佔物理記憶體 25%。

調整後效果

• Key_blocks_unused 穩定在 15% 左右
• 大掃描查詢延遲下降 30%+
• 無 Swap 發生，記憶體壓力可控

避坑原則

1. 引擎匹配： 純 InnoDB 環境設 16~32M 即可 (僅供臨時表)，勿浪費記憶體
2. 上限控制： MyISAM 為主建議 ≤ 物理記憶體 30%，預留 OS、連線緩衝、InnoDB (若混用) 空間
3. 動態調整： SET GLOBAL key_buffer_size = 2048M; 即時生效，但重啟失效，需同步寫入 my.cnf
4. 多鍵緩存進階： 熱表索引專用緩存 (hot_cache) + LOAD INDEX INTO CACHE 預載，解決啟動預熱慢問題

科學讀取 Key_reads、Key_blocks_unused 等指標，結合業務負載特徵，才能給 key_buffer_size 定出最合適的「黃金數值」。