MySQL的Query Cache圖文詳解

一、原理概述

Query Cache就是把“查詢返回的結(jié)果”緩存起來。但是，僅僅只是緩存“查詢返回的結(jié)果”，其實是不準(zhǔn)確的，Query Cache還會緩存查詢語句，在內(nèi)存中將“查詢語句”和“查詢返回的結(jié)果”映射起來。
當(dāng)MySQL接收到一條SELECT類型的查詢語句時，MySQL會對這條查詢語句進(jìn)行hash計算而得到一個hash值。然后，通過該hash值到Query Cache中去匹配。如果沒有得到匹配結(jié)果，則會將這個hash值存放在一個hash鏈表中，同時將此次查詢的結(jié)果存放至緩存中。存放hash值的鏈表的每個節(jié)點(diǎn)都會存放相應(yīng)查詢返回結(jié)果在緩存中的地址，以及該次查詢所涉及到的一些表的相關(guān)信息。如果通過hash值匹配到相同的查詢，則會直接將緩存中相應(yīng)的查詢結(jié)果返回給客戶端。如果MySQL的任何一張表中的任何一條數(shù)據(jù)發(fā)生了變化，便會通知Query Cache，將和這張表相關(guān)的查詢緩存全部失效，并釋放占用的內(nèi)存空間。修改表數(shù)據(jù)的操作包括：INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE和DROP DATABASE等等。

Query Cache的工作原則是：執(zhí)行查詢最快的方式就是不去執(zhí)行。Query Cache的組件圖和流程圖如下所示：

二、Query Cache系統(tǒng)變量

Query Cache的主要可配置系統(tǒng)變量如下所示：

1. have_query_cache

表示mysqld是否支持Query Cache。

2. query_cache_limit

表示Query Cache可以緩存的單條查詢的最大結(jié)果集的大小，默認(rèn)值為1MB。如果某次查詢的結(jié)果集大小超過這個系統(tǒng)變量的值，那么Query Cache就不會緩存這次查詢的結(jié)果集。

3. query_cache_min_res_unit

表示MySQL為Query Cache每次分配內(nèi)存的最小空間大小，也就是用于緩存查詢結(jié)果的最小內(nèi)存空間的大小，默認(rèn)值為4KB。

4. query_cache_size

表示Query Cache可以使用的最大內(nèi)存空間的大小，默認(rèn)值為1MB。設(shè)置的值必須是1024的整數(shù)倍，若不是整數(shù)倍，MySQL則會自動調(diào)整降低至達(dá)到1024倍數(shù)的最大值。

5. query_cache_type

表示Query Cache的工作模式，同時也是Query Cache功能的開關(guān)，可以設(shè)置為0（OFF）、1（ON）和2（DEMAND）三種值：

• 0（OFF）：關(guān)閉Query Cache功能，任何情況下都不會使用Query Cache。

• 1（ON）：開啟Query Cache功能，但是當(dāng)SELECT語句中使用了SQL_NO_CACHE選項之后，將不會使用Query Cache。

• 2（DEMAND）：開啟Query Cache功能，但是只有當(dāng)SELECT語句中使用了SQL_CACHE選項之后，才會使用Query Cache。

6. query_cache_wlock_invalidate

控制當(dāng)有寫鎖加在表上的時候，是否先讓該表相關(guān)的Query Cahce失效，具有1（ON）和0（OFF）兩種取值：

• 1（ON）：在寫鎖定的同時將使該表相關(guān)的所有Query Cache失效。

• 0（OFF）：在寫鎖定的同時仍然允許讀取該表相關(guān)的Query Cache。

