MySQL的Query Cache原理分析

QueryCache(下面簡(jiǎn)稱(chēng)QC)是根據(jù)SQL語(yǔ)句來(lái)cache的。一個(gè)SQL查詢(xún)?nèi)绻詓elect開(kāi)頭,那么mysql服務(wù)器將嘗試對(duì)其使用QC。每個(gè)Cache都是以SQL文本作為key來(lái)存的。

原理
QueryCache(下面簡(jiǎn)稱(chēng)QC)是根據(jù)SQL語(yǔ)句來(lái)cache的。一個(gè)SQL查詢(xún)?nèi)绻詓elect開(kāi)頭,那么MySQL服務(wù)器將嘗試對(duì)其使用QC。每個(gè)Cache都是以SQL文本作為key來(lái)存的。在應(yīng)用QC之前,SQL文本不會(huì)被作任何處理。也就是說(shuō),兩個(gè)SQL語(yǔ)句,只要相差哪怕是一個(gè)字符(例如大小寫(xiě)不一樣;多一個(gè)空格等),那么這兩個(gè)SQL將使用不同的一個(gè)CACHE。
不過(guò)SQL文本有可能會(huì)被客戶(hù)端做一些處理。例如在官方的命令行客戶(hù)端里,在發(fā)送SQL給服務(wù)器之前,會(huì)做如下處理:

過(guò)濾所有注釋
去掉SQL文本前后的空格,TAB等字符。注意,是文本前面和后面的。中間的不會(huì)被去掉。

下面的三條SQL里,因?yàn)镾ELECT大小寫(xiě)的關(guān)系,最后一條和其他兩條在QC里肯定是用的不一樣的存儲(chǔ)位置。而第一條和第二條,區(qū)別在于后者有個(gè)注釋?zhuān)诓煌蛻?hù)端,會(huì)有不一樣的結(jié)果。所以,保險(xiǎn)起見(jiàn),請(qǐng)盡量不要使用動(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語(yǔ)句會(huì)被cache,其他類(lèi)似show,use的語(yǔ)句則不會(huì)被cache。

因?yàn)镼C是如此前端,如此簡(jiǎn)單的一個(gè)緩存系統(tǒng),所以如果一個(gè)表被更新,那么和這個(gè)表相關(guān)的SQL的所有QC都會(huì)被失效。假設(shè)一個(gè)聯(lián)合查詢(xún)里涉及到了表A和表B,如果表A或者表B的其中一個(gè)被更新(update或者delete),這個(gè)查詢(xún)的QC將會(huì)失效。

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

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

存儲(chǔ)塊
在本節(jié)里“存儲(chǔ)塊”和“block”是同一個(gè)意思
QC緩存一個(gè)查詢(xún)結(jié)果的時(shí)候,一般情況下不是一次性地分配足夠多的內(nèi)存來(lái)緩存結(jié)果的。而是在查詢(xún)結(jié)果獲得的過(guò)程中,逐塊存儲(chǔ)。當(dāng)一個(gè)存儲(chǔ)塊被填滿(mǎn)之后,一個(gè)新的存儲(chǔ)塊將會(huì)被創(chuàng)建,并分配內(nèi)存(allocate)。單個(gè)存儲(chǔ)塊的內(nèi)存分配大小通過(guò)query_cache_min_res_unit參數(shù)控制,默認(rèn)為4KB。最后一個(gè)存儲(chǔ)塊,如果不能被全部利用,那么沒(méi)使用的內(nèi)存將會(huì)被釋放。如果被緩存的結(jié)果很大,那么會(huì)可能會(huì)導(dǎo)致分配內(nèi)存操作太頻繁,系統(tǒng)系能也隨之下降;而如果被緩存的結(jié)果都很小,那么可能會(huì)導(dǎo)致內(nèi)存碎片過(guò)多,這些碎片如果太小,就很有可能不能再被分配使用。

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

通過(guò)觀察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個(gè)緩存結(jié)果占用的存儲(chǔ)塊。它們的比例如果接近1:2,則說(shuō)明當(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增長(zhǎng)迅速,意味著很多緩存因?yàn)閮?nèi)存不夠而被釋放,而不是因?yàn)橄嚓P(guān)表被更新。嘗試加大query_cache_size,盡量使Qcache_lowmem_prunes零增長(zhǎng)。
啟動(dòng)參數(shù)
show variables like ‘query_cache%’可以看到這些信息。
query_cache_limit:如果單個(gè)查詢(xún)結(jié)果大于這個(gè)值,則不Cache
query_cache_size:分配給QC的內(nèi)存。如果設(shè)為0,則相當(dāng)于禁用QC。要注意QC必須使用大約40KB來(lái)存儲(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語(yǔ)句控制(另外可能要注意的是,即使這里禁用,上面一個(gè)參數(shù)所設(shè)定的內(nèi)存大小還是會(huì)被分配);1啟用QC,可以在SQL語(yǔ)句使用SQL_NO_CACHE禁用;2可以在SQL語(yǔ)句使用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è)值來(lái)。可以用FLUSH QUERY CACHE語(yǔ)句來(lái)清空f(shuō)ree 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)存過(guò)少而導(dǎo)致QC被刪除的條數(shù)。加大query_cache_size,盡可能保持這個(gè)值0增長(zhǎng)。
Qcache_not_cached:自mysql進(jìn)程啟動(dòng)起,沒(méi)有被cache的只讀查詢(xún)數(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è),未用滿(mǎn)的內(nèi)存將會(huì)被釋放掉。例如一個(gè)QC結(jié)果要占6KB內(nèi)存,如果query_cache_min_res_unit是4KB,則最后將會(huì)生成3個(gè)blocks,第一個(gè)block用來(lái)存儲(chǔ)sql語(yǔ)句文本,這個(gè)不會(huì)被統(tǒng)計(jì)到query+cache_size里,第二個(gè)block為4KB,第三個(gè)block為2KB(先allocate4KB,然后釋放多余的2KB)。每個(gè)表,當(dāng)?shù)谝粋€(gè)和它有關(guān)的SQL查詢(xún)被CACHE的時(shí)候,會(huì)使用一個(gè)block來(lái)存儲(chǔ)表信息。也就是說(shuō),block會(huì)被用在三處地方:表信息,SQL文本,查詢(xún)結(jié)果。

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊13 分享