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

MySQL 記憶體架構深度解析：全域共享區與執行緒私有區完整圖譜

編輯：MySQL 資料庫相關知識

前言

MySQL 記憶體管理直接決定系統穩定性與效能上限。記憶體劃分為 全域共享區 (Global/Shared Memory) 與 執行緒私有區 (Thread/Session Private Memory) 兩大類。本文系統解析各區塊功能、配置參數、監控指標、命中率計算與調優策略，並附記憶體佔用估算公式，助您精準規劃實例記憶體預算。

一、記憶體架構總覽


+--------------------------- MySQL Process (mysqld) ---------------------------+
|                                                                              |
|  +---------------------- Global Shared Memory (實例啟動分配) ------------------+
|  |                                                                              |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | InnoDB Buffer    |  | Query Cache      |  | Table Open Cache |          |
|  |  | Pool             |  | (5.7, 8.0 移除)  |  | (Table Cache)    |          |
|  |  | innodb_buffer_   |  | query_cache_size |  | table_open_cache |          |
|  |  | pool_size        |  |                  |  |                  |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | InnoDB Log       |  | Key Buffer       |  | Thread Cache     |          |
|  |  | Buffer           |  | (MyISAM)         |  | thread_cache_size|          |
|  |  | innodb_log_      |  | key_buffer_size  |  |                  |          |
|  |  | buffer_size      |  |                  |  |                  |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | Binlog Cache     |  | Host Cache       |  | Performance      |          |
|  |  | binlog_cache_size|  | host_cache_size  |  | Schema           |          |
|  |  | (交易級)         |  |                  |  | performance_     |          |
|  |  +------------------+  +------------------+  | schema           |          |
|  |                                              +------------------+          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | InnoDB Dict/     |  | Adapter/         |  | Stored Program   |          |
|  |  | Additional Mem   |  | Plugin Memory    |  | Cache            |          |
|  |  | innodb_additional|  |                  |  | stored_program_  |          |
|  |  | _mem_pool_size   |  |                  |  | cache            |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  +--------------------------------------------------------------------------+
|                                                                              |
|  +---------------------- Thread Private Memory (連線建立分配) ----------------+
|  |                                                                              |
|  |  每個連線 (Thread) 獨立擁有：                                                |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | Sort Buffer      |  | Read Buffer      |  | Read RND Buffer  |          |
|  |  | sort_buffer_size |  | read_buffer_size |  | read_rnd_buffer_ |          |
|  |  |                  |  |                  |  | size             |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | Join Buffer      |  | Binlog Cache     |  | Thread Stack     |          |
|  |  | join_buffer_size |  | binlog_cache_size|  | thread_stack     |          |
|  |  | (無索引 JOIN)    |  | (事務級)         |  |                  |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  |  +------------------+  +------------------+  +------------------+          |
|  |  | NET Buffer       |  | MEM_ROOT         |  | Prepared Stmt    |          |
|  |  | net_buffer_length|  | (分配器)         |  | Cache            |          |
|  |  +------------------+  +------------------+  +------------------+          |
|  |                                                                              |
|  +--------------------------------------------------------------------------+
|                                                                              |
+------------------------------------------------------------------------------+

二、全域共享區詳細解析

1. InnoDB Buffer Pool (核心中的核心)

功能：緩存資料頁 (16KB)、索引頁、插入緩衝、自適應雜湊索引、鎖資訊、Undo 頁面。
參數：innodb_buffer_pool_size (預設 128M，建議專用 DB 70-80% 物理記憶體)。
實例化：innodb_buffer_pool_instances (預設 8，每實例 ≥ 1GB)，減少全域互斥鎖爭用。
預熱：8.0+ 支援 innodb_buffer_pool_dump_at_shutdown/load_at_startup，重啟快速預熱。

命中率計算：

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

目標 > 99% (OLTP)，< 95% 需擴容或檢查熱點資料分佈。

監控關鍵：Innodb_buffer_pool_pages_data (髒頁+乾淨頁)、Innodb_buffer_pool_pages_dirty、Innodb_buffer_pool_pages_free、Innodb_buffer_pool_wait_free (等待釋放計數，>0 表示壓力)。

2. InnoDB Log Buffer

功能：事務 Redo Log 寫入前緩衝，批次 fsync 減少 IOPS。
參數：innodb_log_buffer_size (預設 16M，建議 64M-512M)。大事務/大 Blob 必調大。
刷盤策略：innodb_flush_log_at_trx_commit (1=提交即 fsync，2=寫 OS 快取，0=每秒 fsync)。
監控：Innodb_log_waits (等待計數，>0 表示緩衝不足)。

