MySQL 配置與管理 – 記憶體與快取調整

簡介

在實務環境中，MySQL 的效能往往受到 記憶體配置 與 快取機制 的影響最大。即使硬體資源充足，若未正確調校緩衝區與快取大小，仍可能出現查詢延遲、鎖定衝突或磁碟 I/O 飽和的問題。

本篇文章將從 核心概念、實作範例、常見陷阱 以及 最佳實踐 四個面向，帶領讀者一步步了解如何根據工作負載調整 MySQL 的記憶體與快取設定，讓資料庫在 資源使用 與 效能表現 之間取得最佳平衡。

---

核心概念

1. InnoDB 緩衝池（innodb_buffer_pool_size）

• 功能：儲存資料頁與索引頁的快取，是 InnoDB 最重要的記憶體參數。
• 調校原則：一般建議佔用 70% ~ 80% 的實體記憶體（若該伺服器僅跑 MySQL），若同時有其他服務，則視情況降低。
• 計算方式

-- 假設伺服器有 32GB 記憶體，僅跑 MySQL
SET GLOBAL innodb_buffer_pool_size = 28 * 1024 * 1024 * 1024;  -- 28GB

注意：innodb_buffer_pool_size 必須在 MySQL 啟動時設定，若在執行時變更，需要重啟服務或使用 online buffer pool resizing（MySQL 5.7+）功能。

2. Query Cache（已在 MySQL 8.0 移除）

• 說明：在 MySQL 5.7 以前，query_cache_type、query_cache_size 可用來快取 SELECT 結果。
• 為什麼被淘汰：在高併發寫入環境下，快取失效頻繁，反而成為瓶頸。
• 替代方案：使用 ProxySQL、Redis 或 Application 層快取。

3. 連線快取（thread_cache_size）

• 功能：儲存已關閉的執行緒，避免每次建立連線時重新分配資源。
• 調校建議：設定為 連線峰值的 10% ~ 20%，或根據 SHOW STATUS LIKE 'Threads_created' 觀測值調整。

SET GLOBAL thread_cache_size = 100;  -- 允許快取 100 個執行緒

4. 暫存表快取（tmp_table_size & max_heap_table_size）

• 說明：當 SELECT 需要臨時表時，MySQL 會先使用記憶體暫存 (HEAP)，若超過上述兩個參數的最小值，則會寫入磁碟。
• 調校技巧：將兩者設為相同值，且根據常見的臨時表大小調整，避免不必要的磁碟 I/O。

SET GLOBAL tmp_table_size = 64 * 1024 * 1024;      -- 64MB
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024; -- 同上

5. 其他相關參數

參數	目的	常見設定範例
innodb_log_file_size	InnoDB 重做日誌大小，影響寫入效能與恢復時間	1GB
innodb_flush_log_at_trx_commit	控制事務提交時的磁碟同步頻率	1（最安全）或 2（效能）
performance_schema	內建監控框架，會佔用少量記憶體	開啟以便分析

---

程式碼範例

以下示範 5 個常見的調校腳本，可直接在 MySQL 客戶端或透過 my.cnf 設定檔使用。

範例 1：設定 InnoDB 緩衝池（持久化於 my.cnf）

# /etc/mysql/my.cnf
[mysqld]
innodb_buffer_pool_size = 24G          # 佔用 24GB 記憶體
innodb_log_file_size    = 1G
innodb_flush_log_at_trx_commit = 2     # 提升寫入效能，容忍 1 秒資料遺失風險

重啟 MySQL 後生效：systemctl restart mysql

範例 2：即時調整 thread cache（不需要重啟）

-- 查看目前快取大小與已使用的執行緒數
SHOW GLOBAL VARIABLES LIKE 'thread_cache_size';
SHOW GLOBAL STATUS LIKE 'Threads_created';

-- 調整快取大小
SET GLOBAL thread_cache_size = 150;

範例 3：優化暫存表快取，避免磁碟寫入

-- 設定為 128MB，適合大型報表查詢
SET GLOBAL tmp_table_size = 128 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 128 * 1024 * 1024;

-- 確認設定
SHOW VARIABLES WHERE Variable_name IN ('tmp_table_size','max_heap_table_size');

範例 4：使用 performance_schema 收集快取命中率

-- 開啟 performance_schema（若尚未開啟）
SET GLOBAL performance_schema = ON;

-- 查詢 InnoDB 緩衝池命中率
SELECT
    ROUND((innodb_buffer_pool_reads / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests)) * 100, 2) AS miss_rate_percent
FROM
    performance_schema.global_status
WHERE
    VARIABLE_NAME IN ('Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');

範例 5：自動化腳本（bash）檢查記憶體使用與建議值

