解鎖 MySQL 效能：掌握查詢快取優化

什麼是查詢快取？

查詢快取是 MySQL 中的一項功能，旨在透過快取 SELECT 查詢的結果來提高資料庫效能。當再次執行先前執行過的查詢時，MySQL 可以快速從該快取中提取結果，而不是對資料庫重新執行它。這不僅加快了資料檢索速度，還減少了資料庫的負載，使其對於具有一致參數的頻繁運行的查詢非常有效率。

查詢快取的工作原理

每當發出查詢時，MySQL 首先查看查詢快取以檢查先前是否儲存了相同查詢的結果。如果存在匹配，MySQL 會繞過通常的查詢執行過程並直接提供快取的結果。這要快得多，因為它避免了查詢處理和磁碟存取的耗時步驟，而是利用記憶體存取的速度。

我們現在轉向關鍵的查詢快取變數 - query_cache_type、query_cache_size、query_cache_limit 和 query_cache_min_res_unit - 以及它們對 MySQL 效能的影響。

1. 查詢快取類型

MySQL 中的 query_cache_type 變數控制查詢快取是啟用、停用還是設定為僅按需操作。

query_cache_type 用法

查詢快取儲存 SELECT 語句的文字以及傳送到客戶端的對應結果。如果稍後收到相同的語句，伺服器會從快取中檢索結果，而不是再次解析並執行該語句。啟用查詢快取可以透過從記憶體提供查詢而不是重新執行查詢來顯著減少獲取頻繁運行的查詢結果所需的時間。

將 MySQL 中的 query_cache_type 變數設定為不同的值決定查詢快取的行為方式：

• 0（關閉） – 停用查詢緩存，但仍分配 query_cache_size 位元組的緩衝區。

• 1 (ON) – 為所有 SELECT 查詢啟用查詢緩存，除非在查詢中指定 SQL_NO_CACHE。

• 2 (DEMAND) – 僅對明確使用 SQL CACHE 子句的查詢啟用查詢快取。

query_cache_type配置

Query_cache_type可以在伺服器運作時離線或線上設定。可能首選在線配置，以便進行測試。當伺服器重新啟動時，query_cache_type 將會恢復。

命令列設定：
mysqld>設定全域query_cache_type = XX

將 XX 替換為適合您的資料庫需求的值。若要驗證變數是否已更改：

mysqld>顯示全域變量，如「query_cache_type」

設定檔：
[mysqld]
query_cache_type = XX

將 XX 替換為適合您的資料庫需求的值。重新啟動 MySQL 伺服器。

query_cache_type 注意事項

根據一般建議，對於資料不頻繁更改但讀取頻繁的環境，應將 query_cache_type 設定為 1（ON）。在高度動態的環境中設定為 0（關閉），在這種環境中，維護快取的開銷可能會超過好處。

不加區別地啟用查詢快取可能不會總是帶來效能優勢，甚至在某些情況下會降低效能。設定query_cache_type時請考慮以下因素：

• 查詢快取的大小 – 較大的快取可以容納更多查詢結果，但需要更多記憶體。

• 查詢模式 – 經常更改結果或大型結果集的查詢可能無法從快取中受益。

• 快取失效 – 快取表上的更新、插入或刪除會使對應的快取條目失效，進而導致快取流失。

• 並發 – 由於爭用問題，查詢快取不適合高度並發的工作負載。

• MySQL 版本 – 查詢快取功能已在 MySQL 5.7 中棄用，並在 MySQL 8.0 中刪除，因為它有限制並且可能會導致多執行緒環境中的爭用。

2.查詢快取大小

指定分配用於儲存快取查詢結果的記憶體量。它是決定一次可以緩存多少結果的主要因素。

query_cache_size 用法

query_cache_size 變數決定為查詢快取所分配的記憶體量。應根據工作負載的性質和可用記憶體資源調整該值：

• 小結果集 – 如果您的應用程式經常執行傳回小結果集的查詢，則較大的查詢快取大小可能會有所幫助。這允許在快取中儲存更多查詢，從而減少查詢執行的需要。

