MySQL 內存分配模型完整指南：共享與獨享內存參數詳細解析

MySQL 內存分配模型完整指南

內存分配是 MySQL 調優的重中之重。本文系統梳理「線程共享」與「線程獨享」兩大內存池的所有關鍵參數、用途、觸發場景、調優建議，並給出總佔用計算公式與規劃實戰清單。

一、全局參數速覽

SHOW GLOBAL VARIABLES LIKE '%buffer%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 4194304    |
| innodb_buffer_pool_size | 2013265920 |
| 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
    )

註：線程獨享內存按需分配，實際峰值遠低於理論上限，但容量規劃必須按上限預留。

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

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

五、InnoDB Log 刷盤策略詳解

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    (推薦折中)

sync_binlog = N (N>0)
-- 每 N 次 Binlog 寫入 fdatasync()，建議 500-1000
-- 需配合帶電池備電 RAID 或 NVMe 原子寫入

六、Buffer Pool 命中率監控

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) 
/ Innodb_buffer_pool_read_requests * 100%
-- 目標 > 99%

七、規劃實戰清單

1. 物理內存 - 10-15% (OS/備份/監控)
2. 剩餘 60-80% 給 innodb_buffer_pool_size
3. 估算峰值連接數，按 2-4MB/連接預留線程私有內存
4. 關閉 Query Cache，調大 tmp_table_size/max_heap_table_size
5. 啟用 HugePages 減少 TLB Miss
6. 持續監控 VmRSS、Innodb_buffer_pool_pages_*、Threads_connected