mysql 语句块_MySQL的Query Cache详解

一、原理概述
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详解​ghoulich.xninja.org