高性能MySql進化論(十三):查詢緩存機制，對於很多的資料庫系統都能夠緩存執行計劃，對於完全相同的sql, 可以使用已經已經存在的執行計劃，從而跳過解析和生成執行計劃的過程。MySQL以及Oracle提供瞭更為高級的查詢結果緩存功能，對於完全相同的SQL (字符串完全相同且大小寫敏感) 可以執行返回查詢結果。本文主要介紹MYSQL 查詢緩存的一些特性，Oracle query cache可以參考https://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html
如果你有一個不經常改變的表並且伺服器收到該表的大量相同查詢，查詢緩存在這樣的應用環境中十分有用。對於許多Web伺服器來說存在這種典型情況，它根據資料庫內容生成大量的動態頁面。
1 查詢緩存的工作機制
Mysql 判斷是否命中緩存的辦法很簡單，首先會將要緩存的結果放在引用表中，然後使用查詢語句，資料庫名稱，客戶端協議的版本等因素算出一個hash值，這個hash值與引用表中的結果相關聯。如果在執行查詢時，根據一些相關的條件算出的hash值能與引用表中的數據相關聯，則表示查詢命中
通過have_query_cache伺服器系統變量指示查詢緩存是否可用：
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
為瞭監視查詢緩存性能，使用SHOW STATUS查看緩存狀態變量：
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
|變量名 |值 |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
1.1 查詢緩存機制失效的場景
先不論查詢緩存機制有利有弊，先看看哪些場景下會導致緩存機制失效
1. 如果查詢語句中包含一些不確定因素時（例如包含 函數Current()），該查詢不會被緩存,不確定因素主要包含以下情況
· 引用瞭一些返回值不確定的函數
|
BENCHMARK() |
CONNECTION_ID() |
CURDATE() |
|
CURRENT_DATE() |
CURRENT_TIME() |
CURRENT_TIMESTAMP() |
|
CURTIME() |
DATABASE() |
帶一個參數的ENCRYPT() |
|
FOUND_ROWS() |
GET_LOCK() |
LAST_INSERT_ID() |
|
LOAD_FILE() |
MASTER_POS_WAIT() |
NOW() |
|
RAND() |
RELEASE_LOCK() |
SYSDATE() |
|
不帶參數的UNIX_TIMESTAMP() |
USER() |
· 引用自設定函數(UDFs)。
· 引用自設定變量。
· 引用mysql系統資料庫中的表。
· 下面方式中的任何一種：
SELECT …IN SHARE MODE
SELECT …FOR UPDATE
SELECT …INTO OUTFILE …
SELECT …INTO DUMPFILE …
SELECT * FROM …WHERE autoincrement_col IS NULL
· 被作為編寫好的語句，即使沒有使用占位符。例如，下面使用的查詢：
char *my_sql_stmt = "SELECT a，b FROM table_c";
/* ...*/
mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));
不被緩存。
· 使用TEMPORARY表。
· 不使用任何表。
· 用戶有某個表的列級別權限。
1.2 額外的消耗
如果使用查詢緩存，在進行讀寫操作時會帶來額外的資源消耗，消耗主要體現在以下幾個方面
· 查詢的時候會檢查是否命中緩存，這個消耗相對較小
· 如果沒有命中查詢緩存，MYSQL會判斷該查詢是否可以被緩存，而且系統中還沒有對應的緩存，則會將其結果寫入查詢緩存
· 如果一個表被更改瞭，那麼使用那個表的所有緩沖查詢將不再有效，並且從緩沖區中移出。這包括那些映射到改變瞭的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。
對於InnoDB而言，事物的一些特性還會限制查詢緩存的使用。當在事物A中修改瞭B表時，因為在事物提交之前，對B表的修改對其他的事物而言是不可見的。為瞭保證緩存結果的正確性，InnoDB采取的措施讓所有涉及到該B表的查詢在事物A提交之前是不可緩存的。如果A事物長時間運行，會嚴重影響查詢緩存的命中率
查詢緩存的空間不要設置的太大。
因為查詢緩存是靠一個全局鎖操作保護的，如果查詢緩存配置的內存比較大且裡面存放瞭大量的查詢結果，當查詢緩存失效的時候，會長時間的持有這個全局鎖。因為查詢緩存的命中檢測操作以及緩存失效檢測也都依賴這個全局鎖，所以可能會導致系統僵死的情況
1.3 配置參數
MYSQL提供瞭一些參數來控制查詢緩存的行為，參數如下
· query_cache_limit
MYSQL能夠緩存的最大查詢結果，查詢結果大於該值時不會被緩存。默認值是1048576(1MB)
如果某個查詢的結果超出瞭這個值，Qcache_not_cached的值會加1，如果某個操作總是超出可以考慮在SQL中加上SQL_NO_CACHE來避免額外的消耗
· query_cache_min_res_unit
查詢緩存分配的最小塊的大小(字節)。 默認值是4096(4KB)。
· query_cache_size
為緩存查詢結果分配的內存的數量，單位是字節，且數值必須是1024的整數倍。默認值是0，即禁用查詢緩存。請註意即使query_cache_type設置為0也將分配此數量的內存。
· query_cache_type
設置查詢緩存類型。設置GLOBAL值可以設置後面的所有客戶端連接的類型。客戶端可以設置SESSION值以影響他們自己對查詢緩存的使用。下面的表顯示瞭可能的值：
|
選項 |
描述 |
|
0或OFF |
不要緩存或查詢結果。請註意這樣不會取消分配的查詢緩存區。要想取消，你應將query_cache_size設置為0。 |
|
1或ON |
緩存除瞭以SELECT SQL_NO_CACHE開頭的所有查詢結果。 |
|
2或DEMAND |
隻緩存以SELECT SQL_NO_CACHE開頭的查詢結果。 |
該變量默認設為ON。
· query_cache_wlock_invalidate
一般情況，當客戶端對MyISAM表進行WRITE鎖定時，如果查詢結果位於查詢緩存中，則其它客戶端未被鎖定，可以對該表進行查詢。將該變量設置為1，則可以對表進行WRITE鎖定，使查詢緩存內所有對該表進行的查詢變得非法。這樣當鎖定生效時，可以強制其它試圖訪問表的客戶端來等待。
2 查詢緩存的優化流程
當開啟瞭查詢緩存的功能後，可以通過一些參數以及狀態值來觀察查詢緩存的使用情況。
流程以及涉及到的參數參見下圖
3 查詢緩存的優化
除瞭上圖提到的一些優化策略外，還可以通過下面的措施來提高查詢緩存的效率
· 盡量用小表的簡單替代大表的復雜查詢
· 盡量用批量寫入取代單條寫入
· 控制query_cache_size的大小，甚至是禁用查詢緩存
· 通過DEMAND+SQL_CACHE/SQL_NO_CACHE來靈活控制某個select是否需要進行緩存
· 對於寫密集型的應用，直接禁用查詢緩存。