MySQL緩存機(jī)制簡(jiǎn)單的說(shuō)就是緩存sql文本及查詢結(jié)果，如果運(yùn)行相同的sql，服務(wù)器直接從緩存中取到結(jié)果，而不需要再去解析和執(zhí)行sql。如果表更改 了，那么使用這個(gè)表的所有緩沖查詢將不再有效，查詢緩存值的相關(guān)條目被清空。更改指的是表中任何數(shù)據(jù)或是結(jié)構(gòu)的改變，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改變了的表的使用MERGE表的查詢。顯然，這對(duì)于頻繁更新的表，查詢緩存是不適合的，而對(duì)于一些不常改變數(shù)據(jù)且有 大量相同sql查詢的表，查詢緩存會(huì)節(jié)約很大的性能。

命中條件

緩存存在一個(gè)hash表中,通過(guò)查詢SQL,查詢數(shù)據(jù)庫(kù),客戶端協(xié)議等作為key.在判斷是否命中前,MySQL不會(huì)解析SQL,而是直接使用SQL去查詢緩存,SQL任何字符上的不同,如空格,注釋,都會(huì)導(dǎo)致緩存不命中.

如果查詢中有不確定數(shù)據(jù),例如CURRENT_DATE()和NOW()函數(shù),那么查詢完畢后則不會(huì)被緩存.所以,包含不確定數(shù)據(jù)的查詢是肯定不會(huì)找到可用緩存的

工作流程

1. 服務(wù)器接收SQL,以SQL和一些其他條件為key查找緩存表(額外性能消耗)

2. 如果找到了緩存,則直接返回緩存(性能提升)

3. 如果沒(méi)有找到緩存,則執(zhí)行SQL查詢,包括原來(lái)的SQL解析,優(yōu)化等.

4. 執(zhí)行完SQL查詢結(jié)果以后,將SQL查詢結(jié)果存入緩存表(額外性能消耗)

緩存失效

當(dāng)某個(gè)表正在寫入數(shù)據(jù),則這個(gè)表的緩存(命中檢查,緩存寫入等)將會(huì)處于失效狀態(tài).在Innodb中,如果某個(gè)事務(wù)修改了表,則這個(gè)表的緩存在事務(wù)提交前都會(huì)處于失效狀態(tài),在這個(gè)事務(wù)提交前,這個(gè)表的相關(guān)查詢都無(wú)法被緩存.

緩存的內(nèi)存管理

緩存會(huì)在內(nèi)存中開辟一塊內(nèi)存(query_cache_size)來(lái)維護(hù)緩存數(shù)據(jù),其中有大概40K的空間是用來(lái)維護(hù)緩存的元數(shù)據(jù)的,例如空間內(nèi)存,數(shù)據(jù)表和查詢結(jié)果的映射,SQL和查詢結(jié)果的映射等.

MySQL將這個(gè)大內(nèi)存塊分為小的內(nèi)存塊(query_cache_min_res_unit),每個(gè)小塊中存儲(chǔ)自身的類型,大小和查詢結(jié)果數(shù)據(jù),還有指向前后內(nèi)存塊的指針.

MySQL需要設(shè)置單個(gè)小存儲(chǔ)塊的大小,在SQL查詢開始(還未得到結(jié)果)時(shí)就去申請(qǐng)一塊空間,所以即使你的緩存數(shù)據(jù)沒(méi)有達(dá)到這個(gè)大小,也需要用這個(gè)大小的數(shù)據(jù)塊去存(這點(diǎn)跟Linux文件系統(tǒng)的Block一樣).如果結(jié)果超出這個(gè)內(nèi)存塊的大小,則需要再去申請(qǐng)一個(gè)內(nèi)存塊.當(dāng)查詢完成發(fā)現(xiàn)申請(qǐng)的內(nèi)存塊有富余,則會(huì)將富余的空間釋放掉,這就會(huì)造成內(nèi)存碎片問(wèn)題,見下圖

此處查詢1和查詢2之間的空白部分就是內(nèi)存碎片,這部分空閑內(nèi)存是有查詢1查詢完以后釋放的,假設(shè)這個(gè)空間大小小于MySQL設(shè)定的內(nèi)存塊大小,則無(wú)法再被使用,造成碎片問(wèn)題

