MySQL的查詢緩存

Mysql查詢緩存機制原理QueryCache，QueryCache是根據SQL語句來cache的。一個SQL查詢如果以select開頭，那麼 MySQL服務器將嘗試對其使用 QC。每個Cache都是以SQL文本作為key來存的。在應用QueryCache之前，SQL文本不會被作任何處理。也就是說，兩個SQL語句，只要 相差哪怕是一個字符（例如大小寫不一樣；多一個空格等），那麼這兩個SQL將使用不同的一個QueryCache。

不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端裡，在發送SQL給服務器之前，會做如下處理：
過濾所有注釋
去掉SQL文本前後的空格,TAB等字符。注意，是文本前面和後面的。中間的不會被去掉。

下 面的三條SQL裡，因為SELECT大小寫的關系，最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條，區別在於後者有個注釋，在 不同客戶端，會有不一樣的結果。所以，保險起見，請盡量不要使用動態的注釋。在PHP的mysql擴展裡，SQL的注釋是不會被去掉的。也就是三條 SQL會被存儲在三個不同的緩存裡，雖然它們的結果都是一樣的。
select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';

目前只有select語句會被cache，其他類似show,use的語句則不會被cache。

因為QC是如此前端，如此簡單的一個緩存系統，所以如果一個表被更新，那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B，如果表A或者表B的其中一個被更新（update或者delete），這個查詢的QC將會失效。

也 就是說，如果一個表被頻繁更新，那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QueryCache，可能 會加重Mysql數據庫的負擔，而不是減輕Mysql負擔。我一般的做法是默認打開QueryCache，而對一些涉及頻繁更新的表的SQL語句加上 SQL_NO_CACHE關鍵詞來對其禁用CACHE。這樣可以盡可能避免不必要的內存操作，盡可能保持內存的連續性。

那些查詢很分散的 SQL語句，也不應該使用緩存。例如用Mysql來查詢用戶和密碼的語句——“select pass from user where name='surfchen'”。這樣的語句，在一個系統裡，很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢，都是不一樣的SQL 文本，QueryCache在這裡就幾乎不起作用了，因為緩存的數據幾乎是不會被用到的，它們只會在內存裡占地方。
存儲塊
在本節裡“存儲塊”和“block”是同一個意思

QueryCache 緩存一個查詢結果的時候，一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中，逐塊存儲。當一個存儲塊被填滿之後，一個新 的存儲塊將會被創建，並分配內存（allocate）。單個存儲塊的內存分配大小通過query_cache_min_res_unit參數控制，默認為 4KB。最後一個存儲塊，如果不能被全部利用，那麼沒使用的內存將會被釋放。如果被緩存的結果很大，那麼會可能會導致分配內存操作太頻繁，系統系能也隨之 下降；而如果被緩存的結果都很小，那麼可能會導致內存碎片過多，這些碎片如果太小，就很有可能不能再被分配使用。

除了查詢結果需要存儲塊 之外，每個SQL文本也需要一個存儲塊，而涉及到的表也需要一個存儲塊（表的存儲塊是所有線程共享的，每個表只需要一個存儲塊）。存儲塊總數量=查詢結果 數量*2+涉及的數據庫表數量。也就是說，第一個緩存生成的時候，至少需要三個存儲塊：表信息存儲塊，SQL文本存儲塊，查詢結果存儲塊。而第二個查詢如 果用的是同一個表，那麼最少只需要兩個存儲塊：SQL文本存儲塊，查詢結果存儲塊。

通過觀察 Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個緩存結果占用的存儲塊。它們的比例如果接近 1:2，則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多，則需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit（sql 文本和表信息所在的block占用的內存很小，可以忽略）如果遠遠大於query_cache_size-Qcache_free_memory，那麼可 以嘗試減小 query_cache_min_res_unit的值。
調整大小
如果Qcache_lowmem_prunes增長迅速，意味著很多緩存因為內存不夠而被釋放，而不是因為相關表被更新。嘗試加大query_cache_size，盡量使Qcache_lowmem_prunes零增長。
啟動參數
show variables like 'query_cache%'可以看到這些信息。
query_cache_limit:如果單個查詢結果大於這個值，則不Cache
query_cache_size: 分配給QC的內存。如果設為0，則相當於禁用QC。要注意QC必須使用大約40KB來存儲它的結構，如果設定小於 40KB,則相當於禁用QC。QC存儲的最小單位是1024 byte，所以如果你設定了一個不是1024的倍數的值，這個值會被四捨五入到最接近當前值的等於1024的倍數的值。
query_cache_type:0 完全禁止QC，不受SQL語句控制（另外可能要注意的是，即使這裡禁用，上面一個參數所設定的內存大小還是會被分配）；1啟用QC，可以在SQL語句使用 SQL_NO_CACHE禁用；2可以在SQL語句使用SQL_CACHE啟用。
query_cache_min_res_unit:每次給QC結果分配內存的大小
狀態
show status like 'Qcache%'可以看到這些信息。
Qcache_free_blocks: 當一個表被更新之後，和它相關的cache blocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用FLUSH QUERY CACHE語句來清空free blocks。
Qcache_free_memory:可用內存，如果很小，考慮增加query_cache_size
Qcache_hits:自mysql進程啟動起，cache的命中數量
Qcache_inserts:自mysql進程啟動起，被增加進QC的數量
Qcache_lowmem_prunes:由於內存過少而導致QC被刪除的條數。加大query_cache_size，盡可能保持這個值0增長。
Qcache_not_cached:自mysql進程啟動起，沒有被cache的只讀查詢數量（包括select,show,use,desc等）
Qcache_queries_in_cache:當前被cache的SQL數量
Qcache_total_blocks: 在QC中的blocks數。一個query可能被多個blocks存儲，而這幾個blocks中的最後一個，未用滿的內存將會被釋放掉。例如一個QC結果 要占6KB內存，如果query_cache_min_res_unit是4KB，則最後將會生成3個 blocks，第一個block用來存儲sql語句文本，這個不會被統計到query+cache_size裡，第二個block為4KB，第三個 block為2KB（先allocate4KB，然後釋放多余的2KB）。每個表，當第一個和它有關的SQL查詢被CACHE的時候，會使用一個 block來存儲表信息。也就是說，block會被用在三處地方：表信息，SQL文本，查詢結果。

另外一篇：

如 果 MySQL Server 負載比較高，處理非常繁忙的話，可以啟動Query Cache 以加速響應時間，啟動方法可以在my.cnf(Linux)或my.ini(Windows)中加入不以下項目:(Redhat下面是：/etc /my.cnf;Debian和Ubuntu是在/etc/mysql/my.cnf)

query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576

以上語句的設置中 query_cache_size 是分配256M內存給Query Cache;query_cache_type=1，是給所有的查詢做Cache；query_cache_limit 是指定個別的查詢語句1MB的內存。

這些數據可以根據自己的需求作出適當的更改，設置完成之後，保存文檔，重新啟動MySQL即可。

 

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 。

幾個命令：
mysql> show status like 'Qcache%'; #查看mysql查詢緩存的運行時狀態
mysql> reset query cache;          #重置mysql查詢緩存
mysql> flush query cache           #清理查詢緩存碎片