我的MySQL Query Cache查詢慢不是一般的慢,而是刷新一個(gè)頁面需要7秒左右了,這產(chǎn)無法接受的,但是我查看cpu使用正常,后來經(jīng)過排查好像是熱鬧關(guān)鍵詞的原因,下面來看我的解決辦法.

進(jìn)mysql:SHOW FULL PROCESSLIST;

Command多為Sleep,客戶端過來的連接倒也正常,重復(fù)執(zhí)行上述命令發(fā)現(xiàn)有些Command的狀態(tài)為“Coping to tmp table”,果斷打開my.cnf發(fā)現(xiàn)并無tmp_table_size的設(shè)置,MySQL環(huán)境是使用yum安裝,這也是為什么要編譯MySQL的緣故.

1. mysql> SHOW VARIABLES LIKE 'tmp_table_size'; 
2. +----------------+----------+ 
3. | Variable_name  | Value    | 
4. +----------------+----------+ 
5. | tmp_table_size | 16777216 | 
6. +----------------+----------+ 
7. 1 row in set (0.00 sec) 
8. --Vevb.com 
9. mysql> SHOW VARIABLES LIKE 'max_heap_table_size'; 
10. +---------------------+----------+ 
11. | Variable_name       | Value    | 
12. +---------------------+----------+ 
13. | max_heap_table_size | 16777216 | 
14. +---------------------+----------+ 
15. 1 row in set (0.00 sec) 

這樣一看這兩個(gè)設(shè)置的值并不低,看來并不是tmp table導(dǎo)致的問題,不過為了確保沒問題,在my.cnf中增加:

tmpdir=/tmp

tmp_table_size=1073741824

重啟MySQL,問題依舊,看來設(shè)置足夠,還是其他的原因,打開下mysql-log,發(fā)現(xiàn)被查詢的那個(gè)表正在不停地插入數(shù)據(jù),按照這個(gè)網(wǎng)站的Pv數(shù)量可以判斷有人在頻繁使用檢索功能,這個(gè)功能目前并沒有配置sphinx全文索引,而是直接讀取數(shù)據(jù)庫,并且會(huì)實(shí)時(shí)統(tǒng)計(jì)用戶輸入的關(guān)鍵詞,不過被人為的頻繁插入新數(shù)據(jù)中,會(huì)導(dǎo)致mysql query cache在使用過程中不停地重建,也就是這個(gè)原因?qū)е铝薽ysqld的CPU使用率居高不下,看來這樣實(shí)時(shí)的排序還是應(yīng)該交給Memcache或者Redis去執(zhí)行,并且再次驗(yàn)證GROUP.

mysql的query_cache認(rèn)識(shí)的誤區(qū)

下面我們通過實(shí)驗(yàn)及源碼具體分析,首先,我們先試驗(yàn)一下.

首先,我們看一下mysql query_cache的狀態(tài),我們可以確認(rèn),mysql的query_cache功能是打開的.

其次,我們看一下狀態(tài),因?yàn)檫@個(gè)db是新的db,所以hits，inset都為0,現(xiàn)在我們執(zhí)行一條select語句.

可以看到，執(zhí)行一條select后，現(xiàn)在的qcache狀態(tài)為，insert+1，這樣我們就可以推斷出，現(xiàn)在剛才那條select語句已經(jīng)加入了qcache中。那我們現(xiàn)在再將剛才那條sql前面加上空格，看看會(huì)怎樣呢？

請注意，這條sql，比剛才那條sql前面多了一個(gè)空格。

按照網(wǎng)上的理論，這條sql應(yīng)該會(huì)作為另一個(gè)鍵而插入另一個(gè)cache，不會(huì)復(fù)用先前的cache,但結(jié)果呢？

我們可以看到，hits變?yōu)榱?，而inserts根本沒變，這就說明了，這條在前面加了空格的query命中了沒有空格的query的結(jié)果集。從這，我們就可以得出結(jié)論，網(wǎng)上先前流傳的說法，是不嚴(yán)謹(jǐn)?shù)摹?/p>

Query Cache 如何處理子查詢的？

這是我遇到的最為常見的一個(gè)問題。其實(shí) Query Cache 是以客戶端請求提交的 Query 為對象來處理的，只要客戶端請求的是一個(gè) Query，無論這個(gè) Query 是一個(gè)簡單的單表查詢還是多表 Join，亦或者是帶有子查詢的復(fù)雜 SQL，都被當(dāng)作成一個(gè) Query，不會(huì)被分拆成多個(gè) Query 來進(jìn)行 Cache。所以，存在子查詢的復(fù)雜 Query 也只會(huì)產(chǎn)生一個(gè)Cache對象，子查詢不會(huì)產(chǎn)生單獨(dú)的Cache內(nèi)容。UNION[ALL] 類型的語句也同樣如此。

Query Cache 是以 block 的方式存儲(chǔ)的數(shù)據(jù)塊嗎？