#!/usr/bin/env bash
# 取得 MySQL 版本與實體記憶體（單位：GB）
MYSQL_VER=$(mysql -N -e "SELECT VERSION();" | cut -d'.' -f1)
TOTAL_MEM=$(awk '/MemTotal/ {print $2/1024/1024}' /proc/meminfo | cut -d'.' -f1)

# 建議緩衝池大小：70% of total memory
RECOMMEND_POOL=$(( TOTAL_MEM * 70 / 100 ))

echo "MySQL version : $MYSQL_VER"
echo "Total RAM      : ${TOTAL_MEM}GB"
echo "Recommended innodb_buffer_pool_size : ${RECOMMEND_POOL}GB"

# 若當前設定低於建議，提示使用者
CURRENT_POOL=$(mysql -N -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" | awk '{print $2/1024/1024/1024}')
if (( $(echo "$CURRENT_POOL < $RECOMMEND_POOL" | bc -l) )); then
    echo "⚠️ 目前緩衝池大小 (${CURRENT_POOL}GB) 小於建議值，請考慮調整 my.cnf。"
fi

---

常見陷阱與最佳實踐

陷阱	說明	最佳實踐
過度配置緩衝池	設定 innodb_buffer_pool_size 超過可用記憶體，會導致 OS 換頁（swap），效能急遽下降。	依照 70%~80% 原則，留足 OS 與其他服務的記憶體。
忽略磁碟 I/O	只調整記憶體卻未檢視磁碟的讀寫延遲，快取命中率提升仍被磁碟瓶頸拖慢。	同時監控 iostat、innodb_io_capacity，必要時升級 SSD 或調整 I/O 參數。
關閉 Query Cache	在 MySQL 5.7 仍保留 Query Cache，卻未根據寫入頻率調整，導致鎖表。	MySQL 8.0 完全移除；若必須使用，將 query_cache_type 設為 DEMAND 並限制大小。
忽視臨時表	大量報表查詢產生磁碟暫存表，造成磁碟 I/O 峰值。	增大 tmp_table_size & max_heap_table_size，或改寫查詢避免大臨時表。
未啟用慢查詢日誌	調校後無法定位仍慢的 SQL。	開啟 slow_query_log，配合 pt-query-digest 分析，針對瓶頸 SQL 進行索引或重寫。

小技巧：利用 SHOW ENGINE INNODB STATUS\G 觀察 buffer pool reads、writes，以及 SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS; 取得更細部的快取分布。

---

實際應用場景

場景 1：電商高峰期（寫入密集）

• 挑戰：大量訂單寫入導致 innodb_log_file_size 與 innodb_flush_log_at_trx_commit 成為瓶頸。
• 調校：將 innodb_log_file_size 提升至 2GB，innodb_flush_log_at_trx_commit 設為 2（接受 1 秒內的資料遺失風險），同時 縮小 innodb_buffer_pool_size（保留 60%）以避免 swap。

場景 2：BI 報表系統（讀取密集）

• 挑戰：大量複雜 SELECT 產生臨時表，快取命中率不佳。
• 調校：
  1. 增大 innodb_buffer_pool_size 至 80% 記憶體。
  2. 提升 tmp_table_size 與 max_heap_table_size 至 256MB。
  3. 啟用 performance_schema 監控 temp_table_disk 次數，若仍過高，考慮將報表預先 materialized view。

場景 3：多租戶 SaaS 平台（混合工作負載）

• 挑戰：不同租戶的流量波動大，需兼顧寫入與讀取。
• 調校：
  • 使用 多緩衝池（innodb_buffer_pool_instances）分散鎖競爭。
  • 設定 thread_cache_size 為 150，減少連線建立開銷。
  • 依租戶流量動態調整 innodb_flush_log_at_trx_commit（如夜間批次寫入改為 2）。

---

總結

• 記憶體與快取是 MySQL 效能的核心：正確設定 innodb_buffer_pool_size、thread_cache_size、tmp_table_size 等參數，可顯著降低磁碟 I/O 與連線延遲。
• 調校不是一次完成：需要透過 SHOW GLOBAL STATUS、performance_schema、iostat 等工具持續觀測，並根據工作負載特性微調。
• 避免常見陷阱：過度配置、忽視磁碟瓶頸、盲目使用已淘汰的 Query Cache，都是導致效能反而下降的根源。
• 實務上，根據 寫入密集、讀取密集 或 混合 的不同場景，選擇相應的記憶體分配策略與快取調整方法，才能讓 MySQL 在高併發環境下保持穩定與高速。

透過本文提供的概念說明、實作範例與最佳實踐，讀者應能在自己的 MySQL 環境中快速定位記憶體與快取的瓶須，並以科學的方式進行優化。祝你調校順利、系統穩定！