MySQL數(shù)據(jù)庫(kù)查詢緩存總結(jié)

作者：波波說運(yùn)維 2019-07-11 08:45:00

mysql查詢緩存在數(shù)據(jù)庫(kù)優(yōu)化可以起到很大的作用，今天主要針對(duì)這一塊做一個(gè)總結(jié)，下面一起來(lái)看看吧~

概述

mysql查詢緩存在數(shù)據(jù)庫(kù)優(yōu)化可以起到很大的作用，今天主要針對(duì)這一塊做一個(gè)總結(jié)，下面一起來(lái)看看吧~

一、緩存條件，原理

MySQL Query Cache是用來(lái)緩存我們所執(zhí)行的SELECT語(yǔ)句以及該語(yǔ)句的結(jié)果集，MySql在實(shí)現(xiàn)Query Cache的具體技術(shù)細(xì)節(jié)上類似典型的KV存儲(chǔ)，就是將SELECT語(yǔ)句和該查詢語(yǔ)句的結(jié)果集做了一個(gè)HASH映射并保存在一定的內(nèi)存區(qū)域中。當(dāng)客戶端發(fā)起SQL查詢時(shí)，Query Cache的查找邏輯是，先對(duì)SQL進(jìn)行相應(yīng)的權(quán)限驗(yàn)證，接著就通過Query Cache來(lái)查找結(jié)果(注意必須是完全相同，即使多一個(gè)空格或者大小寫不同都認(rèn)為不同，即使完全相同的SQL，如果使用不同的字符集、不同的協(xié)議等也會(huì)被認(rèn)為是不同的查詢而分別進(jìn)行緩存)。它不需要經(jīng)過Optimizer模塊進(jìn)行執(zhí)行計(jì)劃的分析優(yōu)化，更不需要發(fā)生同任何存儲(chǔ)引擎的交互，減少了大量的磁盤IO和CPU運(yùn) 算，所以有時(shí)候效率非常高。

[[270293]]

查詢緩存的工作流程如下:

1：命中條件

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

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

2：工作流程

服務(wù)器接收SQL,以SQL和一些其他條件為key查找緩存表(額外性能消耗)
如果找到了緩存,則直接返回緩存(性能提升)
如果沒有找到緩存,則執(zhí)行SQL查詢,包括原來(lái)的SQL解析,優(yōu)化等.
執(zhí)行完SQL查詢結(jié)果以后,將SQL查詢結(jié)果存入緩存表(額外性能消耗)

二、相關(guān)SQL語(yǔ)句

2.1、查看SQL緩存參數(shù)：show variables like '%query_cache%';

優(yōu)化體系--mysql數(shù)據(jù)庫(kù)查詢緩存總結(jié)

其中各個(gè)參數(shù)的意義如下：

Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)空閑塊。
Qcache_free_memory：緩存中的空閑內(nèi)存。
Qcache_hits：每次查詢?cè)诰彺嬷忻袝r(shí)就增大
Qcache_inserts：每次插入一個(gè)查詢時(shí)就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
Qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進(jìn)行清理以便為更多查詢提供空間的次數(shù)。這個(gè)數(shù)字最好長(zhǎng)時(shí)間來(lái)看;如果這個(gè) 數(shù)字在不斷增長(zhǎng)，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。(上面的 free_blocks和free_memory可以告訴您屬于哪種情況)
Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語(yǔ)句或者用了now()之類的函數(shù)。
Qcache_queries_in_cache：當(dāng)前緩存的查詢(和響應(yīng))的數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。

2.2、開啟SQL緩存：set global query_cache_type = 1;

2.3、關(guān)閉SQL緩存：set global query_cache_type = 0;

2.4、設(shè)置緩存空間：set global query_cache_size = 1024*1024*64 (64M)

2.5、固定SQL語(yǔ)句聲明不適用緩存：select sql_no_cache * from 表名

注意：改變SQL語(yǔ)句的大小寫或者數(shù)據(jù)表有數(shù)據(jù)改動(dòng)，則不會(huì)調(diào)用緩存。

2.6、配置查詢緩存

vi /etc/my.cnf  
query_cache_size=300M 
query_cache_type=1

三、清除緩存

mysql的FLUSH句法(清除緩存)

FLUSH flush_option [,flush_option]

如果你想要清除一些MySQL使用內(nèi)部緩存，你應(yīng)該使用FLUSH命令。為了執(zhí)行FLUSH，你必須有reload權(quán)限。

flush_option可以是下列任何東西：

