mysql query cache最佳化

標籤：mysql query cache 最佳化

query cache原理

當mysql接收到一條select類型的query時，mysql會對這條query進行hash計算而得到一個hash值，然後通過該hash值到query cache中去匹配，如果沒有匹配中，則將這個hash值存放在一個hash鏈表中，同時將query的結果集存放進cache中，存放hash值的鏈表的每一個hash節點存放了相應query結果集在cache中的地址，以及該query所涉及到的一些table的相關資訊；如果通過hash值匹配到了一樣的query，則直接將cache中相應的query結果集返回給用戶端。如果mysql任何一個表中的任何一條資料發生了變化，便會通知query cache需要與該table相關的query的cache全部失效，並釋放佔用的記憶體位址。

query cache優缺點

優點很明顯，對於一些頻繁select query，mysql直接從cache中返回相應的結果集，而不用再從表table中取出，減少了IO開銷。

即使query cache的收益很明顯，但是也不能忽略它所帶來的一些缺點：

1. query語句的hash計算和hash尋找帶來的資源消耗。mysql會對每條接收到的select類型的query進行hash計算然後尋找該query的cache是否存在，雖然hash計算和尋找的效率已經足夠高了，一條query所帶來的消耗可以忽略，但一旦涉及到高並發，有成千上萬條query時，hash計算和尋找所帶來的開銷就的重視了；

2. query cache的失效問題。如果表變更比較頻繁，則會造成query cache的失效率非常高。表變更不僅僅指表中的資料發生變化，還包括結構或者索引的任何變化；

3.對於不同sql但同一結果集的query都會被緩衝，這樣便會造成記憶體資源的過渡消耗。sql的字元大小寫、空格或者注釋的不同，緩衝都是認為是不同的sql（因為他們的hash值會不同）；

4. 相關參數設定不合理會造成大量記憶體片段，相關的參數設定會稍後介紹。

合理利用query cache

query cache有利有弊，合理的使用query cache可以使其發揮優勢，並且有效避開其劣勢。

1. 並不是所有表都適合使用query cache。造成query cache失效的原因主要是相應的table發生了變更，那麼就應該避免在變化頻繁的table上使用query cache。mysql中針對query cache有兩個專用的sql hint：SQL_NO_CACHE和SQL_CACHE，分別表示強制不使用和強制使用query cache，通過強制不使用query cache，可以讓mysql在頻繁變化的表上不使用query cache，這樣減少了記憶體開銷，也減少了hash計算和尋找的開銷；

2. 設定合理的參數變數和狀態變數。

首先查看query cache的系統變數。

650) this.width=650;" src="http://my.csdn.net/uploads/201208/12/1344743817_5552.png" />

• have_query_cache 表示是否支援query cache；

• query_cache_limit 表示query cache存放的單條query最大結果集，預設值為1M，結果集大小超過該值的query不會被cache；

• query_cache_min_res_unit 表示query cache 每個結果集存放的最小記憶體大小，預設4k；

• query_cache_size 表示系統中用於query cache的記憶體大小；

• query_cache_type 系統是否開啟了query cache功能；

• query_cache_wlock_invalidate 針對myisam儲存引擎，設定當有write lock在某個table上面的時候，讀請求是要等待write lock釋放資源之後再查詢還是允許直接從query cache中讀取結果，預設是OFF，可以直接從query cache中取得結果。

以上參數經常需要調整的是“query_cache_limit”和“query_cache_min_res_unit”，都需要根據業務做一些調整，比如cache的結果集大都小於4k的話，可以適當的調整"query_cache_min_res_unit"的值，以避免造成記憶體的浪費。如果結果集的大小又都大於1M時，就得調整"query_cache_limit"的值，避免因為結果集大小超過限制而不被mysql cache住。

查看query cache相關的狀態變數

650) this.width=650;" src="http://my.csdn.net/uploads/201208/12/1344760080_2359.png" />

• Qcache_free_blocks 表示query cache中目前還有多殺剩餘的blocks，如果該值顯示較大，則說明query cache中的記憶體片段較多，需要進行整理了；

• Qcache_free_memory 表示query cache目前剩餘的記憶體大小；

• Qcache_hits 表示query cache有多少次命中；

• Qcache_inserts 表示未命中cache後將結果集再寫入到cache中的次數；

• Qcache_lowmem_prunes 表示多少條query因為記憶體不足而被清除出query_cache；

• Qcache_not_cached 表示因為query_cache_type的設定或者不能被cache的query的數量；

• Qcache_queries_in_cache 表示當前cache的query的數量；

• Qcache_total_blocks 當前query cache中的block數量。

通過上述這些狀態變數可以瞭解到query cache的健全狀態，從而可以調整相應的系統參數的值。

query cache的限制

1. 5.1.17之前的版本不能cache綁定變數的query，但是從5.1.17版本開始，query cache已經開始支援綁定變數的query了；

2. 所有子查詢中的外部查詢sql不能被cache，只會cache最終的結果集；

3. 在procedure，function以及trigger中的query不能被cache；

4. 包含其他很多每次執行可能得到不一樣結果的函數的query不能被cache。

鑒於上面的這些限制，在使用Query Cache 的過程中，建議通過精確設定的方式來使用，僅僅讓合適的表的資料可以進入Query Cache，僅僅讓某些Query 的查詢結果被Cache。

本文出自 “fire” 部落格，請務必保留此出處http://fire7758.blog.51cto.com/993821/1587979

mysql query cache最佳化