mysql的query_cache誤區詳解

本文章詳細的介紹了關於大家在使用mysql的query_cache認識誤區以及後面詳細的介紹了mysql的query_cache的用法及優點。

一直以來，對於mysql的query_cache，在網上就流行著這樣的說法，“對於mysql的query_cache鍵值就是mysql的query,所以，如果在query中有任何的不同，包括多了個空格，都會導致mysql認為是不同的查詢”，其實，這一種說法是不完全正確的。首先第一點，mysql的query_cache的鍵值並不是簡單的query，而是query加databasename加flag。這個從源碼中就可以看出。在這裡不做重點描述，後續可以針對於這一點再具體分析。重要的是第二點，是不是加了空格，mysql就認為是不同的查詢呢？實際上這個是要分情況而言的，要看這個空格加在哪。 如果空格是加在query之前，比如是在query的起始處加了空格，這樣是絲毫不影響query cache的結果的，mysql認為這是一條query, 而如果空格是在query中，那會影響query cache的結果,mysql會認為是不同的query。

下面我們通過實驗及源碼具體分析。首先，我們先試驗一下：

首先，我們看一下mysql query_cache的狀態：

首先，我們可以確認，mysql的query_cache功能是打開的。

其次，我們看一下狀態：

因為這個db是新的db，所以hits，inset都為0，現在我們執行一條select語句：

狀態變為：

可以看到，執行一條select後，現在的qcache狀態為，insert+1，這樣我們就可以推斷出，現在剛才那條select語句已經加入了qcache中。那我們現在再將剛才那條sql前面加上空格，看看會怎樣呢？

請注意，這條sql，比剛才那條sql前面多了一個空格。

按照網上的理論，這條sql應該會作為另一個鍵而插入另一個cache，不會復用先前的cache,但結果呢？

我們可以看到，hits變為了1，而inserts根本沒變，這就說明了，這條在前面加了空格的query命中了沒有空格的query的結果集。從這，我們就可以得出結論，網上先前流傳的說法，是不嚴謹的。

 

那究竟是怎麼回事呢？到底應該如何呢？為什麼前面有空格的會命中了沒有空格的query的結果集。其實，這些我們可以通過源碼獲得答案。

翻看下mysql的源碼，我這翻看的是5.1的，在send_result_to_client（這個函數既是mysql調用query_cache的函數）這個函數裡面有這樣一段，

 

 /

 代碼如下 復制代碼

