返回 "ProxySQL系列文章：http://www.cnblogs.com/f ck need u/p/7586194.html" ProxySQL支持查詢緩存的功能，可以將後端返回的結果集緩存在自己的記憶體中，在某查詢的緩存條目被清理(例如過期)之前，前端再發起同樣的查詢語句，將直 ...

返回ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html

ProxySQL支持查詢緩存的功能，可以將後端返回的結果集緩存在自己的記憶體中，在某查詢的緩存條目被清理(例如過期)之前，前端再發起同樣的查詢語句，將直接從緩存中取數據並返回給前端。如此一來，ProxySQL處理的性能會大幅提升，也會大幅減輕後端MySQL Server的壓力。

1.開啟query cache功能

ProxySQL的查詢緩存功能由mysql_query_rules表中的cache_ttl欄位控制，該欄位設置每個規則對應的緩存時長，時間單位為毫秒。

當前端發送的SQL語句命中了某規則後(嚴格地說，是最後應用的那條規則，因為鏈式規則下會操作多個規則)，如果這個規則同時還設置了"cache_ttl"欄位的值，則這個SQL語句返回的結果將會被緩存一定時間，過期後將等待專門的線程(purge線程)來清理。

例如：

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,cache_ttl) 
values(1,1,1,10,"^select .* test1.t1",20000);

load mysql query rules to runtime;
save mysql query rules to disk;

select rule_id,destination_hostgroup,match_pattern,cache_ttl from mysql_query_rules;

這表示匹配上述規則(查詢test1.t1表)的查詢結果集將在ProxySQL上緩存20秒。

可以執行下麵的語句進行測試，每個語句迴圈執行10次：

# 在bash下執行
proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e"
for ((i=0;i<10;i++));do
    $proc "select * from test1.t1;"
    $proc "select * from test1.t2;"
    $proc "select * from test2.t1;"
    $proc "select * from test2.t2;"
done

再去查看規則統計表：

Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 10   |    <--rule_id=1的規則命中10次
+---------+------+

Admin> select hostgroup,count_star,sum_time,digest_text from stats_mysql_query_digest;  
+-----------+------------+----------+------------------------+
| hostgroup | count_star | sum_time | digest_text            |
+-----------+------------+----------+------------------------+
| -1        | 9          | 0        | select * from test1.t1 | <-- 9次，hg="-1"
| 10        | 10         | 3640     | select * from test2.t1 |
| 10        | 10         | 3686     | select * from test2.t2 |
| 10        | 10         | 3986     | select * from test1.t2 |
| 10        | 1          | 1236     | select * from test1.t1 | <-- 1次,hg="10"
+-----------+------------+----------+------------------------+

其中，hostgroup=-1表示從緩存中取數據。

從上面的結果中，可以看出select * from test1.t1;語句除了被正常路由到後端執行的一次外，其它9次都是直接從緩存中獲取數據的，且緩存取數據的語句執行總時間sum_time=0。

並不是所有的查詢結果都應該緩存，ProxySQL的緩存空間畢竟有限。所以很有必要去stats_mysql_query_digest表中找出哪些語句應該緩存：執行次數多、總執行時間長、平均執行時間長的語句都應該緩存。這些指標也是合理判斷讀、寫分離的指標。

2.查詢緩存相關的統計數據

ProxySQL雖然實現了查詢緩存功能，但是查詢緩存設計的還不夠完整，缺少一些錦上添花的功能，例如能查詢到的緩存類指標比較少，控制緩存的方式也比較缺乏。不過作者一直在努力改進，不斷完善。

以下是和查詢緩存有關的狀態變數：

Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE '%Cache%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Stmt_Cached              | 0              |
| Query_Cache_Memory_bytes | 0              |
| Query_Cache_count_GET    | 0              |
| Query_Cache_count_GET_OK | 0              |
| Query_Cache_count_SET    | 0              |
| Query_Cache_bytes_IN     | 0              |
| Query_Cache_bytes_OUT    | 0              |
| Query_Cache_Purged       | 0              |
| Query_Cache_Entries      | 0              |
+--------------------------+----------------+

各變數的意義如下：

