一、MySQL查詢緩存（Query Cache）

The MySQL Query Cache

從 MySQL4開始，出現(xiàn)了QueryCache查詢緩存，如果使用了QueryCache，當(dāng)查詢接收到一個(gè)和之前同樣的查詢，服務(wù)器將會從查詢緩存中檢索結(jié)果，而不是再次分析和執(zhí)行上次的查詢。這樣就大大提高了性能，節(jié)省時(shí)間，非常有用。

MySQL查詢緩存保留了查詢返回給客戶端的完整結(jié)果。當(dāng)緩存命中的時(shí)候，服務(wù)器馬上返回保存的結(jié)果，并跳過解析、優(yōu)化和執(zhí)行步驟。

查詢緩存保留了查詢使用過的表，如果表發(fā)生了改變，那么緩存就失效了。也就是說，如果一個(gè)表被頻繁更新，那么就要考慮清楚究竟是否應(yīng)該對相關(guān)的一些SQL進(jìn)行QueryCache了。一個(gè)被頻繁更新的表如果被應(yīng)用了QueryCache，可能會加重?cái)?shù)據(jù)庫的負(fù)擔(dān)，而不是減輕負(fù)擔(dān)。我一般的做法是默認(rèn)打開QueryCache，而對一些涉及頻繁更新的表的SQL語句加上SQL_NO_CACHE關(guān)鍵詞來對其禁用 CACHE。這樣可以盡可能避免不必要的內(nèi)存操作，盡可能保持內(nèi)存的連續(xù)性。

查詢緩存不會存儲有不確定結(jié)果的查詢。因此，任何一個(gè)包含不確定函數(shù)（比如NOW()或CURRENT_DATE()） 的查詢不會被緩存。查詢緩存不會緩存引用了用戶自定義函數(shù)、用戶自定義變量、臨時(shí)表、mysql數(shù)據(jù)庫中的表或者任何一個(gè)有列級權(quán)限的表的查詢。請參閱 MySQL手冊了解所有不會被緩存的查詢類型。

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

QueryCache是根據(jù)SQL語句來cache的。一個(gè)SQL查詢?nèi)绻詓elect開頭，那么MySQL服務(wù)器將嘗試對其使用QueryCache。每個(gè)Cache都是以SQL文本作為key來存的。在應(yīng)用QueryCache之前，SQL文本不會被作任何處理。也就是說，兩個(gè)SQL語句，只要相差哪怕是一個(gè)字符（例如大小寫不一樣；多一個(gè)空格等），那么這兩個(gè)SQL將使用不同的一個(gè)CACHE。

不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端里，在發(fā)送SQL給服務(wù)器之前，會做如下處理：

• 過濾所有注釋
• 去掉SQL文本前後的空格,TAB等字符。注意，是文本前面和後面的。中間的不會被去掉。

下面的三條SQL里，因?yàn)镾ELECT大小寫的關(guān)系，最後一條和其他兩條在QueryCache里肯定是用的不一樣的存儲位置。而第一條和第二條，區(qū)別在于後者有個(gè)注釋，在不同客戶端，會有不一樣的結(jié)果。所以，保險(xiǎn)起見，請盡量不要使用動(dòng)態(tài)的注釋。在PHP的mysql擴(kuò)展里，SQL的注釋是不會被去掉的。也就是三條SQL會被存儲在三個(gè)不同的緩存里，雖然它們的結(jié)果都是一樣的。

select * FROM people where name=’surfchen’;   select * FROM people where /*hey~*/name=’surfchen’;   SELECT * FROM people where name=’surfchen’;   

那些查詢很分散的SQL語句，也不應(yīng)該使用QueryCache。例如用來查詢用戶和密碼的語句——“select pass from user where name=’surfchen’”。這樣的語句，在一個(gè)系統(tǒng)里，很有可能只在一個(gè)用戶登陸的時(shí)候被使用。每個(gè)用戶的登陸所用到的查詢，都是不一樣的SQL 文本，QueryCache在這里就幾乎不起作用了，因?yàn)榫彺娴臄?shù)據(jù)幾乎是不會被用到的，它們只會在內(nèi)存里占地方。