HOSTS 這個(gè)用的最多，經(jīng)常碰見。主要是用來(lái)清空主機(jī)緩存表。如果你的某些主機(jī)改變IP數(shù)字，或如果你得到錯(cuò)誤消息Host ... isblocked，你應(yīng)該清空主機(jī)表。當(dāng)在連接MySQL服務(wù)器時(shí)，對(duì)一臺(tái)給定的主機(jī)有多于 max_connect_errors個(gè)錯(cuò)誤連續(xù)不斷地發(fā)生，MySQL為了安全的需要將會(huì)阻止該主機(jī)進(jìn)一步的連接請(qǐng)求。清空主機(jī)表允許主機(jī)再嘗試連接。
LOGS 關(guān)閉當(dāng)前的二進(jìn)制日志文件并創(chuàng)建一個(gè)新文件，新的二進(jìn)制日志文件的名字在當(dāng)前的二進(jìn)制文件的編號(hào)上加1。
PRIVILEGES 這個(gè)也是經(jīng)常使用的，每當(dāng)重新賦權(quán)后，為了以防萬(wàn)一，讓新權(quán)限立即生效，一般都執(zhí)行一把，目地是從數(shù)據(jù)庫(kù)授權(quán)表中重新裝載權(quán)限到緩存中。
TABLES 關(guān)閉所有打開的表，同時(shí)該操作將會(huì)清空查詢緩存中的內(nèi)容。
FLUSH TABLES WITH READ LOCK 關(guān)閉所有打開的表，同時(shí)對(duì)于所有數(shù)據(jù)庫(kù)中的表都加一個(gè)讀鎖，直到顯示地執(zhí)行unlock tables，該操作常常用于數(shù)據(jù)備份的時(shí)候。
STATUS 重置大多數(shù)狀態(tài)變量到0。
MASTER 刪除所有的二進(jìn)制日志索引文件中的二進(jìn)制日志文件，重置二進(jìn)制日志文件的索引文件為空，創(chuàng)建一個(gè)新的二進(jìn)制日志文件,不過這個(gè)已經(jīng)不推薦使用，改成reset master 了?？梢韵胂?，以前自己是多土啊，本來(lái)一條簡(jiǎn)單的命令就可以搞定的，卻要好幾條命令來(lái)，以前的做法是先查出來(lái)當(dāng)前的二進(jìn)制日志文件名，再用purge 操作。
QUERY CACHE 重整查詢緩存，消除其中的碎片，提高性能，但是并不影響查詢緩存中現(xiàn)有的數(shù)據(jù)，這點(diǎn)和Flush table 和Reset Query Cache(將會(huì)清空查詢緩存的內(nèi)容)不一樣的。
SLAVE 類似于重置復(fù)制吧，讓從數(shù)據(jù)庫(kù)忘記主數(shù)據(jù)庫(kù)的復(fù)制位置，同時(shí)也會(huì)刪除已經(jīng)下載下來(lái)的relay log,與Master一樣，已經(jīng)不推薦使用，改成Reset Slave了。這個(gè)也很有用的。

一般來(lái)講，F(xiàn)lush操作都會(huì)記錄在二進(jìn)制日志文件中，但是FLUSH LOGS、FLUSH MASTER、FLUSH SLAVE、FLUSH TABLES WITH READ LOCK不會(huì)記錄，因此上述操作如果記錄在二進(jìn)制日志文件中話，會(huì)對(duì)從數(shù)據(jù)庫(kù)造成影響。注意：Reset操作其實(shí)扮演的是一個(gè)Flush操作的增強(qiáng)版的角色。

四、緩存的內(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ù)沒有達(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)存碎片問題,見下圖

查詢緩存存儲(chǔ)查詢結(jié)果后剩余的查詢碎片

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

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

五、緩存的使用時(shí)機(jī) & 性能

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

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

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

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

任何事情過猶不及，尤其對(duì)于某些寫頻繁的系統(tǒng)，開啟Query Cache功能可能并不能讓系統(tǒng)性能有提升，有時(shí)反而會(huì)有下降。原因是MySql為了保證Query Cache緩存的內(nèi)容和實(shí)際數(shù)據(jù)絕對(duì)一致，當(dāng)某個(gè)數(shù)據(jù)表發(fā)生了更新、刪除及插入操作，MySql都會(huì)強(qiáng)制使所有引用到該表的查詢SQL的Query Cache失效。對(duì)于密集寫操作，啟用查詢緩存后很可能造成頻繁的緩存失效，間接引發(fā)內(nèi)存激增及CPU飆升，對(duì)已經(jīng)非常忙碌的數(shù)據(jù)庫(kù)系統(tǒng)這是一種極大的負(fù)擔(dān)。

六、查詢緩存問題分析