3. InnoDB Additional Memory Pool (5.6.3 後合併至 Buffer Pool)

歷史功能：儲存資料字典、鎖資訊、自適應雜湊索引等內部結構。
現狀：innodb_additional_mem_pool_size 已棄用 (5.7 移除)，內部結構直接從 Buffer Pool 分配。
注意：若錯誤日誌出現 InnoDB: Warning: Additional memory pool size too small，說明版本較舊需調大此參數。

4. Key Buffer (MyISAM 專用)

功能：緩存 MyISAM 索引塊 (.MYI)。
參數：key_buffer_size (預設 8M)。僅 MyISAM 系統表 (mysql.*, performance_schema) 需要，InnoDB 不用。
建議：64M-256M 足夠，勿過大浪費記憶體。
命中率：(Key_read_requests - Key_reads) / Key_read_requests > 99%。

5. Table Open Cache / Table Definition Cache

快取	參數	預設	建議	內容	監控指標
Table Open Cache	table_open_cache	4000	8000-20000	表實例 (含 .frm 句柄、鎖結構)	Open_tables ≈ table_open_cache、Opened_tables 慢增長
Table Definition Cache	table_definition_cache	1400	4000-10000	.frm 結構定義 (無鎖結構)	Opened_table_definitions 慢增長

表數量 > 1 萬時**必須調大**，否則頻繁開關表導致 CPU 飆升、FD 耗盡。

6. Thread Cache

功能：緩存空閒執行緒，避免頻繁 create/destroy thread 開銷。
參數：thread_cache_size (自動調整，建議 100-200)。
命中率：(Connections - Threads_created) / Connections * 100% > 99%。

7. Query Cache (5.7 有，8.0 移除)

功能：緩存 SELECT 結果集 (SQL 文本哈希 → Result Set)。
致命缺陷：全域互斥鎖、任何寫入導致相關表所有 Query Cache 失效、高併發下鎖爭用極嚴重。
建議：5.7 設 query_cache_type=OFF、query_cache_size=0；8.0 已移除，改用應用層 Redis/Memcached。

8. Binlog Cache (交易級，但屬全域配置)

參數：binlog_cache_size (預設 32K，建議 1-4M)，max_binlog_cache_size (預設 4G，上限)。
功能：事務期間 Binlog 事件暫存，提交時刷入 Binlog 檔案。
監控：Binlog_cache_use (使用記憶體計數)、Binlog_cache_disk_use (溢寫磁碟計數，>0 需調大)。

9. Host Cache / Performance Schema / Stored Program Cache

host_cache_size (預設 128+，IP→主機名快取，skip_host_cache 可禁用)。
performance_schema (預設 ON，記憶體由 performance_schema_max_* 系列控制，約 100-500MB)。
stored_program_cache (預設 256，存儲過程/函數/觸發器解析樹快取)。

三、執行緒私有區詳細解析

緩衝區	參數	預設	建議值	觸發場景	溢出後果	監控指標
Sort Buffer	sort_buffer_size	256K	2-4M	ORDER BY、GROUP BY、DISTINCT、窗口函數需排序且無索引	磁碟臨時表 (Sort_merge_passes 遞增)	Sort_merge_passes, Sort_scan, Sort_range
Read Buffer	read_buffer_size	128K	1-2M	全表掃描循序讀取	多次系統調用讀取	Handler_read_rnd_next (全掃計數)
Read RND Buffer	read_rnd_buffer_size	256K	2-4M	排序後按主鍵隨機讀取 (ref/range 掃描後回表)	隨機 IO 增加	Handler_read_rnd
Join Buffer	join_buffer_size	256K	256K-1M	無索引 JOIN (Block Nested Loop / Batched Key Access)	磁碟臨時表、極慢	Select_full_join, Select_range_check
Binlog Cache (事務級)	binlog_cache_size	32K	1-4M	大事務 Binlog 事件暫存	磁碟臨時檔	Binlog_cache_disk_use
Thread Stack	thread_stack	256K/1M	512K	存儲程序遞歸、複雜 SQL 解析、UDF	Stack Overrun → Crash	錯誤日誌 "Thread stack overrun"
NET Buffer	net_buffer_length	16K	16-64K	客戶端通訊封包組裝	動態擴展至 max_allowed_packet	-

關鍵原則：Session 級緩衝區**總量 = 併發連線數 × 單連線大小**。
若 max_connections=3000、sort_buffer_size=4M、join_buffer_size=4M → 理論峰值 24GB，**極易 OOM**。
生產必須保守設定，依賴索引避免排序/JOIN 落盤，而非調大緩衝區。

