MYSQL 中query_cache_size小結

標籤：date   注釋   單表   提示   大小寫   unit   映射   開關   user   

1 原理 
   MySQL查詢快取儲存查詢返回的完整結果。當查詢命中該緩衝，會立刻返回結果，跳過瞭解析，最佳化和執行階段。 
查詢快取會跟蹤查詢中涉及的每個表，如果這寫表發生變化，那麼和這個表相關的所有緩衝都將失效。 
但是隨著伺服器功能的強大，查詢快取也可能成為整個伺服器的資源競爭單點。 
   
2 初步設定 
   預設這個開關是關閉的，就是禁止使用query_cache，查詢是否使用語句如下： 
  mysql> SHOW VARIABLES LIKE ‘have_query_cache‘; 
     Variable_name    | Value | 
+------------------+-------+ 
| have_query_cache | YES   | 

  注意這個只是顯示，支援query_cache功能而已，預設是關閉的，通過這個語句 
查詢設定情況： 
   mysql> SHOW STATUS LIKE ‘Qcache%‘; 

   如發現結果中query_cache_size =0，則沒設定，設定的方法為在 
my.ini中，設定 
  query_cache_size=128M 
   增加一行：query_cache_type=1 

3  MYSQL如何分配query_cache_size 
    
mySQL用於查詢的緩衝的記憶體被分成一個個變長資料區塊，用來儲存類型，大小，資料等資訊。 
當伺服器啟動的時候，會初始化緩衝需要的記憶體，是一個完整的空閑塊。當查詢結果需要緩衝的時候，先從空閑塊中申請一個資料區塊大於參數query_cache_min_res_unit的配置，即使快取資料很小，申請資料區塊也是這個，因為查詢開始返回結果的時候就分配空間，此時無法預知結果多大。 
分配記憶體塊需要先鎖住空間塊，所以操作很慢，MySQL會盡量避免這個操作，選擇儘可能小的記憶體塊，如果不夠，繼續申請，如果儲存完時有空餘則釋放多餘的。 
    
4  如何判斷是否命中 
    緩衝存放在一個參考資料表中，通過一個雜湊值引用，這個雜湊值包括查詢本身，資料庫，用戶端協議的版本等，任何字元上的不同，例如空格，注釋都會導致緩衝不命中。 
當查詢中有一些不確定的資料時，是不會緩衝的，比方說now(),current_date(),自訂函數，儲存函數，使用者變數，字查詢等。所以這樣的查詢也就不會命中緩衝，但是還會去檢測緩衝的，因為查詢快取在解析SQL之前，所以MySQL並不知道查詢中是否包含該類函數，只是不緩衝，自然不會命中。 

   具體歸納如下： 

BENCHMARK() 
CONNECTION_ID() 
CURDATE() 
CURRENT_DATE() 
CURRENT_TIME() 
CURRENT_TIMESTAMP() 
CURTIME() 
DATABASE() 
帶一個參數的ENCRYPT() 
FOUND_ROWS() 
GET_LOCK() 
LAST_INSERT_ID() 
LOAD_FILE() 
MASTER_POS_WAIT() 
NOW() 
RAND() 
RELEASE_LOCK() 
SYSDATE() 
不帶參數的UNIX_TIMESTAMP() 
USER() 


·        引用自訂函數(UDFs)。 
·        引用自訂變數。 
·        引用mysql系統資料庫中的表。 
·        下面方式中的任何一種： 
SELECT ...IN SHARE MODE 
SELECT ...FOR UPDATE 
SELECT ...INTO OUTFILE ... 
SELECT ...INTO DUMPFILE ... 
SELECT * FROM ...WHERE autoincrement_col IS NULL 

·        被作為編寫好的語句，即使沒有使用預留位置。例如，下面使用的查詢： 
char *my_sql_stmt = "SELECT a，b FROM table_c"; 
   /* ...*/ 
mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt)); 
不被緩衝。 
·        使用TEMPORARY表。 
·        不使用任何錶。 
·        使用者有某個表的列層級許可權。 




開啟Qcache對讀和寫都會帶來額外的消耗： 
a、讀查詢開始之前必須檢查是否命中緩衝。 
b、如果讀查詢可以緩衝，那麼執行完之後會寫入緩衝。 
c、當向某個表寫入資料的時候，必須將這個表所有的緩衝設定為失效，如果緩衝空間很大，則消耗也會很大，可能使系統僵死一段時間，因為這個操作是靠全域鎖操作來保護的。 
對InnoDB表，當修改一個表時，設定了緩衝失效，但是多版本特性會暫時將這修改對其他事務屏蔽，在這個事務提交之前，所有查詢都無法使用緩衝，直到這個事務被提交，所以長時間的事務，會大大降低查詢快取的命中 
    
   一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。 
對於InnoDB而言，事物的一些特性還會限制查詢快取的使用。當在事物A中修改了B表時，因為在事物提交之前，對B表的修改對其他的事物而言是不可見的。為了保證緩衝結果的正確性，InnoDB採取的措施讓所有涉及到該B表的查詢在事物A提交之前是不可快取的。如果A事物長時間運行，會嚴重影響查詢快取的命中率 

