MySQL 核心性能參數深度解析：從 key_buffer 到 InnoDB 緩衝池

監控入口

-- 靜態配置參數
SHOW VARIABLES LIKE '%buffer%';

-- 動態運行狀態
SHOW STATUS LIKE '%buffer%';

MyISAM 專用參數

key_buffer_size

索引塊緩存大小，線程共享。MySQL 5.1+ 支持多緩存實例，可將熱表索引隔離到專用緩存降低競爭。

-- 創建獨立緩存
SET GLOBAL hot_cache.key_buffer_size = 100*1024;

-- 將表索引綁定到緩存
CACHE INDEX t1, t2 IN hot_cache;

-- 啟動預熱
LOAD INDEX INTO CACHE t1, t2;

配置文件持久化：

hot_cache.key_buffer_size=1G
init_file=/path/to/init_index.sql

table_cache (table_open_cache)

表緩存數量上限，與 max_connections 相關。通過 Open_tables（當前打開數）與 Opened_tables（累計打開數）觀察命中率。

• Open_tables 持續接近 table_cache → 需增大
• Opened_tables 快速增長 → 緩存不足頻繁開表

InnoDB 核心參數

innodb_buffer_pool_size

最關鍵參數，同時緩存數據頁與索引頁。建議設為物理內存 60%-80%（專用 DB 服務器）。過大導致 Swap，反而拖垮性能。

innodb_flush_log_at_trx_commit

其他重要參數

• innodb_additional_mem_pool_size：數據字典、內部結構內存池（5.6.3 後已廢棄，併入 buffer pool）
• innodb_log_buffer_size：事務日誌緩存，大事務建議增大（如 64M）
• innodb_log_file_size：重做日誌文件大小，影響檢查點頻率與恢復時間
• innodb_lock_wait_timeout：行鎖等待超時（默認 50s），死鎖檢測優先於此參數
• innodb_support_xa：分布式事務支持（默認 ON），單實例可關閉提升性能

調優建議

1. 優先調整 innodb_buffer_pool_size，觀察 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率
2. 根據業務場景權衡 innodb_flush_log_at_trx_commit
3. 監控 Opened_tables 決定 table_open_cache
4. MyISAM 表僅調整 key_buffer_size