資料庫MySQL/mariadb知識點——查詢快取

標籤：出現   下載   直接   執行計畫   查詢語句   cache   unit   off   個數   

 

 查詢的執行路徑

 

1. SQL語句
2. 查詢快取
3. 解析器
4. 解析樹
5. 預先處理
6. 尋找最好的查詢路徑
7. 查詢最佳化SQL語句
8. 執行計畫
9. API調用儲存引擎
10. 調用資料，返回結果

緩衝SELECT操作或預先處理查詢的結果集和SQL語句，當有新的SELECT語句或預先處理查詢語句請求，先去查詢快取，判斷是否存在可用的記錄集，判斷標準：與緩衝的SQL語句，是否完全一樣，區分大小寫。

不需要對SQL語句做任何解析和執行，當然文法解析必須通過在先，直接從Query Cache中獲得查詢結果，提高查詢效能

查詢快取的判斷規則，不夠智能，也即提高了查詢快取的使用門檻，降低其效率；查詢快取的使用，會增加檢查和清理Query Cache中記錄集的開銷

查詢快取

查詢快取，及將查詢結果的緩衝下載；如果查詢語句完全相同，則直接返回緩衝中的結果；

我們可以使用如下語句，查看當前服務是否開啟了查詢快取功能：

MariaDB [(none)]> SHOW VARIABLES LIKE ‘%query_cache%‘;+------------------------------+----------+| Variable_name                | Value    |+------------------------------+----------+| have_query_cache             | YES      || query_cache_limit            | 1048576  || query_cache_min_res_unit     | 4096     || query_cache_size             | 33554432 || query_cache_strip_comments   | OFF      || query_cache_type             | ON       || query_cache_wlock_invalidate | OFF      |+------------------------------+----------+7 rows in set (0.10 sec)

可以看到，query_cache_type的值為ON，也就是目前已經開啟了查詢快取功能；

query_cache_type的值可以設定為：ON、OFF、DEMAND，分別為已啟動、已禁用和按需緩衝，在檔案my.cnf中設定即可；

vim /etc/my.cnf[mysqld]中添加：query_cache_type = ON　

have_query_cache表示當前資料庫是否支援緩衝功能；值為YES

query_cache_limit表示單條查詢快取的最大值，如果查詢結果超過此大小，即使指定緩衝當前結果，結果也不會被緩衝；預設為1M

query_cache_min_res_unit表示緩衝儲存於記憶體的最小單元，預設為4K，如果查詢結果小於4K，也會佔用4k記憶體，所以設定過大會存記憶體空間的浪費，設定過小，則會頻繁分配記憶體待遇按或頻發的回收

query_cache_size表示查詢快取的總大小

query_cache_wlock_invalidate表示所查詢的表如果被寫鎖鎖定，是否使用緩衝返回結構

只有查詢語句完全相同時，緩衝才能夠被命中；查詢SQL語句相同，大小寫不同即算不同；

MariaDB [(none)]> select * from stu;MariaDB [(none)]> SELECT * FROM stu;

　　MySQL在收到查詢請求時，會對查詢語句進行hash計算，計算出對應的hash，通過這個hash值尋找是否存在對應的緩衝，所以，即使查詢語句的大小寫不同，也會被認為是不同的查詢語句；當前hash碼沒有命中對應的緩衝，mysql則會將對應的hash值放在對應的hash表中，同時將查詢結果存放在對應的緩衝中，如果查詢語句的hash值命中了對應快取項目，則之間從緩衝中返迴響應結果，如果緩衝對應的表中的資料發生了變化，那麼查詢快取中，所有與變化的資料表有關的緩衝都將失效；

　　那麼，我們該如何靈活使用緩衝呢；可用如下方法；

　　在開啟緩衝時（query_cache_type=ON），指定對應的查詢語句不使用緩衝

MariaDB [(none)]> select sql_no_cache name from stu;

　　也可在按需使用緩衝時(query_cache_type=DEMAND),，之地==指定對應的查詢語句使用緩衝

MariaDB [(none)]> select sql_cache name from stu;

查看緩衝使用方式

MariaDB [(none)]> SHOW status LIKE ‘qcache%‘;+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        || Qcache_free_memory      | 33536824 || Qcache_hits             | 0        || Qcache_inserts          | 0        || Qcache_lowmem_prunes    | 0        || Qcache_not_cached       | 27       || Qcache_queries_in_cache | 0        || Qcache_total_blocks     | 1        |+-------------------------+----------+8 rows in set (0.33 sec)

其中各個參數的意義如下：  

Qcache_free_blocks：緩衝中相鄰記憶體塊的個數。數目大說明可能有片段。FLUSH QUERY CACHE會對緩衝中的片段進行整理，從而得到一個空閑塊。  

Qcache_free_memory：緩衝中的空閑記憶體。  

Qcache_hits：每次查詢在緩衝中命中時就增大  

Qcache_inserts：每次插入一個查詢時就增大。叫用次數除以插入次數就是不中比率。  

Qcache_lowmem_prunes：緩衝出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看;如果這個 數字在不斷增長，就表示可能片段非常嚴重，或者記憶體很少。(上面的 free_blocks和free_memory可以告訴您屬於哪種情況)  

Qcache_not_cached：不適合進行緩衝的查詢的數量，通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函數。  

Qcache_queries_in_cache：當前緩衝的查詢(和響應)的數量。  

Qcache_total_blocks：緩衝中塊的數量。 

 

通過上述統計資訊的數值，以及緩衝設定相關值，計算出查詢快取的相關指標，從而判斷查詢快取是否對我們有一定協助；一切以實際生產為準

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

查詢快取利用率 = ( query_cache_size - Qcache_free_memory ) / query_cache_size * 100%

由上述兩個公式可以推斷出；如果Qcache_free_memory緩衝中塊的數量越大，則片段率越高，可以嘗試適當調小query_cache_min_res_unit值，或及時清理緩衝片段。

清理緩衝片段：

MariaDB [(none)]> FLUSH QUERY CACHE

如果想要清除查詢快取中已經存在的緩衝，可用使用

從查詢快取中移除所有查詢結果的緩衝

MariaDB [(none)]> RESET QUERY CACHE;

 

如果查詢快取利用率太低，則表示query_cache_size設定過大，可適當調小，如果緩衝利用率非常

高，同時Qcache_lowmem_prunes的值較大，則表示query_cache_size設定略小。

如果在調整query_cache_min_res_unit值時不確定調整為多大；可以參考如下公式

 ( query_cache_size - Qcache_free_memory ) / Qcache_queries_in_cache

查詢快取命中率 =  ( Qcache_hits / Com_select )  * 100%

其中Com_select表示查詢語句的執行次數（此描述並不準確；應是Qcache_hits + Qcache_inserts），可用如下語句獲得查詢語句執行次數

show status like "Com_select%";

最佳化查詢快取

 

資料庫MySQL/mariadb知識點——查詢快取