7.1 DB端緩存

MySQL 在處理表時使用高度可擴展的算法，因此即使使用少量內存也可以運行 MySQL。當然，為了獲得更好的性能，您需要更多的 RAM。

要查看當前設置，請連接到數據庫


#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

首先，確保緩存已啟用。多變的：

  • query_cache_type必須為 ON (1) 或 DEMAND (2)
  • query_cache_limit- 確定將進入緩存的結果的最大大小
  • query_cache_size不同於零。使用 DEMAND 時，只有包含該指令的請求才會被緩存SQL_CACHE;
  • query_cache_min_res_unit用於存儲緩存查詢結果的已分配內存塊的最小大小。MySQL 不會將緩存存儲在一大塊內存中，而是根據需要分配最小大小query_cache_min_res_unit（默認為 4KB）的塊。最後一個這樣的塊被截斷為數據大小，剩餘的內存被釋放。

緩存的作用是，當服務器收到請求時，它會查看請求的哈希值是否在緩存中。如果哈希匹配 - 服務器立即返回結果 - 無需解析請求、優化等。開銷——伴隨著緩存機制——瀏覽緩存、將查詢結果寫入緩存等。

如果緩存中有很多小請求，那麼這可能會由於大量空閒塊而導致內存碎片。而這又會導致緩存條目因內存不足而被刪除。在這種情況下，降低 的值是有意義的query_cache_min_res_unit。如果您的大多數查詢產生大量結果，則增加此設置可以提高性能。

Java webinar

讓我們嘗試評估一下效果。我們看看緩存命中計數器如何變化（Qcahe_hits），由於內存不足而聲明無效的請求數（Qcache_lowmem_prunes），SELECT類型的請求總數（並且只有它們被緩存）：


#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 當前緩存狀態

要監視查詢緩存，請使用SHOW STATUS：


mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)
  • Qcache_free_blocks顯示緩存中有多少空閒塊（將隨著緩存請求的增加而減少）；
  • Qcache_total_blocks——佔用塊數；
  • Qcache_free_memory- 顯示用於緩存的免費“可用”內存；
  • Qcache_hits- 請求的數量，其結果是從緩存中獲取的，沒有實際訪問數據庫；
  • Qcache_inserts- 添加到緩存中的請求數；
  • Qcache_lowmem_prunes- 由於內存不足而從緩存中刪除的請求數；
  • Qcache_not_cached- 由於使用時間管理功能等而未寫入緩存的請求數；
  • Qcache_queries_in_cache- 緩存中的請求數。

您可以查看 SELECT 查詢的總數：


mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 緩存效率

“建議通過除以變量的值來評估緩存的有效性Qcache_hits on Qcache_hits + Com_select，因為在處理請求時，Qcache_hits 計數器會增加（如果請求是從緩存中處理的）或 Com_select（如果請求未被緩存）。這個方法在O'reilly的《Mysql性能優化》中有推薦

網上還有一個方法

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

如果這個值 > 0.8，那麼 80% 的請求都被緩存了，這是一個很好的指標。

如果%緩存命中率低，則需要增加該值 query_cache_size。

當前值可以這樣查看：

SHOW VARIABLES LIKE 'query_cache_size';

問題又來了：如何選擇合適的值query_cache_size?

這會有所幫助Qcache_lowmem_prunes。該變量存儲由於需要緩存新請求而從緩存中刪除的請求數。有必要爭取這樣的緩存大小，Qcache_lowmem_prunes它只會稍微增加。為此，建議比較Qcache_lowmem_prunes每小時值的差異和mysql在同一小時內收到的請求數。

“在實踐中，可以使用 2 個公式之一來計算 query_cache_size：

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

或者

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (10分鐘內的流量) * 1.2 "

這將緩存請求 10 分鐘 + 額外提供 20% 的內存用於緩存碎片和額外的緩存預留

您可以分別使用Bytes_sent變量計算對請求的響應數量和平均音量

所以query_cache_size我們增加了價值，之後你應該注意價值Qcache_total_blocks，Qcache_free_blocks和Qcache_queries_in_cache。MySQL 以塊的形式存儲緩存。對於 1 個請求，需要 2 個塊：一個用於請求文本本身，第二個用於結果。

如果我們從值考慮表Qcache%

緩存塊總數Qcache_total_blocks – 28

現在緩存了請求 6，這意味著 6 * 2 = 12 個塊正忙

免費塊Qcache_free_blocks – 10。空閒越多Qcache_free_blocks，緩存的“碎片化”程度就越大。

如果大多數查詢的結果數據量很小，那麼有必要減小最小緩存塊大小query_cache_min_res_unit，默認情況下為 4 KB。

如果大多數請求返回大量數據，則值得增加緩存塊的大小。

最主要的是要達到一個最小值Qcache_free_blocks。

如果計數器Qcache_not_cached很大，您可以嘗試增加變量query_cache_limit——這將允許您增加限制並緩存“不適合”的查詢結果。

以下配置變量負責使用查詢緩存：

  • query_cache_size— 查詢緩存的大小。query_cache_size = 0禁用緩存使用；
  • query_cache_limit- 緩存中存儲的最大樣本的大小；
  • query_cache_wlock_invalidate- 確定如果數據所屬的表被鎖定以供讀取，是否將從緩存中取出數據。
    • =

要啟用 mysql 查詢緩存，只需將以下行添加到my.cnf（[mysqld] 節）：


query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

並重啟服務。

7.4 不使用緩存的地方

未緩存：

  • 來自的請求SQL_NO_CACHE
  • 準備好的查詢(Prepared statements);
  • 作為外部查詢的子查詢的查詢；
  • 在存儲過程和函數中查詢；
  • 使用函數的查詢：

    BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (),ENCRYPT ()有一個參數, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (),UNIX_TIMESTAMP ()沒有參數, USER (),UUID ();

  • 在mysql系統數據庫或INFORMATION_SCHEMA中使用存儲函數、用戶變量或引用表​​進行查詢；
  • • 以下形式的請求：
    • SELECT ... IN SHARE MODE
    • SELECT ... FOR UPDATE
    • SELECT ... INTO OUTFILE ...
    • SELECT ... INTO DUMPFILE ...
    • SELECT * FROM ... WHERE autoincrement_col IS NULL
  • 使用臨時表或根本不使用表的查詢；
  • 請求產生警告（warnings）；

您可以使用以下命令對緩存進行碎片整理：

mysql>flush query cache;

清除 - 命令：

mysql>flush query cache;

最重要的

從不使用默認設置在生產中工作. 這將導致服務器的大部分資源未被使用。適當的MySQL調優可以將數據庫性能提高數倍。這不僅可以加快應用程序的速度，還可以應對繁重的負載。