四、記憶體佔用估算公式 (生產規劃用)


Total_MySQL_Memory ≈
  Global_Buffers
  + (Thread_Buffers × Max_Used_Connections)   -- 用實際峰值連線數，非 max_connections
  + Performance_Schema_Memory
  + Other_Overhead (約 500MB-1GB)

Global_Buffers = 
  innodb_buffer_pool_size
  + innodb_log_buffer_size
  + key_buffer_size
  + query_cache_size (5.7)
  + binlog_cache_size (較小，忽略)
  + thread_cache_size × thread_stack (較小)
  + table_open_cache × (表實例開銷 ~ 1-2KB)
  + table_definition_cache × (定義開銷 ~ 1KB)

Thread_Buffers (單連線) = 
  sort_buffer_size
  + read_buffer_size
  + read_rnd_buffer_size
  + join_buffer_size
  + binlog_cache_size
  + thread_stack
  + net_buffer_length
  + MEM_ROOT 預留 (~256KB)

# 範例：32GB RAM, 專用 DB
# Global ≈ 24G (BP) + 512M (LogBuf) + 64M (KeyBuf) + 200M (TableCache) + 500M (PFS) ≈ 25.3G
# Thread (保守) ≈ 4M + 2M + 4M + 1M + 2M + 512K + 64K + 256K ≈ 13.8M
# Peak Connections = 500
# Thread Total ≈ 13.8M × 500 ≈ 6.9G
# Total ≈ 25.3G + 6.9G + 1G (Other) ≈ 33.2G > 32G ⚠️ 風險！

# 安全配置調整：
# innodb_buffer_pool_size = 22G
# max_connections = 2000 (實際峰值 500)
# sort_buffer_size = 2M
# join_buffer_size = 256K
# → Total ≈ 22G + 0.6G + 3.5G + 1G ≈ 27G (安全邊際 5G 給 OS/FS Cache)

五、常見記憶體問題診斷與解決

現象	可能原因	排查指令	解決方案
OOM Killer 殺死 mysqld	總記憶體超限、單大查詢佔用過大、記憶體洩漏	`dmesg \| grep -i oom`、`SHOW GLOBAL STATUS LIKE 'Max_used_connections'`、`pmap -x PID`	降低 Buffer Pool、調小 Session 緩衝、限制並發、升級記憶體
Swap 使用率高	記憶體不足、swappiness 過高	`free -h`、`cat /proc/sys/vm/swappiness`	`swappiness=1`、鎖定記憶體 `memlock_limit`、增加物理記憶體
Sort_merge_passes 持續增長	排序緩衝不足、缺乏索引導致大量排序	`SHOW GLOBAL STATUS LIKE 'Sort%'`、`EXPLAIN` 慢查詢	優化 SQL 加索引、調大 sort_buffer_size (謹慎)、用臨時表分頁
Opened_tables 快速增長	Table Open Cache 太小	`SHOW GLOBAL STATUS LIKE 'Open%tables%'`	調大 table_open_cache、table_definition_cache
Key_reads / Key_read_requests > 1%	Key Buffer 太小或 MyISAM 表過多	`SHOW GLOBAL STATUS LIKE 'Key%'`	調大 key_buffer_size、遷移 MyISAM 到 InnoDB

六、MySQL 8.0 記憶體管理新特性

Buffer Pool 預熱持久化：innodb_buffer_pool_dump_at_shutdown=ON, innodb_buffer_pool_load_at_startup=ON，重啟秒級恢復熱點頁。
動態調整 Buffer Pool：SET GLOBAL innodb_buffer_pool_size=... 無需重啟 (需 innodb_buffer_pool_instances 為 1 或倍數關係)。
查詢快取移除：彻底消除 Query Cache 鎖爭用，記憶體更可預測。
Performance Schema 記憶體追蹤：memory/performance_schema/... 儀表精準記錄各組件記憶體分配。
資源群組：RESOURCE GROUP 綁定 CPU/優先級，間接控制記憶體壓力。

結語

MySQL 記憶體管理的核心矛盾在於：**有限記憶體 vs 無限制的併發連線 × Session 緩衝區**。解決之道：全域緩衝區 (Buffer Pool) 盡量大、Session 緩衝區盡量小、依賴索引避免落盤、連線池控制並發峰值。建立記憶體預算表、定期核對實際佔用、壓測驗證 OOM 邊界，是 DBA 的必修功課。