mysql快取query_cache參數調優

標籤：mysql   快取   query_cache   

預設情況下，mysql沒有開啟query_cache功能。

MariaDB [(none)]> show variables like ‘%query_cache%‘;

+------------------------------+---------+

| Variable_name                | Value   |

+------------------------------+---------+

| have_query_cache             | YES     |    -----表示當前mysql版本支援query cache功能

| query_cache_limit            | 1048576 |    -----

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 0       |    -----表示禁用query cache，預設值

| query_cache_strip_comments   | OFF     |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

+------------------------------+---------+

query_cache_size根據實際情況設定，最好設定1024的倍數，參考值32M

在/etc/my.cnf配置文檔中修改：

query_cache_type=0（OFF）關閉

query_cache_type=1（ON）緩衝所有結果，除非select語句使用SQL_NO_CACHE禁用查詢快取

query_cache_type=2(demand)，只緩衝select語句中通過SQL_CACHE指定需要緩衝的查詢

配置實驗：

OK，配置完後的部分檔案如下：

query_cache_size=128M

query_cache_type=1

儲存檔案，重新啟動MYSQL服務，然後通過如下查詢來驗證是否真正開啟了：

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             | 134217728 |

| query_cache_type             | ON        |

| query_cache_wlock_invalidate | OFF       |

+——————————+———–+

6 rows in set (0.00 sec)

主要看query_cache_size和query_cache_type的值是否跟我們設的一致：

這裡query_cache_size的值是134217728，我們設定的是128M，實際是一樣的，只是單位不同，可以自己換算下：134217728 = 128*1024*1024。

query_cache_type設定為1，顯示為ON，這個前面已經說過了。

總之，看到上邊的顯示表示設定正確，但是在實際的查詢中是否能夠緩衝查詢，還需要手動測試下，我們可以通過show status like ‘%Qcache%’;語句來測試，現在我們開啟了查詢快取功能，在執行查詢前，我們先看看相關參數的值：

mysql> show status like ‘%Qcache%’;

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134208800 |

| Qcache_hits             | 0         |

| Qcache_inserts          | 0         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 2         |

| Qcache_queries_in_cache | 0         |

| Qcache_total_blocks     | 1         |

+————————-+———–+

8 rows in set (0.00 sec)

這裡順便解釋下這個幾個參數的作用：

Qcache_free_blocks:表示查詢快取中目前還有多少剩餘的blocks，如果該值顯示較大，則說明查詢快取中的記憶體片段過多了，可能在一定的時間進行整理。

Qcache_free_memory:查詢快取的記憶體大小，通過這個參數可以很清晰的知道當前系統的查詢記憶體是否夠用，是多了，還是不夠用，DBA可以根據實際情況做出調整。

Qcache_hits:表示有多少次命中緩衝。我們主要可以通過該值來驗證我們的查詢快取的效果。數字越大，緩衝效果越理想。

Qcache_inserts: 表示多少次未命中然後插入，意思是新來的SQL請求在緩衝中未找到，不得不執行查詢處理，執行查詢處理後把結果insert到查詢快取中。這樣的情況的次 數，次數越多，表示查詢快取應用到的比較少，效果也就不理想。當然系統剛啟動後，查詢快取是空的，這很正常。

Qcache_lowmem_prunes:該參數記錄有多少條查詢因為記憶體不足而被移除出查詢快取。通過這個值，使用者可以適當的調整緩衝大小。

Qcache_not_cached: 表示因為query_cache_type的設定而沒有被緩衝的查詢數量。

Qcache_queries_in_cache:當前緩衝中緩衝的查詢數量。

Qcache_total_blocks:當前緩衝的block數量。

下邊我們測試下：

比如執行如下查詢語句

mysql> select * from user where id = 2;

+—-+——-+

| id | name  |

+—-+——-+

|  2 | test2 |

+—-+——-+

1 row in set (0.02 sec)

然後執行show status like ‘%Qcache%’，看看有什麼變化：

mysql> show status like ‘%Qcache%’;

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134207264 |

| Qcache_hits             | 0         |

| Qcache_inserts          | 1         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 3         |

| Qcache_queries_in_cache | 1         |

| Qcache_total_blocks     | 4         |

+————————-+———–+

8 rows in set (0.00 sec)

對比前面的參數值，我們發現Qcache_inserts變化了。Qcache_hits沒有變，下邊我們在執行同樣的查詢
select * from user where id = 2，按照前面的理論分析：Qcache_hits應該等於1，而Qcache_inserts應該值不變（其他參數的值變化暫時不關注，讀者可以自行測試），再次執行：

show status like ‘%Qcache%’，看看有什麼變化：

mysql> show status like ‘%Qcache%’;

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134207264 |

| Qcache_hits             | 1         |

| Qcache_inserts          | 1         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 4         |

| Qcache_queries_in_cache | 1         |

| Qcache_total_blocks     | 4         |

+————————-+———–+

8 rows in set (0.00 sec)

OK，果然跟我們分析的完全一致。

mysql快取query_cache參數調優