MySQL 全域共享記憶體架構深度解析：Query Cache、Buffer Pool 與關鍵緩衝區

前言

全域共享記憶體是 MySQL 實例與底層儲存引擎協作的核心區域，用於暫存各類全域運算結果與可共享的中繼資訊。主要包含：查詢快取、執行緒快取、表快取、二進制日誌緩衝、MyISAM 索引快取、InnoDB Buffer Pool 等。本文將逐一剖析這些關鍵共享記憶體元件的運作原理、配置參數與效能影響。

查詢快取

Query Cache 是 MySQL 獨有的緩存區域，針對完全相同的 SELECT 語句緩存結果集，共享給所有客戶端。運作流程：

1. MySQL 接收 SELECT 查詢
2. 透過固定 Hash 算法計算查詢 Hash 值
3. 在 Query Cache 中查找對應結果集
4. 命中則直接返回；未命中則執行查詢並緩存結果

失效機制：任一表資料發生變更，該表相關所有 Query Cache 立即失效。因此不適合高頻寫入表，極適合靜態資源表、配置表等讀多寫少場景。

關鍵控制參數：

• query_cache_type：0/OFF（禁用）、1/ON（預設使用，SQL_NO_CACHE 可跳過）、2/DEMAND（需 SQL_CACHE 提示才使用）
• query_cache_size：最大記憶體空間

連接執行緒快取

Thread Cache 緩存空閒連接執行緒，避免頻繁建立/銷毀執行緒的開銷，對短連接應用效能提升顯著。

• thread_cache_size：可緩存的執行緒數量（非記憶體大小）
• 命中率計算：(Connections - Threads_created) / Connections * 100%

表快取

Table Cache 緩存表檔案的檔案控制代碼，減少頻繁開關檔案的系統呼叫開銷。

• MySQL 5.1.3 前：table_cache；5.1.3 起：table_open_cache
• 運作：查詢需取得表檔案控制代碼 → 優先從 Cache 取用 → 用畢歸還池中
• 設定值為可緩存的檔案控制代碼數量，非記憶體大小

表定義資訊快取

MySQL 5.1.3 引入，緩存表定義中繼資料，大量表場景下提升存取效率。

• table_definition_cache：可緩存表定義數量（預設 256，最大 524288）
• 同樣為計數單位，非記憶體大小

二進制日誌緩衝區

Binlog Buffer 暫存資料變更產生的 Binary Log，滿足特定條件（如 sync_binlog）批次寫入檔案，提升寫入效能。

• binlog_cache_size：單事務可用記憶體
• max_binlog_cache_size：單事務最大記憶體限制，超限報錯 Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage

MyISAM 索引快取

Key Buffer 將 MyISAM 表索引資訊緩存在記憶體，是影響 MyISAM 效能最關鍵因素之一。

• key_buffer_size：最大記憶體空間

InnoDB 日誌緩衝區

InnoDB Log Buffer 類似 Binlog Buffer，事務日誌先寫入緩衝區，滿足 innodb_flush_log_at_trx_commit 條件或緩衝區寫滿時刷入檔案/同步磁碟。

• innodb_log_buffer_size：最大記憶體空間
• innodb_flush_log_at_trx_commit 關鍵影響效能與持久性：
  • 0：每秒刷入 log file 並同步磁碟，commit 不觸發刷新（效能最好，可能丟失 1 秒資料）
  • 1（預設）：每次 commit 刷入 log file 並同步磁碟（ACID 完全保障，效能較低）
  • 2：commit 刷入 log file 但不同步磁碟，每秒同步一次（折中方案）

注意：OS 檔案系統與磁碟特性可能導致實際同步行為與設定不符，需謹慎測試。

InnoDB 資料與索引緩存

InnoDB Buffer Pool 是 InnoDB 效能的核心，類比 Oracle SGA 的 Database Buffer Cache。關鍵差異：不僅緩存索引，也緩存表資料，完全按照資料檔案的頁結構緩存。

• 命中率計算：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%
• 對 InnoDB 效能影響極大，生產環境建議分配實體記憶體 60%-80%

InnoDB 字典資訊快取

Additional Memory Pool 存放 InnoDB 字典資訊與內部共享資料結構，大小與 InnoDB 表數量相關。

• innodb_additional_mem_pool_size：設定不足時 InnoDB 會自動申請更多記憶體並記錄警告
• MySQL 5.6+ 已整合至 Buffer Pool 管理，此參數漸棄用

其他共享記憶體

除上述核心區域，尚有 back_log 佇列（存放超量連接請求）、各類內部結構快取等，不再逐一列舉。

小結

MySQL 共享記憶體架構直接決定了資料庫的讀寫吞吐與響應延遲。理解各緩衝區的運作機制、配置參數與適用場景，是進行記憶體規劃與效能調優的基礎。建議根據業務讀寫比例、資料熱度、並發模型，針對性調整 innodb_buffer_pool_size、key_buffer_size、query_cache_size 等核心參數，並持續監控命中率與等待指標。