MySQL 記憶體與虛擬記憶體優化全攻略：參數計算公式、關鍵配置詳解與調優實戰範本

MySQL 記憶體與虛擬記憶體優化全攻略：參數計算公式、關鍵配置詳解與調優實戰範本

一、MySQL 記憶體佔用計算公式

最大記憶體佔用 = 全域緩衝區 + (連線級緩衝區 × 最大連線數)

MySQL Used Mem = 
  key_buffer_size + query_cache_size + tmp_table_size
+ innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections × (
    read_buffer_size + read_rnd_buffer_size
  + sort_buffer_size + join_buffer_size
  + binlog_cache_size + thread_stack
  )

二、SQL 快速計算當前配置理論峰值

-- 設定單位變數
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;

-- 固定參數 (若未動態修改，可用 @@global.xxx)
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;

-- 計算最大記憶體 (GB)
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
    @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
  + @@join_buffer_size + @@binlog_cache_size + @thread_stack
  )
) / @giga_bytes AS MAX_MEMORY_GB;

三、參數分類與生效範圍

修改 GLOBAL 需 SUPER 權限。配置檔修改需重啟生效。

四、關鍵參數詳細調優指南

1. max_connections (最大連線數)

• 預設 100，建議 500~2000 (視記憶體與應用並發)
• 過大導致連線級緩衝區總和爆記憶體
• 監控 Threads_connected / Max_used_connections 決定上限

2. key_buffer_size (MyISAM 索引緩衝)

• 僅對 MyISAM 生效
• 專用 DB 可設物理記憶體 20~30%
• 支援多鍵緩存：SET GLOBAL hot_cache.key_buffer_size=100M; + CACHE INDEX t IN hot_cache; + LOAD INDEX INTO CACHE t; 實現熱表索引預載

3. sort_buffer_size / read_buffer_size / join_buffer_size

• **連線級**，每連線獨佔，謹慎設定
• 建議：sort_buffer_size=2~4M, read_buffer_size=1~2M, join_buffer_size=2~4M
• 複雜排序/關聯查詢多可適當調大，但乘以 max_connections 估算總佔用

4. table_cache / table_open_cache (表緩存)

• 預設 2000+，4GB 記憶體建議 1024~2048
• 過大導致「太多開啟檔案」錯誤，需同步調大 open_files_limit=65535 並配合 OS ulimit -n

5. thread_cache_size (執行緒緩存)

• 減少頻繁建立/銷毀執行緒開銷
• 建議 50~200，監控 Threads_created / Connections 命中率 > 95%

6. query_cache_size / query_cache_limit

• **雙面刃**：高頻寫入場景會因失效掃描拖垮效能
• 適用：靜態資料、複雜報表查詢、低寫入頻率
• 使用 Hint 控制：SELECT SQL_CACHE ... / SELECT SQL_NO_CACHE ...
• query_cache_limit (預設 1M) 控制單個結果集上限，過大結果不緩存

7. InnoDB 核心參數

8. 連線超時控制

• wait_timeout = 7200 (2小時，預設 8小時)
• interactive_timeout = 7200
• 防止閒置連線佔用資源，配合連線池 validationQuery 使用

9. 其他實用參數

• skip-name-resolve：跳過 DNS 反解析，加速連線建立 (需配合 IP 授權)
• skip-locking (舊版) / skip-external-locking：關閉外部檔案鎖
• log-slow-queries + long_query_time=2：開啟慢查詢日誌
• max_allowed_packet=16M：防止大包斷裂

五、my.cnf 實戰調優範本 (4GB 記憶體、InnoDB 為主)

[mysqld]
# 基礎
skip-name-resolve
skip-external-locking
default-storage-engine=InnoDB
character-set-server=utf8mb4

# 連線
max_connections = 1000
max_connect_errors = 10000
wait_timeout = 7200
interactive_timeout = 7200

# 緩衝區 (InnoDB 為主)
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 0
innodb_file_per_table = 1

# MyISAM 相容 (若有)
key_buffer_size = 128M

# 連線級 (保守)
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
thread_stack = 256K

# 表/執行緒緩存
table_open_cache = 1024
thread_cache_size = 100
open_files_limit = 65535

# 查詢緩存 (視業務啟用)
query_cache_type = 0
query_cache_size = 0

# 臨時表
tmp_table_size = 64M
max_heap_table_size = 64M

# 日誌
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 安全
max_allowed_packet = 32M
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

六、效能測試工具

• mysqlslap： 官方壓測工具，模擬並發負載
• sysbench： 標準 OLTP 基準測試
• mysqlreport / mysqladmin ext -r -i： 即時狀態分析
• Percona Toolkit (pt-query-digest)： 慢查詢深度分析

參考資源

• MySQL 官方優化文件
• Hack MySQL 工具集
• iMySQL 中文社群

記憶體配置無銀彈，需根據業務讀寫比、資料量、並發模式、硬體規格綜合權衡，並持續監控 SHOW GLOBAL STATUS 與 OS 層指標 (free, iostat, vmstat) 迭代調優。