不是，Query Cache 中緩存的內(nèi)容僅僅只包含該 Query 所需要的結(jié)果數(shù)據(jù)，是結(jié)果集。當(dāng)然，并不僅僅只是結(jié)果數(shù)據(jù)，還包含與該結(jié)果相關(guān)的其他信息，比如產(chǎn)生該 Cache 的客戶端連接的字符集，數(shù)據(jù)的字符集，客戶端連接的 Default Database等。

Query Cache 為什么效率會(huì)非常高，即使所有數(shù)據(jù)都可以 Cache 進(jìn)內(nèi)存的情況下，有些時(shí)候也不如使用 Query Cache 的效率高？

Query Cache 的查找，是在 MySQL 接受到客戶端請求后在對 Query 進(jìn)行權(quán)限驗(yàn)證之后，SQL 解析之前。也就是說，當(dāng) MySQL 接受到客戶端的SQL后，僅僅只需要對其進(jìn)行相應(yīng)的權(quán)限驗(yàn)證后就會(huì)通過 Query Cache 來查找結(jié)果，甚至都不需要經(jīng)過 Optimizer 模塊進(jìn)行執(zhí)行計(jì)劃的分析優(yōu)化，更不許要發(fā)生任何存儲(chǔ)引擎的交互，減少了大量的磁盤 IO 和 CPU 運(yùn)算，所以效率非常高。

客戶端提交的 SQL 語句大小寫對 Query Cache 有影響嗎？

有，由于 Query Cache 在內(nèi)存中是以 HASH 結(jié)構(gòu)來進(jìn)行映射，HASH 算法基礎(chǔ)就是組成 SQL 語句的字符，所以必須要整個(gè) SQL 語句在字符級別完全一致，才能在 Query Cache 中命中，即使多一個(gè)空格也不行。

一個(gè) SQL 語句在 Query Cache 中的內(nèi)容，在什么情況下會(huì)失效？

為了保證 Query Cache 中的內(nèi)容與是實(shí)際數(shù)據(jù)絕對一致，當(dāng)表中的數(shù)據(jù)有任何變化，包括新增，修改，刪除等，都會(huì)使所有引用到該表的 SQL 的 Query Cache 失效。

為什么我的系統(tǒng)在開啟了 Query Cache 之后整體性能反而下降了？

當(dāng)開啟了 Query Cache 之后，尤其是當(dāng)我們的 query_cache_type 參數(shù)設(shè)置為 1 以后，MySQL 會(huì)對每個(gè) SELECT 語句都進(jìn)行 Query Cache 查找，查找操作雖然比較簡單，但仍然也是要消耗一些 CPU 運(yùn)算資源的。而由于 Query Cache 的失效機(jī)制的特性，可能由于表上的數(shù)據(jù)變化比較頻繁，大量的 Query Cache 頻繁的被失效，所以 Query Cache 的命中率就可能比較低下。所以有些場景下，Query Cache 不僅不能提高效率，反而可能造成負(fù)面影響。

如何確認(rèn)一個(gè)系統(tǒng)的 Query Cache 的運(yùn)行是否健康，命中率如何，設(shè)置量是否足夠？

MySQL 提供了一系列的 Global Status 來記錄 Query Cache 的當(dāng)前狀態(tài)，具體如下：

•Qcache_free_blocks：目前還處于空閑狀態(tài)的 Query Cache 中內(nèi)存 Block 數(shù)目

•Qcache_free_memory：目前還處于空閑狀態(tài)的 Query Cache 內(nèi)存總量

•Qcache_hits：Query Cache 命中次數(shù)

•Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數(shù)，也就是沒有命中的次數(shù)

•Qcache_lowmem_prunes：當(dāng) Query Cache 內(nèi)存容量不夠，需要從中刪除老的 Query Cache 以給新的 Cache 對象使用的次數(shù)

•Qcache_not_cached：沒有被 Cache 的 SQL 數(shù)，包括無法被 Cache 的 SQL 以及由于 query_cache_type 設(shè)置的不會(huì)被 Cache 的 SQL

•Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 數(shù)量

•Qcache_total_blocks：Query Cache 中總的 Block 數(shù)量

可以根據(jù)這幾個(gè)狀態(tài)計(jì)算出 Cache 命中率,計(jì)算出 Query Cache 大小設(shè)置是否足夠,總的來說,我個(gè)人不建議將 Query Cache 的大小設(shè)置超過256MB,這也是業(yè)界比較常用的做法.

MySQL Cluster 是否可以使用 Query Cache？

其實(shí)在我們的生產(chǎn)環(huán)境中也沒有使用 MySQL Cluster,所以我也沒有在 MySQL Cluster 環(huán)境中使用 Query Cache 的實(shí)際經(jīng)驗(yàn),只是 MySQL 文檔中說明確實(shí)可以在 MySQL Cluster 中使用 Query Cache,從 MySQL Cluster 的原理來分析,也覺得應(yīng)該可以使用,畢竟 SQL 節(jié)點(diǎn)和數(shù)據(jù)節(jié)點(diǎn)比較獨(dú)立,各司其職,只是 Cache 的失效機(jī)制會(huì)要稍微復(fù)雜一點(diǎn).