MySQL 查詢快取機制深度解析與配置優化
查詢快取 將完整的 SQL 文本與查詢結果集對應儲存。相同 SQL（逐字節完全一致）再次執行時，直接從快取回傳結果，跳過解析、優化、執行全流程。但表任意變更（資料/結構）會導致相關快取失效。本文深度解析運作原理、適用場景、配置參數與監控指標。
一、運作原理
- 快取鍵：SQL 文本逐字節哈希（含資料庫名、協定版本、字元集）。
SELECT * FROM t 與 select * from t 視為不同。
- 快取值：完整結果集（含欄位中繼資料）。
- 失效機制：表發生
INSERT/UPDATE/DELETE/TRUNCATE/ALTER TABLE/DROP TABLE/DROP DATABASE 或 MERGE 表映射表變更時，該表所有快取條目標記失效並釋放記憶體。
- 適用性判斷：
- ✅ 適合：讀多寫少、重複查詢高、資料變更頻率低的表（如配置表、字典表、報表快照）
- ❌ 不適合：高頻寫入表、大結果集、非確定性函數（
NOW(), RAND()）、自定義函數、用戶變數、臨時表、SELECT ... INTO OUTFILE、LOCK IN SHARE MODE/FOR UPDATE
二、核心配置參數
| 參數 | 預設值 | 建議值 | 說明 |
|---|
query_cache_type | ON (1) | ON / DEMAND (2) / OFF (0) | ON=所有 SELECT 快取（除 SQL_NO_CACHE）；DEMAND=僅 SQL_CACHE 快取；OFF=禁用 |
query_cache_size | 0 (5.7+ 預設關閉) | 32M-512M | 快取總記憶體。0=禁用。過大（>1GB）維護開銷大、鎖爭用嚴重 |
query_cache_limit | 1M | 1M-4M | 單一查詢結果最大快取值。超過不快取 |
query_cache_min_res_unit | 4KB | 4KB-8KB | 快取塊最小單位。過小→碎片多；過大→浪費。監控 Qcache_free_blocks 調整 |
query_cache_wlock_invalidate | OFF | OFF | WRITE LOCK 時是否使快取失效。ON 可避免髒讀但增加爭用 |
have_query_cache | YES/NO | - | 編譯時是否支援（唯讀） |
三、動態管理指令
-- 開啟/關閉（全域，即時生效）
SET GLOBAL query_cache_type = ON; -- 或 OFF
SET GLOBAL query_cache_size = 67108864; -- 64MB
-- 會話級控制
SET SESSION query_cache_type = OFF; -- 當前連線不使用快取
-- 快取維護
FLUSH QUERY CACHE; -- 整理碎片，不移除條目
RESET QUERY CACHE; -- 完全清空快取
四、關鍵狀態變數監控
| 變數 | 含義 | 健康指標 |
|---|
Qcache_hits | 快取命中次數（累計） | 持續快速增長 = 命中良好 |
Qcache_inserts | 新增快取條目數 | 與 Hits 比例 = 命中率 |
Qcache_not_cached | 未快取查詢數（不符合條件/超過限制） | 過高檢查 limit/min_res_unit |
Qcache_lowmem_prunes | 因記憶體不足被剔除的條目數 | 持續增長 = 記憶體不足或碎片嚴重 |
Qcache_free_memory | 剩餘可用記憶體 | 過低需擴容或清理 |
Qcache_free_blocks | 空閒記憶體塊數 | 過高 = 碎片嚴重，執行 FLUSH QUERY CACHE |
Qcache_total_blocks | 總記憶體塊數 | 參考 |
Qcache_queries_in_cache | 目前快取查詢數 | 參考 |
五、命中率計算與調優判斷
命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) * 100%
- > 80%：快取極其有效，維持現狀
- 50-80%：有效，可微調
query_cache_limit、min_res_unit
- < 50%：效果差，考慮關閉或改用 DEMAND 模式
- Qcache_lowmem_prunes 快速增長：擴大
query_cache_size 或執行 FLUSH QUERY CACHE
- Qcache_free_blocks / Qcache_total_blocks > 0.3：碎片嚴重，定期
FLUSH QUERY CACHE
六、實戰最佳實踐
- MySQL 5.7+ / 8.0 建議關閉：查詢快取全域互斥鎖在高並發下成為嚴重瓶頸。官方 8.0 已移除。改用 Redis/Memcached 應用層快取。
- 若必須使用：設
query_cache_type = DEMAND，僅在確定受益的查詢加 SQL_CACHE 提示。
- 避免大結果集快取：
query_cache_limit 設 1-2MB，防止單一大查詢佔滿快取。
- 分區表/大表慎用：失效掃描成本高。
- 監控腳本：定期收集上述狀態變數，自動計算命中率、碎片率、剔除率，異常告警。
七、範例驗證流程
mysql> SET GLOBAL query_cache_size = 600000;
mysql> SET SESSION query_cache_type = ON;
mysql> USE test;
mysql> SELECT COUNT(*) FROM animals; -- 首次執行
mysql> SHOW STATUS LIKE 'Qcache_hits'; -- 0
mysql> SELECT COUNT(*) FROM animals; -- 二次執行
mysql> SHOW STATUS LIKE 'Qcache_hits'; -- 1
mysql> SELECT COUNT(*) FROM animals; -- 三次執行
mysql> SHOW STATUS LIKE 'Qcache_hits'; -- 3
mysql> INSERT INTO animals VALUES (9, 'testsds'); -- 表變更
mysql> SELECT COUNT(*) FROM animals; -- 快取失效，重新查詢
mysql> SHOW STATUS LIKE 'Qcache_hits'; -- 仍為 3
mysql> SELECT COUNT(*) FROM animals; -- 新結果快取
mysql> SHOW STATUS LIKE 'Qcache_hits'; -- 4