*
 Test if the query is a SELECT
 (pre-space is removed in dispatch_command).

 First '/' looks like comment before command it is not
 frequently appeared in real life, consequently we can
 check all such queries, too.
 */
    if ((my_toupper(system_charset_info, sql[i])     != 'S' ||
         my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
         my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
        sql[i] != '/')
    {
      DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
      goto err;
    }

這段代碼，是在檢驗語句是否為select語句，重點是上面那段注釋。特別是括弧中的，pre-space is removed in dispatch_command，也就是說，在語句開始之前的多余的空格已經被處理過了，在dispache_command這個函數中去掉了。

我們看下dispache_command這個方法，在這個方法裡有這樣一段：

 代碼如下 復制代碼  if (alloc_query(thd, packet, packet_length))
      break;                    // fatal error is set
    char *packet_end= thd->query() + thd->query_length();
    /* 'b' stands for 'buffer' parameter', special for 'my_snprintf' */
    const char* end_of_stmt= NULL;

在這裡，會調用alloc_query方法，我們看下這個方法的內容：

 代碼如下 復制代碼

bool alloc_query(THD *thd, const char *packet, uint packet_length)
{
  char *query;
  /* Remove garbage at start and end of query */
  while (packet_length > 0 && my_isspace(thd->charset(), packet[0]))
  {
    packet++;
    packet_length--;
  }
  const char *pos= packet + packet_length;     // Point at end null
  while (packet_length > 0 &&
     (pos[-1] == ';' || my_isspace(thd->charset() ,pos[-1])))
  {
    pos--;
    packet_length--;
  }
  /* We must allocate some extra memory for query cache

 The query buffer layout is:
 buffer :==
 <statement> The input statement(s)
 '' Terminating null char (1 byte)
 <length> Length of following current database name (size_t)
 <db_name> Name of current database
 <flags> Flags struct
 */
  if (! (query= (char*) thd->memdup_w_gap(packet,
                                          packet_length,
                                          1 + sizeof(size_t) + thd->db_length +
                                          QUERY_CACHE_FLAGS_SIZE)))
      return TRUE;
  query[packet_length]= '';
  /*
 Space to hold the name of the current database is allocated. We
 also store this length, in case current database is changed during
 execution. We might need to reallocate the 'query' buffer
 */
  char *len_pos = (query + packet_length + 1);
  memcpy(len_pos, (char *) &thd->db_length, sizeof(size_t));

  thd->set_query(query, packet_length);

  /* Reclaim some memory */
  thd->packet.shrink(thd->variables.net_buffer_length);
  thd->convert_buffer.shrink(thd->variables.net_buffer_length);

  return FALSE;
}

這個方法在一開始就會對query進行處理（代碼第4行），將開頭和末尾的garbage remove掉。

大部分的 MySQL 分發版本中，Query Cache 功能默認都是打開的，我們可以通過調整 MySQL Server 的參數選項打開該功能。主要由以下5個參數構成：

query_cache_limit：允許 Cache 的單條 Query 結果集的最大容量，默認是1MB，超過此參數設置的 Query 結果集將不會被 Cache
query_cache_min_res_unit：設置 Query Cache 中每次分配內存的最小空間大小，也就是每個 Query 的 Cache 最小占用的內存空間大小
query_cache_size：設置 Query Cache 所使用的內存大小，默認值為0，大小必須是1024的整數倍，如果不是整數倍，MySQL 會自動調整降低最小量以達到1024的倍數
query_cache_type：控制 Query Cache 功能的開關，可以設置為0(OFF),1(ON)和2(DEMAND)三種，意義分別如下：
0(OFF)：關閉 Query Cache 功能，任何情況下都不會使用 Query Cache
1(ON)：開啟 Query Cache 功能，但是當 SELECT 語句中使用的 SQL_NO_CACHE 提示後，將不使用Query Cache
2(DEMAND)：開啟 Query Cache 功能，但是只有當 SELECT 語句中使用了 SQL_CACHE 提示後，才使用 Query Cache
query_cache_wlock_invalidate：控制當有寫鎖定發生在表上的時刻是否先失效該表相關的 Query Cache，如果設置為 1(TRUE)，則在寫鎖定的同時將失效該表相關的所有 Query Cache，如果設置為0(FALSE)則在鎖定時刻仍然允許讀取該表相關的 Query Cache。
Query Cache 如何處理子查詢的？
這是我遇到的最為常見的一個問題。其實 Query Cache 是以客戶端請求提交的 Query 為對象來處理的，只要客戶端請求的是一個 Query，無論這個 Query 是一個簡單的單表查詢還是多表 Join，亦或者是帶有子查詢的復雜 SQL，都被當作成一個 Query，不會被分拆成多個 Query 來進行 Cache。所以，存在子查詢的復雜 Query 也只會產生一個Cache對象，子查詢不會產生單獨的Cache內容。UNION[ALL] 類型的語句也同樣如此。

Query Cache 是以 block 的方式存儲的數據塊嗎？
不是，Query Cache 中緩存的內容僅僅只包含該 Query 所需要的結果數據，是結果集。當然，並不僅僅只是結果數據，還包含與該結果相關的其他信息，比如產生該 Cache 的客戶端連接的字符集，數據的字符集，客戶端連接的 Default Database等。

Query Cache 為什麼效率會非常高，即使所有數據都可以 Cache 進內存的情況下，有些時候也不如使用 Query Cache 的效率高？
Query Cache 的查找，是在 MySQL 接受到客戶端請求後在對 Query 進行權限驗證之後，SQL 解析之前。也就是說，當 MySQL 接受到客戶端的SQL後，僅僅只需要對其進行相應的權限驗證後就會通過 Query Cache 來查找結果，甚至都不需要經過 Optimizer 模塊進行執行計劃的分析優化，更不許要發生任何存儲引擎的交互，減少了大量的磁盤 IO 和 CPU 運算，所以效率非常高。

客戶端提交的 SQL 語句大小寫對 Query Cache 有影響嗎？
有，由於 Query Cache 在內存中是以 HASH 結構來進行映射，HASH 算法基礎就是組成 SQL 語句的字符，所以必須要整個 SQL 語句在字符級別完全一致，才能在 Query Cache 中命中，即使多一個空格也不行。

一個 SQL 語句在 Query Cache 中的內容，在什麼情況下會失效？
為了保證 Query Cache 中的內容與是實際數據絕對一致，當表中的數據有任何變化，包括新增，修改，刪除等，都會使所有引用到該表的 SQL 的 Query Cache 失效。

為什麼我的系統在開啟了 Query Cache 之後整體性能反而下降了？
當開啟了 Query Cache 之後，尤其是當我們的 query_cache_type 參數設置為 1 以後，MySQL 會對每個 SELECT 語句都進行 Query Cache 查找，查找操作雖然比較簡單，但仍然也是要消耗一些 CPU 運算資源的。而由於 Query Cache 的失效機制的特性，可能由於表上的數據變化比較頻繁，大量的 Query Cache 頻繁的被失效，所以 Query Cache 的命中率就可能比較低下。所以有些場景下，Query Cache 不僅不能提高效率，反而可能造成負面影響。

如何確認一個系統的 Query Cache 的運行是否健康，命中率如何，設置量是否足夠？
MySQL 提供了一系列的 Global Status 來記錄 Query Cache 的當前狀態，具體如下：

Qcache_free_blocks：目前還處於空閒狀態的 Query Cache 中內存 Block 數目
Qcache_free_memory：目前還處於空閒狀態的 Query Cache 內存總量
Qcache_hits：Query Cache 命中次數
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，也就是沒有命中的次數
Qcache_lowmem_prunes：當 Query Cache 內存容量不夠，需要從中刪除老的 Query Cache 以給新的 Cache 對象使用的次數
Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由於 query_cache_type 設置的不會被 Cache 的 SQL
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 數量
Qcache_total_blocks：Query Cache 中總的 Block 數量
可以根據這幾個狀態計算出 Cache 命中率，計算出 Query Cache 大小設置是否足夠，總的來說，我個人不建議將 Query Cache 的大小設置超過256MB，這也是業界比較常用的做法。