原创 MySQL查询缓存的小奥秘

发布时间:2021-08-03 19:13:03 浏览 2179 来源:猿笔记 作者:敖丙

    SQL的执行计划可以缓存在librarycache中避免再次执行相同SQL发生硬解析(语法分析->语义分析->生成执行计划),SQL执行结果缓存在RESULTCACHE内存组件中。查询缓存存储SELECT语句的文本以及发送给客户机的结果集,如果再次执行相同的SQL,Server端将从查询缓存中检索结果返回给客户端,而不是再次解析执行SQL。可以响应另一个客户端执行同样的SQL,1下面是Oracle数据库通过SQL_TEXT生成sql_id的算法。2大家可以发现SQL1和SQL2通过代码生成的sql_id值是不一样。


    本文**GitHub**

    ##前言

    众所周知,缓存的设计思想在RDBMS数据库中无处不在。以2500w行代码、bug众多的Oracle数据库为例,可以将SQL的执行计划缓存在librarycache中,避免再次执行同一个SQL时的硬解析(语法分析->语义分析->生成执行计划),将SQL执行结果缓存在RESULTCACHE内存组件中,有效地将物理IO转换为逻辑IO,提高了SQL执行效率。

    MySQL的QueryCache跟Oracle类似,缓存的是SQL语句文本以及对应的结果集,看起来是一个很棒的Idea,那为什么从MySQL4.0推出之后,5.6中默认禁用,5.7中被deprecated(废弃)以及8.0版本被Removed,今天就聊聊MySQLQueryCache的前世今生。

    ##QueryCache介绍

    MySQL查询缓存(QC: query cache)是在MySQL4.0.1中引入的。查询缓存存储SELECT语句的文本和发送到客户端的结果集。如果再次执行相同的SQL,服务器将从查询缓存中检索结果并将其返回给客户端,而不是再次解析和执行该SQL。会话之间共享查询缓存。因此,一个客户端生成的缓存结果集可以响应另一个客户端执行相同的SQL。

    * *回到最初的问题,如何判断SQL是否共享?**

    以SQL文本是否完全一致来判断,所有字符,包括大小写、空格等等,都可以完全一样的共享。共享的好处是可以避免硬解析,结果可以直接从QC获得,返回给客户端。以下两个SQL不共享,因为一个来自,一个来自。

    sql--SQL1selectid,balancefromaccountwhereid=121;--SQL2selectid,balanceFromaccountwhereid=121;

    以下是Oracle数据库通过SQL_TEXT生成sql_id的算法。如果sql_id不同,则它不是同一个sql,因此它不会被共享,并且会发生硬解析。

    perl#!/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)=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"SQLis:\$stmt\SQL_IDis\$sqlid\";

    您可以发现,由SQL1和SQL2生成的sql_id值互不相同,因此它们不是共享的。

    sqlSQLis:selectid,balancefromaccountwhereid=121SQL_IDisdm5c6ck1g7bdsSQLis:selectid,balanceFromaccountwhereid=121SQL_IDis6xb8gvs5cmc9b

    如果比较两个Java代码文件内容的差异,只需要把这段代码理解透彻,然后就可以转换实现自己的业务逻辑了。

    ##QueryCache配置

    sqlmysql>showvariableslike'%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-不可用,如果用标准二进制MySQL,值总是YES。|

    |query_cache_limit|控制单个查询结果集的最大尺寸,默认是1MB。|

    |query_cache_min_res_unit|查询缓存分片数据块的大小,默认是4KB,可以满足大部分业务场景。|

    |query_cache_size| QueryCache大小(以字节为单位)设置为0以禁用QueryCache。注意:不要将缓存大小设置得太大。因为线程需要在更新过程中锁定querycache,所以对于非常大的缓存,您可能会看到锁争用。|

    |query_cache_type|当query_cache_size>0;该变量影响qc如何工作,有三个取值0,1,2,**0:禁止缓存或检索缓存结果**;**1:启用缓存,SELECTSQL_NO_CACHE的语句除外**;**2:只缓存以SELECTSQL_CACHE开头的语句。**|

    **query_cache_min_res_unit说明**

    默认大小为4KB。如果许多查询结果很小,默认的块大小可能会导致内存碎片。由于内存不足,碎片可能会迫使查询缓存从缓存中删除查询。

    在这种情况下,可以减小query_cache_min_res_unit的值,由于修剪而删除的空闲块和查询的数量由Qcache_free_blocks和Qcache_lowmem_prunes状态变量的值给出,如果大量的查询有较大的结果集,可以增大该参数的值来提高性能。

    **通常开启QueryCache方式**

    shell#修改MySQL配置文件/etc/my.cnf,添加如下配置,重启MySQLserver即可。[mysqld]query_cache_size=32Mquery_cache_type=1

    ##QueryCache使用

    先做一些测试数据,分别测试QueryCache禁用和启用的场景。

    sql--创建一个用户表users,并且插入100w数据。CREATETABLE`users`(`id`bigintNOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULLDEFAULT''COMMENT'姓名',`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|

    ###禁用queryCache场景

    不使用QueryCache时,每次执行同一个查询语句,都会发生硬解析,消耗大量资源。

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

    重复以下查询并观察执行时间。

    sql--第一次执行查询语句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|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.90sec)--profile跟踪情况mysql>showprofilecpu,blockioforquery1;+----------------------+----------+----------+------------+--------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+----------------------+----------+----------+------------+--------------+---------------+|preparing|0.000022|0.000017|0.000004|0|0||Sortingresult|0.000014|0.000009|0.000005|0|0||executing|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场景

    当查询缓存打开时,第一次执行查询语句时,将在QC中缓存SQL文本和查询结果,下次执行相同的SQL,从QC中获取数据并返回给客户端。

    shell#禁用QueryCache的配置query_cache_size=32Mquery_cache_type=1

    sql--第一次执行查询语句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|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.00sec)--profile跟踪数据mysql>showprofilecpu,blockioforquery3;+--------------------------------+----------+----------+------------+--------------+---------------+|Status|Duration|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||checkingprivilegesoncached|0.000004|0.000003|0.000000|0|0||checkingpermissions|0.000034|0.000033|0.000001|0|0||sendingcachedresulttoclien|0.000018|0.000017|0.000001|0|0|

    可以看到,第一次在QueryCache中没有缓存SQL文本和数据,执行时间为0.89s,自从QC启动后,将SQL文本和执行结果缓存在QC中,第二次执行相同的SQL查询语句,直接命中QC,返回数据,无需硬解析,执行时间减少到0。从配置文件中可以看出,sendingcachedresulttoclient直接将QC中的数据发送回客户端。

    # #查询缓存命中率

    **查询缓存相关的status变量**

    sqlmysql>SHOWGLOBALSTATUSLIKE'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|--查询缓存中的块总数。

    * *查询缓存命中率和平均大小* *

    sqlQcache_hitsQuerycachehitrate=------------------------------------------------x100%Qcache_hits+Qcache_inserts+Qcache_not_cachedquery_cache_size=Qcache_free_memoryQueryCacheAvgQuerySize=---------------------------------------Qcache_queries_in_cache

    # #更新操作对质量控制的影响

    例如,支付系统的内部转账逻辑需要在修改余额之前锁定账户。主要步骤如下:

    |Query_ID|Query|Description|

    |--------|----------------------------------------------------------|--------------------------------------------|

    |1|resetquerycache|清空查询缓存。|

    |2|selectbalancefromaccountwhereid=121|第一次执行,未命中QC,添加到QC。|

    |3|selectbalancefromaccountwhereid=121|命中QC,直接返回结果。|

    |4|updateaccountsetbalance=balance-1000whereid=121|更新,锁定querycche进行更新,缓存数据失效。|

    |5|selectbalancefromaccountwhereid=121|缓存已失效,未命中,添加到QC。|

    |6|selectbalancefromaccountwhereid=121|命中QC,直接返回结果。|

    这种情况下,QC不适合,因为查询SQL错过了第一次,把结果返回给了客户端。将SQL文本和结果集添加到QC后,下次执行相同的SQL时,结果直接从QC返回,无需硬解析操作。但是每次更新都是先更新数据,然后锁定QC,再更新缓存的结果,这样会导致之前缓存的结果失效。如果查询SQL再次失败,就必须再次添加到QC,如此频繁

作者信息

敖丙 [等级:3] ??掘金签约作者
发布了 92 篇专栏 · 获得点赞 33539 · 获得阅读 1478301

相关推荐 更多