在查詢開始時(shí)申請(qǐng)分配內(nèi)存Block需要鎖住整個(gè)空閑內(nèi)存區(qū),所以分配內(nèi)存塊是非常消耗資源的.注意這里所說(shuō)的分配內(nèi)存是在MySQL初始化時(shí)就開辟的那塊內(nèi)存上分配的.

緩存的使用時(shí)機(jī)

衡量打開緩存是否對(duì)系統(tǒng)有性能提升是一個(gè)很難的話題

1. 通過(guò)緩存命中率判斷, 緩存命中率 = 緩存命中次數(shù) (Qcache_hits) / 查詢次數(shù) (Com_select)

2. 通過(guò)緩存寫入率, 寫入率 = 緩存寫入次數(shù) (Qcache_inserts) / 查詢次數(shù) (Qcache_inserts)

3. 通過(guò) 命中-寫入率 判斷, 比率 = 命中次數(shù) (Qcache_hits) / 寫入次數(shù) (Qcache_inserts), 高性能MySQL中稱之為比較能反映性能提升的指數(shù),一般來(lái)說(shuō)達(dá)到3:1則算是查詢緩存有效,而最好能夠達(dá)到10:1

緩存配置參數(shù)

1. query_cache_type: 是否打開緩存

可選項(xiàng)

1) OFF: 關(guān)閉

2) ON: 總是打開

3) DEMAND: 只有明確寫了SQL_CACHE的查詢才會(huì)吸入緩存

2. query_cache_size: 緩存使用的總內(nèi)存空間大小,單位是字節(jié),這個(gè)值必須是1024的整數(shù)倍,否則MySQL實(shí)際分配可能跟這個(gè)數(shù)值不同(感覺這個(gè)應(yīng)該跟文件系統(tǒng)的blcok大小有關(guān))

3. query_cache_min_res_unit: 分配內(nèi)存塊時(shí)的最小單位大小

4. query_cache_limit: MySQL能夠緩存的最大結(jié)果,如果超出,則增加 Qcache_not_cached的值,并刪除查詢結(jié)果

5. query_cache_wlock_invalidate: 如果某個(gè)數(shù)據(jù)表被鎖住,是否仍然從緩存中返回?cái)?shù)據(jù),默認(rèn)是OFF,表示仍然可以返回

GLOBAL STAUS 中 關(guān)于 緩存的參數(shù)解釋:

Qcache_free_blocks: 緩存池中空閑塊的個(gè)數(shù)

Qcache_free_memory: 緩存中空閑內(nèi)存量

Qcache_hits: 緩存命中次數(shù)

Qcache_inserts: 緩存寫入次數(shù)

Qcache_lowmen_prunes: 因內(nèi)存不足刪除緩存次數(shù)

Qcache_not_cached: 查詢未被緩存次數(shù),例如查詢結(jié)果超出緩存塊大小,查詢中包含可變函數(shù)等

Qcache_queries_in_cache: 當(dāng)前緩存中緩存的SQL數(shù)量

Qcache_total_blocks: 緩存總block數(shù)

減少碎片策略

1. 選擇合適的block大小

2. 使用 FLUSH QUERY CACHE 命令整理碎片.這個(gè)命令在整理緩存期間,會(huì)導(dǎo)致其他連接無(wú)法使用查詢緩存

PS: 清空緩存的命令式 RESET QUERY CACHE

查詢緩存問(wèn)題分析

InnoDB與查詢緩存

Innodb會(huì)對(duì)每個(gè)表設(shè)置一個(gè)事務(wù)計(jì)數(shù)器,里面存儲(chǔ)當(dāng)前最大的事務(wù)ID.當(dāng)一個(gè)事務(wù)提交時(shí),InnoDB會(huì)使用MVCC中系統(tǒng)事務(wù)ID最大的事務(wù)ID跟新當(dāng)前表的計(jì)數(shù)器.

