概述

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

一、緩存條件，原理

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

[[270293]]

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

1：命中條件

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

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

2：工作流程

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

二、相關SQL語句

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

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

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

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

2.3、關閉SQL緩存：set global query_cache_type = 0;

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

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

注意：改變SQL語句的大小寫或者數(shù)據(jù)表有數(shù)據(jù)改動，則不會調(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)部緩存，你應該使用FLUSH命令。為了執(zhí)行FLUSH，你必須有reload權限。

flush_option可以是下列任何東西：

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

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

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

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

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

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

查詢緩存存儲查詢結果后剩余的查詢碎片

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

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

五、緩存的使用時機 & 性能

衡量打開緩存是否對系統(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ù),一般來說達到3:1則算是查詢緩存有效,而最好能夠達到10:1

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

六、查詢緩存問題分析

分析和配置查詢緩存

總結

這里要注意Query Cache因MySql的存儲引擎不同而實現(xiàn)略有差異，比如MyISAM，緩存的結果集存儲在OS Cache中，而最流行的InnoDB則放在Buffer Pool中。