以上環(huán)境變量經(jīng)常需要調(diào)整的是query_cache_limit和query_cache_min_res_unit，它們都需要根據(jù)實際業(yè)務(wù)進(jìn)行相應(yīng)的調(diào)整。例如，如果緩存的查詢結(jié)果集大多數(shù)都小于4KB的話，則可以適當(dāng)?shù)恼{(diào)整query_cache_min_res_unit的值，以避免造成內(nèi)存的浪費(fèi)。如果查詢結(jié)果集的大小又都大于1MB時，就需要調(diào)整query_cache_limit的值，避免因為結(jié)果集大小超過限制而不被緩存。

三、Query Cache狀態(tài)變量

MySQL提供一系列的狀態(tài)變量來記錄Query Cache的當(dāng)前狀態(tài)，使你能夠確認(rèn)Query Cache的運(yùn)行是否健康、命中率如何、內(nèi)存空間大小是否足夠，等等。Query Cache的狀態(tài)變量如下所示：

1. Qcache_free_blocks

表示Query Cache中目前還有多少空閑的內(nèi)存塊。如果該值比較大，則說明Query Cache中的內(nèi)存碎片可能比較多。FLUSH QUERY CACHE會對緩存中的碎片進(jìn)行整理，從而得到一個較大的空閑內(nèi)存塊。

2. Qcache_free_memory

表示Query Cache目前空閑的內(nèi)存大小。

3. Qcache_hits

表示有多少次查詢在Query Cache命中。

4. Qcache_inserts

向Query Cache中插入新記錄的次數(shù)，也就是查詢沒有命中的次數(shù)。

5. Qcache_lowmem_prunes

表示由于Query Cache的內(nèi)存不足而從緩存中刪除的查詢結(jié)果的數(shù)量。如果這個數(shù)值在不斷增長，那么一般是Query Cache的空閑內(nèi)存不足（通過Qcache_free_memory判斷），或者內(nèi)存碎片較嚴(yán)重（通過Qcache_free_blocks判斷）。

6. Qcache_not_cached

表示沒有被緩存的查詢數(shù)量。有三種情況會導(dǎo)致查詢結(jié)果不會被緩存：其一，由于query_cache_type的設(shè)置；其二，查詢不是SELECT語句；其三，使用了now()之類的函數(shù)，導(dǎo)致查詢語句一直在變化。

7. Qcache_queries_in_cache

表示Query Cache中當(dāng)前包含的查詢結(jié)果數(shù)量。

8. Qcache_total_blocks

表示Query Cache中的內(nèi)存塊總數(shù)量。

四、優(yōu)點(diǎn)與缺點(diǎn)

1. 優(yōu)點(diǎn)

Query Cache的查詢，發(fā)生在MySQL接收到客戶端的查詢請求、查詢權(quán)限驗證之后和查詢SQL解析之前。也就是說，當(dāng)MySQL接收到客戶端的查詢SQL之后，僅僅只需要對其進(jìn)行相應(yīng)的權(quán)限驗證之后，就會通過Query Cache來查找結(jié)果，甚至都不需要經(jīng)過Optimizer模塊進(jìn)行執(zhí)行計劃的分析優(yōu)化，更不需要發(fā)生任何存儲引擎的交互。由于Query Cache是基于內(nèi)存的，直接從內(nèi)存中返回相應(yīng)的查詢結(jié)果，因此減少了大量的磁盤I/O和CPU計算，導(dǎo)致效率非常高。

2. 缺點(diǎn)

即使Query Cache的優(yōu)點(diǎn)很明顯，但是也不能忽略它所帶來的一些缺點(diǎn)：

• 查詢語句的hash計算和hash查找?guī)淼馁Y源消耗。如果將query_cache_type設(shè)置為1（也就是ON），那么MySQL會對每條接收到的SELECT類型的查詢進(jìn)行hash計算，然后查找這個查詢的緩存結(jié)果是否存在。雖然hash計算和查找的效率已經(jīng)足夠高了，一條查詢語句所帶來的開銷可以忽略，但一旦涉及到高并發(fā)，有成千上萬條查詢語句時，hash計算和查找所帶來的開銷就必須重視了。

• Query Cache的失效問題。如果表的變更比較頻繁，則會造成Query Cache的失效率非常高。表的變更不僅僅指表中的數(shù)據(jù)發(fā)生變化，還包括表結(jié)構(gòu)或者索引的任何變化。

• 查詢語句不同，但查詢結(jié)果相同的查詢都會被緩存，這樣便會造成內(nèi)存資源的過度消耗。查詢語句的字符大小寫、空格或者注釋的不同，Query Cache都會認(rèn)為是不同的查詢（因為他們的hash值會不同）。

• 相關(guān)系統(tǒng)變量設(shè)置不合理會造成大量的內(nèi)存碎片，這樣便會導(dǎo)致Query Cache頻繁清理內(nèi)存。

五、常見問答

1. 應(yīng)當(dāng)在什么條件下使用Query Cache？

