MySQL的Query Cache图文详解

吾爱主题阅读：188 2024-04-02 08:03:42 评论：0

一、原理概述

query cache就是把“查询返回的结果”缓存起来。但是，仅仅只是缓存“查询返回的结果”，其实是不准确的，query cache还会缓存查询语句，在内存中将“查询语句”和“查询返回的结果”映射起来。
当mysql接收到一条select类型的查询语句时，mysql会对这条查询语句进行hash计算而得到一个hash值。然后，通过该hash值到query cache中去匹配。如果没有得到匹配结果，则会将这个hash值存放在一个hash链表中，同时将此次查询的结果存放至缓存中。存放hash值的链表的每个节点都会存放相应查询返回结果在缓存中的地址，以及该次查询所涉及到的一些表的相关信息。如果通过hash值匹配到相同的查询，则会直接将缓存中相应的查询结果返回给客户端。如果mysql的任何一张表中的任何一条数据发生了变化，便会通知query cache，将和这张表相关的查询缓存全部失效，并释放占用的内存空间。修改表数据的操作包括：insert、update、delete、truncate、alter table、drop table和drop database等等。

query cache的工作原则是：执行查询最快的方式就是不去执行。query cache的组件图和流程图如下所示：

二、query cache系统变量

query cache的主要可配置系统变量如下所示：

1. have_query_cache

表示mysqld是否支持query cache。

2. query_cache_limit

表示query cache可以缓存的单条查询的最大结果集的大小，默认值为1mb。如果某次查询的结果集大小超过这个系统变量的值，那么query cache就不会缓存这次查询的结果集。

3. query_cache_min_res_unit

表示mysql为query cache每次分配内存的最小空间大小，也就是用于缓存查询结果的最小内存空间的大小，默认值为4kb。

4. query_cache_size

表示query cache可以使用的最大内存空间的大小，默认值为1mb。设置的值必须是1024的整数倍，若不是整数倍，mysql则会自动调整降低至达到1024倍数的最大值。

5. query_cache_type

表示query cache的工作模式，同时也是query cache功能的开关，可以设置为0（off）、1（on）和2（demand）三种值：

0（off）：关闭query cache功能，任何情况下都不会使用query cache。
1（on）：开启query cache功能，但是当select语句中使用了sql_no_cache选项之后，将不会使用query cache。
2（demand）：开启query cache功能，但是只有当select语句中使用了sql_cache选项之后，才会使用query cache。

6. query_cache_wlock_invalidate

控制当有写锁加在表上的时候，是否先让该表相关的query cahce失效，具有1（on）和0（off）两种取值：

1（on）：在写锁定的同时将使该表相关的所有query cache失效。
0（off）：在写锁定的同时仍然允许读取该表相关的query cache。

以上环境变量经常需要调整的是query_cache_limit和query_cache_min_res_unit，它们都需要根据实际业务进行相应的调整。例如，如果缓存的查询结果集大多数都小于4kb的话，则可以适当的调整query_cache_min_res_unit的值，以避免造成内存的浪费。如果查询结果集的大小又都大于1mb时，就需要调整query_cache_limit的值，避免因为结果集大小超过限制而不被缓存。

三、query cache状态变量

mysql提供一系列的状态变量来记录query cache的当前状态，使你能够确认query cache的运行是否健康、命中率如何、内存空间大小是否足够，等等。query cache的状态变量如下所示：

1. qcache_free_blocks

表示query cache中目前还有多少空闲的内存块。如果该值比较大，则说明query cache中的内存碎片可能比较多。flush query cache会对缓存中的碎片进行整理，从而得到一个较大的空闲内存块。

2. qcache_free_memory

表示query cache目前空闲的内存大小。

3. qcache_hits

表示有多少次查询在query cache命中。

4. qcache_inserts

向query cache中插入新记录的次数，也就是查询没有命中的次数。

5. qcache_lowmem_prunes

