共計(jì) 10243 個(gè)字符，預(yù)計(jì)需要花費(fèi) 26 分鐘才能閱讀完成。
本篇內(nèi)容主要講解“何為 MySQL 查詢緩存”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓丸趣 TV 小編來帶大家學(xué)習(xí)“何為 MySQL 查詢緩存”吧!
我們知道，緩存的設(shè)計(jì)思想在 RDBMS 數(shù)據(jù)庫中無處不在，就拿號(hào)稱 2500w 行代碼，bug 堆積如山的 Oracle 數(shù)據(jù)庫來說，SQL 的執(zhí)行計(jì)劃可以緩存在 library cache 中避免再次執(zhí)行相同 SQL 發(fā)生硬解析(語法分析 - 語義分析 - 生成執(zhí)行計(jì)劃)，SQL 執(zhí)行結(jié)果緩存在 RESULT CACHE 內(nèi)存組件中，有效的將物理 IO 轉(zhuǎn)化成邏輯 IO，提高 SQL 執(zhí)行效率。
MySQL 的 QueryCache 跟 Oracle 類似，緩存的是 SQL 語句文本以及對(duì)應(yīng)的結(jié)果集，看起來是一個(gè)很棒的 Idea，那為什么從 MySQL 4.0 推出之后，5.6 中默認(rèn)禁用，5.7 中被 deprecated(廢棄)以及 8.0 版本被 Removed，今天就聊聊 MySQL QueryCache 的前世今生。
QueryCache 介紹
MySQL 查詢緩 (QC：QueryCache) 在 MySQL 4.0.1 中引入，查詢緩存存儲(chǔ) SELECT 語句的文本以及發(fā)送給客戶機(jī)的結(jié)果集，如果再次執(zhí)行相同的 SQL，Server 端將從查詢緩存中檢索結(jié)果返回給客戶端，而不是再次解析執(zhí)行 SQL，查詢緩存在 session 之間共享，因此，一個(gè)客戶端生成的緩存結(jié)果集，可以響應(yīng)另一個(gè)客戶端執(zhí)行同樣的 SQL。
回到開頭的問題，如何判斷 SQL 是否共享?
通過 SQL 文本是否完全一致來判斷，包括大小寫，空格等所有字符完全一模一樣才可以共享，共享好處是可以避免硬解析，直接從 QC 獲取結(jié)果返回給客戶端，下面的兩個(gè) SQL 是不共享滴，因?yàn)橐粋€(gè)是 from，另一個(gè)是 From。
--SQL 1 select id, balance from account where id = 121; --SQL 2 select id, balance From account where id = 121;下面是 Oracle 數(shù)據(jù)庫通過 SQL_TEXT 生成 sql_id 的算法，如果 sql_id 不一樣說明就不是同一個(gè) SQL，就不共享，就會(huì)發(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ā)現(xiàn) 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如果讓你比較兩個(gè) Java 代碼文件的內(nèi)容的有何差異，只需要將這段代碼理解透了，就可以改造實(shí)現(xiàn)自己的業(yè)務(wù)邏輯。
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 |Variable_nameDescriptionhave_query_cache 查詢緩存是否可用，YES- 可用；NO- 不可用，如果用標(biāo)準(zhǔn)二進(jìn)制 MySQL，值總是 YES。query_cache_limit 控制單個(gè)查詢結(jié)果集的最大尺寸，默認(rèn)是 1MB。query_cache_min_res_unit 查詢緩存分片數(shù)據(jù)塊的大小，默認(rèn)是 4KB，可以滿足大部分業(yè)務(wù)場景。query_cache_size 查詢緩存大小，單位 Bytes，設(shè)置為 0 是禁用 QueryCache，注意：不要將緩存的大小設(shè)置得太大，由于在更新過程中需要線程鎖定 QueryCache，因此對(duì)于非常大的緩存，您可能會(huì)看到鎖爭用問題。query_cache_type 當(dāng) query_cache_size 0；該變量影響 qc 如何工作，有三個(gè)取值 0，1，2，0：禁止緩存或檢索緩存結(jié)果；1：啟用緩存，SELECT SQL_NO_CACHE 的語句除外；2：只緩存以 SELECT SQL_CACHE 開頭的語句。
Variable_name Description
have_query_cache 查詢緩存是否可用，YES- 可用;NO- 不可用，如果用標(biāo)準(zhǔn)二進(jìn)制 MySQL，值總是 YES。
query_cache_limit 控制單個(gè)查詢結(jié)果集的最大尺寸，默認(rèn)是 1MB。
query_cache_min_res_unit 查詢緩存分片數(shù)據(jù)塊的大小，默認(rèn)是 4KB，可以滿足大部分業(yè)務(wù)場景。
query_cache_size 查詢緩存大小，單位 Bytes，設(shè)置為 0 是禁用 QueryCache，注意：不要將緩存的大小設(shè)置得太大，由于在更新過程中需要線程鎖定 QueryCache，因此對(duì)于非常大的緩存，您可能會(huì)看到鎖爭用問題。
query_cache_type 當(dāng) query_cache_size 該變量影響 qc 如何工作，有三個(gè)取值 0，1，2，0：禁止緩存或檢索緩存結(jié)果;1：啟用緩存，SELECT SQL_NO_CACHE 的語句除外;2：只緩存以 SELECT SQL_CACHE 開頭的語句。
query_cache_min_res_unit 說明
默認(rèn)大小是 4KB，如果有很多查詢結(jié)果很小，那么默認(rèn)數(shù)據(jù)塊大小可能會(huì)導(dǎo)致內(nèi)存碎片，由于內(nèi)存不足，碎片可能會(huì)強(qiáng)制查詢緩存從緩存中刪除查詢。
在這種情況下，可以減小 query_cache_min_res_unit 的值，由于修剪而刪除的空閑塊和查詢的數(shù)量由 Qcache_free_blocks 和 Qcache_lowmem_prunes 狀態(tài)變量的值給出，如果大量的查詢有較大的結(jié)果集，可以增大該參數(shù)的值來提高性能。
通常開啟 QueryCache 方式
# 修改 MySQL 配置文件 /etc/my.cnf，添加如下配置，重啟 MySQL server 即可。 [mysqld] query_cache_size = 32M query_cache_type = 1QueryCache 使用
先搞點(diǎn)測試數(shù)據(jù)，分別對(duì)禁用和開啟 QueryCache 下的場景進(jìn)行測試。
-- 創(chuàng)建一個(gè)用戶表 users，并且插入 100w 數(shù)據(jù)。 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 手機(jī)號(hào) , `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間 , `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改時(shí)間 , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT= 用戶信息表 select count(*) from users; +----------+ | count(*) | +----------+ | 1000000 |禁用 queryCache 場景
在不使用 QueryCache 的時(shí)候，每次執(zhí)行相同的查詢語句，都要發(fā)生一次硬解析，消耗大量的資源。
# 禁用 QueryCache 的配置 query_cache_size = 0 query_cache_type = 0重復(fù)執(zhí)行下面查詢，觀察執(zhí)行時(shí)間。
-- 第一次執(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í)行時(shí)間都是 0.89s 左右，幾乎沒有差別，同時(shí)時(shí)間主要消耗在 Creating sort index 階段。
開啟 queryCache 場景
開啟查詢緩存時(shí)，查詢語句第一次被執(zhí)行時(shí)會(huì)將 SQL 文本及查詢結(jié)果緩存在 QC 中，下一次執(zhí)行同樣的 SQL 執(zhí)行從 QC 中獲取數(shù)據(jù)返回給客戶端即可。
# 禁用 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 跟蹤數(shù)據(jù) 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 文本及數(shù)據(jù)，執(zhí)行時(shí)間 0.89s，由于開啟了 QC，SQL 文本及執(zhí)行結(jié)果被緩存在 QC 中，第二次執(zhí)行執(zhí)行同樣的 SQL 查詢語句，直接命中 QC 且返回?cái)?shù)據(jù)，不需要發(fā)生硬解析，所以執(zhí)行時(shí)間降低為 0s，從 profile 里看到 sending cached result to client 直接發(fā)送 QC 中的數(shù)據(jù)返回給客戶端。
查詢緩存命中率
查詢緩存相關(guān)的 status 變量
mysql SHOW GLOBAL STATUS LIKE QCache\_% +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | -- 查詢緩存中可用內(nèi)存塊的數(shù)目。 | Qcache_free_memory | 33268592 | -- 查詢緩存的可用內(nèi)存量。 | Qcache_hits | 121 | -- 從 QC 中獲取結(jié)果集的次數(shù)。 | Qcache_inserts | 91 | -- 將查詢結(jié)果集添加到 QC 的次數(shù)，意味著查詢已經(jīng)不在 QC 中。 | Qcache_lowmem_prunes | 0 | -- 由于內(nèi)存不足而從查詢緩存中刪除的查詢數(shù)。 | Qcache_not_cached | 0 | -- 未緩存的查詢數(shù)目。 | Qcache_queries_in_cache | 106 | -- 在查詢緩存中注冊(cè)的查詢數(shù)。 | Qcache_total_blocks | 256 | -- 查詢緩存中的塊總數(shù)。查詢緩存命中率及平均大小
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更新操作對(duì) QC 影響
舉個(gè)例子，支付系統(tǒng)的里轉(zhuǎn)賬邏輯，先要鎖定賬戶再修改余額，主要步驟如下：
Query_IDQueryDescription1reset query cache 清空查詢緩存。2select balance from account where id = 121 第一次執(zhí)行，未命中 QC，添加到 QC。3select balance from account where id = 121 命中 QC，直接返回結(jié)果。4update account set balance = balance – 1000 where id = 121 更新，鎖定 query cche 進(jìn)行更新，緩存數(shù)據(jù)失效。5select balance from account where id = 121 緩存已失效，未命中，添加到 QC。6select balance from account where id = 121 命中 QC，直接返回結(jié)果。
對(duì)于這種情況來說，QC 是不太適合的，因?yàn)榈谝淮螆?zhí)行查詢 SQL 未命中，返回結(jié)果給客戶端，添加 SQL 文本及結(jié)果集到 QC 之后，下一次執(zhí)行同樣的 SQL 直接從 QC 返回結(jié)果，不需要硬解析操作，但是每次 Update 都是先更新數(shù)據(jù)，然后鎖定 QC 然后更新緩存結(jié)果，會(huì)導(dǎo)致之前的緩存結(jié)果失效，再次執(zhí)行相的查詢 SQL 還是未命中，有得重新添加到 QC，這樣頻繁的鎖定 QC- 檢查 QC- 添加 QC- 更新 QC 非常消耗資源，降低數(shù)據(jù)庫的并發(fā)處理能力。
為何放棄 QueryCache
一般業(yè)務(wù)場景
從業(yè)務(wù)系統(tǒng)的操作類型，可以分為 OLTP(OnLine Transaction Processing 聯(lián)機(jī)事務(wù)處理系統(tǒng))和 OLAP(OnLine Analysis Processing 聯(lián)機(jī)分析處理系統(tǒng))，對(duì)于政企業(yè)務(wù)，也可以分為 BOSS(Business Operation Support System- 業(yè)務(wù)操作支撐系統(tǒng)，簡稱業(yè)支)和 BASS(Business Analysis Support System- 業(yè)務(wù)分析支撐系統(tǒng)，簡稱經(jīng)分)，來總結(jié)下這兩類系統(tǒng)的特點(diǎn)。
適合 QueryCache 的場景
首先，查詢緩存 QC 的大小只有幾 MB，不適合將緩存設(shè)置得太大，由于在更新過程中需要線程鎖定 QueryCache，因此對(duì)于非常大的緩存，可能會(huì)看到鎖爭用問題。那么，哪些情況有助于從查詢緩存中獲益呢? 以下是理想條件：
相同的查詢是由相同或多個(gè)客戶機(jī)重復(fù)發(fā)出的。
被訪問的底層數(shù)據(jù)本質(zhì)上是靜態(tài)或半靜態(tài)的。
查詢有可能是資源密集型和 / 或構(gòu)建簡短但計(jì)算復(fù)雜的結(jié)果集，同時(shí)結(jié)果集比較小。
并發(fā)性和查詢 QPS 都不高。
這 4 種情況只是理想情況下，實(shí)際的業(yè)務(wù)系統(tǒng)都是有 CRUD 操作的，數(shù)據(jù)更新比較頻繁，查詢接口的 QPS 比較高，所以能滿足上面的理想情況下的業(yè)務(wù)場景實(shí)在很少，我能想到就是配置表，數(shù)據(jù)字典表這些基本都是靜態(tài)或半靜態(tài)的，可以時(shí)通過 QC 來提高查詢效率。
不適合 QueryCache 的場景
如果表數(shù)據(jù)變化很快，則查詢緩存將失效，并且由于不斷從緩存中刪除查詢，從而使服務(wù)器負(fù)載升高，處理速度變得更慢，如果數(shù)據(jù)每隔幾秒鐘更新一次或更加頻繁，則查詢緩存不太可能合適。
同時(shí)，查詢緩存使用單個(gè)互斥體來控制對(duì)緩存的訪問，實(shí)際上是給服務(wù)器 SQL 處理引擎強(qiáng)加了一個(gè)單線程網(wǎng)關(guān)，在查詢 QPS 比較高的情況下，可能成為一個(gè)性能瓶頸，會(huì)嚴(yán)重降低查詢的處理速度。因此，MySQL 5.6 中默認(rèn)禁用了查詢緩存。
刪除 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 的默認(rèn)禁用，5.7 的廢棄以及 8.0 的徹底刪除，Oracle 也是綜合了各方面考慮做出了這樣的選擇。
上面聊了下適合和不適合的 QueryCache 的業(yè)務(wù)場景，發(fā)現(xiàn)這個(gè)特性對(duì)業(yè)務(wù)場景要求過于苛刻，與實(shí)際業(yè)務(wù)很難吻合，而且開啟之后，對(duì)數(shù)據(jù)庫并發(fā)度和處理能力都會(huì)降低很多，下面總結(jié)下為何 MySQL 從 Disabled- Deprecated- Removed QueryCache 的主要原因。
同時(shí)查詢緩存碎片化還會(huì)導(dǎo)致服務(wù)器的負(fù)載升高，影響數(shù)據(jù)庫的穩(wěn)定性，在 Oracle 官方搜索 QueryCache 可以發(fā)現(xiàn)，有很多 Bug 存在，這也就決定了 MySQL 8.0 直接果斷的 Remove 了該特性。
到此，相信大家對(duì)“何為 MySQL 查詢緩存”有了更深的了解，不妨來實(shí)際操作一番吧！這里是丸趣 TV 網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
