【Mysql】mysql数据库查询缓存原理是什么

一、概述

查询缓存（Query Cache，简称QC），存储SELECT语句及其产生的数据结果。

mysql的query cache用来缓存和query有关的数据。具体来说Query cache有其特殊的业务场景，它不像其他的数据库产品，缓存查询语句的执行计划等信息；而query cache则是缓存客户端提交给mysql的select语句以及语句的结果集，就是将select语句和语句的结果做hash映射关系后保存在一定的内存区域内。




二、工作原理

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

• 缓存select操作或预处理查询（5.1.17开始支持）的结果集和SQL语句；key为sql，value为查询结果集；
• 新的select语句或预处理查询语句，执行时，先去查询缓存，判断是否存在可用的结果集，判断标准：与缓存的SQL语句是否完全一致，区分大小写以及中间的空格；以这个sql为key去缓存中查询，如果匹配，就把缓存的结果集返回；

例如：
select age from user 与 select AGE from user不会匹配，因为大小写不同；
select age from use 与 select age from user不会匹配，因为空格不同；
sql两边的空格可忽略，可以认为是对key进行过trim操作之后再进行equals比较。

三、配置

1、查看mysql设置参数

执行

show variables like '%query_cache%';

可以看到相关参数：

• query_cache_type：0-不启用查询缓存；1-启用查询缓存，只要符合缓存的要求就会缓存，供其他客户端使用；2-启用查询缓存，只要查询语句中添加了参数-SQL_CACHE，且符合查询缓存的要求，客户端的查询语句和记录集就可以缓存起来，供其他客户端使用；

• query_cache_size：设置缓存区总大小，允许设置query_cache_size的值最小为40K，默认1M，对于最大值则可以几乎认为无限制。实际生产环境的应用经验告诉我们，该值并不是越大越好，应该合理设置，推荐设置为：64M/128M；

• query_cache_limit：限制查询缓存区最大能缓存的单条查询记录集大小，可以避免一个大的查询记录结果集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024K之间的值。

• query_cache_min_res_unit：设置查询缓存分配内存的最小单位，要适当的设置该参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能会导致内存碎片数的上升。默认为4K，建议设置大小为1k~16k。
• query_cache_wlock_invalidate：该参数主要涉及MyISAM引擎，若一个客户端对某表添加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认为0即允许；

查看缓存使用情况

show status like '%Qcache%%';

可以看到相关参数：

Qcache_hits：缓存命中次数；

Qcache_inserts：缓存中插入次数，每缓存一次加1，注意这个不是缓存数量；

开启查询缓存

设置选项query_cache_type = 1 ，同时设置query_cache_size = 67108864；

注：query_cache_size的值设置在100MB以内即可。在MySQL里查询缓存是由一个全局锁在控制，每次更新查询缓存的内存块都需要进行锁定。

关闭查询缓存

设置选项query_cache_type = 0，同时设置query_cache_size = 0。

适用场景

用于频繁提交同一个语句，并且该表数据变化不是很频繁的场景，例如一些静态页面，或者页面中的某块不经常发生变化的信息。

由于查询缓存需要缓存最新数据结果，因此表数据发生任何变化（insert、update、delete或其他可能产生数据变化的操作），都会导致查询缓存被刷新。因而，对于一个更新频率非常低而只读查询频率非常高的场景下，打开查询缓存还是比较有优势的。

不适用场景：查询缓存对什么样的查询语句，无法缓存其结果集

查询缓存严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议版本、字符集等因素都会影响。下面为查询缓存不适用的几个场景：

• 子查询； 
• 过程、函数、触发器、event中调用的SQL，或者引用到这些结果的；
• 查询中涉及一些特殊函数时，查询语句中包含有获得值的函数，以及自定义函数，如：，例如：BENCHMARK()、CURDATE()、CURRENT_TIME()、CURRENT_TIMESTAMP()、NOW()、SLEEP()、CONNECTION_ID()、CURRENT_DATE()、CURRENT_USER()、PASSWORD()、RAND()、UUID()、ENCRYPT()、LAST_INSERT_ID()等等；
• 对系统数据库的查询：查询涉及到mysql，information_schema或performance_schema。
• 查询语句中使用了…LOCK IN SHARE MODE、…FOR UPDATE、SELECT..INTO OUTFILE/DUMPFILE、SELECT * FROM ... WHERE autoincrement_col IS NULL的查询；
• 对临时表的查询操作；SELECT执行计划用到临时表；
• 不涉及任何表或视图的查询语句；未引用任何表的查询，例如SELECT 1+2；
• 查询产生了告警(warnings)；存在警告信息的查询语句；
• SELECT语句中存在SQL_NO_CACHE关键字；
• 涉及到分区表。
• 查询语句中使用SESSION级别变量或存储过程中的局部变量；
• 查询语句中类似select ... into ... 导出数据语句；
• 事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；
• 某用户只有列级别权限的查询语句；

四、维护

• 查询缓存区的碎片整理：查询缓存使用一段时间后，一般都会出现缓存碎片，为此需要监控相关的状态值，并定期进行内存碎片的整理，碎片整理的操作语句为：FLUSH QUERY CACHE。
• 清空查询缓存的数据：那些操作可能触发查询缓存，把所有缓存信息清空，以避免触发或需要的时候，知道如何做。两类可触发查询缓存数据全部清空的命令：reset query cache；flush tables；

五、性能监控

    1、mysql提供一系列Global Status记录Query Cache当前状态，具体如下：

•  Qcache_free_blocks:目前处于空闲状态的Query Cache中内存block数目；
• Qcache_free_memory：目前处于空闲状态的Query Cache内存总量；
• Qcache_hits：query cache 命中次数；
• Qcache_inserts：向Query Cache中插入新的Query Cache次数，也就是没有命中的次数。
• Qcache_lowmem_prunes：当Query Cache内存容量不够，需要从删除旧的Query Cache以给新Cache对象使用的次数；
• Qcache_not_cached：没有被Cache的SQL数，包括无法被Cache的SQL以及由于Query_cache_type设置而不会被cache的SQL。
• Qcache_queries_in_cache：目前在Query cache中的SQL数量；
• Qcache_total_blocks：Query cache中总的block数量。

    2、重要监控率的计算：

• 查询缓存内存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%;
• 查询缓存命中率=Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%;
• 查询缓存内存使用率=（query_cache_size - Qcache_free_memory) / query_cache_size * 100%;

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

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

六、查询缓存的更新策略

    query cache一旦达到上限后，就会剔除老的query cache对象。同时，为了保证query cache中的内容与实际数据绝对一致，当表中的数据有任何变化，包括新增、修改、删除等，都会使所有应用到该表的SQL的query cache失效。

七、查询缓存的优缺点

• 不需要对SQL语句做任何解析和执行，当然语法解析必须通过先，直接从query cache中获取查询结果；
• 查询缓存的判断规则不够智能，也提高了查询缓存的使用门槛，降低其效率；
• query cache的启用，会增加检查和清理query cache中记录集的开销，而且存在SQL语句缓存的表，每一张都只有一个对应的全局锁。

八、查询缓存的使用业务

• 整个系统以读为主的业务，比如门户型、新闻类、报表型、论坛等网站；
• 查询语句操作的表对象，非频繁的进行DML操作，可以使用query_cache_type=2模式，然后SQL语句加SQL_CACHE参数指定。

参考：https://www.iteye.com/blog/student-lp-2100442