MySQL 執行緒專用記憶體詳解：Sort Buffer、Join Buffer 與關鍵參數調優

前言

執行緒專用記憶體為每個客戶端連線獨享，用於儲存排序、分組、連接、讀寫緩衝、結果集暫存等操作資料。大多數可透過參數控制大小，但總記憶體消耗 = 單執行緒配置 × 最大連線數，配置不當極易導致 OOM 或嚴重 Swap，反而拖垮效能。本文詳細解析各專用記憶體用途、參數與調優要點。

核心專用記憶體元件

1. 執行緒堆疊

• 參數：thread_stack
• 用途：儲存執行緒識別資訊、執行期基礎資料（執行緒 ID、呼叫堆疊等）
• 建議：預設通常足夠（256KB-1MB），深度遞迴或複雜存儲過程才需調大

2. 排序緩衝區

• 參數：sort_buffer_size
• 用途：ORDER BY、GROUP BY、DISTINCT 等排序操作（filesort）
• 機制：記憶體不足時落盤臨時檔案完成排序，磁碟 I/O 開銷極大
• 關鍵優化：善用索引完成排序（避免 filesort）、避免 SELECT * 增加排序行寬
• 建議：預設 256KB-2MB 通常足夠，大型排序臨時調大 session 級別

3. 連接緩衝區

• 參數：join_buffer_size
• 用途：無索引連接時，緩存「被驅動表」資料減少掃描次數
• 機制：Buffer 滿時不落盤，而是分批 Join → 清空 → 繼續填充，導致被驅動表多次掃描
• 建議：預設 256KB 足夠；優先優化索引讓連接走 ref/eq_ref，避免依賴 Join Buffer

4. 循序讀取緩衝區

• 參數：read_buffer_size
• 用途：全表掃描、全索引掃描等循序讀取場景
• 機制：按儲存順序讀取資料塊暫存，滿或讀完再返回上層
• 建議：預設 128KB-256KB 通常足夠，大量全掃描可適度調大

5. 隨機讀取緩衝區

• 參數：read_rnd_buffer_size
• 用途：索引查找回表、排序後結果集 Join 等非循序讀取
• 建議：預設 256KB 足夠，大量隨機 I/O 場景可調大

6. 網路緩衝區

• 參數：net_buffer_size（初始值），max_allowed_packet（上限）
• 用途：儲存連線資訊、暫存待發送結果集，滿足特定大小再批次發送
• 建議：預設 16KB 足夠，大結果集傳輸由 max_allowed_packet 控制上限

7. 批量插入緩衝區

• 參數：bulk_insert_buffer_size
• 用途：INSERT ... VALUES(),(),() 批量插入、LOAD DATA INFILE
• 機制：資料先入緩衝區，滿或結束時一次性寫入
• 注意：僅支援 MyISAM，InnoDB 不使用此緩衝區

8. 臨時表記憶體

• 參數：tmp_table_size、max_heap_table_size
• 用途：ORDER BY、GROUP BY、DISTINCT、UNION 等需臨時表的操作
• 機制：小於閾值建 MEMORY 表（記憶體），超過轉 MyISAM 落盤
• 實際上限：MIN(tmp_table_size, max_heap_table_size)
• 建議：預設 16MB-64MB，避免臨時表頻繁落盤；但勿盲目調大

配置黃金法則

專用記憶體總量 = Σ(各參數) × max_connections

• 預設 max_connections=151，若每執行緒分配 10MB，理論峰值超 1.5GB
• 實際峰值通常較低（非所有連線同時用滿），但極端情況必須可控
• 建議：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size 維持預設或微調；tmp_table_size/max_heap_table_size 設 64MB-256MB；重點投資共享記憶體
• 監控 Created_tmp_disk_tables、Sort_merge_passes、Select_full_join 等狀態變數判斷是否需調整

小結

執行緒專用記憶體是「雙面刃」：適度配置加速單查詢，過度配置拖垮整體穩定。核心原則：共享記憶體優先，專用記憶體保守，靠索引與 SQL 優化解決效能問題，而非單純堆疊記憶體。