Query_Cache_Memory_bytes：查詢結果集已成功緩存在記憶體中的總大小，不包含那些元數據；
Query_Cache_count_GET：從查詢緩存中取數據的請求總次數(GET requests)；
Query_Cache_count_GET_OK：成功從緩存中GET到緩存的請求總次數(即命中緩存且緩存未過期)；
Query_Cache_count_SET：緩存到查詢緩存中的結果集總數(即有多少個查詢的結果集進行了緩存)；
Query_Cache_bytes_IN：寫入到查詢緩存的總數據量；
Query_Cache_bytes_OUT：從查詢緩存中取出的總數據量；
Query_Cache_Purged：從緩存中移除(purged)的緩存結果集(緩存記錄)數量；
Query_Cache_Entries：當前查詢緩存中還有多少個緩存記錄。

無法查詢當前緩存空間中的具體的緩存記錄信息。

3.ProxySQL查詢緩存的細節

如下圖：

ProxySQL通過變數mysql_query_cache_size_MB控製為查詢緩存開闢多大的空間，通過變數mysql-threshold_resultset_size定義ProxySQL能緩存的單個最大結果集大小。

Admin> show  variables like '%size%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| mysql-eventslog_filesize       | 104857600 |
| mysql-threshold_resultset_size | 4194304   |  <<<<
| mysql-query_cache_size_MB      | 256       |  <<<<
| mysql-stacksize                | 1048576   |
+--------------------------------+-----------+

ProxySQL通過SET調用將後端返回的結果集放進查詢緩存空間，通過GET調用從查詢緩存空間取出緩存數據返回給前端。當GET調用發現緩存記錄已過期，會將其放進purge隊列中。

purge線程會定時清理purge隊列中的過期記錄，還會定時掃描緩存空間以找出過期記錄。當查詢緩存空間已經滿了(mysql-query_cache_size_MB)，下一次SET調用也會觸發purge線程來清理已經過期的緩存記錄。所以，SET調用永遠不會失敗。但需要註意的是，如果已緩存內容占用的緩存空間低於3%，則purge線程不會清理任何內容，即使它們已經過期了。

緩存記錄的數據結構如下：

key:value:length:expire_ms:access_ms:ref_count

其中：

key/value分別是緩存的id和實際緩存的數據，key是根據username + schemaname + SQL statement做hash運算得到的值，這可以保證用戶只會訪問到它自己的、指定schema的結果集。
length用於記錄緩存記錄的長度。因為mysql-threshold_resultset_size變數的原因，超出該變數閾值的那部分額外結果集不會放進緩存。
expire_ms：用來記錄每個緩存記錄還有多長時間過期。
access_ms：記錄每個緩存記錄最近一次被訪問離現在已多久。
ref_count：記錄各緩存記錄當前被引用數量。每次GET調用某緩存記錄，該緩存記錄的ref_count都會加1，調用完成後減1。這是為了避免正在調用的緩存記錄正好過期又正好被purge線程發現而被清理。

所以，後面的3個欄位都是為了讓緩存記錄過期而設計的。不過，目前ProxySQL還不支持根據access_ms來判斷是否清理，例如緩存空間已滿，且access_ms的時間已過去很久，但只要未過期，就不會被清理。這個欄位可能是為以後的LRU(或其它類似緩存清理演算法)做準備的。

4.容易誤解的查詢緩存

每個查詢緩存記錄的key是根據username + schemaname +SQL做hash運算出來的，這裡的SQL是完整的包含參數SQL語句，而非參數化後的語句，如果SQL語句進行了重寫，則使用重寫後的完整的SQL語句參與hash運算。這很重要。

例如：

select * from tab where id between 10 and 20;
select * from tab where id between 10 and 19;
select * from tab where id=15;
select * from tab where id between 10 and 19;

第一個語句會緩存id=10到id=20之間的結果集，但第二個語句和第三個語句不會從這個緩存記錄中取數據，而是從後端查詢後設置自己的緩存記錄，即使第二個語句參數化後的語句和第一個完全一樣(它們是同一類語句，如下代碼所示)，所需要的數據也已經被第一個語句緩存。第四個語句會命中第二個語句的緩存。

select * from tab where id between ? and ?

但因為第二個語句和第一個語句命中的規則是同一個，所以這類語句的執行總次數會遞增。

如下圖：

MySQL中間件之ProxySQL(9)：ProxySQL的查詢緩存功能

1.開啟query cache功能

2.查詢緩存相關的統計數據

3.ProxySQL查詢緩存的細節

4.容易誤解的查詢緩存