mysql的查詢快取說明_Mysql

對mysql查詢快取從五個角度進行詳細的分析：Query Cache的工作原理、如何配置、如何維護、如何判斷查詢快取的效能、適合的業務情境分析。

工作原理

查詢快取的工作原理，基本上可以概括為：
緩衝SELECT操作或預先處理查詢（注釋：5.1.17開始支援）的結果集和SQL語句；
新的SELECT語句或預先處理查詢語句，先去查詢快取，判斷是否存在可用的記錄集，判斷標準：與緩衝的SQL語句，是否完全一樣，區分大小寫；

查詢快取對什麼樣的查詢語句，無法緩衝其記錄集，大致有以下幾類：
查詢語句中加了SQL_NO_CACHE參數；
查詢語句中含有獲得值的函數，包涵自訂函數，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；
對系統資料庫的查詢：mysql、information_schema
查詢語句中使用SESSION層級變數或預存程序中的局部變數；
查詢語句中使用了LOCK  IN SHARE MODE、FOR UPDATE的語句
查詢語句中類似SELECT …INTO 匯出資料的語句；
交易隔離等級為：Serializable情況下，所有查詢語句都不能緩衝；
對暫存資料表的查詢操作；
存在警告資訊的查詢語句；
不涉及任何錶或視圖的查詢語句；
某使用者只有列層級許可權的查詢語句；

查詢快取的優缺點：

不需要對SQL語句做任何解析和執行，當然文法解析必須通過在先，直接從Query  Cache中獲得查詢結果；
查詢快取的判斷規則，不夠智能，也即提高了查詢快取的使用門檻，降低其效率；
Query Cache的起用，會增加檢查和清理Query Cache中記錄集的開銷，而且存在SQL語句緩衝的表，每一張表都只有一個對應的全域鎖；

配置

是否啟用mysql查詢快取，可以通過2個參數：query_cache_type和query_cache_size，其中任何一個參數設定為0都意味著關閉查詢快取功能，但是正確的設定推薦query_cache_type=0。

query_cache_type
範圍為：0 -– 不啟用查詢快取；
範圍為：1 -– 啟用查詢快取，只要符合查詢快取的要求，用戶端的查詢語句和記錄集鬥可以
緩衝起來，共其他用戶端使用；
範圍為：2 -– 啟用查詢快取，只要查詢語句中添加了參數：sql_cache，且符合查詢快取的要求，用戶端的查詢語句和記錄集，則可以緩衝起來，共其他用戶端使用；

query_cache_size
允許設定query_cache_size的值最小為40K，對於最大值則可以幾乎認為無限制，實際生產環境的應用經驗告訴我們，該值並不是越大， 查詢快取的命中率就越高，也不是對伺服器負載下降貢獻大，反而可能抵消其帶來的好處，甚至增加伺服器的負載，至於該如何設定，下面的章節講述，推薦設定 為：64M；

query_cache_limit
限制查詢快取區最大能緩衝的查詢記錄集，可以避免一個大的查詢記錄集佔去大量的記憶體地區，而且往往小查詢記錄集是最有效緩衝記錄集，預設設定為1M，建議修改為16k~1024k之間的範圍，不過最重要的是根據自己應用的實際情況進行分析、預估來設定；

query_cache_min_res_unit
設定查詢快取分配記憶體的最小單位，要適當地設定此參數，可以做到為減少記憶體塊的申請和分配次數，但是設定過大可能導致記憶體片段數值上升。預設值為4K，建議設定為1k~16K

query_cache_wlock_invalidate
該參數主要涉及MyISAM引擎，若一個用戶端對某表加了寫鎖，其他用戶端發起的查詢請求，且查詢語句有對應的查詢快取記錄，是否允許直接讀取查詢快取的記錄集資訊，還是等待寫鎖的釋放。預設設定為0，也即允許；

維護

查詢緩區的磁碟重組

查詢快取使用一段時間之後，一般都會出現記憶體片段，為此需要監控相關狀態值，並且定期進行記憶體片段的整理，磁碟重組的動作陳述式：FLUSH QUERY CACHE；

清空查詢快取的資料
那些操作操作可能觸發查詢快取，把所有緩衝的資訊清空，以避免觸發或需要的時候，知道如何做，二類可觸發查詢快取資料全部清空的命令：
(1).RESET QUERY CACHE；
(2).FLUSH TABLES；

效能監控

片段率
查詢快取記憶體片段率=Qcache_free_blocks / Qcache_total_blocks * 100%

命中率
查詢快取命中率=(Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

記憶體使用量率
查詢快取記憶體使用量率=(query_cache_size – Qcache_free_memory) / query_cache_size * 100%

Qcache_lowmem_prunes
該參數值對於檢測查詢快取區的記憶體大小設定是否，有非常關鍵性的作用，其代表的意義為：查詢快取去因記憶體不足而不得不從查詢快取區刪除的查詢快取資訊，刪除演算法為LRU；
query_cache_min_res_unit

記憶體塊分配的最小單元非常重要，設定過大可能增加記憶體片段的機率發生，太小又可能增加記憶體配置的消耗，為此在系統平穩運行一個階段性後，可參考公式的計算值：
查詢快取最小記憶體塊 = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
query_cache_size

我們如何判斷query_cache_size是否設定過小，依然也只有先預設定一個值，推薦為：32M~128M之間的地區，待系統平穩運行一個時間段（至少1周），並且觀察這周內的相關狀態值：
(1).Qcache_lowmem_prunes；
(2).命中率；
(3).記憶體使用量率；

若整個平穩運行期監控獲得的資訊，為命中率高於80%，記憶體使用量率超過80%，並且Qcache_lowmem_prunes的值不停地增加，而且增加的數值還較大，則說明我們為查詢緩衝區分配的記憶體過小，可以適當地增加查詢快取區的記憶體大小；

若是整個平穩運行期監控獲得的資訊，為命中率低於40%，Qcache_lowmem_prunes的值也保持一個平穩狀態，則說明我們的查詢緩衝區的內 存設定過大，或者說業務情境重複執行一樣查詢語句的機率低，同時若還監測到一定量的freeing items，那麼必須考慮把查詢快取的記憶體條小，甚至關閉查詢快取功能；

業務情境

通過上述的知識梳理和分析，我們至少知道查詢快取的以下幾點：
查詢快取能夠加速已經存在緩衝的查詢語句的速度，可以不用重新解析和執行而獲得正確得記錄集；
查詢快取中涉及的表，每一個表對象都有一個屬於自己的全域性質的鎖；
表若是做DDL、FLUSH TABLES 等類似操作，觸發相關表的查詢快取資訊清空；
表對象的DML操作，必須優先判斷是否需要清理相關查詢快取的記錄資訊，將不可避免地出現鎖等待事件；
查詢快取的記憶體配置問題，不可避免地產生一些記憶體片段；
查詢快取對是否是一樣的查詢語句，要求非常苛刻，而且還不智能；

我們再重新回到本節的重點上，查詢快取適合什麼樣的業務情境呢？只要是清楚了查詢快取的上述優缺點，就不難羅列出來，業務情境要求：
整個系統以讀為主的業務，比如門戶型、新聞類、報表型、論壇等網站；
查詢語句操作的表對象，非頻繁地進行DML操作，可以使用query_cache_type=2模式，然後SQL語句加SQL_CACHE參數指定；