MySQL key_buffer_size 參數調優實戰：MyISAM 索引緩存命中率分析

參數作用

key_buffer_size 專供 **MyISAM** 索引塊緩存，**不影響 InnoDB** (InnoDB 用 innodb_buffer_pool_size)。設置過小導致頻繁磁碟讀取，過大浪費內存並可能觸發 Swap。

關鍵監控指標

SHOW GLOBAL STATUS LIKE 'Key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 27813678764  -- 索引讀取請求總數
| Key_reads         | 6798830     -- 未命中緩存，直接讀磁碟次數
+-------------------+-----------+

未命中率計算

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

示例：6798830 / 27813678764 ≈ 0.0244% (4000 次請求才 1 次磁碟讀，**極佳**)。

經驗閾值

• < 0.1%：健康 (每 1000 請求 < 1 次磁碟讀)
• 0.1% ~ 1%：可接受，建議適當增大
• > 1%：嚴重不足，必須擴容
• < 0.01%：過大，可適當縮減釋放內存

緩存塊利用率分析

SHOW GLOBAL STATUS LIKE 'key_blocks_u%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_blocks_unused | 0          -- 未用塊數
| Key_blocks_used   | 413543     -- 歷史峰值使用塊數
+-------------------+-----------+

利用率 = Key_blocks_used / (Key_blocks_used + Key_blocks_unused) * 100%

示例：100% (無空閒塊) → 索引僵滯或緩存不足，需擴容或清理無用索引。

理想區間：80% 左右，留有增長空間且無浪費。

調優實戰步驟

1. 收集基線：運行業務高峰期 1~2 小時，記錄上述指標。
2. 計算未命中率與塊利用率。
3. 調整 my.cnf：

   [mysqld]
   key_buffer_size = 512M  # 根據內存總量佔 10~25%，MyISAM 為主庫可調大

4. 重啟驗證，持續觀察 24h。
5. 若 InnoDB 為主，**將 key_buffer_size 降至 16~32M** 僅滿足系統表/臨時表需求，將內存讓給 innodb_buffer_pool_size。

避坑指南

• 不要盲目設為物理內存 50%+，32 位系統進程地址空間 2/3GB 限制易 OOM。
• MyISAM 表大量 REPAIR TABLE/OPTIMIZE TABLE 會臨時佔用 key buffer，預留餘量。
• 監控 Key_blocks_unused 長期為 0 且未命中率高 → 確認是否有「隱式臨時表」過多 (EXPLAIN 中 Using temporary)，優化 SQL 降臨時表比例。