MySQL 緩衝與緩存配置深度解析

概述

MySQL 可調優參數分為服務器級（全局）與會話級兩類。正確配置緩衝區與緩存能顯著提升查詢性能，本文系統梳理核心指標的監控與調優方法。

一、服務器級關鍵緩存配置

1. 表緩存

MySQL 將打開的表文件描述符緩存於內存，由 table_open_cache（舊版 table_cache）控制上限。

監控指標：

SHOW STATUS LIKE 'open%tables';

關鍵判斷：

• Opened_tables 快速增長 → 緩存命中率低，需增大 table_open_cache
• Open_tables 遠小於設置值 → 設置過大，可適當縮減

建議設置： table_open_cache = 5000（視實際並發調整）

2. 線程緩存

高併發短連接場景下，線程創建銷毀開銷大，緩存空閒線程可加速連接建立。

監控指標：

SHOW STATUS LIKE 'threads%';

核心指標： Threads_created 持續快速增長 → 需增大 thread_cache_size

建議設置： thread_cache_size = 40（根據 Threads_created 趨勢調整）

3. 關鍵字緩衝區

專為 MyISAM 索引塊服務，InnoDB 使用獨立的 Buffer Pool。

監控指標：

SHOW STATUS LIKE '%key_read%';

計算公式： 未命中率 = Key_reads / Key_read_requests

• 未命中率 > 0.1% (每 1000 次超過 1 次磁盤讀取) → 擴大 key_buffer_size

建議設置： key_buffer_size = 384M（僅 MyISAM 主導場景有效）

4. 臨時表緩存

複雜查詢（GROUP BY、ORDER BY、UNION 等）產生的臨時表優先在內存創建，超限則轉磁盤。

監控指標：

SHOW STATUS LIKE 'created_tmp%';

關鍵比率： Created_tmp_disk_tables / Created_tmp_tables

• 比率較高 → 增加 tmp_table_size 與 max_heap_table_size（兩者需同步設置）

二、會話級緩存配置

⚠️ 注意： 會話級參數隨連接數線性放大內存佔用，設置需極謹慎。

1. 排序緩衝區

ORDER BY、GROUP BY 觸發排序時分配。

SHOW STATUS LIKE "sort%";

• Sort_merge_passes 大 → 增加 sort_buffer_size（建議 4M-8M）

2. 讀取緩衝區

全表掃描時按行讀取數據用。

SHOW STATUS LIKE "com_select";
SHOW STATUS LIKE "handler_read_rnd_next";

表掃描比率 = Handler_read_rnd_next / Com_select

• 比率 > 4000 → 檢查 read_buffer_size（建議 4M）
• 比率 > 8M → 需結合開發優化 SQL、加索引

3. 查詢緩存

適用場景： 讀多寫少、重複查詢多、表變更少。

SHOW VARIABLES LIKE '%query_cache%';

核心配置：

query_cache_type = 1  # ON: 緩存所有非 SQL_NO_CACHE 查詢
query_cache_size = 128M  # 建議 1024 倍數
query_cache_limit = 1M   # 單次查詢結果超過此值不緩存

驗證生效：

SHOW STATUS LIKE '%Qcache%';

• Qcache_hits 增長 → 緩存生效
• Qcache_lowmem_prunes 增長 → 內存不足需擴容

三、配置最佳實踐總結

四、避坑指南

1. 盲目加大會話級參數：1000 連接 × 8MB sort_buffer = 8GB 內存風險
2. InnoDB 濫用 Key Buffer：InnoDB 核心是 innodb_buffer_pool_size，Key Buffer 僅服務 MyISAM
3. 查詢緩存濫用：寫入頻繁、查詢不重複的系統開啟查詢緩存反而拖慢性能
4. 忽略 tmp_table 雙參數：tmp_table_size 與 max_heap_table_size 取較小值生效，必須同步調大