實際上，并不是所有表都適合使用Query Cache。造成Query Cache失效的原因主要是相應(yīng)的表發(fā)生了變更，那么就應(yīng)該避免在變更頻繁的表上使用Query Cache。MySQL針對Query Cache有兩個專用的SQL選項：SQL_NO_CACHE和SQL_CACHE。若將query_cache_type設(shè)置為1（ON），那么通過SQL_NO_CACHE選項便能強(qiáng)制不使用Query Cache；若將query_cache_type設(shè)置為2（DEMAND），那么通過SQL_CACHE選項便能強(qiáng)制使用Query Cache。通過強(qiáng)制不使用Query Cache，可以讓MySQL在頻繁變更的表上不使用Query Cache，這樣減少了內(nèi)存開銷，也減少了hash計算和查找的開銷。

2. Query Cache與查詢語句有什么關(guān)系？

無論MySQL收到的查詢語句是單表還是多表或是包含子查詢的SQL，都被作為一個查詢，不會被分拆成多個查詢來進(jìn)行緩存，包括Union語句。

3. 客戶端提交的查詢語句的大小寫對Query Cache有影響嗎？

有影響。由于Query Cache在內(nèi)存中是以hash結(jié)構(gòu)來進(jìn)行映射的，hash算法的基礎(chǔ)就是組成查詢語句的字符，所以必須要整個查詢語句在字符級別完全一致，才能在Query Cache中命中。

4. 一個查詢語句在Query Cache中緩存的查詢結(jié)果，在什么情況下會失效？

為了保證Query Cache中的內(nèi)容與是實際數(shù)據(jù)絕對一致，當(dāng)表中的數(shù)據(jù)有任何變化，包括新增、修改、刪除等，都會使所有引用到該表的Query Cache緩存數(shù)據(jù)失效。

5. Query Cache碎片率是什么？有什么用？

Query Cache碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果Query Cache碎片率超過20%，則可以用FLUSH QUERY CACHE整理內(nèi)存碎片；如果你的查詢都是小數(shù)據(jù)量的話，可以嘗試減小query_cache_min_res_unit。

6. Query Cache利用率是什么？有什么用？

Query Cache利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

Query Cache利用率在25%以下的話，說明query_cache_size設(shè)置的過大，可適當(dāng)減??；Query Cache利用率在80%以上，而且Qcache_lowmem_prunes > 50的話，說明query_cache_size可能有點(diǎn)小，或者就是內(nèi)存碎片太多。

7. Query Cache命中率是什么？有什么用？

① 可緩存查詢的Query Cache命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
② 涵蓋所有查詢的Query Cache命中率 = Qcache_hits / (Qcache_hits + Com_select) * 100%

若命中率在50-70%的范圍之內(nèi)，則表明Query Cache的緩存效率較高。如果命中率明顯小于50%，那么建議禁用（將query_cache_type設(shè)置為0(OFF)）或按需使用（將query_cache_type設(shè)置為2(DEMAND)）Query Cache，節(jié)省的內(nèi)存可以用作InnoDB的緩沖池。

8. 如何判斷Query Cache是空閑內(nèi)存不足，還是內(nèi)存碎片太多？

如果Qcache_lowmem_prunes值比較大，表示Query Cache的內(nèi)存空間大小設(shè)置太小，需要增大。

如果Qcache_free_blocks值比較大，表示內(nèi)存碎片較多，需要使用FLUSH QUERY CACHE語句清理內(nèi)存碎片。

9. 系統(tǒng)變量query_cache_min_res_unit應(yīng)當(dāng)設(shè)置為多大？

query_cache_min_res_unit的計算公式如下所示：

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

其中，一般不建議將Query Cache的大小（也就是query_cache_size系統(tǒng)變量）設(shè)置超過256MB。

Mysql查詢緩存Query_cache的功用

MySQL的查詢緩存并非緩存執(zhí)行計劃，而是查詢及其結(jié)果集，這就意味著只有相同的查詢操作才能命中緩存，因此MySQL的查詢緩存命中率很低，另一方面，對于大結(jié)果集的查詢，其查詢結(jié)果可以從cache中直接讀取，有效的提升了查詢效率。

那么如何設(shè)置緩存的大小呢？來看一下：

1、在mysql客戶端命令行中我們可以這么查看緩存是否開啟以及緩存設(shè)置的大?。?/strong>

mysql> 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             | 16106127360 |
| query_cache_type             | ON          |
| query_cache_wlock_invalidate | OFF         |
+------------------------------+-------------+
6 rows in set (0.01 sec)

mysql> show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 1031832  |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 16489053 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> use mob_adn
Database changed
mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
| 87151154 |
+----------+
1 row in set (3 min 18.29 sec)

mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
| 87151154 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
| 87151154 |
+----------+
1 row in set (0.00 sec)