共计 9547 个字符，预计需要花费 24 分钟才能阅读完成。
有情怀，有干货，微信搜寻【三太子敖丙】关注这个不一样的程序员。
本文 GitHub https://github.com/JavaFamily 已收录，有一线大厂面试残缺考点、材料以及我的系列文章。
前言
咱们晓得，缓存的设计思维在 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_cache_min_res_unit 阐明
默认大小是 4KB，如果有很多查问后果很小，那么默认数据块大小可能会导致内存碎片，因为内存不足，碎片可能会强制查问缓存从缓存中删除查问。
在这种状况下，能够减小 query_cache_min_res_unit 的值，因为修剪而删除的闲暇块和查问的数量由 Qcache_free_blocks 和 Qcache_lowmem_prunes 状态变量的值给出，如果大量的查问有较大的后果集，能够增大该参数的值来进步性能。
通常开启 QueryCache 形式
# 批改 MySQL 配置文件 /etc/my.cnf，增加如下配置，重启 MySQL server 即可。[mysqld]
query_cache_size = 32M
query_cache_type = 1QueryCache 应用
先搞点测试数据，别离对禁用和开启 QueryCache 下的场景进行测试。
-- 创立一个用户表 users，并且插入 100w 数据。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 '创立工夫',
`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 的时候，每次执行雷同的查问语句，都要产生一次硬解析，耗费大量的资源。
# 禁用 QueryCache 的配置
query_cache_size = 0
query_cache_type = 0反复执行上面查问，察看执行工夫。
-- 第一次执行查问语句
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)
-- 第二次执行同样的查问语句
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 |能够看到，屡次执行同样的 SQL 查问语句，执行工夫都是 0.89s 左右，简直没有差异，同时工夫次要耗费在 Creating sort index 阶段。
开启 queryCache 场景
开启查问缓存时，查问语句第一次被执行时会将 SQL 文本及查问后果缓存在 QC 中，下一次执行同样的 SQL 执行从 QC 中获取数据返回给客户端即可。
# 禁用 QueryCache 的配置
query_cache_size = 32M
query_cache_type = 1-- 第一次执行查问语句
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)
-- 第二次执行查问语句
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 跟踪数据
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 |能够看到，第一次执行 QueryCache 里没有缓存 SQL 文本及数据，执行工夫 0.89s，因为开启了 QC，SQL 文本及执行后果被缓存在 QC 中，第二次执行执行同样的 SQL 查问语句，间接命中 QC 且返回数据，不须要产生硬解析，所以执行工夫升高为 0s，从 profile 里看到 sending cached result to client 间接发送 QC 中的数据返回给客户端。
查问缓存命中率
查问缓存相干的 status 变量
mysql>SHOW GLOBAL STATUS LIKE '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 | -- 查问缓存中的块总数。查问缓存命中率及均匀大小
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 影响
举个例子，领取零碎的里转账逻辑，先要锁定账户再批改余额，次要步骤如下：
| 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，间接返回后果。 |
对于这种状况来说，QC 是不太适宜的，因为第一次执行查问 SQL 未命中，返回后果给客户端，增加 SQL 文本及后果集到 QC 之后，下一次执行同样的 SQL 间接从 QC 返回后果，不须要硬解析操作，然而每次 Update 都是先更新数据，而后锁定 QC 而后更新缓存后果，会导致之前的缓存后果生效，再次执行相的查问 SQL 还是未命中，有得从新增加到 QC，这样频繁的锁定 QC-> 查看 QC-> 增加 QC-> 更新 QC 十分耗费资源，升高数据库的并发解决能力。
为何放弃 QueryCache
个别业务场景
从业务零碎的操作类型，能够分为 OLTP（OnLine Transaction Processing 联机事务处理零碎）和 OLAP（OnLine Analysis Processing 联机剖析解决零碎），对于政企业务，也能够分为 BOSS（Business Operation Support System- 业务操作撑持零碎，简称业支）和 BASS（Business Analysis Support System- 业务剖析撑持零碎，简称经分），来总结下这两类零碎的特点。
适宜 QueryCache 的场景
首先，查问缓存 QC 的大小只有几 MB，不适宜将缓存设置得太大，因为在更新过程中须要线程锁定 QueryCache，因而对于十分大的缓存，可能会看到锁争用问题。那么，哪些状况有助于从查问缓存中获益呢？以下是现实条件：
- 雷同的查问是由雷同或多个客户机反复收回的。
- 被拜访的底层数据实质上是动态或半动态的。
- 查问有可能是资源密集型和 / 或构建简短但计算简单的后果集，同时后果集比拟小。
- 并发性和查问 QPS 都不高。
这 4 种状况只是现实状况下，理论的业务零碎都是有 CRUD 操作的，数据更新比拟频繁，查问接口的 QPS 比拟高，所以能满足下面的现实状况下的业务场景切实很少，我能想到就是配置表，数据字典表这些根本都是动态或半动态的，能够时通过 QC 来进步查问效率。
不适宜 QueryCache 的场景
如果表数据变动很快，则查问缓存将生效，并且因为一直从缓存中删除查问，从而使服务器负载升高，处理速度变得更慢，如果数据每隔几秒钟更新一次或更加频繁，则查问缓存不太可能适合。
同时，查问缓存应用单个互斥体来管制对缓存的拜访，实际上是给服务器 SQL 解决引擎强加了一个单线程网关，在查问 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 的业务场景，发现这个个性对业务场景要求过于刻薄，与理论业务很难吻合，而且开启之后，对数据库并发度和解决能力都会升高很多，上面总结下为何 MySQL 从Disabled->Deprecated->Removed QueryCache 的次要起因。
同时查问缓存碎片化还会导致服务器的负载升高，影响数据库的稳定性，在 Oracle 官网搜寻 QueryCache 能够发现，有很多 Bug 存在，这也就决定了 MySQL 8.0 间接果决的 Remove 了该个性。
总结
下面为大家介绍了 MySQL QueryCache 从推出 -> 禁用 -> 废除 -> 删除的心路历程，设计之初是为了缩小反复 SQL 查问带来的硬解析开销，同时将物理 IO 转化为逻辑 IO，来进步 SQL 的执行效率，然而 MySQL 通过了多个版本的迭代，同时在硬件存储倒退之快的明天，QC 简直没有任何收益，而且还会升高数据库并发解决能力，最终在 8.0 版本间接 Removd 掉了。
其实缓存设计思维在硬件和软件畛域无处不在，硬件方面：RAID 卡，CPU 都有本人缓存，软件方面就太多了，OS 的 cache，数据库的 buffer pool 以及 Java 程序的缓存，作为一名研发工程师，须要依据业务场景抉择适合缓存计划是十分重要的，如果都不适合，就需进行定制化开发缓存，来更好的 Match 本人的业务场景，明天就聊这么多，心愿对大家有所帮忙。
我是敖丙，你晓得的越多，你不晓得的越多 ，感激各位人才的： 点赞 、 珍藏 和评论，咱们下期见！
文章继续更新，能够微信搜一搜「三太子敖丙 」第一工夫浏览，回复【 材料】有我筹备的一线大厂面试材料和简历模板，本文 GitHub https://github.com/JavaFamily 曾经收录，有大厂面试残缺考点，欢送 Star。
