對于很多的數(shù)據(jù)庫系統(tǒng)都能夠緩存執(zhí)行計劃，對于完全相同的sql, 可以使用已經(jīng)已經(jīng)存在的執(zhí)行計劃，從而跳過解析和生成執(zhí)行計劃的過程。MYSQL以及Oracle提供了更為高級的查詢結(jié)果緩存功能，對于完全相同的SQL (字符串完全相同且大小寫敏感) 可以執(zhí)行返回查詢結(jié)果。本文主要介紹MYSQL 查詢緩存的一些特性，Oracle query cache可以參考http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

如果你有一個不經(jīng)常改變的表并且服務器收到該表的大量相同查詢，查詢緩存在這樣的應用環(huán)境中十分有用。對于許多Web服務器來說存在這種典型情況，它根據(jù)數(shù)據(jù)庫內(nèi)容生成大量的動態(tài)頁面。

1        查詢緩存的工作機制

Mysql 判斷是否命中緩存的辦法很簡單，首先會將要緩存的結(jié)果放在引用表中，然后使用查詢語句，數(shù)據(jù)庫名稱，客戶端協(xié)議的版本等因素算出一個hash值，這個hash值與引用表中的結(jié)果相關聯(lián)。如果在執(zhí)行查詢時，根據(jù)一些相關的條件算出的hash值能與引用表中的數(shù)據(jù)相關聯(lián)，則表示查詢命中

通過have_query_cache服務器系統(tǒng)變量指示查詢緩存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

為了監(jiān)視查詢緩存性能，使用SHOW STATUS查看緩存狀態(tài)變量：

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.       如果查詢語句中包含一些不確定因素時（例如包含 函數(shù)Current()），該查詢不會被緩存,不確定因素主要包含以下情況

·        引用了一些返回值不確定的函數(shù)
 

·        引用自定義函數(shù)(UDFs)。

·        引用自定義變量。

·        引用mysql系統(tǒng)數(shù)據(jù)庫中的表。

·        下面方式中的任何一種：

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表。

·        不使用任何表。

·        用戶有某個表的列級別權(quán)限。

1.2     額外的消耗

如果使用查詢緩存，在進行讀寫操作時會帶來額外的資源消耗，消耗主要體現(xiàn)在以下幾個方面

·        查詢的時候會檢查是否命中緩存，這個消耗相對較小

·        如果沒有命中查詢緩存，MYSQL會判斷該查詢是否可以被緩存，而且系統(tǒng)中還沒有對應的緩存，則會將其結(jié)果寫入查詢緩存

·        如果一個表被更改了，那么使用那個表的所有緩沖查詢將不再有效，并且從緩沖區(qū)中移出。這包括那些映射到改變了的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

對于InnoDB而言，事物的一些特性還會限制查詢緩存的使用。當在事物A中修改了B表時，因為在事物提交之前，對B表的修改對其他的事物而言是不可見的。為了保證緩存結(jié)果的正確性，InnoDB采取的措施讓所有涉及到該B表的查詢在事物A提交之前是不可緩存的。如果A事物長時間運行，會嚴重影響查詢緩存的命中率

查詢緩存的空間不要設置的太大。

因為查詢緩存是靠一個全局鎖操作保護的，如果查詢緩存配置的內(nèi)存比較大且里面存放了大量的查詢結(jié)果，當查詢緩存失效的時候，會長時間的持有這個全局鎖。因為查詢緩存的命中檢測操作以及緩存失效檢測也都依賴這個全局鎖，所以可能會導致系統(tǒng)僵死的情況

1.3     配置參數(shù)

MYSQL提供了一些參數(shù)來控制查詢緩存的行為，參數(shù)如下

·              query_cache_limit

MYSQL能夠緩存的最大查詢結(jié)果，查詢結(jié)果大于該值時不會被緩存。默認值是1048576(1MB)

如果某個查詢的結(jié)果超出了這個值，Qcache_not_cached的值會加1，如果某個操作總是超出可以考慮在SQL中加上SQL_NO_CACHE來避免額外的消耗

·              query_cache_min_res_unit

查詢緩存分配的最小塊的大小(字節(jié))。 默認值是4096(4KB)。

·              query_cache_size

為緩存查詢結(jié)果分配的內(nèi)存的數(shù)量，單位是字節(jié)，且數(shù)值必須是1024的整數(shù)倍。默認值是0，即禁用查詢緩存。請注意即使query_cache_type設置為0也將分配此數(shù)量的內(nèi)存。

·              query_cache_type

設置查詢緩存類型。設置GLOBAL值可以設置后面的所有客戶端連接的類型?？蛻舳丝梢栽O置SESSION值以影響他們自己對查詢緩存的使用。下面的表顯示了可能的值：

該變量默認設為ON。

·              query_cache_wlock_invalidate

一般情況，當客戶端對MyISAM表進行WRITE鎖定時，如果查詢結(jié)果位于查詢緩存中，則其它客戶端未被鎖定，可以對該表進行查詢。將該變量設置為1，則可以對表進行WRITE鎖定，使查詢緩存內(nèi)所有對該表進行的查詢變得非法。這樣當鎖定生效時，可以強制其它試圖訪問表的客戶端來等待。

2        查詢緩存的優(yōu)化流程

當開啟了查詢緩存的功能后，可以通過一些參數(shù)以及狀態(tài)值來觀察查詢緩存的使用情況。

流程以及涉及到的參數(shù)參見下圖

3        查詢緩存的優(yōu)化

除了上圖提到的一些優(yōu)化策略外，還可以通過下面的措施來提高查詢緩存的效率

·              盡量用小表的簡單替代大表的復雜查詢

·              盡量用批量寫入取代單條寫入

·              控制query_cache_size的大小，甚至是禁用查詢緩存

·              通過DEMAND+SQL_CACHE/SQL_NO_CACHE來靈活控制某個select是否需要進行緩存

·              對于寫密集型的應用，直接禁用查詢緩存

4        Reference

更多信息可以參考：http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache