何為MySQL查詢緩存

本篇內(nèi)容主要講解“何為MySQL查詢緩存”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“何為MySQL查詢緩存”吧!

成都創(chuàng)新互聯(lián)為您提適合企業(yè)的網(wǎng)站設(shè)計(jì)?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強(qiáng)的網(wǎng)絡(luò)競(jìng)爭(zhēng)力！結(jié)合企業(yè)自身，進(jìn)行網(wǎng)站設(shè)計(jì)及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到做網(wǎng)站、成都網(wǎng)站建設(shè)， 我們的網(wǎng)頁(yè)設(shè)計(jì)師為您提供的解決方案。

何為MySQL查詢緩存

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

MySQL的QueryCache跟Oracle類似，緩存的是SQL語(yǔ)句文本以及對(duì)應(yīng)的結(jié)果集，看起來(lái)是一個(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語(yǔ)句的文本以及發(fā)送給客戶機(jī)的結(jié)果集，如果再次執(zhí)行相同的SQL，Server端將從查詢緩存中檢索結(jié)果返回給客戶端，而不是再次解析執(zhí)行SQL，查詢緩存在session之間共享，因此，一個(gè)客戶端生成的緩存結(jié)果集，可以響應(yīng)另一個(gè)客戶端執(zhí)行同樣的SQL。

何為MySQL查詢緩存

回到開(kāi)頭的問(wèn)題，如何判斷SQL是否共享?

通過(guò)SQL文本是否完全一致來(lái)判斷，包括大小寫，空格等所有字符完全一模一樣才可以共享，共享好處是可以避免硬解析，直接從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ù)庫(kù)通過(guò)SQL_TEXT生成sql_id的算法，如果sql_id不一樣說(shuō)明就不是同一個(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通過(guò)代碼生成的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_nameDescription
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ù)場(chǎng)景。
query_cache_size查詢緩存大小，單位Bytes，設(shè)置為0是禁用QueryCache，注意：不要將緩存的大小設(shè)置得太大，由于在更新過(guò)程中需要線程鎖定QueryCache，因此對(duì)于非常大的緩存，您可能會(huì)看到鎖爭(zhēng)用問(wèn)題。
query_cache_type當(dāng)query_cache_size>0；該變量影響qc如何工作，有三個(gè)取值0，1，2，0：禁止緩存或檢索緩存結(jié)果；1：?jiǎn)⒂镁彺妫琒ELECT SQL_NO_CACHE的語(yǔ)句除外；2：只緩存以SELECT SQL_CACHE開(kāi)頭的語(yǔ)句。

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ù)場(chǎng)景。

query_cache_size  查詢緩存大小，單位Bytes，設(shè)置為0是禁用QueryCache，注意：不要將緩存的大小設(shè)置得太大，由于在更新過(guò)程中需要線程鎖定QueryCache，因此對(duì)于非常大的緩存，您可能會(huì)看到鎖爭(zhēng)用問(wèn)題。

query_cache_type  當(dāng)query_cache_size>0;該變量影響qc如何工作，有三個(gè)取值0，1，2，0：禁止緩存或檢索緩存結(jié)果;1：?jiǎn)⒂镁彺?，SELECT  SQL_NO_CACHE的語(yǔ)句除外;2：只緩存以SELECT SQL_CACHE開(kāi)頭的語(yǔ)句。

query_cache_min_res_unit說(shuō)明

默認(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ù)的值來(lái)提高性能。

通常開(kāi)啟QueryCache方式

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

QueryCache使用

先搞點(diǎn)測(cè)試數(shù)據(jù)，分別對(duì)禁用和開(kāi)啟QueryCache下的場(chǎng)景進(jìn)行測(cè)試。

--創(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場(chǎng)景

在不使用QueryCache的時(shí)候，每次執(zhí)行相同的查詢語(yǔ)句，都要發(fā)生一次硬解析，消耗大量的資源。

何為MySQL查詢緩存

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

重復(fù)執(zhí)行下面查詢，觀察執(zhí)行時(shí)間。

--第一次執(zhí)行查詢語(yǔ)句 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í)行同樣的查詢語(yǔ)句 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查詢語(yǔ)句，執(zhí)行時(shí)間都是0.89s左右，幾乎沒(méi)有差別，同時(shí)時(shí)間主要消耗在Creating sort index階段。

開(kāi)啟queryCache場(chǎng)景

開(kāi)啟查詢緩存時(shí)，查詢語(yǔ)句第一次被執(zhí)行時(shí)會(huì)將SQL文本及查詢結(jié)果緩存在QC中，下一次執(zhí)行同樣的SQL執(zhí)行從QC中獲取數(shù)據(jù)返回給客戶端即可。

何為MySQL查詢緩存

#禁用QueryCache的配置 query_cache_size = 32M query_cache_type = 1
--第一次執(zhí)行查詢語(yǔ)句 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í)行查詢語(yǔ)句 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里沒(méi)有緩存SQL文本及數(shù)據(jù)，執(zhí)行時(shí)間0.89s，由于開(kāi)啟了QC，SQL文本及執(zhí)行結(jié)果被緩存在QC中，第二次執(zhí)行執(zhí)行同樣的SQL查詢語(yǔ)句，直接命中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)賬邏輯，先要鎖定賬戶再修改余額，主要步驟如下：

何為MySQL查詢緩存

Query_IDQueryDescription
1reset 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ì)于這種情況來(lái)說(shuō)，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ù)庫(kù)的并發(fā)處理能力。

為何放棄QueryCache

一般業(yè)務(wù)場(chǎng)景

從業(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)，簡(jiǎn)稱業(yè)支)和BASS(Business Analysis Support  System-業(yè)務(wù)分析支撐系統(tǒng)，簡(jiǎn)稱經(jīng)分)，來(lái)總結(jié)下這兩類系統(tǒng)的特點(diǎn)。

何為MySQL查詢緩存

適合QueryCache的場(chǎng)景

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

  • 相同的查詢是由相同或多個(gè)客戶機(jī)重復(fù)發(fā)出的。

  • 被訪問(wèn)的底層數(shù)據(jù)本質(zhì)上是靜態(tài)或半靜態(tài)的。

  • 查詢有可能是資源密集型和/或構(gòu)建簡(jiǎn)短但計(jì)算復(fù)雜的結(jié)果集，同時(shí)結(jié)果集比較小。

  • 并發(fā)性和查詢QPS都不高。

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

不適合QueryCache的場(chǎng)景

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

同時(shí)，查詢緩存使用單個(gè)互斥體來(lái)控制對(duì)緩存的訪問(wèn)，實(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ù)場(chǎng)景，發(fā)現(xiàn)這個(gè)特性對(duì)業(yè)務(wù)場(chǎng)景要求過(guò)于苛刻，與實(shí)際業(yè)務(wù)很難吻合，而且開(kāi)啟之后，對(duì)數(shù)據(jù)庫(kù)并發(fā)度和處理能力都會(huì)降低很多，下面總結(jié)下為何MySQL從Disabled->Deprecated->Removed  QueryCache的主要原因。

何為MySQL查詢緩存

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

到此，相信大家對(duì)“何為MySQL查詢緩存”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

文章題目：何為MySQL查詢緩存
網(wǎng)頁(yè)路徑：http://bm7419.com/article12/ipoddc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、網(wǎng)站排名、面包屑導(dǎo)航、品牌網(wǎng)站建設(shè)、網(wǎng)站策劃、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

網(wǎng)站優(yōu)化排名