表示由于query cache的内存不足而从缓存中删除的查询结果的数量。如果这个数值在不断增长，那么一般是query cache的空闲内存不足（通过qcache_free_memory判断），或者内存碎片较严重（通过qcache_free_blocks判断）。

6. qcache_not_cached

表示没有被缓存的查询数量。有三种情况会导致查询结果不会被缓存：其一，由于query_cache_type的设置；其二，查询不是select语句；其三，使用了now()之类的函数，导致查询语句一直在变化。

7. qcache_queries_in_cache

表示query cache中当前包含的查询结果数量。

8. qcache_total_blocks

表示query cache中的内存块总数量。

四、优点与缺点

1. 优点

query cache的查询，发生在mysql接收到客户端的查询请求、查询权限验证之后和查询sql解析之前。也就是说，当mysql接收到客户端的查询sql之后，仅仅只需要对其进行相应的权限验证之后，就会通过query cache来查找结果，甚至都不需要经过optimizer模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互。由于query cache是基于内存的，直接从内存中返回相应的查询结果，因此减少了大量的磁盘i/o和cpu计算，导致效率非常高。

2. 缺点

即使query cache的优点很明显，但是也不能忽略它所带来的一些缺点：

查询语句的hash计算和hash查找带来的资源消耗。如果将query_cache_type设置为1（也就是on），那么mysql会对每条接收到的select类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。虽然hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了。
query cache的失效问题。如果表的变更比较频繁，则会造成query cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。
查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，query cache都会认为是不同的查询（因为他们的hash值会不同）。
相关系统变量设置不合理会造成大量的内存碎片，这样便会导致query cache频繁清理内存。

五、常见问答

1. 应当在什么条件下使用query cache？

实际上，并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的表发生了变更，那么就应该避免在变更频繁的表上使用query cache。mysql针对query cache有两个专用的sql选项：sql_no_cache和sql_cache。若将query_cache_type设置为1（on），那么通过sql_no_cache选项便能强制不使用query cache；若将query_cache_type设置为2（demand），那么通过sql_cache选项便能强制使用query cache。通过强制不使用query cache，可以让mysql在频繁变更的表上不使用query cache，这样减少了内存开销，也减少了hash计算和查找的开销。

2. query cache与查询语句有什么关系？

无论mysql收到的查询语句是单表还是多表或是包含子查询的sql，都被作为一个查询，不会被分拆成多个查询来进行缓存，包括union语句。

3. 客户端提交的查询语句的大小写对query cache有影响吗？

有影响。由于query cache在内存中是以hash结构来进行映射的，hash算法的基础就是组成查询语句的字符，所以必须要整个查询语句在字符级别完全一致，才能在query cache中命中。

4. 一个查询语句在query cache中缓存的查询结果，在什么情况下会失效？

为了保证query cache中的内容与是实际数据绝对一致，当表中的数据有任何变化，包括新增、修改、删除等，都会使所有引用到该表的query cache缓存数据失效。

5. query cache碎片率是什么？有什么用？

query cache碎片率 = qcache_free_blocks / qcache_total_blocks * 100%
如果query cache碎片率超过20%，则可以用flush query cache整理内存碎片；如果你的查询都是小数据量的话，可以尝试减小query_cache_min_res_unit。

6. query cache利用率是什么？有什么用？

query cache利用率 = (query_cache_size - qcache_free_memory) / query_cache_size * 100%

query cache利用率在25%以下的话，说明query_cache_size设置的过大，可适当减小；query cache利用率在80%以上，而且qcache_lowmem_prunes > 50的话，说明query_cache_size可能有点小，或者就是内存碎片太多。

7. query cache命中率是什么？有什么用？

① 可缓存查询的query cache命中率 = qcache_hits / (qcache_hits + qcache_inserts) * 100%
② 涵盖所有查询的query cache命中率 = qcache_hits / (qcache_hits + com_select) * 100%

