我們知道，緩存的設計思想在RDBMS數據庫中無處不在，就拿號稱2500w行代碼，bug堆積如山的Oracle數據庫來說，SQL的執行計劃可以緩存在library cache中避免再次執行相同SQL發生硬解析(語法分析->語義分析->生成執行計劃)，SQL執行結果緩存在RESULT CACHE內存組件中，有效的將物理IO轉化成邏輯IO，提高SQL執行效率。

MySQL的QueryCache跟Oracle類似，緩存的是SQL語句文本以及對應的結果集，看起來是一個很棒的Idea，那為什么從MySQL 4.0推出之后，5.6中默認禁用，5.7中被deprecated(廢棄)以及8.0版本被Removed，今天就聊聊MySQL QueryCache的前世今生。

QueryCache介紹

MySQL查詢緩(QC：QueryCache)在MySQL 4.0.1中引入，查詢緩存存儲SELECT語句的文本以及發送給客戶機的結果集，如果再次執行相同的SQL，Server端將從查詢緩存中檢索結果返回給客戶端，而不是再次解析執行SQL，查詢緩存在session之間共享，因此，一個客戶端生成的緩存結果集，可以響應另一個客戶端執行同樣的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，就不共享，就會發生硬解析。

#!/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"; 

大家可以發現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代碼文件的內容的有何差異，只需要將這段代碼理解透了，就可以改造實現自己的業務邏輯。

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狀態變量的值給出，如果大量的查詢有較大的結果集，可以增大該參數的值來提高性能。

通常開啟QueryCache方式

# 修改MySQL配置文件/etc/my.cnf，添加如下配置，重啟MySQL server即可。 
[mysqld] 
query_cache_size = 32M 
query_cache_type = 1 

QueryCache使用

先搞點測試數據，分別對禁用和開啟QueryCache下的場景進行測試。

--創建一個用戶表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 '創建時間', 
  `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的時候，每次執行相同的查詢語句，都要發生一次硬解析，消耗大量的資源。

#禁用QueryCache的配置 
query_cache_size = 0 
query_cache_type = 0 

重復執行下面查詢，觀察執行時間。

--第一次執行查詢語句 
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) 
--第二次執行同樣的查詢語句 
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 | 

可以看到，多次執行同樣的SQL查詢語句，執行時間都是0.89s左右，幾乎沒有差別，同時時間主要消耗在Creating sort index階段。

開啟queryCache場景

開啟查詢緩存時，查詢語句第一次被執行時會將SQL文本及查詢結果緩存在QC中，下一次執行同樣的SQL執行從QC中獲取數據返回給客戶端即可。

#禁用QueryCache的配置 
query_cache_size = 32M 
query_cache_type = 1 

--第一次執行查詢語句 
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) 
--第二次執行查詢語句 
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 | 

可以看到，第一次執行QueryCache里沒有緩存SQL文本及數據，執行時間0.89s，由于開啟了QC，SQL文本及執行結果被緩存在QC中，第二次執行執行同樣的SQL查詢語句，直接命中QC且返回數據，不需要發生硬解析，所以執行時間降低為0s，從profile里看到sending cached result to client直接發送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是不太適合的，因為第一次執行查詢SQL未命中，返回結果給客戶端，添加SQL文本及結果集到QC之后，下一次執行同樣的SQL直接從QC返回結果，不需要硬解析操作，但是每次Update都是先更新數據，然后鎖定QC然后更新緩存結果，會導致之前的緩存結果失效，再次執行相的查詢SQL還是未命中，有得重新添加到QC，這樣頻繁的鎖定QC->檢查QC->添加QC->更新QC非常消耗資源，降低數據庫的并發處理能力。

為何放棄QueryCache

一般業務場景

從業務系統的操作類型，可以分為OLTP(OnLine Transaction Processing 聯機事務處理系統)和OLAP(OnLine Analysis Processing聯機分析處理系統)，對于政企業務，也可以分為BOSS(Business Operation Support System-業務操作支撐系統，簡稱業支)和BASS(Business Analysis Support System-業務分析支撐系統，簡稱經分)，來總結下這兩類系統的特點。 

適合QueryCache的場景

首先，查詢緩存QC的大小只有幾MB，不適合將緩存設置得太大，由于在更新過程中需要線程鎖定QueryCache，因此對于非常大的緩存，可能會看到鎖爭用問題。那么，哪些情況有助于從查詢緩存中獲益呢?以下是理想條件：

1. 相同的查詢是由相同或多個客戶機重復發出的。
2. 被訪問的底層數據本質上是靜態或半靜態的。
3. 查詢有可能是資源密集型和/或構建簡短但計算復雜的結果集，同時結果集比較小。
4. 并發性和查詢QPS都不高。

這4種情況只是理想情況下，實際的業務系統都是有CRUD操作的，數據更新比較頻繁，查詢接口的QPS比較高，所以能滿足上面的理想情況下的業務場景實在很少，我能想到就是配置表，數據字典表這些基本都是靜態或半靜態的，可以時通過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的業務場景，發現這個特性對業務場景要求過于苛刻，與實際業務很難吻合，而且開啟之后，對數據庫并發度和處理能力都會降低很多，下面總結下為何MySQL從Disabled->Deprecated->Removed QueryCache的主要原因。

同時查詢緩存碎片化還會導致服務器的負載升高，影響數據庫的穩定性，在Oracle官方搜索QueryCache可以發現，有很多Bug存在，這也就決定了MySQL 8.0直接果斷的Remove了該特性。

總結

上面為大家介紹了MySQL QueryCache從推出->禁用->廢棄->刪除的心路歷程，設計之初是為了減少重復SQL查詢帶來的硬解析開銷，同時將物理IO轉化為邏輯IO，來提高SQL的執行效率，但是MySQL經過了多個版本的迭代，同時在硬件存儲發展之快的今天，QC幾乎沒有任何收益，而且還會降低數據庫并發處理能力，最終在8.0版本直接Removd掉了。

其實緩存設計思想在硬件和軟件領域無處不在，硬件方面：RAID卡，CPU都有自己緩存，軟件方面就太多了，OS的cache，數據庫的buffer pool以及Java程序的緩存，作為一名研發工程師，需要根據業務場景選擇合適緩存方案是非常重要的，如果都不合適，就需進行定制化開發緩存，來更好的Match自己的業務場景，今天就聊這么多，希望對大家有所幫助。