• 頻繁的相同查詢 – 在重複執行相同查詢的場景中，增加 query_cache_size 可以透過快取這些查詢及其結果來提高效能。

• 查詢快取命中率 – 監控查詢快取命中率可以深入了解快取的有效性。如果命中率較低，增加query_cache_size可能有助於提高快取效率。

query_cache_size配置

Query_cache_size可以在伺服器運作時離線或線上設定。可能首選在線配置，以便進行測試。當伺服器重新啟動時，query_cache_size 將會恢復。

命令列設定：
mysqld>設定全域 query_cache_size = XX

將 XX 替換為適合您的資料庫需求的值。驗證變數是否已更改：

mysqld>顯示全域變量，如「query_cache_size」

設定檔：
_[mysqld]
query_cache_size _= XX

將 XX 替換為適合您的資料庫需求的值。重新啟動 MySQL 伺服器。

query_cache_size 注意事項

query_cache_size 應根據可用記憶體和工作負載的性質進行設定。設定太大會導致記憶體耗盡，設定太小可能會限制其有效性。

監控快取的使用率（命中與插入）將指導適當的大小調整。從中等大小開始，例如 64MB 到 128MB，然後根據效能和可用系統記憶體進行調整。

設定query_cache_size時請考慮以下因素：

• 查詢模式 – 經常更改結果或大型結果集的查詢可能無法從快取中受益。

• 快取失效 – 快取表上的更新、插入或刪除會使對應的快取條目失效，進而導致快取流失。

• 並發 – 由於爭用問題，查詢快取不適合高度並發的工作負載。

• MySQL 版本 – 由於多執行緒環境中的限制和爭用，查詢快取功能已在 MySQL 5.7 中棄用並在 MySQL 8.0 中移除。

3.查詢快取限制

此變數設定可以快取的單一查詢結果的最大大小。它可以防止大型查詢消耗不成比例的快取空間。

query_cache_limit 用法

當查詢結果超過query_cache_limit時，結果不會被快取。這可以防止過大或資源密集型查詢用可能不會經常重複使用的結果填充快取。透過為query_cache_limit設定適當的值，可以確保只快取更小、更常用的查詢結果，從而優化記憶體的使用。

query_cache_limit 配置

Query_cache_limit 可以在伺服器運作時離線或線上設定。可能首選在線配置，以便進行測試。當伺服器重新啟動時，query_cache_limit 將會恢復。

命令列設定：
mysqld>設定全域 query_cache_limit = XX

將 XX 替換為適合您的資料庫需求的值。若要驗證變數是否已更改：

mysqld>顯示全域變量，如「query_cache_limit」

設定檔：
[mysqld]
query_cache_limit = XX

將 XX 替換為適合您的資料庫需求的值。重新啟動 MySQL 伺服器。

query_cache_limit 注意事項

通常建議將 query_cache_limit 設定為 1MB 到 4MB 之間，具體取決於查詢的性質和可用快取大小。需要注意的是，將 query_cache_limit 設定得太低可能會導致有用的查詢結果被排除在快取之外，從而降低查詢快取的有效性。

4.query_cache_min_res_unit

MySQL 中的 query_cache_min_res_unit 變數決定查詢快取分配的區塊的最小大小（以位元組為單位）。此設定透過控制快取結果的粒度來影響查詢快取的效率。

query_cache_min_res_unit 用法

當查詢結果儲存在查詢快取中時，會佔用一定的記憶體。 query_cache_min_res_unit 變數定義為這些快取結果所分配的記憶體區塊的最小大小。如果查詢結果小於這個值，它仍然會佔用query_cache_min_res_unit定義的最小大小。

query_cache_min_res_unit 配置

Query_cache_min_res_unit 可以在伺服器執行時離線或線上設定。可能首選在線配置，以便進行測試。當伺服器重新啟動時，query_cache_min_res_unit 將會恢復。

命令列設定：
mysqld>設定全域 query_cache_min_res_unit = XX

