共計 8778 個字符，預計需要花費 22 分鐘才能閱讀完成。
本篇內容主要講解“什么是 MySQL 查詢緩存”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“什么是 MySQL 查詢緩存”吧!
QueryCache 介紹
MySQL 查詢緩 (QC：QueryCache) 在 MySQL 4.0.1 中引入，查詢緩存存儲 SELECT 語句的文本以及發(fā)送給客戶機的結果集，如果再次執(zhí)行相同的 SQL，Server 端將從查詢緩存中檢索結果返回給客戶端，而不是再次解析執(zhí)行 SQL，查詢緩存在 session 之間共享，因此，一個客戶端生成的緩存結果集，可以響應另一個客戶端執(zhí)行同樣的 SQL。
回到開頭的問題，如何判斷 SQL 是否共享?
通過 SQL 文本是否完全一致來判斷，包括大小寫，空格等所有字符完全一模一樣才可以共享，共享好處是可以避免硬解析，直接從 QC 獲取結果返回給客戶端，下面的兩個 SQL 是不共享滴，因為一個是 from，另一個是 From。
--SQL 1 select id, balance from account where id = 121; --SQL 2 select id, balance From account where id = 121;下面是 Oracle 數據庫通過 SQL_TEXT 生成 sql_id 的算法，如果 sql_id 不一樣說明就不是同一個 SQL，就不共享，就會發(fā)生硬解析。
#!/usr/bin/perl -w use Digest::MD5 qw(md5 md5_hex md5_base64); use Math::BigInt; my $stmt = select id, balance from account where id = 121\0 my $hash = md5 $stmt; my($a,$b,$msb,$lsb) = unpack(V* ,$hash); my $sqln = $msb*(2**32)+$lsb; my $stop = log($sqln) / log(32) + 1; my $sqlid = my $charbase32 = 0123456789abcdfghjkmnpqrstuvwxyz my @chars = split , $charbase32; for($i=0; $i $stop-1; $i++){ my $x = Math::BigInt- new($sqln); my $seq = $x- bdiv(32**$i)- bmod(32); $sqlid = $chars[$seq].$sqlid; } print SQL is:\n $stmt \nSQL_ID is\n $sqlid\n大家可以發(fā)現 SQL 1 和 SQL 2 通過代碼生成的 sql_id 值是不一樣，所以不共享。
SQL is: select id, balance from account where id = 121 SQL_ID is dm5c6ck1g7bds SQL is: select id, balance From account where id = 121 SQL_ID is 6xb8gvs5cmc9b如果讓你比較兩個 Java 代碼文件的內容的有何差異，只需要將這段代碼理解透了，就可以改造實現自己的業(yè)務邏輯。
QueryCache 配置
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 | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF |
query_cache_min_res_unit 說明
默認大小是 4KB，如果有很多查詢結果很小，那么默認數據塊大小可能會導致內存碎片，由于內存不足，碎片可能會強制查詢緩存從緩存中刪除查詢。
在這種情況下，可以減小 query_cache_min_res_unit 的值，由于修剪而刪除的空閑塊和查詢的數量由 Qcache_free_blocks 和 Qcache_lowmem_prunes 狀態(tài)變量的值給出，如果大量的查詢有較大的結果集，可以增大該參數的值來提高性能。
通常開啟 QueryCache 方式
# 修改 MySQL 配置文件 /etc/my.cnf，添加如下配置，重啟 MySQL server 即可。 [mysqld] query_cache_size = 32M query_cache_type = 1QueryCache 使用
先搞點測試數據，分別對禁用和開啟 QueryCache 下的場景進行測試。
-- 創(chuàng)建一個用戶表 users，并且插入 100w 數據。 CREATE TABLE `users` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT COMMENT 姓名 , `age` tinyint NOT NULL DEFAULT 0 COMMENT age , `gender` char(1) NOT NULL DEFAULT M COMMENT 性別 , `phone` varchar(16) NOT NULL DEFAULT COMMENT 手機號 , `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間 , `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改時間 , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT= 用戶信息表 select count(*) from users; +----------+ | count(*) | +----------+ | 1000000 |禁用 queryCache 場景
在不使用 QueryCache 的時候，每次執(zhí)行相同的查詢語句，都要發(fā)生一次硬解析，消耗大量的資源。
# 禁用 QueryCache 的配置 query_cache_size = 0 query_cache_type = 0重復執(zhí)行下面查詢，觀察執(zhí)行時間。
-- 第一次執(zhí)行查詢語句 mysql select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.89 sec) -- 第二次執(zhí)行同樣的查詢語句 mysql select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.90 sec) -- profile 跟蹤情況 mysql show profile cpu,block io for query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | preparing | 0.000022 | 0.000017 | 0.000004 | 0 | 0 | | Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 | | executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 | | Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 | | Creating sort index | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |可以看到，多次執(zhí)行同樣的 SQL 查詢語句，執(zhí)行時間都是 0.89s 左右，幾乎沒有差別，同時時間主要消耗在 Creating sort index 階段。
開啟 queryCache 場景
開啟查詢緩存時，查詢語句第一次被執(zhí)行時會將 SQL 文本及查詢結果緩存在 QC 中，下一次執(zhí)行同樣的 SQL 執(zhí)行從 QC 中獲取數據返回給客戶端即可。
# 禁用 QueryCache 的配置
query_cache_size = 32M
query_cache_type = 1
-- 第一次執(zhí)行查詢語句 mysql select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.89 sec) -- 第二次執(zhí)行查詢語句 mysql select * from users order by create_time desc limit 10; +---------+------------+-----+--------+-------------+---------------------+---------------------+ | id | name | age | gender | phone | create_time | update_time | +---------+------------+-----+--------+-------------+---------------------+---------------------+ | 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 | ....... 10 rows in set (0.00 sec) -- profile 跟蹤數據 mysql show profile cpu,block io for query 3; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | Waiting for query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 | | checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 | | checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 | | checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 | | sending cached result to clien | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |可以看到，第一次執(zhí)行 QueryCache 里沒有緩存 SQL 文本及數據，執(zhí)行時間 0.89s，由于開啟了 QC，SQL 文本及執(zhí)行結果被緩存在 QC 中，第二次執(zhí)行執(zhí)行同樣的 SQL 查詢語句，直接命中 QC 且返回數據，不需要發(fā)生硬解析，所以執(zhí)行時間降低為 0s，從 profile 里看到 sending cached result to client 直接發(fā)送 QC 中的數據返回給客戶端。
查詢緩存命中率
查詢緩存相關的 status 變量
mysql SHOW GLOBAL STATUS LIKE QCache\_% +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | -- 查詢緩存中可用內存塊的數目。 | Qcache_free_memory | 33268592 | -- 查詢緩存的可用內存量。 | Qcache_hits | 121 | -- 從 QC 中獲取結果集的次數。 | Qcache_inserts | 91 | -- 將查詢結果集添加到 QC 的次數，意味著查詢已經不在 QC 中。 | Qcache_lowmem_prunes | 0 | -- 由于內存不足而從查詢緩存中刪除的查詢數。 | Qcache_not_cached | 0 | -- 未緩存的查詢數目。 | Qcache_queries_in_cache | 106 | -- 在查詢緩存中注冊的查詢數。 | Qcache_total_blocks | 256 | -- 查詢緩存中的塊總數。查詢緩存命中率及平均大小
Qcache_hits Query cache hit rate = ------------------------------------------------ x 100% Qcache_hits + Qcache_inserts + Qcache_not_cached query_cache_size = Qcache_free_memory Query Cache Avg Query Size = --------------------------------------- Qcache_queries_in_cache更新操作對 QC 影響
舉個例子，支付系統的里轉賬邏輯，先要鎖定賬戶再修改余額，主要步驟如下：
對于這種情況來說，QC 是不太適合的，因為第一次執(zhí)行查詢 SQL 未命中，返回結果給客戶端，添加 SQL 文本及結果集到 QC 之后，下一次執(zhí)行同樣的 SQL 直接從 QC 返回結果，不需要硬解析操作，但是每次 Update 都是先更新數據，然后鎖定 QC 然后更新緩存結果，會導致之前的緩存結果失效，再次執(zhí)行相的查詢 SQL 還是未命中，有得重新添加到 QC，這樣頻繁的鎖定 QC- 檢查 QC- 添加 QC- 更新 QC 非常消耗資源，降低數據庫的并發(fā)處理能力。
為何放棄 QueryCache
一般業(yè)務場景
從業(yè)務系統的操作類型，可以分為 OLTP(OnLine Transaction Processing 聯機事務處理系統)和 OLAP(OnLine Analysis Processing 聯機分析處理系統)，對于政企業(yè)務，也可以分為 BOSS(Business Operation Support System- 業(yè)務操作支撐系統，簡稱業(yè)支)和 BASS(Business Analysis Support System- 業(yè)務分析支撐系統，簡稱經分)，來總結下這兩類系統的特點。
適合 QueryCache 的場景
首先，查詢緩存 QC 的大小只有幾 MB，不適合將緩存設置得太大，由于在更新過程中需要線程鎖定 QueryCache，因此對于非常大的緩存，可能會看到鎖爭用問題。那么，哪些情況有助于從查詢緩存中獲益呢? 以下是理想條件：
鴻蒙官方戰(zhàn)略合作共建——HarmonyOS 技術社區(qū)
相同的查詢是由相同或多個客戶機重復發(fā)出的。
被訪問的底層數據本質上是靜態(tài)或半靜態(tài)的。
查詢有可能是資源密集型和 / 或構建簡短但計算復雜的結果集，同時結果集比較小。
并發(fā)性和查詢 QPS 都不高。
這 4 種情況只是理想情況下，實際的業(yè)務系統都是有 CRUD 操作的，數據更新比較頻繁，查詢接口的 QPS 比較高，所以能滿足上面的理想情況下的業(yè)務場景實在很少，我能想到就是配置表，數據字典表這些基本都是靜態(tài)或半靜態(tài)的，可以時通過 QC 來提高查詢效率。
不適合 QueryCache 的場景
如果表數據變化很快，則查詢緩存將失效，并且由于不斷從緩存中刪除查詢，從而使服務器負載升高，處理速度變得更慢，如果數據每隔幾秒鐘更新一次或更加頻繁，則查詢緩存不太可能合適。
同時，查詢緩存使用單個互斥體來控制對緩存的訪問，實際上是給服務器 SQL 處理引擎強加了一個單線程網關，在查詢 QPS 比較高的情況下，可能成為一個性能瓶頸，會嚴重降低查詢的處理速度。因此，MySQL 5.6 中默認禁用了查詢緩存。
刪除 QueryCache
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type，可以看到從 MySQL 5.6 的默認禁用，5.7 的廢棄以及 8.0 的徹底刪除，Oracle 也是綜合了各方面考慮做出了這樣的選擇。
上面聊了下適合和不適合的 QueryCache 的業(yè)務場景，發(fā)現這個特性對業(yè)務場景要求過于苛刻，與實際業(yè)務很難吻合，而且開啟之后，對數據庫并發(fā)度和處理能力都會降低很多，下面總結下為何 MySQL 從 Disabled- Deprecated- Removed QueryCache 的主要原因。
同時查詢緩存碎片化還會導致服務器的負載升高，影響數據庫的穩(wěn)定性，在 Oracle 官方搜索 QueryCache 可以發(fā)現，有很多 Bug 存在，這也就決定了 MySQL 8.0 直接果斷的 Remove 了該特性。
到此，相信大家對“什么是 MySQL 查詢緩存”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！
