mysql query cache用法與效能詳細介紹

mysql Query Cache 預設為開啟。從某種程度可以提高查詢的效果，但是未必是最優的解決方案，如果有的大量的修改和查詢時，由於修改造成的cache失效，會給伺服器造成很大的開銷，可以通過query_cache_type【0(OFF)1(ON)2(DEMAND)】來控制緩衝的開關.

需要注意的是mysql query cache 是對大小寫敏感的，因為Query Cache 在記憶體中是以 HASH 結構來進行映射，HASH 演算法基礎就是組成 SQL 陳述式的字元，所以 任何sql語句的改變重新cache,這也是項目開發中要建立sql語句書寫規範的原因吧

1. 何時cache
a) mysql query cache內容為 select 的結果集, cache 使用完整的 sql 字串做 key, 並區分大小寫，空格等。即兩個sql必須完全一致才會導致cache命中。
b) prepared statement永遠不會cache到結果，即使參數完全一樣。據說在 5.1 之後會得到改善。
c) where條件中如包含了某些函數永遠不會被cache, 比如current_date, now等。
d) date 之類的函數如果返回是以小時或天層級的，最好先算出來再傳進去。
select * from foo where date1=current_date -- 不會被 cache
select * from foo where date1='2008-12-30' -- 被cache, 正確的做法
e) 太大的result set不會被cache (< query_cache_limit)

2. 何時invalidate
a) 一旦表資料進行任何一行的修改，基於該表相關cache立即全部失效。
b) 為什麼不做聰明一點判斷修改的是否cache的內容？因為分析cache內容太複雜，伺服器需要追求最大的效能。

3. 效能
a) cache 未必所有場合總是會改善效能
當有大量的查詢和大量的修改時，cache機制可能會造成效能下降。因為每次修改會導致系統去做cache失效操作，造成不小開銷。
另外系統cache的訪問由一個單一的全域鎖來控制，這時候大量>的查詢將被阻塞，直至鎖釋放。所以不要簡單認為設定cache必定會帶來效能提升。
b) 大result set不會被cache的開銷
太大的result set不會被cache, 但mysql預先不知道result set的長度，所以只能等到reset set在cache添加到臨界值 query_cache_limit 之後才會簡單的把這個cache 丟棄。這並不是一個高效的操作。如果mysql status中Qcache_not_cached太大的話, 則可對潛在的大結果集的sql顯式添加 SQL_NO_CACHE 的控制。
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

4. 記憶體池使用
mysql query cache 使用記憶體池技術，自己管理記憶體釋放和分配，而不是通過作業系統。記憶體池使用的基本單位是變長的block, 一個result set的cache通過鏈表把這些block串起來。因為存放result set的時候並不知道這個resultset最終有多大。block最短長度為 query_cache_min_res_unit, resultset 的最後一個block會執行trim操作。

Query Cache 在提高資料庫效能方面具有非常重要的作用。

其設定也非常簡單，僅需要在設定檔寫入兩行： query_cache_type 和 query_cache _size，而且 MySQL 的 query cache 非常快！而且一旦命中，就直接發送給用戶端，節約大量的 CPU 時間。

當然，非 SELECT 語句對緩衝是有影響的，它們可能使緩衝中的資料到期。一個 UPDATE 語句引起的部分表修改，將導致對該表所有的緩衝資料失效，這是 MySQL 為了平衡效能而沒有採取的措施。因為，如果每次 UPDATE 需要檢查修改的資料，然後撤出部分緩衝將導致代碼的複雜度增加。

 query_cache_type 0 代表不使用緩衝， 1 代表使用緩衝，2 代表根據需要使用。

設定 1 代表緩衝永遠有效，如果不需要緩衝，就需要使用如下語句：

代碼如下	複製代碼
SELECT SQL_NO_CACHE * FROM my_table WHERE ...

如果設定為 2 ，需要開啟緩衝，可以用如下語句：

代碼如下	複製代碼
SELECT SQL_CACHE * FROM my_table WHERE ...

用 SHOW STATUS 可以查看緩衝的情況：

代碼如下

複製代碼

mysql> show status like 'Qca%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_queries_in_cache | 8 | | Qcache_inserts | 545875 | | Qcache_hits | 83951 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2343256 | | Qcache_free_memory | 33508248 | | Qcache_free_blocks | 1 | | Qcache_total_blocks | 18 | +-------------------------+----------+ 8 rows in set (0.00 sec)

如果需要計算命中率，需要知道伺服器執行了多少 SELECT 語句：

代碼如下	複製代碼
mysql> show status like 'Com_sel%'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_select | 2889628 | +---------------+---------+ 1 row in set (0.01 sec)

在本例中， MySQL 命中了 2,889,628 條查詢中的 83,951 條，而且 INSERT 語句只有 545,875 條。因此，它們兩者的和和280萬的總查詢相比有很大差距，因此，我們知道本例使用的緩衝類型是 2 。

而在類型是 1 的例子中， Qcache_hits 的數值會遠遠大於 Com_select