直播賣貨系統，使用資料庫查詢快取

更正下部落格。
其實MySQL8.0已經取消了查詢快取。如果是小型專案，又不想用Reids或者MC等快取，查詢結果相對固定，可以在MySQL5.7以下版本設定快取。
##############################################################此處為分割線#########################
與朋友或同事談到mysql查詢快取功能的時候，個人喜歡把Query Cache比作荔枝， 是非常營養的東西，但是一次性吃太多了，就容易導致上火而流鼻血，雖然不是特別恰當的比喻，但是有很多相似的地方，另外Query Cache有其特殊的業務場景，也不像其他資料庫產品，快取查詢語句的執行計劃等資訊，而是直接快取查詢語句的記錄集和對應的SQL語句。本文就給大家介 紹下直播賣貨系統查詢快取的相關知識，希望可以引導大家正確地使用Query Cache這個獨門武器。

對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引數指定；

宣告：本文由雲豹科技轉發自 YornChu部落格，如有侵權請聯絡作者刪除