mysql資料庫效能最佳化 - 查詢快取

標籤：

 

查詢快取

      緩衝機制簡單的說就是緩衝sql文本和查詢結果，如果運行相同的sql，伺服器直接從緩衝中取到結果，而不需要去解析和執行sql，如果表更改了，那麼使用這個表的所有緩衝查詢將不再有效，查詢快取值的相關條目被清空。更改指的是表中任何資料或是結構的改變，包括insert、update、delete、truncate、alter table、drop table或drop database等。這對頻繁更新的表，查詢快取是不適合的，而對一些不常改變資料且大量相同的sql查詢的表，查詢快取會節約很大的效能。

     查詢必須是完全相同的才能夠被認為是相同的。另外，同樣的查詢字串由於其他原因可能認為是不同的，使用不同的資料庫、不同的協議版本或者不同的預設字元集的查詢被認為是不同的查詢並且分別進行緩衝。

 1、query cache開啟方法

編輯mysql的my.cnf，添加如下內容，然後重啟mysql：

• query_cache_size = 268435456   --設定query cache所使用的記憶體大小，預設為0，大小必須是1024的整數部
• query_cache_type = 1   --給所有的查詢做cache
• query_cache_limit = 1048576  --允許cache的單條query結果集的最大容量，預設是1MB，超過此參數設定的query結果集將不會被cache

 2、query cache運行狀態分析

      show status like ‘Qcache%‘; 

• Qcache_free_blocks：數目大說明可能有片段
• Qcache_free_memory：緩衝中的空閑記憶體
• Qcache_hits：每次查詢在緩衝中命中時就增大
• Qcache_insert：每次插入一個查詢時就增大
• Qcache_lowmem_prunes：緩衝出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看，如果不斷增長，標示可能片段非常嚴重，或者記憶體很少。（結合Qcache_free_blocks和Qcache_free_memory分析屬於哪種情況）
• Qcache_total_blocks：緩衝中塊的總數量
• Qcache_not_cached：不適合進行緩衝的查詢的數量，通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函數。

 3、query cache設定狀態分析

       show  variables like ‘%query_cache%‘;

• query_cache_limit：允許cache的單條query結果集的最大容量，預設是1MB，超過此參數設定的query結果集將不會被cache
• query_cache_size：設定query cache所使用的記憶體大小，預設為0，大小必須是1024的整數倍
• query_cache_type：控制query cache功能的開關，可設定為：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_min_res_unit：緩衝塊的最小大小。是一柄”雙刃劍”，預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體片段和浪費。

4、幾個率

query cache命中率=Qcache_hits / （Qcache_hits+Qcache_inserts）

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

--如果查詢快取片段率超過20%，可以使用flush query cache; 整理緩衝片段

查詢快取利用率=（query_cache_size - query_free_memory）/ query_cache_size * 100%  

--查詢快取利用率在25%以下的話說明query_cache_size設定過大，可適當減小；查詢快取利用率在80%以上而且query_lowmem_prunes > 50的話，說明query_cache_size可能有點小，要不就是片段太多。

 

mysql資料庫效能最佳化 - 查詢快取