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ù)值不同(感覺(jué)這個(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)為是不同的：
|
1
2
|
SELECT * FROM tbl_name Select * from tbl_name |
查詢緩存相關(guān)參數(shù)
|
1
|
mysql> SHOW VARIABLES LIKE '%query_cache%'; |
|
1
2
3
4
5
6
7
8
9
10
|
+------------------------------+---------+ | 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ī)制例子：
|
1
|
[mysql@csdba1850 ~]$ mysql -u root -p |
|
1
2
3
4
5
6
|
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. |
|
1
|
mysql> set global query_cache_size = 600000; --設(shè)置緩存內(nèi)存 |
|
1
|
Query OK, 0 rows affected (0.00 sec) |
|
1
|
mysql> set session query_cache_type = ON; --開啟查詢緩存 |
|
1
|
Query OK, 0 rows affected (0.00 sec) |
|
1
|
mysql> use test |
|
1
2
3
4
|
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 |
|
1
|
mysql> show tables; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
+----------------+ | 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ù)，是累加值。 |
|
1
|
mysql> SHOW STATUS LIKE 'Qcache_hits'; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
+---------------+-------+ | 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) |
|
1
|
mysql> SHOW STATUS LIKE 'Qcache%'; |
|
1
2
3
4
5
6
|
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | --表示sql在緩存中直接得到結(jié)果，不需要再去解析 +---------------+-------+ 8 rows in set (0.00 sec) |
|
1
|
mysql> select count(*) from animals; |
|
1
2
3
4
5
6
|
+----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) |
|
1
|
mysql> select count(*) from animals; |
|
1
2
3
4
5
6
|
+----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) |
|
1
|
mysql> SHOW STATUS LIKE 'Qcache_hits'; |
|
1
2
3
4
5
6
|
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | --上面的sql也是是從緩存中直接取到結(jié)果 +---------------+-------+ 1 row in set (0.00 sec) |
|
1
|
mysql> insert into animals select 9,'testsds' ; --插入數(shù)據(jù)后，跟這個(gè)表所有相關(guān)的sql緩存就會(huì)被清空掉 |
|
1
2
|
Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
|
1
|
mysql> select count(*) from animals; |
|
1
2
3
4
5
6
|
+----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) |
|
1
|
mysql> SHOW STATUS LIKE 'Qcache_hits'; |
|
1
2
3
4
5
6
|
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | --還是等于3，說(shuō)明上一條sql是沒(méi)有直接從緩存中直接得到的 +---------------+-------+ 1 row in set (0.00 sec) |
|
1
|
mysql> select count(*) from animals; |
|
1
2
3
4
5
6
|
+----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) |
|
1
|
mysql> SHOW STATUS LIKE 'Qcache_hits'; |
|
1
2
3
4
5
6
|
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 4 | +---------------+-------+ 1 row in set (0.00 sec) |
