MySQL 內存架構深度解析：線程共享與獨享內存完整分配模型

MySQL 內存架構深度解析

內存分配是 MySQL 調優的核心。理解「線程共享」與「線程獨享」兩大內存池的組成、參數控制、計算公式，才能精準規劃實例記憶體上限，避免 OOM 或資源浪費。

一、全局內存參數速覽

SHOW GLOBAL VARIABLES LIKE '%buffer%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 4194304    |
| innodb_buffer_pool_size | 2013265920 |  -- 1.9GB
| innodb_change_buffering | inserts    |
| innodb_log_buffer_size  | 8388608    |
| join_buffer_size        | 1048576    |
| key_buffer_size         | 16777216   |
| myisam_sort_buffer_size | 262144     |
| net_buffer_length       | 16384      |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 1048576    |
| read_rnd_buffer_size    | 1048576    |
| sort_buffer_size        | 1048576    |
| sql_buffer_result       | OFF        |
+-------------------------+------------+

SHOW GLOBAL VARIABLES LIKE '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 1048576              |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 0                    |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 100                  |
| thread_cache_size            | 100                  |
+------------------------------+----------------------+

二、內存總佔用計算公式

Used_Mem = 
  -- 全局共享內存
  key_buffer_size
  + query_cache_size
  + innodb_buffer_pool_size
  + innodb_additional_mem_pool_size  -- 5.7 移除
  + innodb_log_buffer_size
  -- 線程獨享內存 × max_connections
  + max_connections * (
      read_buffer_size
      + read_rnd_buffer_size
      + sort_buffer_size
      + join_buffer_size
      + binlog_cache_size
      + thread_stack
      + tmp_table_size
      + bulk_insert_buffer_size
    )

關鍵認知：線程獨享內存不會在連線建立時一次性分配，而是按需、分階段分配（如排序時才分配 sort_buffer）。實際峰值遠低於理論上限，但規劃時必須按上限預留。

三、線程獨享內存詳細解析

四、線程共享內存詳細解析

五、InnoDB Log Buffer 刷盤策略

innodb_flush_log_at_trx_commit = 0 | 1 | 2
-- 0: log_buffer --1s--> log_file --實時--> disk  (延遲寫，高效能風險丟數據)
-- 1: log_buffer --實時--> log_file --實時--> disk  (最安全，效能最低)
-- 2: log_buffer --實時--> log_file --1s--> disk    (折中推薦，配合電池備電 RAID)

sync_binlog = N (N>0)
-- 每 N 次 Binlog 寫入觸發 fdatasync()，建議 500-1000

六、Buffer Pool 命中率監控

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) 
/ Innodb_buffer_pool_read_requests * 100%
-- 目標 > 99%，否則擴大 buffer_pool 或優化索引/查詢

七、規劃實戰清單

1. 確定物理內存，預留 10-15% 給 OS/監控/備份進程
2. 設定 innodb_buffer_pool_size 為剩餘 60-80%
3. 估算峰值連接數 max_connections，按 2-4MB/連接預留線程私有內存
4. 關閉 Query Cache，調大 tmp_table_size/max_heap_table_size
5. 啟用 HugePages（大頁內存）減少 TLB Miss，需 OS 層面配置
6. 監控 VmRSS、Innodb_buffer_pool_pages_*、Threads_connected 動態調整