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

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

1        查詢緩存的工作機(jī)制

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

通過(guò)have_query_cache服務(wù)器系統(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     查詢緩存機(jī)制失效的場(chǎng)景

先不論查詢緩存機(jī)制有利有弊，先看看哪些場(chǎng)景下會(huì)導(dǎo)致緩存機(jī)制失效

1.       如果查詢語(yǔ)句中包含一些不確定因素時(shí)（例如包含 函數(shù)Current()），該查詢不會(huì)被緩存,不確定因素主要包含以下情況

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

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

·        引用自定義變量。

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

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

SELECT ...IN SHARE MODE

SELECT ...FOR UPDATE

SELECT ...INTO OUTFILE ...

SELECT ...INTO DUMPFILE ...

SELECT * FROM ...WHERE autoincrement_col IS NULL

·        被作為編寫好的語(yǔ)句，即使沒(méi)有使用占位符。例如，下面使用的查詢：

char *my_sql_stmt = "SELECT a，b FROM table_c";

   /* ...*/

mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));

不被緩存。

·        使用TEMPORARY表。

·        不使用任何表。

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

1.2     額外的消耗

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

·        查詢的時(shí)候會(huì)檢查是否命中緩存，這個(gè)消耗相對(duì)較小

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

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

對(duì)于InnoDB而言，事物的一些特性還會(huì)限制查詢緩存的使用。當(dāng)在事物A中修改了B表時(shí)，因?yàn)樵谑挛锾峤恢?，?duì)B表的修改對(duì)其他的事物而言是不可見(jiàn)的。為了保證緩存結(jié)果的正確性，InnoDB采取的措施讓所有涉及到該B表的查詢?cè)谑挛顰提交之前是不可緩存的。如果A事物長(zhǎng)時(shí)間運(yùn)行，會(huì)嚴(yán)重影響查詢緩存的命中率

查詢緩存的空間不要設(shè)置的太大。

因?yàn)椴樵兙彺媸强恳粋€(gè)全局鎖操作保護(hù)的，如果查詢緩存配置的內(nèi)存比較大且里面存放了大量的查詢結(jié)果，當(dāng)查詢緩存失效的時(shí)候，會(huì)長(zhǎng)時(shí)間的持有這個(gè)全局鎖。因?yàn)椴樵兙彺娴拿袡z測(cè)操作以及緩存失效檢測(cè)也都依賴這個(gè)全局鎖，所以可能會(huì)導(dǎo)致系統(tǒng)僵死的情況

1.3     配置參數(shù)

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

·              query_cache_limit

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

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

·              query_cache_min_res_unit

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

·              query_cache_size

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

·              query_cache_type

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

該變量默認(rèn)設(shè)為ON。

·              query_cache_wlock_invalidate

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

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

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

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

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

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

·              盡量用小表的簡(jiǎn)單替代大表的復(fù)雜查詢

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

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

·              通過(guò)DEMAND+SQL_CACHE/SQL_NO_CACHE來(lái)靈活控制某個(gè)select是否需要進(jìn)行緩存

·              對(duì)于寫密集型的應(yīng)用，直接禁用查詢緩存

4        Reference

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