查詢mysql的緩存優化詳解

本文主要給大家介紹查詢mysql的緩存優化詳解，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

寫在前面：查詢緩存從MySQL 5.7.20開始已被棄用，并在MySQL 8.0中被刪除。
Query cache 作用于整個 MySQL，主要用來緩存 MySQL 中的查詢結果集，也就是一條SQL語句執行的結果集，所以僅僅只能針對select語句。當我們打開了 Query Cache 功能，MySQL在接受到一條select語句的請求后，如果命中緩存，也就是說所需結果集已經在緩存中了，后面一系列步驟都不用再執行，直接從緩存拿到結果集返回給客戶端，可以極大的提高查詢性能！
sql 查詢數據庫 執行步驟如下：

從上圖可以看出，當select 命中緩存后，后面一系列的sql解析，預處理，優化器處理都不用進行！

數據庫緩存打開方式：

query_cache_size參數可以根據自己數據庫查詢運行狀況進行自行調整，調整原則稍后介紹！

query_cache_type可以設置為0，1或者2(DEMOND)，分別表示完全不使用query cache，除顯式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有顯示要求才使用query cache（使用sql_cache）

可以用如下命令show variables like '%have_query_cache%';查看是否開啟，其中have_query_cache為是否開啟，query_cache_limit 指定單個查詢能夠使用的緩沖區大小，缺省為1M；query_cache_min_res_unit為系統分配的最小緩存塊大小，默認是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據 查詢，就容易造成內存碎片和浪費；

通過以下命令查看緩存相關變量 show variables like '%query_cache%';

通過以下命令查看查詢緩存狀態 show 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 數量
檢查換成命中情況
查詢緩存命中率的計算公式是 Qcache_hits/(Qcache_hits+Com_select)。
query_cache_min_res_unit默認是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢緩存碎片率超過20%，可以用FLUSH QUERYCACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數據量的話。

查詢緩存利用率 = (query_cache_size - Qcache_free_memory) /query_cache_size * 100%

查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes >50的話說明query_cache_size可能有點小，要不就是碎片太多。

查詢緩存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

優化提示：
如果Qcache_lowmem_prunes 值比較大，表示查詢緩存區大小設置太小，需要增大。
如果Qcache_free_blocks 較多，表示內存碎片較多，需要清理，flush query cache

高性能mysql中關于query_cache_min_res_unit大小調優給出的計算公式 query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache

mysql在判斷是否命中緩存時，任何字符的不同 ，不確定的函數 都將導致緩存無法命中，子查詢，存儲過程也將導致查詢緩存無法命中；
mysql查詢緩存在帶來查詢性能提升的同時，也會帶來其他的一些問題：首先、打開查詢緩存，對讀對寫都會帶來額外的消耗
1）首先在讀查詢之前，必須先去檢查是否命中緩存
2）如果命中緩存，那么直接返回結果集給用戶，不在執行后續一系列操作，如果沒有命中緩存，那么在查詢完成后，還需要將結果集存入查詢緩存，這本身也是一個消耗
3）對寫操作也有很大影響，因為我們在對表進行時，也需要先將該表的查詢緩存設置為失效，如果這個表的緩存很大，那么這也是一個相當大的消耗。
對查詢緩存的寫和設置失效操作都是一個排他鎖操作
1）對于innodb 來說，事務特性會限制查詢的使用，事務中修改了某個表，那么，需要將這個表的查詢緩存設置為失效，RR，RC 模式下，事物特性會隔離這個修改對其他事務不可見！在這個事務提交前，這個表的其他相關查詢都是無法緩存的，只有在這個表修改事物提交后，這個表的相關查詢才可以緩存，因此，長時間的大的更新事務將導致查詢緩存效率大大下降！
2）如果我們開啟了一個很大的查詢緩存，在緩存失效操作時，可能導致整個數據庫系統都出現性能問題，因為緩存失效操作是一個全局鎖，其他
所有需要做緩存失效操作的查詢都要等待這個鎖，而且無論緩存命中檢測，還是緩存失效檢測都需要等待這個鎖。緩存過大時，這將導致嚴重的性能瓶頸。
因此我們在開啟查詢緩存時，一定是根據業務需要，開啟一個大小比較合理的查詢緩存，在線上避免大的事務！

看了以上關于查詢mysql的緩存優化詳解，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。