主頁 > 知識庫 > MySQL查詢緩存的小知識

MySQL查詢緩存的小知識

前言

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

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

QueryCache介紹

MySQL查詢緩（QC：QueryCache）在MySQL 4.0.1中引入，查詢緩存存儲SELECT語句的文本以及發(fā)送給客戶機的結(jié)果集，如果再次執(zhí)行相同的SQL，Server端將從查詢緩存中檢索結(jié)果返回給客戶端，而不是再次解析執(zhí)行SQL，查詢緩存在session之間共享，因此，一個客戶端生成的緩存結(jié)果集，可以響應(yīng)另一個客戶端執(zhí)行同樣的SQL。

回到開頭的問題，如何判斷SQL是否共享？

通過SQL文本是否完全一致來判斷，包括大小寫，空格等所有字符完全一模一樣才可以共享，共享好處是可以避免硬解析，直接從QC獲取結(jié)果返回給客戶端，下面的兩個SQL是不共享滴，因為一個是from，另一個是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不一樣說明就不是同一個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ā)現(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

如果讓你比較兩個Java代碼文件的內(nèi)容的有何差異，只需要將這段代碼理解透了，就可以改造實現(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_name	Description
have_query_cache	查詢緩存是否可用，YES-可用；NO-不可用，如果用標(biāo)準(zhǔn)二進制MySQL，值總是YES。
query_cache_limit	控制單個查詢結(jié)果集的最大尺寸，默認是1MB。
query_cache_min_res_unit	查詢緩存分片數(shù)據(jù)塊的大小，默認是4KB，可以滿足大部分業(yè)務(wù)場景。
query_cache_size	查詢緩存大小，單位Bytes，設(shè)置為0是禁用QueryCache，注意：不要將緩存的大小設(shè)置得太大，由于在更新過程中需要線程鎖定QueryCache，因此對于非常大的緩存，您可能會看到鎖爭用問題。
query_cache_type	當(dāng)query_cache_size>0；該變量影響qc如何工作，有三個取值0，1，2，0：禁止緩存或檢索緩存結(jié)果；1：啟用緩存，SELECT SQL_NO_CACHE的語句除外；2：只緩存以SELECT SQL_CACHE開頭的語句。

query_cache_min_res_unit說明

默認大小是4KB，如果有很多查詢結(jié)果很小，那么默認數(shù)據(jù)塊大小可能會導(dǎo)致內(nèi)存碎片，由于內(nèi)存不足，碎片可能會強制查詢緩存從緩存中刪除查詢。

在這種情況下，可以減小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 = 1

QueryCache使用

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

--創(chuàng)建一個用戶表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 '手機號',
 `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

重復(fù)執(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文本及查詢結(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í)行時間0.89s，由于開啟了QC，SQL文本及執(zhí)行結(jié)果被緩存在QC中，第二次執(zhí)行執(zhí)行同樣的SQL查詢語句，直接命中QC且返回數(shù)據(jù)，不需要發(fā)生硬解析，所以執(zhí)行時間降低為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  | --在查詢緩存中注冊的查詢數(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

更新操作對QC影響

舉個例子，支付系統(tǒng)的里轉(zhuǎn)賬邏輯，先要鎖定賬戶再修改余額，主要步驟如下：

Query_ID	Query	Description
1	reset query cache	清空查詢緩存。
2	select balance from account where id = 121	第一次執(zhí)行，未命中QC，添加到QC。
3	select balance from account where id = 121	命中QC，直接返回結(jié)果。
4	update account set balance = balance - 1000 where id = 121	更新，鎖定query cche進行更新，緩存數(shù)據(jù)失效。
5	select balance from account where id = 121	緩存已失效，未命中，添加到QC。
6	select balance from account where id = 121	命中QC，直接返回結(jié)果。

對于這種情況來說，QC是不太適合的，因為第一次執(zhí)行查詢SQL未命中，返回結(jié)果給客戶端，添加SQL文本及結(jié)果集到QC之后，下一次執(zhí)行同樣的SQL直接從QC返回結(jié)果，不需要硬解析操作，但是每次Update都是先更新數(shù)據(jù)，然后鎖定QC然后更新緩存結(jié)果，會導(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)機事務(wù)處理系統(tǒng)）和OLAP（OnLine Analysis Processing聯(lián)機分析處理系統(tǒng)），對于政企業(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)的特點。

適合QueryCache的場景

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

相同的查詢是由相同或多個客戶機重復(fù)發(fā)出的。
被訪問的底層數(shù)據(jù)本質(zhì)上是靜態(tài)或半靜態(tài)的。
查詢有可能是資源密集型和/或構(gòu)建簡短但計算復(fù)雜的結(jié)果集，同時結(jié)果集比較小。
并發(fā)性和查詢QPS都不高。

這4種情況只是理想情況下，實際的業(yè)務(wù)系統(tǒng)都是有CRUD操作的，數(shù)據(jù)更新比較頻繁，查詢接口的QPS比較高，所以能滿足上面的理想情況下的業(yè)務(wù)場景實在很少，我能想到就是配置表，數(shù)據(jù)字典表這些基本都是靜態(tài)或半靜態(tài)的，可以時通過QC來提高查詢效率。

不適合QueryCache的場景

如果表數(shù)據(jù)變化很快，則查詢緩存將失效，并且由于不斷從緩存中刪除查詢，從而使服務(wù)器負載升高，處理速度變得更慢，如果數(shù)據(jù)每隔幾秒鐘更新一次或更加頻繁，則查詢緩存不太可能合適。

同時，查詢緩存使用單個互斥體來控制對緩存的訪問，實際上是給服務(wù)器SQL處理引擎強加了一個單線程網(wǎng)關(guān)，在查詢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è)務(wù)場景，發(fā)現(xiàn)這個特性對業(yè)務(wù)場景要求過于苛刻，與實際業(yè)務(wù)很難吻合，而且開啟之后，對數(shù)據(jù)庫并發(fā)度和處理能力都會降低很多，下面總結(jié)下為何MySQL從Disabled->Deprecated->Removed QueryCache的主要原因。

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

總結(jié)

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

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

我是敖丙，你知道的越多，你不知道的越多，感謝各位人才的：點贊、收藏和評論，我們下期見！

以上就是MySQL查詢緩存的小知識的詳細內(nèi)容，更多關(guān)于MySQL查詢緩存的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

MySQL null的一些易錯點
MySQL SHOW STATUS語句的使用
Mysql技術(shù)內(nèi)幕之InnoDB鎖的深入講解
MySQL 主從同步,事務(wù)回滾的實現(xiàn)原理
MySQL 有關(guān)MHA搭建與切換的幾個錯誤log匯總
mysql 的indexof函數(shù)用法說明

標(biāo)簽：黃山仙桃湘潭衡水蘭州崇左湖南銅川

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL查詢緩存的小知識》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。