二、緩存設(shè)置

打開查詢緩存，是通過幾個(gè)步驟來設(shè)置的，例如：雖然你設(shè)置Mysql允許查詢緩存，但

是如果你設(shè)置的查詢緩存大小為了0，這和沒有允許沒什么區(qū)別。

所以必須是幾個(gè)步驟的設(shè)置才能真正打開查詢緩存這個(gè)功能。

下面我用 mysql6.0 最為演示最常用的設(shè)置查詢緩存

第一： query_cache_type 使用查詢緩存的方式

一般，我們會把 query_cache_type 設(shè)置為 ON,默認(rèn)情況下應(yīng)該是ON

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON |
+--------------------+
這樣 當(dāng)我們執(zhí)行 select id,name from tableName; 這樣就會用到查詢緩存。

在 query_cache_type 打開的情況下，如果你不想使用緩存，需要指明
select sql_no_cache id,name from tableName;

當(dāng)然也可以禁用查詢緩存： mysql> set session query_cache_type=off;
這里我們不討論這個(gè)，我們演示常用的設(shè)置。

第二： 系統(tǒng)變量 have_query_cache 設(shè)置查詢緩存是否可用

mysql> show variables like ’have_query_cache’;

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
上面的顯示，表示設(shè)置查詢緩存是可用的。

第三： 系統(tǒng)變量 query_cache_size

表示查詢緩存大小，也就是分配內(nèi)存大小給查詢緩存，如果你分配大小為0，

那么 第一步 和 第二步 起不到作用，還是沒有任何效果。

mysql> select @@global.query_cache_size;

+---------------------------+
| @@global.query_cache_size |
+---------------------------+
| 16777216 |
+---------------------------+
上面是 mysql6.0設(shè)置默認(rèn)的，之前的版本好像默認(rèn)是0的，那么就要自己設(shè)置下。

設(shè)置 set @@global.query_cache_size=1000000; 這里是設(shè)置1M左右，900多K。

再次查看下 select @@global.query_cache_size;

+---------------------------+
| @@global.query_cache_size |
+---------------------------+
| 999424 |
+---------------------------+
顯示我們設(shè)置新的大小，表示設(shè)置成功。

第四： query_cache_limit 控制緩存查詢結(jié)果的最大值

例如： 如果查詢結(jié)果很大， 也緩存？這個(gè)明顯是不可能的。

MySql 可以設(shè)置一個(gè)最大的緩存值，當(dāng)你查詢緩存數(shù)結(jié)果數(shù)據(jù)超過這個(gè)值就不會
進(jìn)行緩存。缺省為1M，也就是超過了1M查詢結(jié)果就不會緩存。

mysql> select @@global.query_cache_limit;

+----------------------------+
| @@global.query_cache_limit |
+----------------------------+
| 1048576 |
+----------------------------+

這個(gè)是默認(rèn)的數(shù)值，如果需要修改，就像設(shè)置緩存大小一樣設(shè)置，使用set
重新指定大小。

三、變量

show variables like ’query_cache%’可以看到這些信息。

query_cache_limit
如果單個(gè)查詢結(jié)果大于這個(gè)值，則不Cache

query_cache_size
分配給QueryCache的內(nèi)存。如果設(shè)為0，則相當(dāng)于禁用QueryCache。要注意QueryCache必須使用大約40KB來存儲它的結(jié)構(gòu)，如果設(shè)定小于40KB,則相當(dāng)于禁用QueryCache。QueryCache存儲的最小單位是1024 byte，所以如果你設(shè)定了一個(gè)不是1024的倍數(shù)的值，這個(gè)值會被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。

query_cache_type
OFF 完全禁止QueryCache，不受SQL語句控制（另外可能要注意的是，即使這里禁用，上面一個(gè)參數(shù)所設(shè)定的內(nèi)存大小還是會被分配）；ON啟用QueryCache，可以在SQL語句使用SQL_NO_CACHE禁用；

query_cache_min_res_unit
每次給QueryCache結(jié)果分配內(nèi)存的大小

