MySQL 內存分配優化實戰：key_buffer 與 innodb_buffer_pool 科學配置指南

快速配置方案

純 MyISAM 環境

• key_buffer_size = 可用內存的 20%
• innodb_buffer_pool_size = 0

純 InnoDB 環境

• innodb_buffer_pool_size = 可用內存的 70%
• key_buffer_size = 10M（極小但非零）

調優實戰經驗原則

1. 優先備份 my.cnf/my.ini
2. 根據儲存引擎與可用內存設置核心緩存參數
3. 慢查詢優化首選：添加索引、調整表結構、優化 SQL，而非調參
4. 謹慎啟用查詢緩存，除非充分理解優缺點與適用場景
5. 除非遇到具體問題（如最大連接數），否則不修改其他參數
6. 確保修改 [mysqld] 區塊下的配置

核心緩存機制深度解析

Key Buffer（MyISAM 索引緩存）

MyISAM 緩存分兩部分：

• 索引塊（1KB/B-tree，存儲於 .MYI）→ 緩存至 Key Buffer
• 數據塊（存儲於 .MYD）→ 交由操作系統文件系統緩存管理

⚠️ 必須預留足夠內存給操作系統。某些 OS 顯示內存使用率超 90% 實際仍有大量可用內存。

驗證 Key Buffer 是否足夠：

SHOW GLOBAL STATUS LIKE 'Key%';
-- 計算 Key_read_requests / Key_reads，比值 > 10 表示足夠

Buffer Pool（InnoDB 緩存池）

InnoDB 所有緩存均在 Buffer Pool，大小由 innodb_buffer_pool_size 控制。包含：

• 打開表中的 16KB 數據/索引頁
• 附加元數據開銷

MySQL 5.5+ 支援：

• 可配置頁大小（8KB/4KB）
• 多 Buffer Pool 實例（innodb_buffer_pool_instances），每個實例獨立 mutex，緩解多核爭用

另一種計算緩存大小的方法

1. 主緩存設為最小值（多應用共存或 RAM < 2GB 時）
2. 執行 SHOW TABLE STATUS 獲取表狀態
3. 計算所有 MyISAM 表 Index_length 總和 → 設 key_buffer_size ≤ 此值
4. 計算所有 InnoDB 表 Data_length + Index_length 總和 → 設 innodb_buffer_pool_size = 總和的 110%
5. 若發生 swap，適當調減兩參數

SQL 快速統計各引擎佔用：

SELECT ENGINE,
  ROUND(SUM(data_length)/1024/1024,1) AS "Data MB",
  ROUND(SUM(index_length)/1024/1024,1) AS "Index MB",
  ROUND(SUM(data_length+index_length)/1024/1024,1) AS "Total MB",
  COUNT(*) AS "Num Tables"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema NOT IN ('information_schema','performance_schema')
GROUP BY ENGINE;

多核 CPU 與 Mutex 瓶頸

MySQL 設計於單核時代，關鍵路徑存在少量 mutex：

• MyISAM key_buffer
• Query Cache
• InnoDB buffer_pool

多核普及導致 mutex 爭用成為性能瓶頸：CPU 核心超 4-8 核時 MySQL 變慢而非變快。改進情況：

• Percona XtraDB（MySQL 5.5 基礎）支援至 ~32 核
• MySQL 5.6 宣稱支援 48 核

超線程建議

• 禁用超線程
• 或限制使用 8 核以內
• 超線程共享物理緩存，異構負載時相互干擾

32/64 位系統與 MySQL 內存限制

場景	限制	建議配置
32位 OS + 32位 MySQL	進程限制 4GB，實際可用更少（如 FreeBSD maxdsiz 默認 512MB）	確認 ulimit -m，設置緩存為可用內存 20%-70%
64位 OS + 32位 MySQL	MySQL 仍受 4GB 限制	key_buffer_size ≤ 3GB，buffer_pool ≤ 3GB，強烈建議升級 64位 MySQL
64位 OS + 64位 MySQL	無實際限制	見下文詳細建議

64位 MySQL 推薦配置

• 純 MyISAM：key_buffer_size = 20% RAM（5.1.23 前限制 4GB），innodb_buffer_pool_size = 0
• 純 InnoDB：innodb_buffer_pool_size = 70% RAM，大內存考慮多實例（1-16 個，各 ≥1GB），key_buffer_size = 20M
• 混合引擎：兩參數均適當降低

其他關鍵參數

最大連接數與線程棧

• 每線程 ~200KB，1000 連接 ≈ 200MB+
• max_connections 過大引發其他問題
• MySQL 5.6+/MariaDB 5.5+ 支援線程池
• 線程棧溢出罕見，需時設 thread_stack=256K

Table Cache（table_open_cache）

• OS 限制進程打開文件數（ulimit -n，通常上萬，可能被限 1024）
• 每表需 1-3 文件，分區表等同多表
• 舊版線性掃描實現，過大反而降性能
• 監控：Opened_files / Uptime > 5 增大，≈1 可減小

查詢緩存 —— 強烈建議關閉

query_cache_type = OFF
query_cache_size = 0

理由：單一全局 mutex 導致高並發嚴重爭用；任何寫入導致相關表所有緩存失效（線性清理算法，大 QC 極慢）；微小 SQL 差異產生重複緩存項。

若必須使用：query_cache_size ≤ 50M、query_cache_type = DEMAND、顯式指定 SQL_CACHE/SQL_NO_CACHE。

Thread Cache

thread_cache_size = 10 即可，極小內存開銷，加速連接創建。

二進制日誌

啟用 log_bin 時建議 expire_logs_days = 14 防止磁盤填滿。

Swappiness 設置

MySQL 需穩定內存分配，任何 swap 嚴重損害性能。

• 專用 DB 服務器：swappiness = 5（折中值）
• 避免 0（OOM 風險）與高值（主動換出）

NUMA 架構優化

問題：OS 預設將內存分配到第一個 CPU 節點，耗盡後導致 MySQL 內存被 swap 出去。

解決：BIOS 設置內存分配為 Interleave（交錯），犧牲 ~50% 遠端訪問延遲，換取穩定性能。收益：單位百分點級。

Huge Pages（大頁面）

TLB 未命中導致雙重查找開銷。配置 4MB Huge Pages 給 innodb_buffer_pool：

1. OS 分配足量 Huge Pages
2. MySQL 啟用 large-pages

收益：單位百分點級。

MEMORY 引擎提醒

max_heap_table_size 默認 16MB，勿調過大竊佔 RAM。

參數修改生效方式

• 配置文件 [mysqld] 區塊添加 參數=值（支援 M/G 後綴），重啟生效
• 運行時動態修改：SET @@global.參數=值（不支援單位後綴），即時生效
• 建議雙重修改：動態生效 + 配置文件持久化

Web 服務器連接池配置

Apache MaxClients ≤ 50，避免超過 MySQL max_connections。

推薦工具

• MySQLTuner
• TUNING-PRIMER

⚠️ 工具報告「Maximum possible memory usage > 100%」屬保守估算（假設所有連接並發執行內存密集操作），無需恐慌。

碎片表優化：OPTIMIZE TABLE 針對高「free space」或大量 DELETE/UPDATE 的表，月執行一次足矣。