MySQL Query Cache 查詢快取深度解析：機制、限制、監控與最佳實踐

MySQL Query Cache 深度解析

MySQL Query Cache (QC) 在解析前攔截 SELECT，以 SQL 文本為 Key (Byte-by-byte 完全匹配) 緩存完整結果集。雖然 8.0 已移除，但在 5.6/5.7 存量系統中仍廣泛存在，理解其機制對遷移決策與歷史系統維護至關重要。

一、核心機制

1. 透明性：應用無感知，SQL 語義不變即可命中。
2. Key 生成：SELECT 文本 + 數據庫名 + 客戶端字符集 + 其他會話變量 → 精確二進制匹配。大小寫、空格、註釋、換行均導致不同 Key。
3. 結果集存儲：完整行數據 (非執行計畫)，無額外序列化開銷，返回極快。
4. 失效粒度：表級。任意寫入 (INSERT/UPDATE/DELETE/TRUNCATE/ALTER) 導致該表相關所有快取條目失效。
5. 內存管理：塊式分配 (query_cache_min_res_unit)，LRU 淘汰，易產生碎片。

二、關鍵限制 (必讀)

三、配置參數

[mysqld]
query_cache_type = 1              # 0=OFF, 1=ON (默認), 2=DEMAND (需 SQL_CACHE)
query_cache_size = 64M            # 總內存，0 禁用
query_cache_limit = 2M            # 單查詢結果上限，超過不緩存
query_cache_min_res_unit = 4K     # 分配塊大小，影響碎片率
query_cache_wlock_invalidate = OFF # 寫鎖時是否失效快取

四、監控指標與公式

SHOW STATUS LIKE 'Qcache%';

核心計算公式

-- 命中率
Hit Rate = Qcache_hits / (Qcache_hits + Com_select)

-- 平均快取大小
Avg Size = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

-- 碎片率
Frag Ratio = Qcache_free_blocks / Qcache_total_blocks  -- > 0.2 需整理

-- 內存壓力
Prune Rate = Qcache_lowmem_prunes / Uptime  -- 持續增長需擴容或降低 limit

五、調優策略

1. 評估適用性：讀多寫少 (讀寫比 > 10:1)、表數據穩定、SQL 模板固定 (ORM 參數化) → 適合開啟。寫多、高併發、大表頻繁變更 → 關閉 (query_cache_type=0)。
2. 內存尺寸：起步 64M，觀察 Qcache_lowmem_prunes，逐步調整至穩定。避免 > 512M (全局鎖競爭劇增)。
3. 碎片整理：FLUSH QUERY CACHE 整理碎片不清空內容；RESET QUERY CACHE 清空所有快取。
4. 按需模式：query_cache_type=2 + SELECT SQL_CACHE ... 精確控制哪些查詢入快取。
5. 規範 SQL：統一大小寫、去除註釋、固定參數順序，提升命中率。

六、為何 8.0 移除 Query Cache?

• 全局互斥鎖瓶頸：任何讀/寫快取操作均需持有 LOCK_query_cache，高併發下嚴重退化為串行。
• 失效風暴：寫入導致全表快取失效，鎖住快取遍歷刪除，阻塞所有查詢線程。
• 命中率普遍低：Byte-by-byte 匹配 + 表級失效 + 不確定性函數，實際命中率常 < 20%。
• 現代替代方案更優：應用層快取、ProxySQL 快取、Redis、物化視圖、物化表、Covering Index。

七、遷移建議 (5.7 → 8.0)

1. 監控 Qcache_hits / (Qcache_hits + Com_select)，若 < 10% 直接關閉無感遷移。
2. 若有熱點查詢依賴 QC，遷移前識別 Top 20 命中 SQL，改用應用層快取 (Redis/Memcached) 或 ProxySQL Query Cache。
3. 調整 innodb_buffer_pool_size 吸收原 QC 內存，提升物理讀效能。
4. 啟用 optimizer_switch='derived_merge=on,derived_condition_pushdown=on' 利用優化器新特性補償。

Query Cache 是 MySQL 早期為單核、低併發、讀多寫少場景設計的產物。隨著多核普及、高併發常態化、InnoDB MVCC 成熟，其架構缺陷已無法通過參數調優根治。理解其機制與限制，既是對歷史系統的負責，也是技術演進的見證。