MySQL Query Cache匯入查詢緩慢解決辦法

進mysql：SHOW FULL PROCESSLIST;

Command多為Sleep，用戶端過來的串連倒也正常，重複執行上述命令發現有些Command的狀態為“Coping to tmp table”，果斷開啟my.cnf發現並無tmp_table_size的設定，MySQL環境是使用yum安裝，這也是為什麼要編譯MySQL的緣故

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

這樣一看這兩個設定的值並不低，看來並不是tmp table導致的問題，不過為了確保沒問題，在my.cnf中增加：

tmpdir=/tmp
tmp_table_size=1073741824

重啟MySQL，問題依舊。看來設定足夠，還是其他的原因。開啟下mysql-log，發現被查詢的那個表正在不停地插入資料，按照這個網站的Pv數量可以判斷有人在頻繁使用檢索功能，這個功能目前並沒有配置sphinx全文索引，而是直接讀取資料庫，並且會即時統計使用者輸入的關鍵詞。不過被人為的頻繁插入新資料中，會導致mysql query cache在使用過程中不停地重建，也就是這個原因導致了mysqld的CPU使用率居高不下。看來這樣即時的排序還是應該交給Memcache或者Redis去執行，並且再次驗證GROUP

mysql的query_cache認識的誤區

下面我們通過實驗及源碼具體分析。首先，我們先實驗一下：

首先，我們看一下mysql query_cache的狀態：

 

首先，我們可以確認，mysql的query_cache功能是開啟的。

其次，我們看一下狀態：

因為這個db是新的db，所以hits，inset都為0，現在我們執行一條select語句：

狀態變為：

可以看到，執行一條select後，現在的qcache狀態為，insert+1，這樣我們就可以推斷出，現在剛才那條select語句已經加入了qcache中。那我們現在再將剛才那條sql前面加上空格，看看會怎樣呢？

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

按照網上的理論，這條sql應該會作為另一個鍵而插入另一個cache，不會複用先前的cache,但結果呢？

我們可以看到，hits變為了1，而inserts根本沒變，這就說明了，這條在前面加了空格的query命中了沒有空格的query的結果集。從這，我們就可以得出結論，網上先前流傳的說法，是不嚴謹的。

Query Cache 如何處理子查詢的？
這是我遇到的最為常見的一個問題。其實 Query Cache 是以用戶端請求提交的 Query 為對象來處理的，只要用戶端請求的是一個 Query，無論這個 Query 是一個簡單的單表查詢還是多表 Join，亦或者是帶有子查詢的複雜 SQL，都被當作成一個 Query，不會被分拆成多個 Query 來進行 Cache。所以，存在子查詢的複雜 Query 也只會產生一個Cache對象，子查詢不會產生單獨的Cache內容。UNION[ALL] 類型的語句也同樣如此。

Query Cache 是以 block 的方式儲存的資料區塊嗎？
不是，Query Cache 中緩衝的內容僅僅只包含該 Query 所需要的結果資料，是結果集。當然，並不僅僅只是結果資料，還包含與該結果相關的其他資訊，比如產生該 Cache 的用戶端串連的字元集，資料的字元集，用戶端串連的 Default Database等。

Query Cache 為什麼效率會非常高，即使所有資料都可以 Cache 進記憶體的情況下，有些時候也不如使用 Query Cache 的效率高？
Query Cache 的尋找，是在 MySQL 接受到用戶端請求後在對 Query 進行許可權驗證之後，SQL 解析之前。也就是說，當 MySQL 接受到用戶端的SQL後，僅僅只需要對其進行相應的許可權驗證後就會通過 Query Cache 來尋找結果，甚至都不需要經過 Optimizer 模組進行執行計畫的分析最佳化，更不許要發生任何儲存引擎的互動，減少了大量的磁碟 IO 和 CPU 運算，所以效率非常高。

用戶端提交的 SQL 陳述式大小寫對 Query Cache 有影響嗎？
有，由於 Query Cache 在記憶體中是以 HASH 結構來進行映射，HASH 演算法基礎就是組成 SQL 陳述式的字元，所以必須要整個 SQL 陳述式在字元層級完全一致，才能在 Query Cache 中命中，即使多一個空格也不行。

一個 SQL 陳述式在 Query Cache 中的內容，在什麼情況下會失效？
為了保證 Query Cache 中的內容與是實際資料絕對一致，當表中的資料有任何變化，包括新增，修改，刪除等，都會使所有引用到該表的 SQL 的 Query Cache 失效。

為什麼我的系統在開啟了 Query Cache 之後整體效能反而下降了？
當開啟了 Query Cache 之後，尤其是當我們的 query_cache_type 參數設定為 1 以後，MySQL 會對每個 SELECT 語句都進行 Query Cache 尋找，尋找操作雖然比較簡單，但仍然也是要消耗一些 CPU 運算資源的。而由於 Query Cache 的失效機制的特性，可能由於表上的資料變化比較頻繁，大量的 Query Cache 頻繁的被失效，所以 Query Cache 的命中率就可能比較低下。所以有些情境下，Query Cache 不僅不能提高效率，反而可能造成負面影響。

如何確認一個系統的 Query Cache 的運行是否健康，命中率如何，設定量是否足夠？
MySQL 提供了一系列的 Global Status 來記錄 Query Cache 的目前狀態，具體如下：

•Qcache_free_blocks：目前還處於空閑狀態的 Query Cache 中記憶體 Block 數目
•Qcache_free_memory：目前還處於空閑狀態的 Query Cache 記憶體總量
•Qcache_hits：Query Cache 叫用次數
•Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，也就是沒有命中的次數
•Qcache_lowmem_prunes：當 Query Cache 記憶體容量不夠，需要從中刪除老的 Query Cache 以給新的 Cache 對象使用的次數
•Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由於 query_cache_type 設定的不會被 Cache 的 SQL
•Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 數量
•Qcache_total_blocks：Query Cache 中總的 Block 數量
可以根據這幾個狀態計算出 Cache 命中率，計算出 Query Cache 大小設定是否足夠，總的來說，我個人不建議將 Query Cache 的大小設定超過256MB，這也是業界比較常用的做法。

MySQL Cluster 是否可以使用 Query Cache？
其實在我們的生產環境中也沒有使用 MySQL Cluster，所以我也沒有在 MySQL Cluster 環境中使用 Query Cache 的實際經驗，只是 MySQL 文檔中說明確實可以在 MySQL Cluster 中使用 Query Cache。從 MySQL Cluster 的原理來分析，也覺得應該可以使用，畢竟 SQL 節點和資料節點比較獨立，各司其職，只是 Cache 的失效機制會要稍微複雜一點。