MySQL查询缓存的优缺点

女爷i 2022-03-15 00:34 357阅读 0赞

**(学习留存，如有侵权，请告知，立刻删除！)**

与朋友或同事谈到mysql查询缓存功能的时候，个人喜欢把Query Cache比作[荔枝][Link 1]， 是非常营养的东西，但是一次性吃太多了，就容易导致上火而[流鼻血][Link 2]，虽然不是特别恰当的比喻，但是有很多相似的地方，另外Query Cache有其特殊的业务场景，也不像其他数据库产品，缓存查询语句的执行计划等信息，而是直接缓存查询语句的记录集和对应的SQL语句。本文就给大家介 绍下查询缓存的相关知识，希望可以引导大家正确地使用Query Cache这个独门武器。

对mysql查询缓存从五个角度进行详细的分析：Query Cache的工作原理、如何配置、如何维护、如何判断查询缓存的性能、适合的业务场景分析。

工作原理

查询缓存的工作原理，基本上可以用二句话概括：

缓存SELECT操作或预处理查询（注释：5.1.17开始支持）的结果集和SQL语句；

新的SELECT语句或预处理查询语句，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写；

查询缓存对什么样的查询语句，无法缓存其记录集，大致有以下几类：

查询语句中加了SQL\_NO\_CACHE参数；

查询语句中含有获得值的函数，包涵自定义函数，如：CURDATE()、GET\_LOCK()、RAND()、CONVERT\_TZ等；

对系统数据库的查询：mysql、information\_schema

查询语句中使用SESSION级别变量或存储过程中的局部变量；

查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句

查询语句中类似SELECT …INTO 导出数据的语句；

事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；

对临时表的查询操作；

存在警告信息的查询语句；

不涉及任何表或视图的查询语句；

某用户只有列级别权限的查询语句；

查询缓存的优缺点：

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果；

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；   
Query Cache的起用，会增加检查和清理Query Cache中记录集的开销，而且存在SQL语句缓存的表，每一张表都只有一个对应的全局锁；

配置

是否启用mysql查询缓存，可以通过2个参数：query\_cache\_type和query\_cache\_size，其中任何一个参数设置为0都意味着关闭查询缓存功能，但是正确的设置推荐query\_cache\_type=0。

query\_cache\_type

值域为：0 -– 不启用查询缓存；

值域为：1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集斗可以缓存起来，共其他客户端使用；

值域为：2 -– 启用查询缓存，只要查询语句中添加了参数：sql\_cache，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，共其他客户端使用；

query\_cache\_size

允许设置query\_cache\_size的值最小为40K，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大， 查询缓存的命中率就越高，也不是对[服务器][Link 3]负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置 为：64M；

query\_cache\_limit

限制查询缓存区最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置；

query\_cache\_min\_res\_unit

设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K

query\_cache\_wlock\_invalidate

该参数主要涉及MyISAM引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认设置为0，也即允许；

维护

查询缓存区的碎片整理

查询缓存使用一段时间之后，一般都会出现内存碎片，为此需要监控相关状态值，并且定期进行内存碎片的整理，碎片整理的操作语句：FLUSH QUERY CACHE；

清空查询缓存的数据

那些操作操作可能触发查询缓存，把所有缓存的信息清空，以避免触发或需要的时候，知道如何做，二类可触发查询缓存数据全部清空的命令：

(1). RESET QUERY CACHE；

(2). FLUSH TABLES；

性能监控

碎片率

查询缓存内存碎片率=Qcache\_free\_blocks / Qcache\_total\_blocks \* 100%

命中率

查询缓存命中率=(Qcache\_hits – Qcache\_inserts) / Qcache\_hits \* 100%

内存使用率

查询缓存内存使用率=(query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%

Qcache\_lowmem\_prunes

该参数值对于检测查询缓存区的内存大小设置是否，有非常关键性的作用，其代表的意义为：查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息，删除算法为LRU；

query\_cache\_min\_res\_unit

内存块分配的最小单元非常重要，设置过大可能增加内存碎片的概率发生，太小又可能增加内存分配的消耗，为此在系统平稳运行一个阶段性后，可参考公式的计算值：

查询缓存最小内存块 = (query\_cache\_size – Qcache\_free\_memory) / Qcache\_queries\_in\_cache

query\_cache\_size

我们如何判断query\_cache\_size是否设置过小，依然也只有先预设置一个值，推荐为：32M~128M之间的区域，待系统平稳运行一个时间段（至少1周），并且观察这周内的相关状态值：

(1). Qcache\_lowmem\_prunes；

(2). 命中率；

(3). 内存使用率；

若整个平稳运行期监控获得的信息，为命中率高于80%，内存使用率超过80%，并且Qcache\_lowmem\_prunes的值不停地增加，而且增加的数值还较大，则说明我们为查询缓冲区分配的内存过小，可以适当地增加查询缓存区的内存大小；

若是整个平稳运行期监控获得的信息，为命中率低于40%，Qcache\_lowmem\_prunes的值也保持一个平稳状态，则说明我们的查询缓冲区的内 存设置过大，或者说业务场景重复执行一样查询语句的概率低，同时若还监测到一定量的freeing items，那么必须考虑把查询缓存的内存条小，甚至关闭查询缓存功能；

业务场景   
通过上述的知识梳理和分析，我们至少知道查询缓存的以下几点：

查询缓存能够加速已经存在缓存的查询语句的速度，可以不用重新解析和执行而获得正确得记录集；

查询缓存中涉及的表，每一个表对象都有一个属于自己的全局性质的锁；

表若是做DDL、FLUSH TABLES 等类似操作，触发相关表的查询缓存信息清空；

表对象的DML操作，必须优先判断是否需要清理相关查询缓存的记录信息，将不可避免地出现锁等待事件；

查询缓存的内存分配问题，不可避免地产生一些内存碎片；

查询缓存对是否是一样的查询语句，要求非常苛刻，而且还不智能；

我们再重新回到本节的重点上，查询缓存适合什么样的业务场景呢？只要是清楚了查询缓存的上述优缺点，就不难罗列出来，业务场景要求：

整个系统以读为主的业务，比如门户型、新闻类、报表型、论坛等网站；

查询语句操作的表对象，非频繁地进行DML操作，可以使用query\_cache\_type=2模式，然后SQL语句加SQL\_CACHE参数指定；

[Link 1]: https://www.baidu.com/s?wd=%E8%8D%94%E6%9E%9D&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E6%B5%81%E9%BC%BB%E8%A1%80&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 3]: https://www.baidu.com/s?wd=%E6%9C%8D%E5%8A%A1%E5%99%A8&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd