MySQL 查詢快取機制深度解析與調優指南

MySQL 查詢快取機制深度解析

查詢快取 將「SQL 文本 + 查詢結果」以 Hash 表形式存放於記憶體。相同 SQL 再次執行時，直接回傳快取結果，跳過解析、優化、執行全流程。但表結構或資料變更（INSERT/UPDATE/DELETE/TRUNCATE/ALTER/DROP）會導致相關快取失效。對於「讀多寫少、重複查詢高」的場景（如報表、配置表），開啟快取可顯著提升吞吐；反之「寫入頻繁」的表則不適合。

一、命中條件

• SQL 文本 逐位元組完全相同（大小寫、空格、註解、字符集、協定版本、預設資料庫皆須一致）
• 不含不確定函數：NOW()、RAND()、CURRENT_DATE()、UUID()、USER() 等
• 不含用戶自變量、預存函數（除非聲明 DETERMINISTIC）
• 查詢不涉及系統表、mysql 資料庫表、INFORMATION_SCHEMA、performance_schema
• 未使用 SQL_NO_CACHE 提示

二、工作流程

1. Server 接收 SQL，以 SQL+DB+客戶端協定等為 Key 查詢快取表（額外 CPU/鎖開銷）
2. 命中 → 直接回傳結果（極大效能提升）
3. 未命中 → 正常解析、優化、執行
4. 執行完成 → 將結果寫入快取（額外記憶體/鎖開銷）

三、快取失效機制

• 表級鎖定：任意寫入操作會對該表所有快取條目標記失效
• InnoDB 事務：事務提交前，該表快取處於失效狀態；提交瞬間批量失效
• FLUSH TABLES、RESET QUERY CACHE 手動清空

四、記憶體管理與碎片問題

快取記憶體由 query_cache_size 分配，約 40KB 用於元資料。最小儲存單元 query_cache_min_res_unit（預設 4KB）。

• 查詢開始即預分配一個單元，結果寫入時若超過則再申請下一個
• 查詢結束釋放剩餘空間 → 產生記憶體碎片
• 碎片過多導致 Qcache_free_blocks 增加、Qcache_lowmem_prunes 上升（被動淘汰）

五、關鍵效能指標

六、核心配置參數

[mysqld]
query_cache_type = DEMAND     # OFF/ON/DEMAND，建議 DEMAND 由應用控制
query_cache_size = 256M       # 總快取記憶體，必須 1024 倍數
query_cache_min_res_unit = 2K # 小結果集調小減少碎片，大結果集調大減少分配次數
query_cache_limit = 2M        # 單查詢最大快取結果，超過不快取
query_cache_wlock_invalidate = OFF  # 表鎖定時是否仍回傳快取

七、碎片整理與維護

• FLUSH QUERY CACHE：整理碎片（執行期間鎖定快取，短暫阻塞）
• RESET QUERY CACHE：完全清空快取
• 定期監控 Qcache_lowmem_prunes，若持續增長需擴容或調整 min_res_unit

八、InnoDB 特殊行為

• 每表維護一個事務計數器（最大事務 ID）
• 唯有事務 ID > 計數器的查詢可使用快取（MVCC 一致性讀保障）
• 所有加鎖讀（SELECT ... LOCK IN SHARE MODE、FOR UPDATE）完全繞過快取

九、實戰範例驗證

SET GLOBAL query_cache_size = 600000;
SET SESSION query_cache_type = ON;
USE test;
SELECT COUNT(*) FROM animals;  -- 第一次執行
SHOW STATUS LIKE 'Qcache_hits';  -- 0
SELECT COUNT(*) FROM animals;  -- 第二次執行
SHOW STATUS LIKE 'Qcache_hits';  -- 1
INSERT INTO animals VALUES (9,'test');  -- 寫入導致快取失效
SELECT COUNT(*) FROM animals;  -- 再次執行，未命中
SHOW STATUS LIKE 'Qcache_hits';  -- 仍為 1
SELECT COUNT(*) FROM animals;  -- 重新快取
SHOW STATUS LIKE 'Qcache_hits';  -- 2

十、最佳實踐建議

1. 預設關閉（query_cache_type=OFF），僅對經證實「高重複、低變更」的查詢使用 SQL_CACHE 提示開啟
2. 快取大小設為 64MB~256MB，過大反而增加鎖競爭與碎片整理開銷
3. 監控 Qcache_hits / Qcache_inserts 比值，長期 < 3:1 應考慮關閉
4. MySQL 5.7+ 已標記棄用，MySQL 8.0 完全移除；新架構建議改用 Redis、ProxySQL 等外部快取層