概述

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

一、緩存條件，原理

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

[[270293]]

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

1：命中條件

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

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

2：工作流程

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

二、相關SQL語句

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

 

 

 

其中各個參數的意義如下：

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

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語句的大小寫或者數據表有數據改動，則不會調用緩存。

2.6、配置查詢緩存

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

 

 

三、清除緩存

mysql的FLUSH句法(清除緩存)

FLUSH flush_option [,flush_option] 

如果你想要清除一些MySQL使用內部緩存，你應該使用FLUSH命令。為了執行FLUSH，你必須有reload權限。

flush_option可以是下列任何東西：

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

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

四、緩存的內存管理

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

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

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

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

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

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

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

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

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

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

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

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

六、查詢緩存問題分析

 

 

分析和配置查詢緩存

總結

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