mysql緩存命中率_MySQL緩存命中率概述及如何提高緩存命中率

MySQL緩存命中率概述

工作原理：

查詢緩存的工作原理，基本上可以概括為： 緩存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%';查看

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%。

參數說明：

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數據庫也不例外。在這里，筆者將結合自己的工作經驗，跟大家探討一下，MySQL數據庫中緩存的管理技巧：如何合理配置MySQL數據庫緩存，提高緩存命中率。

什么時候應用系統會從緩存中獲取數據?

數據庫從服務器上讀取數據時，可以從硬盤的數據文件中獲取數據，也可以從數據庫緩存中讀取數據。現在數據庫管理員需要搞清楚的是，在什么樣的情況下，系統是從緩存中讀取數據，而不是從硬盤的數據文件中讀取數據?

簡單的說，數據緩存就是內存中的一塊存儲區域，其存儲了用戶的SQL文本以及相關的查詢結果。通常情況下，用戶下次查詢時，如果所使用的SQL文本是相同的，並且自從上次查詢后，相關的紀錄沒有被更新過，此時數據庫就直接采用緩存中的內容。從這個原則中，可以看到如果要直接使用緩存中的數據，至少要滿足以下幾個條件。

一是所采用的SQL文本是相同的。當前后兩次用戶使用了相同的SQL語句(假設不考慮其他條件)，則服務器會從緩存中讀取結果，而不需要再去解析和執行SQL語句。這里需要注意的是，這里的SQL文本必須一次不差的完全相同。如果前后兩次查詢，使用了不同的查詢條件。如第一次查詢時沒有輸入Where條件語句。后來發現數據量過多，利用了Where條件了過濾查詢的結果。此時即使最后的查詢結果是相同的，系統仍然是從數據文件中獲取數據，而不是從數據緩存中。再如，Select后面所使用的字段名稱也必須是相同的。如果有一個字段名稱不同或者前后兩次查詢所使用的字段數量不同，則系統都會認為是不同的SQL語句，而重新解析並查詢。

二是從數據緩存的角度考慮，大小寫是不敏感的。如前后兩次查詢時，采用的字段名稱可能只有大小寫的差異。如第一次使用的是大小，第二次使用的是小寫，這系統認為仍然是相同的SQL語句。或者說關鍵字大小寫等等這都是不敏感的。

三是要滿足二次查詢之間，數據記錄包括表結構都沒有被更改過。如果記錄所在的標更改了，如增加了一個字段等等，此時使用這個表的所有緩沖數據系統將自動清空。這里需要注意，這里指的更改是一個廣義的更改，包括表中任何數據或者結果的改變。舉一個簡單的例子，第一次查詢時用戶需要查詢2010年的出貨數據。查詢后有用戶在這個表中插入了一條2011年1月份的出貨信息。然后又有用戶需要查詢2010年的出貨信息。使用的SQL語句與第一次查詢時完全相同。在這種情況下，數據庫系統會使用緩存中的數據嗎?答案是否定的。因為當中間用戶插入一條記錄時，系統會自動清空跟這個表相關的所有緩存記錄。當第二次查詢時，緩存中已經沒有這張表對應的緩存信息。此時就需要重新解析並查詢。

四是需要注意，默認字符集對緩存命中率的影響。通常情況下，如果客戶端與服務器之間所采用的默認字符集不同，則即使查詢語句相同、在兩次查詢之間記錄與表結構也沒有被更改，系統仍然認為是不同的查詢。對於這一點需要特別的注意，大家比較容易忽視。

提高緩存命中率的建議

從上面的條件分析中可以看出，利用緩存中的數據具有比較嚴格的條件。其實這些條件也是合情合理的。主要是為了保障數據的一致性。對以上這些條件有深入的認識之后，現在數據庫管理員需要考慮的是，如何來提高這個緩存的命中率?對此筆者有如下幾個建議。

一是在配置時，客戶端與服務器端要使用相同的字符集。如果客戶端(或者說第三方工具)與服務器端使用的字符集不同，那么任何情況下都不會使用緩存功能。特別在國內，需要用到中文的字符集。此時特別需要注意，客戶端默認字符集要與服務器端的默認字符集相同。注意，這里是相同，而不是兼容。有時候即使采用了不同的字符集，客戶端上仍然可以正常顯示。這主要是因為有些字符集雖然不相同，但是是相互兼容的。在緩存管理上，需要相同，光兼容還不行。

二是在客戶端上，要固化查詢的語句。如現在有財務人員和采購人員同時從系統中查詢11月份的出貨數據。顯然他們崗位職責不同，所需要字段的內容是不同的。此時在客戶端出，可以允許用戶設置自己所需要的表單格式。但是筆者建議，后台所采用的SQL語句最好是相同的。這里數據會經過三個渠道：后台數據庫、客戶端、用戶。筆者的意識時，后台數據庫與客戶端之間的交互采用相同的SQL語句。然后客戶端與用戶之間進行交互時，根據用戶定義的格式(包括字段前后的排列、不包括查詢條件語句的差異)向用戶顯示數據。此時由於采用了相同的SQL語句(只是用戶對於顯示格式的要求不同)，從而可以提高應用系統的查詢效率。

三是提高內存中緩存的配置，來提高命中率。一般在服務器啟動時，操作系統會跟數據庫軟件協商緩存空間的大小。當緩存工作不足時，緩存中最舊的緩存記錄會被最新的消息所覆蓋。可見，如果能夠提高緩存空間，就可以提高命中率。這就好像打靶，目標多了，命中的幾率也會高許多。不過用戶的並發數越多，這個設置的效果會越不明顯。

四是通過分區表可以提高緩存的命中率。在上面的條件分析中，大家可以看到，只要所查詢的表中插入了一條記錄，系統就會清空緩存記錄。現在以查詢出貨記錄為例。出貨記錄表每天都在更新，而用戶在年初時，會經常需要查詢上一年的出貨記錄。此時由於這個表中的數據每個小時都在更新，那么緩存中的信息會不斷的被情況。此時緩存的命中率顯然不會很高。針對這種情況，筆者建議可以采用分區表。如可以通過系統設置，將2010年的出貨記錄單獨存放在一個出貨的分區表中。即每一個年度都使用一張單獨的分區表。此時2011年的紀錄，就不會影響到2010年的分區表。此時如果用戶重復查詢2010年的出貨信息，只要其使用的SQL語句相同(沒有采用不同的查詢條件)，那么就可以享受緩存機制所帶來的效益，提高應用系統的查詢效果。。

多個應用對緩存的影響

通常情況下，MySQL數據庫的緩存是根據服務器內存的大小自動分配的。如果一台服務器上只有一個MySQL應用，那么固然最好。不過在實際工作中，為了降低信息化投資的成本，往往會在同一台服務器上布置多個信息化應用。由於其他信息化應用也需要使用內存的空間作為緩存，那么MySQL數據庫中緩存空間就可能變小。如果遇到這種情況下，數據庫管理員需要跟系統工程師進行協商，為各種不同的應用根據性能要求的不同，手工設置不同的緩存空間。如此的話，就可以避免同一台服務器上不同信息化應用對緩存的沖突。