MySQL查询缓存的小知识-巨人网络通讯

前言

我们知道，缓存的设计思想在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  |

Variable_name	Description
have_query_cache	查询缓存是否可用，YES-可用；NO-不可用，如果用标准二进制MySQL，值总是YES。
query_cache_limit	控制单个查询结果集的最大尺寸，默认是1MB。
query_cache_min_res_unit	查询缓存分片数据块的大小，默认是4KB，可以满足大部分业务场景。
query_cache_size	查询缓存大小，单位Bytes，设置为0是禁用QueryCache，注意：不要将缓存的大小设置得太大，由于在更新过程中需要线程锁定QueryCache，因此对于非常大的缓存，您可能会看到锁争用问题。
query_cache_type	当query_cache_size>0；该变量影响qc如何工作，有三个取值0，1，2，0：禁止缓存或检索缓存结果；1：启用缓存，SELECT SQL_NO_CACHE的语句除外；2：只缓存以SELECT SQL_CACHE开头的语句。

Query_ID	Query	Description
1	reset query cache	清空查询缓存。
2	select balance from account where id = 121	第一次执行，未命中QC，添加到QC。
3	select balance from account where id = 121	命中QC，直接返回结果。
4	update account set balance = balance - 1000 where id = 121	更新，锁定query cche进行更新，缓存数据失效。
5	select balance from account where id = 121	缓存已失效，未命中，添加到QC。
6	select balance from account where id = 121	命中QC，直接返回结果。

全部栏目

目录

前言

QueryCache介绍

QueryCache配置

QueryCache使用

禁用queryCache场景

开启queryCache场景

查询缓存命中率

更新操作对QC影响

为何放弃QueryCache

一般业务场景

适合QueryCache的场景

不适合QueryCache的场景

删除QueryCache

总结

全 部 栏 目

目录

前言

QueryCache介绍

QueryCache配置

QueryCache使用

禁用queryCache场景

开启queryCache场景

查询缓存命中率

更新操作对QC影响

为何放弃QueryCache

一般业务场景

适合QueryCache的场景

不适合QueryCache的场景

删除QueryCache

总结

全部栏目