mysql query cache

1.概述：

   MySQL Query Cache 緩存用戶端送出給MySQL的SELECT（注意隻是select）語句以及該語句的結果集。

注意：query_cache是mysql server端的查詢緩存，在存儲引擎之上。存儲引擎層還有存儲引擎的緩存，表也有表的緩存，日志也有日志的緩存，還可以用nosql實作二級三級甚至更多層的緩存.....緩存是提高性能的上方寶劍，因為記憶體的速度比磁盤的速度要快的多的多，甯願在記憶體中執行1000次也不在磁盤上執行一次，緩存可以跳過解析和優化的操作進而大幅度提高查詢性能。

更具體的可以看源碼sql/sql_cache.cc 。

2.mysql的Query Cache原理：

   用戶端的select語句通過一定的hash算法進行計算，存放在hash桶中，并對結果集存放在記憶體中，存放query hash值的連結清單中存放了hash值和結果集的記憶體位址和query涉及的所有table的辨別等資訊。前端的sql過來會先進行hash計算，如果能夠在cache中找到，就直接從記憶體中取出結果傳回給前端，如果沒有則mysql解析器會對sql進行解析并且優化。注意查詢cache是在sql解析器前執行的，所有速度非常快，因為又省去了一個操作。

3.失效機制：

    當後端任何一個表的一條資料，索引，結構發生變化時，就會将與此表關聯的query chache失效，并且釋放記憶體。是以對于資料變化頻繁的sql就不要cache了。那樣不但不會提高性能還能得到相反的結果，因為每次多了查詢緩存的操作。

    這裡要指出的是，這種失效機制并不科學，因為有些表的改動并不會導緻結果集的改變。但是這種方法簡單，開銷也比較小。

4.相關設定參數：

SHOW VARIABLES LIKE '%query_cache%'；

query_cache_limit：允許 Cache 的單條 Query 結果集的最大容量，預設是1MB，超過此參數設定的 Query 結果集将不會被 Cache

query_cache_min_res_unit：設定 Query Cache 中每次配置設定記憶體的最小空間大小，也就是每個 Query 的 Cache 最小占用的記憶體空間大小，預設4KB，要設定合理，不然會造成碎片過多，造成記憶體的浪費。

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。預設false

5.Query Cache 處理子查詢：

   Query Cache 是以用戶端請求送出的Query 為對象來處理的，隻要用戶端請求的是一個Query，無論這個 Query 是一個簡單的單表查詢還是多表 Join，亦或者是帶有子查詢的複雜 SQL，都被當作成一個Query，不會被分拆成多個Query 來進行Cache。是以，存在子查詢的複雜Query 也隻會産生一個Cache對象，子查詢不會産生單獨的Cache内容。UNION[ALL] 類型的語句也同樣如此。

6.Query Cache 導緻性能反而下降的原因：

   1.開啟Query Cache并且query_cache_type 參數設定為1，或者是2但是緩存了太多的不必要sql，導緻MySQL 對每個SELECT 語句都進行Query Cache 查找，這樣就比直接查找多一次查找緩存的操作；

   2.并且由于Query Cache 的失效機制的特性，比如表上的資料變化比較頻繁，大量的 Query Cache 頻繁的被失效，是以 Query Cache 的命中率就可能非常低；

   3.query_cache_min_res_unit設定不合理導緻記憶體碎片太多；

   4.query cache 緩存的是結果集而不是資料頁，是以由于sql寫的不合理導緻同一結果集的sql 被緩存多次，浪費記憶體。字元大小寫、空格或者注釋的不同，緩存都是認為是不同的sql（因為他們的hash值會不同）。

   5.對于Innodb，事務會讓緩存失效，當事務内的語句更改了表，即使Innodb的多版本機制隐藏了事務的變化，伺服器也會使所有（不管事務内還是外）引用了該表的查詢緩存的哦偶失效，直到事務送出，是以經常使用事務或使  緩存的命中率下降。

是以有些場景下，Query Cache 不僅不能提高效率，反而可能造成負面影響。

從緩存中受益最大的查詢是需要很多資源産生得到的結果，并且變化不是很頻繁的。

7.Query cache帶來的額外開銷：

1.sql優化器在分析之前必須檢查緩存

   2.如果查詢是可以緩存，但是不在緩存中，那麼産生結果後進行儲存會帶來額外開銷

   3.寫入資料的查詢也會帶來而外開銷，因為他必須去檢查緩存中是否有相關sql，如果有得讓它失效。

8.确認系統的Query Cache 的使用情況，命中率：

show status like 'Qcache%' ;

   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的次數。

   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 數量

   Query Cache 命中率= Qcache_hits/（Qcache_hits+Qcache_inserts） ； Query Cache 的大小設定一般不要超過256MB。

   如果從查詢緩存中傳回一個查詢結果，伺服器把Qcache_hits狀态變量的值加一，而不是Com_select變量。

9.未命中緩存的情況：

   1.查詢不可緩存，比如包含不确定函數，比如current_date等。

   2.結果太大，超過了query_cache_limit的大小。

   3.由于記憶體空間不夠，被移除了。

10.MySQL Cluster 和 Query Cache：

  MYSQL 文檔中說明确實可以在 MySQL Cluster 中使用 Query Cache。這塊還需要繼續研究。

11.記憶體與碎片：

1.首先緩存自身大小為40K。mysql伺服器自己管理自己的記憶體，不依賴于作業系統。

   2.伺服器每次配置設定一個塊至少是query_cache_min_res_unit的大小，但它不能精确的配置設定，伺服器不是在獲得所有結果才傳回給客戶的，而是産生一行就發送一行，因為這樣的效率高，但結果是緩存的結果不精确。  

   3.flush query cache 移除緩存碎片，它會把所有的存儲塊向上移動，把空閑塊移動到底部，但它運作的時候，會阻塞通路查詢緩存，鎖定整個伺服器。該語句不從緩存中移出任何查詢。

12.Query cache的限制：

1.5.1.17之前的版本不能緩存cache綁定變量的query，從5.1.17開始支援。

   2.Procedure、function、Trigger、臨時表 、使用者有某個表的列級權限，的query不能被緩存。

   3.包含很多每次執行結果都不一樣的系統函數不能被緩存，比如：current_date()。如果你想讓他緩存，比如緩存今天的可以把current_date()的實際值賦予它。

   4.mysql5.1之前的準備語句也不能被緩存（prepared statement）。

   5.mysql, <code>INFORMATION_SCHEMA相關表的查詢也不會被緩存。</code>

12.其他相關：

SELECT查詢的總數量等價于：

Com_select+ Qcache_hits+ queries with errors found by parser

Com_select的值等價于：

Qcache_inserts+ Qcache_not_cached+ queries with errors found during columns/rights check

本文轉自 小強測試幫 51CTO部落格，原文連結：http://blog.51cto.com/xqtesting/1376090，如需轉載請自行聯系原作者