MySQL 核心優化參數深度解析：key_buffer_size、table_cache 與 tmp_table_size 調優實戰

MySQL 核心優化參數深度解析：key_buffer_size、table_cache 與 tmp_table_size 調優實戰

參數查看基礎

• SHOW VARIABLES：靜態配置參數 (啟動後固定)
• SHOW STATUS：動態運行狀態 (持續變化)

一、key_buffer_size (MyISAM 索引緩衝區)

作用

所有執行緒共享，決定索引讀取速度。僅對 MyISAM 生效。

健康度檢查

mysql> SHOW STATUS LIKE 'key_read%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| Key_read_requests | 3916880184 |
| Key_reads         | 1014261    |
+-------------------+------------+

• 未命中率 = Key_reads / Key_read_requests ≈ 1:4000 (0.025%) → **優秀**
• 目標：≤ 1:1000

塊使用率

mysql> SHOW STATUS LIKE 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 0     |
| Key_blocks_used   | 50000 |
+-------------------+-------+

• Key_blocks_unused = 0 → 緩衝區**完全滿載**，需調大
• 理想：used / (used + unused) ≈ 80%

配置實例

該伺服器 key_buffer_size = 512M (536870912 bytes)，雖未命中率低，但塊已滿載，建議擴至 1~2GB (視記憶體)。

二、table_cache / table_open_cache (表緩存)

作用

緩存打開的表描述符 (frm 檔案句柄)，避免重複開啟開銷。每表佔 2 個檔案描述符。

健康度檢查

mysql> SHOW STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 512   |  -- 當前打開表數
| Opened_tables | 1200  |  -- 歷史累計打開表數
+---------------+-------+

• Open_tables == table_cache 且 Opened_tables **持續快速增長** → 緩存不足，需調大
• FLUSH TABLES 會關閉閒置表，Open_tables 下降，Opened_tables 不變

配置策略

• 預設：2GB 以下 256~512，4GB 約 2048
• 不可盲目調大： 過大導致「檔案描述符耗盡」 (Too many open files)，引發不穩定
• **必須同步調大：** open_files_limit = 65535 (my.cnf) + OS 層 ulimit -n 65535 (寫入 /etc/security/limits.conf 永久生效)
• 4GB 記憶體建議 table_open_cache = 1024~2048，open_files_limit = 8192+

三、tmp_table_size / max_heap_table_size (內部臨時表)

作用

• tmp_table_size：內存臨時表大小上限
• max_heap_table_size：用戶建立 MEMORY 表上限，**亦限制內部臨時表**
• 實際上限 = MIN(tmp_table_size, max_heap_table_size)
• 超過則轉為磁碟 MyISAM/InnoDB 臨時表 (極慢)

健康度檢查

mysql> SHOW STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_tables      | 50000 |  -- 總臨時表
| Created_tmp_disk_tables | 500   |  -- 磁碟臨時表
+-------------------------+-------+

• 磁碟臨時表比率 = Created_tmp_disk_tables / Created_tmp_tables
• 目標：**< 10%**，理想 < 1%
• 比率高 → 調大 tmp_table_size、max_heap_table_size (建議同值，如 64M~256M)

注意

• 含 TEXT/BLOB、GROUP BY/DISTINCT 無索引、UNION 等易產生臨時表
• 過大臨時表佔用連線級記憶體，乘以 max_connections 評估風險

四、InnoDB 核心參數補充

innodb_buffer_pool_size

• **InnoDB 效能核心**，緩存數據頁 + 索引頁 (MyISAM 僅緩存索引)
• 專用 DB 建議 **物理記憶體 60~80%**
• 含 ~21% 額外開銷 (頁描述、自適應哈希、崩潰恢復)
• 例：12GB buffer pool → 實際佔用 ~14.5GB，+OS+執行緒棧 ≈ 16GB
• **不可動態修改**，啟動前規劃好

innodb_additional_mem_pool_size

• 5.6+ 已廢棄，功能併入 buffer pool
• 舊版建議 8~20M

innodb_flush_log_at_trx_commit

• 1 (預設)：每提交 fsync，最安全
• 2：寫 OS Cache，每秒 fsync，效能↑ 風險秒級丟失
• 0：僅寫 OS Cache，效能最高，崩潰可能丟 1 秒事務

innodb_log_buffer_size

• 事務日誌緩存，大事務調大 (8~64M)

五、綜合調優建議

參數調優無標準答案，**以監控指標為導向、以業務負載為依歸、以硬體資源為邊界**，持續觀測 SHOW GLOBAL STATUS 迭代優化。