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

前用naviacte for mysql 用sql語句查詢時發現 第一次查詢比較慢，在用同樣的sql查詢時會比較快，之後查資料，才發現mysql也有緩存機制；意思是將select查詢的結果集和sql語句映射到內存緩存起來。當有存在緩存數據的時候，服務器馬上返回服務器的結果，跳過解析sql的過程。

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