MySQL中怎么實現(xiàn)查詢緩沖

今天就跟大家聊聊有關(guān)MySQL中怎么實現(xiàn)查詢緩沖，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

如何調(diào)整MySQL查詢緩沖

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

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

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

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

select*FROMpeoplewherename='surfchen';

select*FROMpeoplewhere/*hey~*/name='surfchen';

SELECT*FROMpeoplewherename='surfchen';

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

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

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

那些查詢很分散的SQL語句，也不應(yīng)該使用QC。例如用來查詢用戶和密碼的語句——“selectpassfromuserwherename='surfchen'”。這樣的語句，在一個系統(tǒng)里，很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢，都是不一樣的SQL文本，QC在這里就幾乎不起作用了，因為緩存的數(shù)據(jù)幾乎是不會被用到的，它們只會在內(nèi)存里占地方。

存儲塊，在本節(jié)里“存儲塊”和“block”是同一個意思。

如何調(diào)整MySQL查詢緩沖

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

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

通過觀察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個緩存結(jié)果占用的存儲塊。它們的比例如果接近1:2，則說明當前的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)存很小，可以忽略）如果遠遠大于query_cache_size-Qcache_free_memory，那么可以嘗試減小query_cache_min_res_unit的值。

調(diào)整大小

如果Qcache_lowmem_prunes增長迅速，意味著很多緩存因為內(nèi)存不夠而被釋放，而不是因為相關(guān)表被更新。嘗試加大query_cache_size，盡量使Qcache_lowmem_prunes零增長。

啟動參數(shù)

showvariableslike'query_cache%'看到的是這些信息。

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

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

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

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

狀態(tài)

showstatuslike'Qcache%'看到的是這些信息。

Qcache_free_blocks:當一個表被更新之后，和它相關(guān)的cacheblocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。這些blocks將會被統(tǒng)計到這個值來?？梢杂肍LUSHQUERYCACHE語句來清空freeblocks。

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

Qcache_hits:自mysql進程啟動起，cache的命中數(shù)量

Qcache_inserts:自mysql進程啟動起，被增加進QC的數(shù)量

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

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

Qcache_queries_in_cache:當前被cache的SQL數(shù)量

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

showglobalstatuslike'Com_select'看到的是未中cache的查詢次數(shù)，包括讀寫查詢。

SELECT查詢的總數(shù)量等價于：

Com_select+Qcache_hits+querieswitherrorsfoundbyparser

Com_select的值等價于：

Qcache_inserts+Qcache_not_cached+querieswitherrorsfoundduringcolumns/rightscheck

常用計算公式：

Qcache命中率：Qcache_hits/(Com_select+Qcache_hits)

Qcache碎片率：Qcache_free_blocks/Qcache_total_blocks

Query結(jié)果集平均大小：(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache。

看完上述內(nèi)容，你們對MySQL中怎么實現(xiàn)查詢緩沖有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。