查詢快取的空間不要設定的太大。 
因為查詢快取是靠一個全域鎖操作保護的，如果查詢快取配置的記憶體比較大且裡面存放了大量的查詢結果，當查詢快取失效的時候，會長時間的持有這個全域鎖。因為查詢快取的命中檢測操作以及緩衝失效檢測也都依賴這個全域鎖，所以可能會導致系統僵死的情況 


5 具體參數含義： 
   show status like ‘%Qcache%’; 
   Variable_name           | Value     | 

+————————-+———–+ 

| Qcache_free_blocks      | 1         | 

| Qcache_free_memory      | 134208800 | 

| Qcache_hits             | 0         | 

| Qcache_inserts          | 0         | 

| Qcache_lowmem_prunes    | 0         | 

| Qcache_not_cached       | 2         | 

| Qcache_queries_in_cache | 0         | 

| Qcache_total_blocks     | 1         | 


  解析： 
Qcache_free_blocks:表示查詢快取中目前還有多少剩餘的blocks，如果該值顯示較大，則說明查詢快取中的記憶體片段過多了，可能在一定的時間進行整理。 
   

    減少片段： 
合適的query_cache_min_res_unit可以減少片段，這個參數最合適的大小和應用程式查詢結果的平均大小直接相關，可以通過記憶體實際消耗（query_cache_size - Qcache_free_memory）除以Qcache_queries_in_cache計算平均緩衝大小。 
可以通過Qcache_free_blocks來觀察片段，這個值反應了剩餘的空閑塊，如果這個值很多，但是 
Qcache_lowmem_prunes卻不斷增加，則說明片段太多了。可以使用flush query cache整理片段，重新排序，但不會清空，清空命令是reset query cache。整理片段期間，查詢快取無法被訪問，可能導致伺服器僵死一段時間，所以查詢快取不宜太大。 



Qcache_free_memory:查詢快取的記憶體大小，通過這個參數可以很清晰的知道當前系統的查詢記憶體是否夠用，是多了，還是不夠用，DBA可以根據實際情況做出調整。 

Qcache_hits:表示有多少次命中緩衝。我們主要可以通過該值來驗證我們的查詢快取的效果。數字越大，緩衝效果越理想。 

Qcache_inserts: 表示多少次未命中然後插入，意思是新來的SQL請求在緩衝中未找到，不得不執行查詢處理，執行查詢處理後把結果insert到查詢快取中。這樣的情況的次 數，次數越多，表示查詢快取應用到的比較少，效果也就不理想。當然系統剛啟動後，查詢快取是空的， 

這很正常。 

Qcache_lowmem_prunes:該參數記錄有多少條查詢因為記憶體不足而被移除出查詢快取。通過這個值，使用者可以適當的調整緩衝大小。 


Qcache_not_cached: 表示因為query_cache_type的設定而沒有被緩衝的查詢數量。 

Qcache_queries_in_cache:當前緩衝中緩衝的查詢數量。 

Qcache_total_blocks:當前緩衝的block數量。 
   

  提高查詢快取的使用率： 
如果片段不是問題，命中率卻非常低，可能是記憶體不足，可以通過 Qcache_free_memory 參數來查看沒有使用的記憶體。 
如果2者都沒有問題，命中率依然很低，那麼說明緩衝不適合你的當前系統。可以通過設定 
query_cache_size = 0或者query_cache_type 來關閉查詢快取。 

   ------------------------------------------------------------- 
下面講解 show variables like ‘%query_cache%‘時的參數 

query_cache_limit：允許 Cache 的單條 Query 結果集的最大容量，預設是1MB，超過此參數設定的 Query 結果集將不會被 Cache 
query_cache_min_res_unit：設定 Query Cache 中每次分配記憶體的最小空間大小，也就是每個 Query 的 Cache 最小佔用的記憶體空間大小 
query_cache_size：設定 Query Cache 所使用的記憶體大小，預設值為0，大小必須是1024的整數倍，如果不是整數倍，MySQL 會自動調整降低最小量以達到1024的倍數 
query_cache_type：控制 Query Cache 功能的開關，可以設定為0(OFF),1(ON)和2(DEMAND)三種，意義分別如下： 
0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache 
1(ON)：開啟 Query Cache 功能，但是當 SELECT 語句中使用的 SQL_NO_CACHE 提示後，將不使用Query Cache 
2(DEMAND)：開啟 Query Cache 功能，但是只有當 SELECT 語句中使用了 SQL_CACHE 提示後，才使用 Query Cache 
query_cache_wlock_invalidate：控制當有寫鎖定發生在表上的時刻是否先失效該表相關的 Query Cache，如果設定為 1(TRUE)，則在寫鎖定的同時將失效該表相關的所有 Query Cache，如果設定為0(FALSE)則在鎖定時刻仍然允許讀取該表相關的 Query Cache。 


  相關問答： 
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 的失效機制會要稍微複雜一點。

MYSQL 中query_cache_size小結