若命中率在50-70%的范围之内，则表明query cache的缓存效率较高。如果命中率明显小于50%，那么建议禁用（将query_cache_type设置为0(off)）或按需使用（将query_cache_type设置为2(demand)）query cache，节省的内存可以用作innodb的缓冲池。

8. 如何判断query cache是空闲内存不足，还是内存碎片太多？

如果qcache_lowmem_prunes值比较大，表示query cache的内存空间大小设置太小，需要增大。

如果qcache_free_blocks值比较大，表示内存碎片较多，需要使用flush query cache语句清理内存碎片。

9. 系统变量query_cache_min_res_unit应当设置为多大？

query_cache_min_res_unit的计算公式如下所示：

query_cache_min_res_unit = (query_cache_size - qcache_free_memory) / qcache_queries_in_cache

其中，一般不建议将query cache的大小（也就是query_cache_size系统变量）设置超过256mb。

mysql查询缓存query_cache的功用

mysql的查询缓存并非缓存执行计划，而是查询及其结果集，这就意味着只有相同的查询操作才能命中缓存，因此mysql的查询缓存命中率很低，另一方面，对于大结果集的查询，其查询结果可以从cache中直接读取，有效的提升了查询效率。

那么如何设置缓存的大小呢？来看一下：

1、在mysql客户端命令行中我们可以这么查看缓存是否开启以及缓存设置的大小：

1 2 3 4 5 6 7 8 9 10 11 12 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 | 16106127360 | | query_cache_type | on | | query_cache_wlock_invalidate | off | + ------------------------------+-------------+ 6 rows in set (0.01 sec)

其中：

query_cache_type: 是否开启缓存功能，取值为on, off, demand，默认值为on
　　- 值为off或0时，查询缓存功能关闭；
　　- 值为on或1时，查询缓存功能打开，select的结果符合缓存条件即会缓存，否则，不予缓存，显式指定sql_no_cache，不予缓存；
　　- 值为demand或2时，查询缓存功能按需进行，显式指定sql_cache的select语句才会缓存；其它均不予缓存

query_cache_wlock_invalidate：表示当有其他客户端正在对myisam表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

query_cache_limit 指定单个查询能够使用的缓冲区大小，缺省为1m；

query_cache_min_res_unit为系统分配的最小缓存块大小，默认是4kb，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费；query_cache_size：表示缓存的大小。

了解了以上的指标后我们就可以在mysql的配置文件my.cnf中进行设置。然后重启mysl服务器即可。在[mysqld]下面添加参数。一般是设置query_cache_size和query_cache_type两项。

2、上面查看的是我们进行缓存的配置，它一般从配置文件中读取值，但是有时候我们需要实时查看当前mysql中的数据缓存大小。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show status like '%qcache%' ; + -------------------------+----------+ | variable_name | value | + -------------------------+----------+ | qcache_free_blocks | 1 | | qcache_free_memory | 1031832 | | qcache_hits | 0 | | qcache_inserts | 0 | | qcache_lowmem_prunes | 0 | | qcache_not_cached | 16489053 | | qcache_queries_in_cache | 0 | | qcache_total_blocks | 1 | + -------------------------+----------+ 8 rows in set (0.00 sec)

解释：

qcache_free_memory：缓存中的空闲内存。

qcache_total_blocks：缓存中块的数量。

qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。如何确定就需要查看qcache_free_blocks和qcache_free_memory 两项指标。

3、清空缓存：

flush query cache命令

加大缓存有助于我们查询的效率提高：

这里举个例子：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysql> use mob_adn database changed mysql> select count (*) from creative_output; + ----------+ | count (*) | + ----------+ | 87151154 | + ----------+ 1 row in set (3 min 18.29 sec) mysql> select count (*) from creative_output; + ----------+ | count (*) | + ----------+ | 87151154 | + ----------+ 1 row in set (0.00 sec) mysql> select count (*) from creative_output; + ----------+ | count (*) | + ----------+ | 87151154 | + ----------+ 1 row in set (0.00 sec)