您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 緩衝區與緩存核心參數調優實戰指南

編輯：MySQL 資料庫相關知識

調優核心思想：有限內存的動態平衡

MySQL 擁有 100+ 可調參數，但掌握核心 8-10 個即可覆蓋 90% 場景。調優本質是：**在物理內存硬性約束下，將內存分配到收益最高的緩衝區，並通過狀態變量驗證效果**。

一、服務器級（全局）參數

1. 表緩存：`table_open_cache` (舊版 `table_cache`)

控制可同時打開的表文件描述符數量。每個線程獨佔打開表句柄。

SHOW STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 5000  |  -- 當前打開表數
| Opened_tables | 195   |  -- 歷史累計打開表數（含因緩存不足重新打開）
+---------------+-------+

Opened_tables 快速增長 → 緩存命中率低，增大 table_open_cache
Open_tables 遠小於設置值 → 設置過大，可適當縮減
典型值：500-2000（視表總數與併發連接數而定）

2. 線程緩存：`thread_cache_size`

緩存空閒線程供復用，避免頻繁創建/銷毀線程開銷。

SHOW STATUS LIKE 'Threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 27     |
| Threads_connected | 15     |
| Threads_created   | 838610 |  -- 關鍵：歷史創建線程總數
| Threads_running   | 3      |
+-------------------+--------+

Threads_created 快速增長 → 緩存不足，增大 thread_cache_size（如 32-128）
目標：Threads_created / Connections 比率極低（<1%）

3. MyISAM 關鍵字緩衝區：`key_buffer_size`

專供 MyISAM 索引塊緩存，所有線程共享。InnoDB **不使用**此緩衝區。

SHOW STATUS LIKE '%key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads         | 98247     |
+-------------------+-----------+

未命中率 = Key_reads / Key_read_requests
若 > 0.1%（即每 1000 請求 >1 次磁盤讀）→ 增大 key_buffer_size
純 MyISAM 環境：可設為物理內存 25-35%（預留 OS 文件系統緩存數據塊空間）
混合/純 InnoDB 環境：僅需 16-32MB 滿足臨時表索引

4. InnoDB 核心緩衝池：`innodb_buffer_pool_size`

InnoDB 性能命脈，緩存數據頁+索引頁+自適應哈希+鎖信息+插入緩衝等。InnoDB 對緩衝池極度敏感，默認值 (128M) 幾乎不可用。

專用 InnoDB 實例：設為物理內存 **60-80%**
監控：SHOW ENGINE INNODB STATUS 中 Buffer pool hit rate 應 > 99%
若數據集較小且增長緩慢，無需盡量設大，避免內存浪費

5. 臨時表相關：`tmp_table_size` / `max_heap_table_size`

內存臨時表大小上限，**取兩者較小值**。超限轉磁盤 MyISAM 表，性能驟降。

SHOW STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |  -- 磁盤臨時表
| Created_tmp_tables      | 32912 |  -- 總臨時表
+-------------------------+-------+

磁盤臨時表佔比 = Created_tmp_disk_tables / Created_tmp_tables
佔比 > 10-20% → 增加兩參數（建議同值，如 64M-256M）
但需警惕：大型 GROUP BY / ORDER BY 可能產生巨大臨時表，盲目增大參數可能導致 OOM

二、會話級（每連接）參數 —— 謹慎設置，乘以最大連接數即總內存佔用

1. 排序緩衝：`sort_buffer_size`

每個需排序的線程獨佔。ORDER BY, GROUP BY, DISTINCT 觸發。

SHOW STATUS LIKE 'Sort%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 1       |  -- 關鍵：磁盤歸併排序次數
| Sort_range        | 79192   |
| Sort_rows         | 2066532 |
| Sort_scan         | 44006   |
+-------------------+---------+

Sort_merge_passes 快速增長 → 內存不足導致磁盤臨時文件歸併，增大 sort_buffer_size（如 2M-8M）
4GB 內存機器建議 4-8MB；**切勿設過大（如 64M）**，1000 併發即 64GB 理論佔用

2. 順序掃描緩衝：`read_buffer_size` / `read_rnd_buffer_size`

全表掃描 / 二級索引掃描時分配。

SHOW STATUS LIKE 'Com_select';           -- 318243
SHOW STATUS LIKE 'Handler_read_rnd_next'; -- 165959471
-- 表掃描比率 = 165959471 / 318243 ≈ 521:1

比率 > 4000 → 增大 read_buffer_size（如 2M-4M）
比率極高（>10000） → **優先優化 SQL/索引**，而非單靠增大緩衝區

3. 聯接緩衝：`join_buffer_size`

無索引聯接（ALL/Index 類型）時分配，每個聯接操作獨佔一個。若查詢多表聯接，可能分配多個。

通常 1-8MB 足夠
核心對策：**補齊索引，避免無索引聯接**，而非依賴增大此參數

三、綜合調優檢查清單

現象	狀態指標	調整參數
Opened_tables 快速增長	Opened_tables / Uptime 高	↑ table_open_cache
Key_reads 過多	Key_reads / Key_read_requests > 0.1%	↑ key_buffer_size (MyISAM)
全表掃描頻繁	Handler_read_rnd_next / Com_select > 4000	↑ read_buffer_size + 優化索引
Threads_created 快速增長	Threads_created / Connections > 1%	↑ thread_cache_size
磁盤臨時表過多	Created_tmp_disk_tables / Created_tmp_tables > 20%	↑ tmp_table_size, max_heap_table_size
排序溢寫磁盤	Sort_merge_passes 快速增長	↑ sort_buffer_size + 優化 ORDER BY

四、內存預算示例（16GB 物理內存，專用 DB 服務器）

OS 預留:           2 GB
InnoDB Buffer Pool: 10 GB  (innodb_buffer_pool_size=10G)
Key Buffer:          64 MB  (key_buffer_size=64M)  -- 僅臨時表索引
Query Cache:          0    (已棄用/關閉)
Sort Buffer:          4 MB  (sort_buffer_size=4M)  -- × max_connections
Read Buffer:          2 MB  (read_buffer_size=2M)  -- × max_connections
Join Buffer:          2 MB  (join_buffer_size=2M)  -- × max_connections
Thread Stack:       256 KB  (thread_stack=256K)   -- × max_connections
Table Cache:        2000    (table_open_cache=2000)
Thread Cache:        64     (thread_cache_size=64)
Max Connections:     500    (max_connections=500)
-- 會話級理論峰值: (4+2+2+0.25)MB × 500 ≈ 4.1 GB
-- 總計理論峰值: 2 + 10 + 0.06 + 4.1 ≈ 16.16 GB (接近物理上限，實際併發通常遠低於最大值)
-- 生產建議：max_connections 設為實際峰值 1.5-2 倍，會話級參數取中間值，留 20-30% 內存給 OS 文件緩存與突發

總結

MySQL 內存調優沒有銀彈，**「監控狀態 → 定位瓶頸 → 微調參數 → 驗證效果」** 才是正確閉環。會話級參數尤需克制，寧可適度犧牲單查詢性能，也要保證系統在高併發下不 OOM。InnoDB Buffer Pool 是核心資產，優先保障其命中率；其他緩衝區輔助解決特定場景（排序、掃描、聯接）的內存壓力。定期收集 SHOW GLOBAL STATUS 快照，建立基線，異常時才有對比依據。