我们知道缓存的设计思想在RDBMS数据库中无处不在。以号称2500万行代码，bug成堆的Oracle数据库为例，可以缓存SQL执行计划，避免在库缓存中硬解析同一条SQL（语法分析->语义分析->生成执行计划），SQL执行结果缓存在RESULTCACHE内存组件中，有效地将物理IO转化为逻辑IO，提高SQL执行效率。MySQL的QueryCache类似于Oracle。它缓存SQL语句文本和相应的结果集。这似乎是个好主意。那为什么在MySQL4.0之后的5.6中默认禁用，而在5.7和8.0中deprecated（废弃）呢？版本已经Removed了，今天就来说说MySQLQueryCache的前世今生。QueryCache介绍MySQL查询缓存（QC：QueryCache）是在MySQL4.0.1中引入的。查询缓存存储SELECT语句的文本和发送给客户端的结果集。如果再次执行相同的SQL，服务器将从查询缓存中检索结果。返回到客户端，不再重新解析和执行SQL，查询缓存在会话之间共享，因此一个客户端生成的缓存结果集可以响应另一个客户端执行相同的SQL。回到开头的问题，如何判断SQL是否共享？判断SQL文本是否完全一致，包括大小写、空格等所有字符，都可以共享。共享的好处是可以避免硬解析，直接从QC获取结果返回给Client，下面两条SQL就不共享了，因为一个是from，一个是from。--SQL1selectid,balancefromaccountwhereid=121;--SQL2selectid,balanceFromaccountwhereid=121;下面是Oracle数据库通过SQL_TEXT生成sql_id的算法。如果sql_id不同，说明不是同一条sql，不会共享，会发生硬解析。#!/usr/bin/perl-wuseDigest::MD5qw(md5md5_hexmd5_base64);useMath::BigInt;my$stmt="selectid,balancefromaccountwhereid=121\0";my$hash=md5$stmt;my($a,$b,$msb,$lsb)=解包("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"SQLis:\n$stmt\nSQL_IDis\n$sqlid\n";可以发现SQL1和SQL2中代码生成的sql_id值不同，所以不共享。SQLis:selectid,balancefromaccountwhereid=121SQL_IDisdm5c6ck1g7bdsSQLis:selectid,balanceFromaccountwhereid=121SQL_IDis6xb8gvs5cmc9b如果让你比较两个Java代码文件内容的差异，你只需要看透这段代码，就可以改造实现自己的业务逻辑。QueryCache配置mysql>showvariablelike'%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服务器。[mysqld]query_cache_size=32Mquery_cache_type=1先使用QueryCache获取一些测试数据，分别测试QueryCachedisabled和enabled下的场景。--创建用户表users，插入100w条数据。创建表`users`(`id`bigintNOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULLDEFAULT''COMMENT'name',`age`tinyintNOTNULLDEFAULT'0'COMMENT'age',`gender`char(1)NOTNULLDEFAULT'M'COMMENT'性别',`phone`varchar(16)NOTNULLDEFAULT''COMMENT'手机号',`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户信息表';selectcount(*)fromusers;+------------+|count(*)|+--------+|1000000|DisablequeryCache场景是不使用QueryCache的时候，每次执行同一条查询语句，都会发生硬解析，消耗大量资源。#禁用QueryCache的配置query_cache_size=0query_cache_type=0重复下面的查询，观察执行时间。--第一次执行查询语句mysql>select*fromusersorderbycreate_timedesclimit10;+--------+------------+-----+--------+------------+--------------------+--------------------+|id|name|age|gender|phone|create_time|update_time|+--------+-------------+------+--------+------------+----------------+----------------------+|997855|User997854|54|M|15240540354|2020-12-1514:34:50|2020-12-1514:34:50|.......10rowsinset(0.89sec)--第二次执行同一条查询语句mysql>select*fromuserorderbycreate_timedesclimit10;+--------+-------------+-----+--------+------------+--------------------+--------------------+|id|name|age|gender|phone|create_time|update_time|+---------+------------+-----+--------+------------+------------------+--------------------+|997855|用户997854|54|M|15240540354|2020-12-1514:34:50|2020-12-1514:34:50|......10rowsinset(0.90sec)--profiletrackingmysql>showprofilecpu,blockioforquery1;+-------------------+------------+--------+-----------+----------------+----------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+--------------------+----------+------------+------------+------------+--------------+|准备|0.000022|0.000017|0.000004|0|0||排序结果|0.000014|0.000009|0.000005|0|0||执行|0.000011|0.000007|0.000004|0|0||Sendingdata|0.000021|0.000016|0.000004|0|0||Creatingsortindex|0.906290|0.826584|0.000000|0|0|可以看出，同一条SQL查询语句的执行时间约为0.89s，几乎没有在同时，时间主要消耗在创建排序索引阶段。当开启queryCache场景并开启查询缓存时，第一次执行查询语句时会将SQL文本和查询结果缓存在QC中，下次执行相同的SQL时获取从质量控制。数据返回给客户端。#禁用QueryCache的配置query_cache_size=32Mquery_cache_type=1--第一次执行查询语句mysql>select*fromusersorderbycreate_timedesclimit10;+--------+------------+-----+--------+------------+-------------------+-------------------+|id|name|age|gender|phone|create_time|update_time|+--------+-----------+-----+--------+------------+-----------------------+--------------------+|997855|User997854|54|M|15240540354|2020-12-1514:34:50|2020-12-1514:34:50|......10rowsinset(0.89sec)--第二次执行查询语句mysql>select*fromusersorderbycreate_timedesclimit10;+---------+------------+-----+--------+------------+---------------------+--------------------+|id|姓名|年龄|性别|电话|创建时间|更新时间|+--------+------------+-----+--------+------------+--------------------+--------------------+|997855|User997854|54|M|15240540354|2020-12-1514:34:50|2020-12-1514:34:50|......10rowsinset(0.00sec)--profile跟踪数据mysql>showprofilecpu,blockioforquery3;+-------------------------------+------------+------------+------------+-------------+----------------+|状态|持续时间|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+------------------------------+------------+--------+------------+------------+--------------+|Waitingforquerycachelock|0.000016|0.000015|0.000001|0|0||checkingquerycacheforquery|0.000007|0.000007|0.000000|0|0|0|3|3|0.000004|0.000004|0.000003|0.000000|0.000000|0|0|0|3|3.000034|0.000034|0.000034|0.000033|0.000001|0.000001|0|0|0|sendingcachedcachedCachedCachedCachedCachEdtoclien|0.00000001|0.00000017|0.000000|0.000000|0.000017结果发现，第一次执行SQL文本和数据并没有缓存在QueryCache中，执行时间为0.89s。因为启用了QC，所以SQL文本和执行结果都缓存在QC中。第二次执行执行相同的SQL查询语句，直接命中QC。并且返回的数据不需要硬解析，所以执行时间减少到0s。从profile可以看出，发送缓存结果给client是直接将QC中的数据发送给client返回给client查询缓存命中率。查询缓存相关状态变量mysql>SHOWGLOBALSTATUSLIKE'QCache\_%';+------------------------+-----------+|变量名|值|+------------------------+----------+|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_hitsQuerycachehitrate=---------------------------------------------------x100%Qcache_hits+Qcache_inserts+Qcache_not_cachedquery_cache_size=Qcache_free_memoryQueryCacheAvgQuerySize=--------------------------------------------Qcache_queries_in_cache更新操作对QC的影响比如在支付系统的转账逻辑中，必须先锁定账户，再修改余额。主要步骤如下：QC不适合这种情况，因为查询SQL不是第一次命中，返回结果给客户端。将SQL文本和结果集加入QC后，下次执行同样的SQL时，直接从QC返回结果。不需要硬解析操作，但每次Update都会先执行。更新数据，然后锁定QC再更新缓存的结果，会导致之前缓存的结果失效。再次执行对应的查询SQL还是miss，只好重新加入QC，所以频繁加锁QC->检查QC->添加QC->更新QC，消耗大量资源，降低并发数据库的处理能力。为什么放弃QueryCache一般的业务场景从业务系统的运行类型可以分为OLTP（OnLineTransactionProcessing在线事务处理系统）和OLAP（OnLineAnalysisProcessing在线分析处理系统）。对于政企业务，也可以分为BOSS（BusinessOperationSupportSystem-业务运营支持系统，简称业智）和BASS（BusinessAnalysisSupportSystem-业务分析支持系统，简称精分），总结一下这两类系统的特点。适合使用QueryCache的场景一、查询缓存QC的大小只有几MB，不适合设置缓存过大。由于线程在更新过程中需要锁定QueryCache，对于非常大的缓存，您可能会看到锁争用问题。那么，哪些情况有助于从查询缓存中获益呢？以下是理想情况：同一个查询由同一个或多个客户端重复发出。被访问的底层数据本质上是静态或半静态的。查询可能是资源密集型的和/或使用相对较小的结果集构建简短但计算复杂的结果集。并发和查询QPS不高。这四种情况只是理想情况。实际业务系统存在CRUD操作，数据更新比较频繁，查??询接口QPS比较高，所以能够满足上述理想条件的业务场景非常少。我能想到的就是，配置表和数据字典表基本都是静态或者半静态的，有条件的时候可以使用QC来提高查询效率。不适合使用QueryCache的场景如果表数据变化很快，查询缓存就会失效，并且不断从缓存中删除查询会增加服务器负载，处理速度会变慢。如果数据每隔几秒或更频繁地更新一次，查询缓存就不太适用。同时，查询缓存使用单个互斥锁来控制对缓存的访问。实际上，它在服务器SQL处理引擎上强加了一个单线程网关。当查询QPS比较高时，可能会成为性能瓶颈，严重降低查询性能。处理速度。因此，查询缓存在MySQL5.6中默认是禁用的。删除QueryCache查询缓存从MySQL5.7.20开始被弃用，并在MySQL8.0中被移除。Deprecation包括query_cache_type，可以看出从MySQL5.6的默认disable、5.7的弃用和8.0的完全删除，Oracle也是综合考虑各方面做出了这样的选择。上面我们讲了适合和不适合的QueryCache业务场景，发现这个特性对业务场景的要求太高，很难和实际业务匹配。而且，启用后，数据库的并发量和处理能力都会大大降低。下面总结一下为什么MySQL从Disabled->Deprecated->RemovedQueryCache。同时，查询缓存碎片也会增加服务器的负载，影响数据库的稳定性。Oracle官方搜索QueryCache可以发现很多bug，这就决定了MySQL8.0直接果断的去掉了这个特性。综上所述，上面介绍了MySQLQueryCache从launch->disable->discard->delete的思路。最初的设计是为了减少SQL重复查询带来的硬解析开销，同时将物理IO转化为逻辑IO，提高SQL的执行效率，但是MySQL经历了多个版本的迭代。同时，在硬件存储高速发展的今天，QC几乎没有任何好处，还会降低数据库的并发处理能力。最后在8.0版本直接去掉了。事实上，缓存的设计思想在硬件和软件领域无处不在。硬件方面：RAID卡，CPU都有自己的缓存，软件方面太多了。OS缓存、数据库缓冲池和Java程序缓存，作为研究工程师，根据业务场景选择合适的缓存方案非常重要。如果不合适，就需要自己开发定制的缓存，更好的匹配自己的业务场景。今天就说这么多，希望对大家有所帮助。
