MySQL的查詢緩存小記
2024-07-24 13:00:40
供稿：網(wǎng)友
Mysql查詢緩存機(jī)制原理QueryCache，QueryCache是根據(jù)SQL語句來cache的。一個(gè)SQL查詢?nèi)绻詓elect開頭，那么 MySQL服務(wù)器將嘗試對其使用 QC。每個(gè)Cache都是以SQL文本作為key來存的。在應(yīng)用QueryCache之前，SQL文本不會(huì)被作任何處理。也就是說，兩個(gè)SQL語句，只要 相差哪怕是一個(gè)字符（例如大小寫不一樣；多一個(gè)空格等），那么這兩個(gè)SQL將使用不同的一個(gè)QueryCache。
不過SQL文本有可能會(huì)被客戶端做一些處理。例如在官方的命令行客戶端里，在發(fā)送SQL給服務(wù)器之前，會(huì)做如下處理：
過濾所有注釋
去掉SQL文本前后的空格,TAB等字符。注意，是文本前面和后面的。中間的不會(huì)被去掉。
下 面的三條SQL里，因?yàn)镾ELECT大小寫的關(guān)系，最后一條和其他兩條在QC里肯定是用的不一樣的存儲(chǔ)位置。而第一條和第二條，區(qū)別在于后者有個(gè)注釋，在 不同客戶端，會(huì)有不一樣的結(jié)果。所以，保險(xiǎn)起見，請盡量不要使用動(dòng)態(tài)的注釋。在PHP的mysql擴(kuò)展里，SQL的注釋是不會(huì)被去掉的。也就是三條 SQL會(huì)被存儲(chǔ)在三個(gè)不同的緩存里，雖然它們的結(jié)果都是一樣的。
select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';
目前只有select語句會(huì)被cache，其他類似show,use的語句則不會(huì)被cache。
因?yàn)镼C是如此前端，如此簡單的一個(gè)緩存系統(tǒng)，所以如果一個(gè)表被更新，那么和這個(gè)表相關(guān)的SQL的所有QC都會(huì)被失效。假設(shè)一個(gè)聯(lián)合查詢里涉及到了表A和表B，如果表A或者表B的其中一個(gè)被更新（update或者delete），這個(gè)查詢的QC將會(huì)失效。
也 就是說，如果一個(gè)表被頻繁更新，那么就要考慮清楚究竟是否應(yīng)該對相關(guān)的一些SQL進(jìn)行QC了。一個(gè)被頻繁更新的表如果被應(yīng)用了QueryCache，可能 會(huì)加重Mysql數(shù)據(jù)庫的負(fù)擔(dān)，而不是減輕Mysql負(fù)擔(dān)。我一般的做法是默認(rèn)打開QueryCache，而對一些涉及頻繁更新的表的SQL語句加上 SQL_NO_CACHE關(guān)鍵詞來對其禁用CACHE。這樣可以盡可能避免不必要的內(nèi)存操作，盡可能保持內(nèi)存的連續(xù)性。
那些查詢很分散的 SQL語句，也不應(yīng)該使用緩存。例如用Mysql來查詢用戶和密碼的語句——“select pass from user where name='surfchen'”。這樣的語句，在一個(gè)系統(tǒng)里，很有可能只在一個(gè)用戶登陸的時(shí)候被使用。每個(gè)用戶的登陸所用到的查詢，都是不一樣的SQL 文本，QueryCache在這里就幾乎不起作用了，因?yàn)榫彺娴臄?shù)據(jù)幾乎是不會(huì)被用到的，它們只會(huì)在內(nèi)存里占地方。
存儲(chǔ)塊
在本節(jié)里“存儲(chǔ)塊”和“block”是同一個(gè)意思
QueryCache 緩存一個(gè)查詢結(jié)果的時(shí)候，一般情況下不是一次性地分配足夠多的內(nèi)存來緩存結(jié)果的。而是在查詢結(jié)果獲得的過程中，逐塊存儲(chǔ)。當(dāng)一個(gè)存儲(chǔ)塊被填滿之后，一個(gè)新 的存儲(chǔ)塊將會(huì)被創(chuàng)建，并分配內(nèi)存（allocate）。單個(gè)存儲(chǔ)塊的內(nèi)存分配大小通過query_cache_min_res_unit參數(shù)控制，默認(rèn)為 4KB。最后一個(gè)存儲(chǔ)塊，如果不能被全部利用，那么沒使用的內(nèi)存將會(huì)被釋放。如果被緩存的結(jié)果很大，那么會(huì)可能會(huì)導(dǎo)致分配內(nèi)存操作太頻繁，系統(tǒng)系能也隨之 下降；而如果被緩存的結(jié)果都很小，那么可能會(huì)導(dǎo)致內(nèi)存碎片過多，這些碎片如果太小，就很有可能不能再被分配使用。
除了查詢結(jié)果需要存儲(chǔ)塊 之外，每個(gè)SQL文本也需要一個(gè)存儲(chǔ)塊，而涉及到的表也需要一個(gè)存儲(chǔ)塊（表的存儲(chǔ)塊是所有線程共享的，每個(gè)表只需要一個(gè)存儲(chǔ)塊）。存儲(chǔ)塊總數(shù)量=查詢結(jié)果 數(shù)量*2+涉及的數(shù)據(jù)庫表數(shù)量。也就是說，第一個(gè)緩存生成的時(shí)候，至少需要三個(gè)存儲(chǔ)塊：表信息存儲(chǔ)塊，SQL文本存儲(chǔ)塊，查詢結(jié)果存儲(chǔ)塊。而第二個(gè)查詢?nèi)?果用的是同一個(gè)表，那么最少只需要兩個(gè)存儲(chǔ)塊：SQL文本存儲(chǔ)塊，查詢結(jié)果存儲(chǔ)塊。
通過觀察 Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個(gè)緩存結(jié)果占用的存儲(chǔ)塊。它們的比例如果接近 1:2，則說明當(dāng)前的query_cache_min_res_unit參數(shù)已經(jīng)足夠大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多，則需要增加query_cache_min_res_unit的大小。
Qcache_queries_in_cache*query_cache_min_res_unit（sql 文本和表信息所在的block占用的內(nèi)存很小，可以忽略）如果遠(yuǎn)遠(yuǎn)大于query_cache_size-Qcache_free_memory，那么可 以嘗試減小 query_cache_min_res_unit的值。
調(diào)整大小
如果Qcache_lowmem_prunes增長迅速，意味著很多緩存因?yàn)閮?nèi)存不夠而被釋放，而不是因?yàn)橄嚓P(guān)表被更新。嘗試加大query_cache_size，盡量使Qcache_lowmem_prunes零增長。
啟動(dòng)參數(shù)
show variables like 'query_cache%'可以看到這些信息。
query_cache_limit:如果單個(gè)查詢結(jié)果大于這個(gè)值，則不Cache
query_cache_size: 分配給QC的內(nèi)存。如果設(shè)為0，則相當(dāng)于禁用QC。要注意QC必須使用大約40KB來存儲(chǔ)它的結(jié)構(gòu)，如果設(shè)定小于 40KB,則相當(dāng)于禁用QC。QC存儲(chǔ)的最小單位是1024 byte，所以如果你設(shè)定了一個(gè)不是1024的倍數(shù)的值，這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。
query_cache_type:0 完全禁止QC，不受SQL語句控制（另外可能要注意的是，即使這里禁用，上面一個(gè)參數(shù)所設(shè)定的內(nèi)存大小還是會(huì)被分配）；1啟用QC，可以在SQL語句使用 SQL_NO_CACHE禁用；2可以在SQL語句使用SQL_CACHE啟用。
query_cache_min_res_unit:每次給QC結(jié)果分配內(nèi)存的大小
狀態(tài)
show status like 'Qcache%'可以看到這些信息。
Qcache_free_blocks: 當(dāng)一個(gè)表被更新之后，和它相關(guān)的cache blocks將被free。但是這個(gè)block依然可能存在隊(duì)列中，除非是在隊(duì)列的尾部。這些blocks將會(huì)被統(tǒng)計(jì)到這個(gè)值來。可以用FLUSH QUERY CACHE語句來清空free blocks。
Qcache_free_memory:可用內(nèi)存，如果很小，考慮增加query_cache_size
Qcache_hits:自mysql進(jìn)程啟動(dòng)起，cache的命中數(shù)量
Qcache_inserts:自mysql進(jìn)程啟動(dòng)起，被增加進(jìn)QC的數(shù)量
Qcache_lowmem_prunes:由于內(nèi)存過少而導(dǎo)致QC被刪除的條數(shù)。加大query_cache_size，盡可能保持這個(gè)值0增長。
Qcache_not_cached:自mysql進(jìn)程啟動(dòng)起，沒有被cache的只讀查詢數(shù)量（包括select,show,use,desc等）
Qcache_queries_in_cache:當(dāng)前被cache的SQL數(shù)量
Qcache_total_blocks: 在QC中的blocks數(shù)。一個(gè)query可能被多個(gè)blocks存儲(chǔ)，而這幾個(gè)blocks中的最后一個(gè)，未用滿的內(nèi)存將會(huì)被釋放掉。例如一個(gè)QC結(jié)果 要占6KB內(nèi)存，如果query_cache_min_res_unit是4KB，則最后將會(huì)生成3個(gè) blocks，第一個(gè)block用來存儲(chǔ)sql語句文本，這個(gè)不會(huì)被統(tǒng)計(jì)到query+cache_size里，第二個(gè)block為4KB，第三個(gè) block為2KB（先allocate4KB，然后釋放多余的2KB）。每個(gè)表，當(dāng)?shù)谝粋€(gè)和它有關(guān)的SQL查詢被CACHE的時(shí)候，會(huì)使用一個(gè) block來存儲(chǔ)表信息。也就是說，block會(huì)被用在三處地方：表信息，SQL文本，查詢結(jié)果。
另外一篇：
如 果 MySQL Server 負(fù)載比較高，處理非常繁忙的話，可以啟動(dòng)Query Cache 以加速響應(yīng)時(shí)間，啟動(dòng)方法可以在my.cnf(Linux)或my.ini(Windows)中加入不以下項(xiàng)目:(Redhat下面是：/etc /my.cnf;Debian和Ubuntu是在/etc/mysql/my.cnf)
query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576
以上語句的設(shè)置中 query_cache_size 是分配256M內(nèi)存給Query Cache;query_cache_type=1，是給所有的查詢做Cache；query_cache_limit 是指定個(gè)別的查詢語句1MB的內(nèi)存。
這些數(shù)據(jù)可以根據(jù)自己的需求作出適當(dāng)?shù)母模O(shè)置完成之后，保存文檔，重新啟動(dòng)MySQL即可。
query_cache_type 0 代表不使用緩沖， 1 代表使用緩沖，2 代表根據(jù)需要使用。
設(shè)置 1 代表緩沖永遠(yuǎn)有效，如果不需要緩沖，就需要使用如下語句：
SELECT SQL_NO_CACHE * FROM my_table WHERE ...
如果設(shè)置為 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)
如果需要計(jì)算命中率，需要知道服務(wù)器執(zhí)行了多少 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 的數(shù)值會(huì)遠(yuǎn)遠(yuǎn)大于 Com_select 。
幾個(gè)命令：
mysql> show status like 'Qcache%'; #查看mysql查詢緩存的運(yùn)行時(shí)狀態(tài)
mysql> reset query cache; #重置mysql查詢緩存
mysql> flush query cache #清理查詢緩存碎片