四、狀態(tài)

show status like ’QueryCacheache%’可以看到這些信息。

QueryCacheache_free_blocks

當(dāng)一個(gè)表被更新之後，和它相關(guān)的cache blocks將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部。這些blocks將會被統(tǒng)計(jì)到這個(gè)值來?？梢杂肍LUSH QUERY CACHE語句來清空free blocks。

QueryCacheache_free_memory

可用內(nèi)存，如果很小，考慮增加query_cache_size

QueryCacheache_hits

自mysql進(jìn)程啟動(dòng)起，cache的命中數(shù)量

QueryCacheache_inserts

自mysql進(jìn)程啟動(dòng)起，被增加進(jìn)QueryCache的數(shù)量

QueryCacheache_lowmem_prunes

由于內(nèi)存過少而導(dǎo)致QueryCache被刪除的條數(shù)。加大query_cache_size，盡可能保持這個(gè)值0增長。

QueryCacheache_not_cached

自mysql進(jìn)程啟動(dòng)起，沒有被cache的只讀查詢數(shù)量（包括select,show,use,desc等）

QueryCacheache_queries_in_cache

當(dāng)前被cache的SQL數(shù)量

QueryCacheache_total_blocks

在 QueryCache中的blocks數(shù)。一個(gè)query可能被多個(gè)blocks存儲，而這幾個(gè)blocks中的最後一個(gè)，未用滿的內(nèi)存將會被釋放掉。例如一個(gè)QueryCache結(jié)果要占6KB內(nèi)存，如果query_cache_min_res_unit是4KB，則最後將會生成3個(gè)blocks，第一個(gè)block用來存儲sql語句文本，這個(gè)不會被統(tǒng)計(jì)到query+cache_size里，第二個(gè)block為4KB，第三個(gè)block為2KB（先allocate4KB，然後釋放多馀的2KB）。每個(gè)表，當(dāng)?shù)谝粋€(gè)和它有關(guān)的SQL查詢被CACHE的時(shí)候，會使用一個(gè)block來存儲表信息。也就是說，block會被用在三處地方：表信息，SQL文本，查詢結(jié)果。

五、存儲塊(block)

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

除了查詢結(jié)果需要存儲塊之外，每個(gè)SQL文本也需要一個(gè)存儲塊，而涉及到的表也需要一個(gè)存儲塊（表的存儲塊是所有線程共享的，每個(gè)表只需要一個(gè)存儲塊）。存儲塊總數(shù)量=查詢結(jié)果數(shù)量*2+涉及的數(shù)據(jù)庫表數(shù)量。也就是說，第一個(gè)緩存生成的時(shí)候，至少需要三個(gè)存儲塊：表信息存儲塊，SQL文本存儲塊，查詢結(jié)果存儲塊。而第二個(gè)查詢?nèi)绻玫氖峭粋€(gè)表，那么最少只需要兩個(gè)存儲塊：SQL文本存儲塊，查詢結(jié)果存儲塊。

通過觀察QueryCacheache_queries_in_cache和QueryCacheache_total_blocks可以知道平均每個(gè)緩存結(jié)果占用的存儲塊。它們的比例如果接近1:2，則說明當(dāng)前的query_cache_min_res_unit參數(shù)已經(jīng)足夠大了。如果QueryCacheache_total_blocks比 QueryCacheache_queries_in_cache多很多，則需要增加query_cache_min_res_unit的大小。

QueryCacheache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的block占用的內(nèi)存很小，可以忽略）如果遠(yuǎn)遠(yuǎn)大于query_cache_size-QueryCacheache_free_memory，那么可以嘗試減小 query_cache_min_res_unit的值。

如果QueryCacheache_lowmem_prunes增長迅速，意味著很多緩存因?yàn)閮?nèi)存不夠而被釋放，而不是因?yàn)橄嚓P(guān)表被更新。嘗試加大query_cache_size，盡量使QueryCacheache_lowmem_prunes零增長。

參考 http://dev./doc/refman/5.1/zh/database-administration.html#query-cache