只有比這個(gè)最大ID大的事務(wù)能使用查詢緩存,其他比這個(gè)ID小的事務(wù)則不能使用查詢緩存.

另外,在InnoDB中,所有有加鎖操作的事務(wù)都不使用任何查詢緩存

查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。另外，同樣的查詢字符串由于其它原因可能認(rèn)為是不同的。使用不同的數(shù)據(jù)庫(kù)、不同的協(xié)議版本或者不同 默認(rèn)字符集的查詢被認(rèn)為是不同的查詢并且分別進(jìn)行緩存。

下面sql查詢緩存認(rèn)為是不同的：

SELECT * FROM tbl_name 
Select * from tbl_name

查詢緩存相關(guān)參數(shù)

mysql> SHOW VARIABLES LIKE '%query_cache%'; 

+------------------------------+---------+ 
| Variable_name        | Value  | 
+------------------------------+---------+ 
| have_query_cache       | YES   | --查詢緩存是否可用 
| query_cache_limit      | 1048576 | --可緩存具體查詢結(jié)果的最大值 
| query_cache_min_res_unit   | 4096  |  
| query_cache_size       | 599040 | --查詢緩存的大小 
| query_cache_type       | ON   | --阻止或是支持查詢緩存 
| query_cache_wlock_invalidate | OFF   |  
+------------------------------+---------+ 

下面是一個(gè)簡(jiǎn)單的MySQL查詢緩存機(jī)制例子：

[mysql@csdba1850 ~]$ mysql -u root -p 

Enter password:  
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 3 
Server version: 5.0.45-community MySQL Community Edition (GPL) 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 

mysql> set global query_cache_size = 600000; --設(shè)置緩存內(nèi)存 

Query OK, 0 rows affected (0.00 sec) 

mysql> set session query_cache_type = ON; --開啟查詢緩存 

Query OK, 0 rows affected (0.00 sec) 

 
mysql> use test 

Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Database changed 

mysql> show tables; 

+----------------+ 
| Tables_in_test | 
+----------------+ 
| animals    |  
| person     |  
+----------------+ 
5 rows in set (0.00 sec) 
 
mysql> select count(*) from animals; 
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 
 
--Qcache_hits表示sql查詢?cè)诰彺嬷忻械睦塾?jì)次數(shù)，是累加值。 

mysql> SHOW STATUS LIKE 'Qcache_hits'; 

+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 0   | --0次 
+---------------+-------+ 
8 rows in set (0.00 sec) 
 
mysql> select count(*) from animals; 
+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

mysql> SHOW STATUS LIKE 'Qcache%'; 

+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 1   | --表示sql在緩存中直接得到結(jié)果，不需要再去解析 
+---------------+-------+ 
8 rows in set (0.00 sec) 

mysql> select count(*) from animals; 

+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

mysql> select count(*) from animals; 

+----------+ 
| count(*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in set (0.00 sec) 

mysql> SHOW STATUS LIKE 'Qcache_hits'; 

+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 3   |  --上面的sql也是是從緩存中直接取到結(jié)果 
+---------------+-------+ 
1 row in set (0.00 sec) 

 
mysql> insert into animals select 9,'testsds' ; --插入數(shù)據(jù)后，跟這個(gè)表所有相關(guān)的sql緩存就會(huì)被清空掉 

Query OK, 1 row affected (0.00 sec) 
Records: 1 Duplicates: 0 Warnings: 0 

 
mysql> select count(*) from animals; 

+----------+ 
| count(*) | 
+----------+ 
|    7 |  
+----------+ 
1 row in set (0.00 sec) 

 
mysql> SHOW STATUS LIKE 'Qcache_hits'; 

+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 3  | --還是等于3，說(shuō)明上一條sql是沒(méi)有直接從緩存中直接得到的 
+---------------+-------+ 
1 row in set (0.00 sec) 

mysql> select count(*) from animals; 

+----------+ 
| count(*) | 
+----------+ 
|    7 |  
+----------+ 
1 row in set (0.00 sec) 

mysql> SHOW STATUS LIKE 'Qcache_hits';  

+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 4   |  
+---------------+-------+ 
1 row in set (0.00 sec)