萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查詢緩存開啟與調優指南:原理、適用場景與避坑全解析

什麼是查詢緩存?

Query Cache 將 SELECT 語句文本 與其 結果集 以 Key-Value 形式存放在內存中。相同 SQL(區分大小寫、空格、註釋)再次執行時,直接返回緩存結果,跳過解析、優化、執行全過程。

開啟配置

[mysqld]
query_cache_type = 1        # 0=關閉, 1=開啟(默認), 2=DEMAND(需 SQL_CACHE 提示)
query_cache_size = 64M      # 緩存總內存,0 實際關閉
query_cache_limit = 2M      # 單個查詢結果超過此值不緩存
query_cache_min_res_unit = 2K  # 分配最小塊,防碎片

驗證生效

SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 2048     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
+------------------------------+----------+

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 66725432 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |

工作原理與判定規則

✅ 可緩存條件

  • 精確匹配:SQL 文本逐字節相同(大小寫、空格、註釋皆需一致)。
  • 確定性函數:不含 NOW()、RAND()、UUID()、USER() 等非確定性函數。
  • 非系統表:不查詢 mysql、information_schema、performance_schema。
  • 無鎖語句:無 LOCK IN SHARE MODE、FOR UPDATE。
  • 非事務隔離級別 SERIALIZABLE。
  • 結果集 < query_cache_limit。

❌ 自動失效場景

  • 表任意數據變更(INSERT/UPDATE/DELETE/TRUNCATE/ALTER/DROP) → 該表相關緩存全部失效。
  • 表結構變更(DDL)。
  • 表被鎖定(LOCK TABLES)。

優缺點權衡

優點缺點
極大降低讀查詢延遲(微秒級返回)寫入頻繁表緩存命中率極低,反成累贅
減輕 CPU(解析/優化/執行)壓力全局鎖保護緩存結構,高併發下成瓶頸
應用層無感知,零代碼改動內存碎片化嚴重,FLUSH QUERY CACHE 會阻塞

適用場景判斷

✅ 適合:數據變更極低、重複查詢極高的只讀/多讀少寫場景。
例子:配置表、字典表、靜態報表、商品分類、首頁熱門推薦。
❌ 不適合:高頻寫入表(訂單、日誌、用戶行為)、大結果集、非確定性查詢。
MySQL 8.0 已移除 Query Cache,官方建議改用 Redis、應用層緩存、Materialized View 等方案。

調優實戰建議

  1. 緩存大小:從 64M 起步,觀察 Qcache_lowmem_prunes(因內存不足被淘汰次數),若持續增長則調大。
  2. 碎片整理:定期 FLUSH QUERY CACHE(會阻塞),或設 query_cache_min_res_unit 減少碎片。
  3. 選擇性緩存:query_cache_type=2 + SQL 中顯式 SELECT SQL_CACHE ... / SELECT SQL_NO_CACHE ...。
  4. 監控指標:Qcache_hits / (Qcache_hits + Qcache_inserts) = 命中率,< 20% 考慮關閉。

替代方案展望

  • Redis/Memcached:應用層控制緩存鍵、TTL、失效策略,靈活可擴展。
  • MySQL 8.0+ CTE + 窗口函數:複雜報表查詢下推計算,減少中間結果集傳輸。
  • 物化視圖:定時刷新預聚合表,適合固定維度報表。
copyright © 萬盛學電腦網 all rights reserved