將 XX 替換為適合您的資料庫需求的值。若要驗證變數是否已更改：

mysqld>顯示全域變量，如「query_cache_min_res_unit」
_
設定檔：
_[mysqld]
query_cache_min_res_unit = XX

將 XX 替換為適合您的資料庫需求的值。重新啟動 MySQL 伺服器。

query_cache_min_res_unit 注意事項

設定 query_cache_min_res_unit 涉及將變數設為適當的值，以平衡記憶體消耗與快取效率。應根據工作負載中查詢結果的平均大小來選擇該值。

• 較小的值可能會導致更有效率的記憶體使用，但可能會因更多快取條目而增加開銷。

• 相反，較大的值可能會減少快取條目的數量，但可能會導致較小查詢結果的記憶體浪費。

分析您的工作負載以確定查詢結果的平均大小。根據此分析調整query_cache_min_res_unit的值，以達到記憶體消耗和快取效率之間的平衡。對於大多數設置，該大小將介於 16MB 和 64MB 之間。

只需 4 個步驟即可啟用和調整查詢快取

從 MySQL 5.7.20 開始，查詢快取已被棄用，並在 MySQL 8.0 中完全刪除。如果您的 MySQL 版本仍然能夠使用 query_cache，則必須啟用它，因為預設情況下它是停用的。要在 MySQL、MariaDB 或 Percona 中啟用和配置查詢緩存，您通常需要存取伺服器的 my.cnf 或 my.ini 檔案。以下是逐步方法：

1。啟用查詢快取 – 將 query_cache_type 設為 1 或 2。將 query_cache_type 或 query_cache_size 設定為零將始終停用快取。對於選擇性快取（建議大多數用例），您可以使用：

query_cache_type = 1

1. 設定快取大小 – 定義 query_cache_size。起始點可能是總可用記憶體的 10-20%，但這需要根據您的工作負載進行調整：

query_cache_size = 100M

3。定義結果大小限制 – 設定query_cache_limit 以控制儲存結果的大小。這可能從幾兆位元組開始，具體取決於您的典型查詢大小：

query_cache_limit = 2M

4。調整最小結果單位 – 依照您的需求修改query_cache_min_res_unit。將其減少到預設值以下可以幫助更有效地利用快取空間，特別是如果您預計有很多小查詢：

query_cache_min_res_unit = 512

透過運行狀況檢查監控查詢快取

QCache Fragmentation 是 MySQL 中查詢快取效能的關鍵指標。此功能旨在儲存 SELECT 查詢的結果，以便可以快速滿足重複的請求，而無需重新執行查詢，從而提高效能。然而，隨著時間的推移，查詢快取可能會變得碎片化，導致效率降低。

運算QCache碎片

查看我們全面的運行狀況檢查文檔，其中包含有關如何計算 QCache 碎片的資訊和逐步說明。

計算完 QCache Fragmentation 和 QcacheDeleteRate 後，您需要解釋結果。理想情況下，QCache Fragmentation 應小於 10，QcacheDeleteRate 應小於 20。

建议采取的行动

如果 QCache Fragmentation 较高，您可能需要调整 Query Cache 的大小以减少碎片。如果 QcacheDeleteRate 较高，您可能需要增加查询缓存的大小或优化查询以减少 INSERT 的数量。

轻松优化您的查询缓存

调整 MySQL 查询缓存涉及调整多项设置以优化数据库性能，从管理内存使用到减少查询时间。虽然这里讨论的变量形成了良好的基础，但有效的管理需要根据实际系统负载和性能进行持续监控和更新。

为了简化此过程，请考虑使用像 Releem 这样强大的管理工具来自动执行这些调整。这样的工具可以持续监控您的系统性能并实时动态更新query_cache设置。

这让您有时间专注于更广泛的目标，同时 Releem 处理复杂的查询缓存优化。

以上是解鎖 MySQL 效能：掌握查詢快取優化的詳細內容。更多資訊請關注PHP中文網其他相關文章！