sax888

什麼是MySQL查詢高速緩衝？
         查詢緩存存儲SELECT查詢的文本以及發送給客戶端的相應結果。如果隨後收到一個相同的查詢，服務器從查詢緩存中重新得到查詢結果，而不再需要解析和執行查詢。
緩存的效果如何？
          如果執行的所有查詢是簡單的(如從只有一行數據的表中選取一行)，但查詢是不同的，查詢不能被緩存，查詢緩存激活率是13%。這可以看作是最壞的情形。在實際應用中，查詢要複雜得多，因此，查詢緩存使用率一般會很低。
          從只有一行的表中查找一行數據時，使用查詢緩存比不使用速度快238%。這可以看作查詢使用緩存時速度提高最小的情況
注意：
        查詢緩存不返回舊的數據。當表更改後，查詢緩存值的相關條目被清空。
        如果你有許多mysqld服務器更新相同的MyISAM表，在這種情況下查詢緩存不起作用。
        服務器啟動時要禁用查詢緩存，設置query_cache_size系統變量為0。禁用方式是通過在configure中使用--without-query-cache選項，可以從服務器中徹底去除查詢緩存能力。
查詢高速緩衝工作原理是怎樣的？
         當查詢解析之前先進行比較操作，SELECT * FROM table_name 和 Select * From table_name被認為是不同的兩個操作，也就意味著查詢操作必須是逐字節相同的操作語句串才能夠使用高速緩衝；同樣的查詢字符串有可能認為是不同的，如：使用不同的數據庫、不同的協議版本或者不同的默認字符集的查詢，所以高速緩衝將建立不同的查詢緩衝。
         當一個表被更改，那麼使用那個表的所有緩衝查詢將不再有效，並從緩衝區中移出。可能被哪些語句更改呢？它包括：INSERT UPDATE DELETE TRUNCATE ALTER TABLE DROP TABLE 和 DROP DATABASE。
         SELECT SQL_CALC_FOUND_ROWS * ......和SELECT FOUND_ROWS() type類型的查詢使用查詢緩存。即使因創建的行數也被保存在緩衝區內，前面的查詢從緩存中提取，FOUND_ROWS()也返回正確的值。
         哪些函數將不被緩存？

               引用自定義函數、變量、Mysql系統數據庫中的表。
           SELECT ... IN SHARE MODE
           SELECT ... FOR UPDATE
           SELECT ... INTO OUTFILE...
           SELECT ... INTO DUMPFILE ...
           SELECT * FROM ... WHERE autoincrement_col IS NULL
          使用TEMPORARY 表
          不使用任何表
          用戶有某個表的列集權限。
如何設置查詢高速緩衝SELECT 選項？
        SQL_CACHE：如果query_cache_type系統變量的值是ON或DEMAND，查詢結果被緩存。
        SQL_NO_CACHE：查詢結果不被緩存。
