MySQL key_buffer_size 參數優化實戰：從原理到調優的完整指南

前言：MyISAM 索引效能的「生命線」

key_buffer_size 是 MyISAM 儲存引擎索引緩衝區的大小控制參數，直接決定索引讀取速度。雖然 InnoDB 已成主流，但 MyISAM 仍廣泛用於內部臨時表、系統表及部分歷史業務表。本文結合官方文檔、實戰案例與監控體系，構建從原理認知到精準調優的完整知識鏈路。

一、參數本質與官方定義

• 作用範圍：僅緩存 MyISAM 表的**索引塊**，**不緩存數據塊**（數據讀取依賴 OS File System Cache）。
• 共享特性：所有執行緒共享同一緩衝區（除非配置多 Key Cache）。
• 分配機制：設定值為**上限**，實際按需動態分配，啟動時不佔用全部記憶體。
• 版本限制：32 位系統上限 4GB；64 位理論支援更大，但受限於行程地址空間與 OS 限制。

二、多 Key Cache 進階玩法（MySQL 5.0+）

將熱點表索引隔離至專屬緩存，避免冷熱數據擠佔：

-- 1. 創建命名緩存
SET GLOBAL key_buffer_1.key_buffer_size = 8M;
SET GLOBAL key_buffer_2.key_buffer_size = 8M;

-- 2. 綁定表到指定緩存
CACHE INDEX t1, t2 IN key_buffer_1;

-- 3. 預熱載入索引
LOAD INDEX INTO CACHE t1, t2;

適用場景：核心業務表（如用戶表、訂單索引）固定佔用專屬緩存，防止大報表掃描沖刷熱點索引。

三、核心監控指標與計算公式

1. 索引命中率—— 核心 KPI

mysql> SHOW GLOBAL STATUS LIKE 'Key_read%';
+-------------------+-------------+
| Variable_name     | Value       |
+-------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads         | 6798830     |
+-------------------+-------------+

未命中率 = Key_reads / Key_read_requests × 100%

• 優秀：≤ 0.01%（1 萬次請求僅 1 次磁碟讀）
• 良好：≤ 0.1%（千次請求 1 次磁碟讀）
• 需擴容：> 0.1%
• 過度分配：< 0.001%（可適度縮減，釋放記憶體）

案例：上述數據未命中率 0.0244%，屬良好區間；若目標極致，可擴容至 0.01% 以下。

2. 緩存使用率—— 輔助決策

mysql> 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%

• 理想區間：≈ 80%（留 20% 空間應對新索引與波動）
• 100%：緩存滿載，需擴容或檢查是否存在過度/冗餘索引。

四、設定紅線與最佳實踐

1. 記憶體分配原則

• MyISAM 為主庫：物理記憶體 **25%~40%**（如 64GB 機器設 16~25GB）。
• 混合/InnoDB 為主：16M~512M** 足矩（僅服務臨時表、系統表）。
• 絕對禁忌：超過物理記憶體 **50%** → 觸發 OS Swapping，效能雪崩。MyISAM 數據讀取依賴 OS Cache，**必須預留足夠記憶體給文件系統緩存**。