mysql查詢快取命中率

標籤：for   dem   copy   通過   包括   工作原理   標準   value   rdate   

 工作原理：

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

查詢快取對什麼樣的查詢語句，無法緩衝其記錄集，大致有以下幾類：

1、 查詢語句中加了SQL_NO_CACHE參數；

2、查詢語句中含有獲得值的函數，包涵自訂函數，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；

3、 對系統資料庫的查詢：mysql、information_schema 查詢語句中使用SESSION層級變數或預存程序中的局部變數；

4、查詢語句中使用了LOCK  IN SHARE MODE、FOR UPDATE的語句 查詢語句中類似SELECT …INTO 匯出資料的語句； 

5、對暫存資料表的查詢操作； 存在警告資訊的查詢語句； 不涉及任何錶或視圖的查詢語句； 某使用者只有列層級許可權的查詢語句；

6、 交易隔離等級為：Serializable情況下，所有查詢語句都不能緩衝；

 

配置

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

query_cache_type 範圍為：

0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache;

1(ON)： 啟用查詢快取，只要符合查詢快取的要求，用戶端的查詢語句和記錄集鬥可以 緩衝起來，共其他用戶端使用；

2(DEMAND):  啟用查詢快取，只要查詢語句中添加了參數：sql_cache，且符合查詢快取的要求，用戶端的查詢語句和記錄集，則可以緩衝起來，共其他用戶端使用；

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

 

緩衝選項的說明：

用show global status like ‘QCache%‘;查看

mysql> show global status like ‘QCache%‘;+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Qcache_free_blocks      | 1       || Qcache_free_memory      | 1031368 || Qcache_hits             | 0       || Qcache_inserts          | 0       || Qcache_lowmem_prunes    | 0       || Qcache_not_cached       | 83      || Qcache_queries_in_cache | 0       || Qcache_total_blocks     | 1       |+-------------------------+---------+8 rows in set (0.02 sec)

 

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

 

記憶體片段的產生。當一塊分配的記憶體沒有完全使用時，MySQL會把這塊記憶體Trim掉，把沒有使用的那部分歸還以重 複利用。比如，第一次分配4KB,只用了3KB，剩1KB，第二次連續操作，分配4KB，用了2KB，剩2KB，這兩次連續操作共剩下的 1KB+2KB=3KB，不足以做個一個記憶體單元分配， 這時候，記憶體片段便產生了。使用flush query cache，可以消除片段

 

下面是命中率和記憶體使用量率的一些演算法

      query_cache_min_res_unit的估計值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

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

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

 

InnoDB儲存引擎的緩衝池

     通常InnoDB儲存引擎緩衝池的命中不應該小於99%，如：

mysql> show global status like ‘innodb%read%‘\G           *************************** 1. row ***************************Variable_name: Innodb_buffer_pool_read_ahead_rnd        Value: 0*************************** 2. row ***************************Variable_name: Innodb_buffer_pool_read_ahead        Value: 0*************************** 3. row ***************************Variable_name: Innodb_buffer_pool_read_ahead_evicted        Value: 0*************************** 4. row ***************************Variable_name: Innodb_buffer_pool_read_requests        Value: 81337*************************** 5. row ***************************Variable_name: Innodb_buffer_pool_reads        Value: 432*************************** 6. row ***************************Variable_name: Innodb_data_pending_reads        Value: 0*************************** 7. row ***************************Variable_name: Innodb_data_read        Value: 9261056*************************** 8. row ***************************Variable_name: Innodb_data_reads        Value: 450*************************** 9. row ***************************Variable_name: Innodb_pages_read        Value: 431*************************** 10. row ***************************Variable_name: Innodb_rows_read        Value: 29010 rows in set (0.15 sec)

參數說明：
Innodb_buffer_pool_reads: 表示從物理磁碟讀取頁的次數

Innodb_buffer_pool_read_ahead: 預讀的次數

Innodb_buffer_pool_read_ahead_evicted: 預讀的頁，但是沒有讀取就從緩衝池中被替換的頁的數量，一般用來判斷預讀的效率

Innodb_buffer_pool_read_requests: 從緩衝池中讀取頁的次數

Innodb_data_read: 總共讀入的位元組數

Innodb_data_reads: 發起讀取請求的次數，每次讀取可能需要讀取多個頁

 

Innodb緩衝池命中率計算：

從上面資訊可以求得：

mysql> select 81337/(81337+0+432);+---------------------+| 81337/(81337+0+432) |+---------------------+|              0.9947 |+---------------------+1 row in set (0.00 sec)

mysql查詢快取命中率