MySQL的查詢緩存機(jī)制基本學(xué)習(xí)教程

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

命中條件

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

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

工作流程

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

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

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

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

緩存失效

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

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

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

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

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

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

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

緩存的使用時機(jī)

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

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

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

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

緩存配置參數(shù)

1. query_cache_type: 是否打開緩存

可選項(xiàng)

1) OFF: 關(guān)閉

2) ON: 總是打開

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

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

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

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

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

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

Qcache_free_blocks: 緩存池中空閑塊的個數(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 命令整理碎片.這個命令在整理緩存期間,會導(dǎo)致其他連接無法使用查詢緩存

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

查詢緩存問題分析

InnoDB與查詢緩存

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

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

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

查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。另外，同樣的查詢字符串由于其它原因可能認(rèn)為是不同的。使用不同的數(shù)據(jù)庫、不同的協(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 | 
+------------------------------+---------+ 

下面是一個簡單的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查詢在緩存中命中的累計(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ù)后，跟這個表所有相關(guān)的sql緩存就會被清空掉 

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，說明上一條sql是沒有直接從緩存中直接得到的 
+---------------+-------+ 
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) 

您可能感興趣的文章:

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com