共计 10243 个字符，预计需要花费 26 分钟才能阅读完成。
本篇内容主要讲解“何为 MySQL 查询缓存”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“何为 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_nameDescriptionhave_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 开头的语句。
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 该变量影响 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_IDQueryDescription1reset query cache 清空查询缓存。2select balance from account where id = 121 第一次执行，未命中 QC，添加到 QC。3select balance from account where id = 121 命中 QC，直接返回结果。4update account set balance = balance – 1000 where id = 121 更新，锁定 query cche 进行更新，缓存数据失效。5select balance from account where id = 121 缓存已失效，未命中，添加到 QC。6select 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 查询缓存”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