如何設置查詢高速緩衝配置？
        通過have_query_cache服務器系統變量指示查詢緩存是否可用。
        SHOW VARIABLES LIKE 'have_query_cache'；
       所有查詢緩存系統變量名以query_cache_ 開頭。為了設置查詢緩存大小，設置query_cache_size系統變量。設置為0表示禁用查詢緩存。 默認緩存大小設置為0；也就是禁用查詢緩存。當設置query_cache_size變量為非零值時，應記住查詢緩存至少大約需要40KB來分配其數據結構。(具體大小取決於系統結構）。如果你把該值設置的太小，將會得到一個警告。
        SET GLOBAL query_cache_size = 40000;
        SHOW WARNINGS\G
         如果查詢緩存大小設置為大於0，query_cache_type變量影響其工作方式。這個變量可以設置為下面的值：
           0或OFF將阻止緩存或查詢緩存結果。
           1或ON將允許緩存，以SELECT SQL_NO_CACHE開始的查詢語句除外。
           2或DEMAND，僅對以SELECT SQL_CACHE開始的那些查詢語句啟用緩存。
         當一個查詢結果（返回給客戶端的數據）從查詢緩衝中提取期間，它在查詢緩存中排序。因此，數據通常不在大的數據塊中處理。查詢緩存根據數據排序要求分配數據塊，因此，當一個數據塊用完後分配一個新的數據塊。因為內存分配操作是昂貴的(費時的)，所以通過query_cache_min_res_unit系統變量給查詢緩存分配最小值。當查詢執行時，最新的結果數據塊根據實際數據大小來確定，因此可以釋放不使用的內存。根據你的服務器執行查詢的類型，你會發現調整query_cache_min_res_unit變量的值是有用的：
         設置query_cache_type變量的GLOBAL值將決定更改後所有連接客戶端的緩存行為。具體客戶端可以通過設置query_cache_type變量的會話值控制它們本身連接的緩存行為。例如，一個客戶可以禁用自己的查詢緩存，方法如下：
mysql> SET SESSION query_cache_type = OFF;
         要控制可以被緩存的具體查詢結果的最大值，應設置query_cache_limit變量。 默認值是1MB。
         
當一個查詢結果（返回給客戶端的數據）從查詢緩衝中提取期間，它在查詢緩存中排序。因此，數據通常不在大的數據塊中處理。查詢緩存根據數據排序要求分配數據塊，因此，當一個數據塊用完後分配一個新的數據塊。因為內存分配操作是昂貴的(費時的)，所以通過 query_cache_min_res_unit系統變量給查詢緩存分配最小值。當查詢執行時，最新的結果數據塊根據實際數據大小來確定，因此可以釋放不使用的內存。根據你的服務器執行查詢的類型，你會發現調整query_cache_min_res_unit變量的值是有用的：
         query_cache_min_res_unit默認值是4KB。這應該適合大部分情況。
         如果你有大量返回小結果數據的查詢，默認數據塊大小可能會導致內存碎片，顯示為大量空閒內存塊。由於缺少內存，內存碎片會強制查詢緩存從緩存內存中修整（刪除）查詢。這時，你應該減少query_cache_min_res_unit變量的值。空閒塊和由於修整而移出的查詢的數量通過 Qcache_free_blocks和Qcache_lowmem_prunes變量的值給出。
·          如果大量查詢返回大結果（檢查 Qcache_total_blocks和Qcache_queries_in_cache狀態變量），你可以通過增加 query_cache_min_res_unit變量的值來提高性能。但是，注意不要使它變得太大（參見前面的條目）
本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/linuxin/archive/2008/07/17/2665015.aspx

  查詢緩存區（query cache）是Mysql 4.0版本後引入的一項功能，本意是提高SQL查詢性能，即把某些SQL查詢命令的結果存放在內存裡，當其他用戶再次執行一條完全相同的查詢命令時，Mysql將會把緩存在內存裡的查詢結果直接發送給他，而不用再對數據庫表進行查詢。
   但是並不是說查詢緩存區就只有優點，而沒有缺點，我舉個例子，一個項目裡如果是update， delete ，insert等數據庫操作比select操作更多，那我想覺得這時候就不適合開啟查詢緩存區的功能，為什麼呢，原因很簡單，查詢緩存區緩存的是查詢的結果，如果數據庫表內容經常的改變，那很多查詢結果多必須從查詢緩存區裡刪除掉，特別是內存較小的系統，這樣的話，只會加劇數據庫的負擔。
  基於這一點，用不用開啟查詢緩存區的功能，查閱不少文檔後，總結幾條如下：

1. 就是查詢緩存區適合select 操作較多，而insert update delete操作較少的情況，並且重複的SQL查詢越多，效果越好。

2. 就是SQL查詢語句命令必須嚴格的相同，包括字母大小寫，空格，舉個例子
有這麼兩行查詢語句，

select username,email from  members order by username limit 10;

Select username,email from  members order by username limit 10;

咋看怎麼長的都一樣，而且查詢的結果也是一樣的，是的，沒錯，除了「select」和「Select」一個字母大小寫的不同外，其他的都一樣，但是在Mysql內部，SQL分析器卻會把這兩條長的一樣的語句當作兩條不同的語句，mysql的確很傻很天真，嘿嘿，
這裡還要注意的是mysql對數據庫和數據表是區分大小寫的，而對數據列是不區分大小寫的。

3.就是select 命令裡不能含有一些特殊的函數，比如CUDATE(),NOW(),有這些函數在裡面，查詢緩存區幾乎不起作用，只會給系統帶來額外開銷。


查詢緩存區的啟用
   如果 query_cache_size=0，那麼當前沒有開啟查詢緩存區
在my.cnf的[mysqld]配置段
開啟查詢緩存區有幾個相關項，如
query_cache_size =128M
query_cache_type = 1  (有三個值，分別是0，1，2)
qurey_cache_limit=128K;
意思是給啟用查詢緩存區並給查詢緩存區分配128M的內存空間， 而且允許查詢的結果數據必須不大於128KB大小
query_cache_type為1，表示在SQL語句裡加上了SQL_NO_CACHE關鍵字的，強制不緩存查詢結果。
query_cache_type為0，表示關閉SQL語句裡的SQL_CACHE和SQL_NO_CACHE功能，也就是禁用查詢緩存功能。
query_cache_type為2，表示在SQL語句裡，只有包含著SQL_CACHE的查詢才會進入查詢緩存區。

下篇我將介紹下查詢緩存區的優化方法。
查看查詢緩存區的相關變量：
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                 | Value     |
+------------------------------+----------+|
have_query_cache              | YES       |
| query_cache_limit             | 1048576   |
| query_cache_min_res_unit      | 4096      |
| query_cache_size              | 33554432 |
| query_cache_type              | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+----------+
6 rows in set (0.00 sec)
have_query_cache  
是否支持查詢緩存區 「YES」表是支持查詢緩存區
query_cache_limit  可緩存的Select查詢結果的最大值 1048576 byte /1024 = 1024kB 即最大可緩存的select查詢結果必須小於1024KB
query_cache_min_res_unit 每次給query cache結果分配內存的大小 默認是 4096 byte 也即 4kB
在這裡，我延伸三點重要的東西
1.當查詢進行的時候，Mysql把查詢結果保存在qurey cache中，但是有時候要保存的結果比較大，超過了query_cache_min_res_unit的值 ，這時候mysql將一邊檢索結果，一邊進行慢慢保存結果，所以，有時候並不是
把所有結果全部得到後再進行一次性保存，而是每次分配一塊query_cache_min_res_unit 大小的內存空間保存結果集，
使用完後，接著再分配一個這樣的塊，如果還不不夠，接著再分配一個塊，依此類推，也就是說，有可能在一次查詢中，
mysql要進行多次內存分配的操作，而我們應該知道，頻繁操作內存都是要耗費時間的。
2. 內存碎片的產生。當一塊分配的內存沒有完全使用時，MySQL會把這塊內存Trim掉，把沒有使用的那部分歸還以重複利用。比如，第一次分配4KB,只用 了3KB，剩1KB，第二次連續操作，分配4KB，用了2KB，剩2KB，這兩次連續操作共剩下的1KB+2KB=3KB，不足以做個一個內存單元分配， 這時候，內存碎片便產生了。
3.內存塊的概念，先看下這個：
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value     |
+-------------------------+----------+|
|Qcache_free_blocks        | 5096     |
| Qcache_free_memory       | 18964096 |
| Qcache_hits              | 12192192 |
| Qcache_inserts           | 3560370  |
| Qcache_lowmem_prunes     | 17326    |
| Qcache_not_cached        | 303599   |
| Qcache_queries_in_cache  | 10201    |
| Qcache_total_blocks      | 25937    |
+-------------------------+----------+
8 rows in set (0.00 sec)
Qcache_total_blocks  表示所有的塊
Qcache_free_blocks  表示未使用的塊 這個值比較大，那意味著，內存碎片比較多，用flush query cache清理後，
為被使用的塊其值應該為1或0 ，因為這時候所有的內存都做為一個連續的快在一起了：
mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name            | Value     |
+-------------------------+----------+
| Qcache_free_blocks       | 1         |
| Qcache_free_memory       | 18539240 |
| Qcache_hits              | 12192502 |
| Qcache_inserts           | 3560515   |
| Qcache_lowmem_prunes     | 17326     |
| Qcache_not_cached        | 303607    |
| Qcache_queries_in_cache | 10318     |
| Qcache_total_blocks      | 21081     |
+-------------------------+----------+
8 rows in set (0.00 sec)
其他幾個狀態變量的意義：
Qcache_free_memory    表示查詢緩存區現在還有多少的可用內存
Qcache_hits        表示查詢緩存區的命中個數，也就是直接從查詢緩存區作出響應處理的查詢個數
Qcache_inserts    表示查詢緩存區此前總過緩存過多少條查詢命令的結果
Qcache_lowmem_prunes   表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數
Qcache_not_cached    表示沒有進入查詢緩存區的查詢命令個數
Qcache_queries_in_cache 查詢緩存區當前緩存著多少條查詢命令的結果
優化提示：
如果Qcache_lowmem_prunes 值比較大，表示查詢緩存區大小設置太小，需要增大。
如果Qcache_free_blocks 較多，表示內存碎片較多，需要清理，flush query cache
根據我看的 《High Performance MySQL》中所述，關於query_cache_min_res_unit大小的調優
，書中給出了一個計算公式，可以供調優設置參考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
還要注意一點的是，FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片，改善內存使用狀況，
但不會清理查詢緩存區的內容，這個要和RESET QUERY CACHE相區別，不要混淆，後者才是清除查詢緩存區中
的所有的內容。

sax888

在網站建設或者運營中，如果 MySQL server 是非常繁忙，可以開啟 query cache 以加速回應時間，開啟方法可以在 my.cnf 裡面加入以下項目: (Redhat 下面是 /etc/my.cnf；Debian 是在 etc/mysql/my.cnf).

query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 1048576

以上語法的設定裡面，query_cache_size 是分配 256MB 記憶體空間給 query cache；query_cache_type 設定成 1，是給所有的查詢做 cache；query_cache_limit 是指定個別的查詢語句 1KB 的記憶體空間。

這些數值可根據自己的需要作出更改，設定及存檔後，重新啟動 MySQL 即可。

Query Cache 在提高數據庫性能方面具有非常重要的作用。

其設定也非常簡單，僅需要在配置文件寫入兩行： query_cache_type 和 query_cache _size，而且 MySQL 的 query cache 非常快！而且一旦命中，就直接發送給客戶端，節約大量的 CPU 時間。

當然，非 SELECT 語句對緩衝是有影響的，它們可能使緩衝中的數據過期。一個 UPDATE 語句引起的部分表修改，將導致對該表所有的緩衝數據失效，這是 MySQL 為了平衡性能而沒有採取的措施。因為，如果每次 UPDATE 需要檢查修改的數據，然後撤出部分緩衝將導致代碼的複雜度增加。

query_cache_type 0 代表不使用緩衝， 1 代表使用緩衝，2 代表根據需要使用。
其實MySQL中有內存表了, 完全可以不用這個功能. 對於Query Cache, MySQL提供了query_cache_type變量來控制發送到數據庫的SQL是否進行Cache, 有三個值:

0, 對SQL語句不進行Cache.
1, 對於有SQL_NO_CACHE提示的SQL不進行Cache.
2, 僅對於有SQL_CACHE進示的SQL進行Cache.