理解MySQL查询缓存
2014-02-07 16:00:59 
阿炯
MySQL查询缓存(Query Cache)是直接缓存查询语句的记录集和对应的SQL语句。本文就给大家介绍下查询缓存的相关知识，希望可以引导大家正确地使用Query Cache这个独门武器。mysql的查询缓存保存查询返回的完整结果，当查询命中该缓存时mysql会立刻返回结果，跳过了解析、优化和执行阶段。缓存可能成为整个服务器的资源竞争点，在多核服务器上还可能导致服务器僵死，查询缓存默认是关闭的。判断命中
查询缓存系统会跟踪查询中涉及的每个表, 如果这些表发生任何变化, 那么和整个表相关的所有缓存数据都将失效
通过查询语句hash判断命中, 查询语句的任何变化都将导致未命中, 包括注释和空格
查询中存在不确定函数
常用配置
query_cache_type 是否打开查询缓存, off | on | demand
query_cache_size 可使用总内存字节
query_cache_min_res_unit 查询缓存分配内存时最小单位
query_cache_limit mysql能够缓存的最大查询结果
query_cache_wlock_invalidate 涉及的表有写锁时是否仍然使用缓存
通用优化
用多个小表代替一个大表
批量写入可以有效降低缓存失效次数
设置较小的缓存大小(query_cache_size)
密集写应用, 直接关闭查询缓存
查询缓存最高境界不去执行, 再高境界不发送查询到mysql, 利用应用程序或者第三方缓存
对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，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大， 查询缓存的命中率就越高，也不是对服务器负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置 为：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_hits + Qcache_inserts) * 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参数指定；
《高性能MySQL》中对MySQL查询缓存的理解
很多数据库产品都能够缓存查询的执行计划，对于相同类型的SQL就可以跳过SQL解析和执行计划生成阶段。MySQL还有另一种不同的缓存类型：缓存完整的SELECT查询结果，也就是“查询缓存”。
查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的缓存数据都将失效。查询缓存对应用程序是完全透明的。应用程序无须关心MySQL是通过查询缓存返回的结果还是实际执行返回的结果。
另外，随着现在的通用服务器越来越强大，查询缓存可能是一个影响服务器扩展性的因素。它可能成为整个服务器的资源竞争单点，在多核服务器上还可能导致服务器僵死。
1.1查询缓存的工作机制
MySQL判断是否命中缓存的办法很简单，首先会将要缓存的结果放在引用表中，然后使用查询语句，数据库名称，客户端协议的版本等因素算出一个hash值，这个hash值与引用表中的结果相关联。如果在执行查询时，根据一些相关的条件算出的hash值能与引用表中的数据相关联，则表示查询命中。
通过have_query_cache服务器系统变量指示查询缓存是否可用：
SHOW VARIABLES LIKE 'have_query_cache';
为了监视查询缓存性能，使用SHOW STATUS查看缓存状态变量：
mysql> SHOW STATUS LIKE 'Qcache%';
任何字符的不同，例如空格、注释都会导致缓存的不命中。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、系统表、或者任何包含列级别权限的表，都不会被缓存。
在检查查询缓存之前，MySQL只做一件事情，就是通过一个大小写不敏感的检查看看SQL语句是不是以SEL开头。
如果查询语句中包含任何不确定函数，那么在查询缓存中不可能找到缓存结果的。
1.2额外的消耗
如果使用查询缓存，在进行读写操作时会带来额外的资源消耗，消耗主要体现在以下几个方面：
·查询的时候会检查是否命中缓存，这个消耗相对较小。检查缓存命中
·如果没有命中查询缓存，MYSQL会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存。写查询缓存
·如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。更新/删除查询缓存
对于InnoDB而言，事务的一些特性还会限制查询缓存的使用。当在事务A中修改了B表时，因为在事务提交之前，对B表的修改对其他的事务而言是不可见的。为了保证缓存结果的正确性，InnoDB采取的措施让所有涉及到该B表的查询在事务A提交之前是不可缓存的。如果A事务长时间运行，会严重影响查询缓存的命中率。
查询缓存的空间不要设置的太大。因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况。
1.3查询缓存如何使用内存
查询缓存是完全存储在内存中的，所以在配置和使用它之前，我们需要先了解它是如何使用内存的。除了查询结果之外，它还需要一些内存专门用来确定哪些内存目前是可用的、哪些是已经用掉的、哪些用来存储数据表和查询结果之间的映射、哪些用来存储查询字符串和查询结果。这些基本的管理维护数据结构大概需要40KB的内存资源，除些之外，MySQL用于查询缓存的内存被分成一个个的数据块，数据块是变长的。
当服务器启动的时候，它先初始化查询缓存需要的内存。这个内存池初始是一个完整的空闲块。当有查询结果需要缓存的时候，MySQL先从大的空间块中申请一个数据块用于存储结果。这个数据块需要大于参数query_cache_min_re_unit。
因为需要先锁住空间块，然后找到合适大小数据块，所以分配内存是一个非常慢的操作。MySQL尽量避免这个操作的次数。当需要缓存一个查询结果的时候，它先选择一个尽可能小的内存块，然后将结果存入其中Qcache_inserts。如果数据块全部用完，但仍有剩余数据需要存储，那么MySQL会申请一个新数据块仍然是尽可能小的数据块－－继续存储结果数据。当查询完成时，如果申请的内存还有剩余，MySQL会将其释放，并放入空闲内存部分。这里的分配内存块，并不是通过malloc函数向操作系统申请内存，而是MySQL初次创建查询缓存的时候一次性申请下来的，并由自己管理一大块内存
1.4什么情况下查询缓存能发挥作用
1、查询缓存可以降低查询执行的时间，但是却不能减少查询结果传输的网络消耗，如果这个消耗是整个查询过程的主要瓶颈，那么查询缓存的作用也很小。
2、对于那些需要消耗大量资源的查询通常都是非常适合缓存的，对于复杂的SELECT语句都可以使用查询缓存，不过需要注意的是，涉及表上的UPDATE、DELETE、INSERT操作相比SELECT来说要非常少才行。
3、查询缓存命中率：Qcache_hits/(Qcahce_hits+Com_select)，查询缓存命中率多大才是好的命中率，需要具体情况具体分析。只要查询缓存带来的效率提升大于查询缓存带来的额外消耗，即使30%的命中率也是值得。另外，缓存了哪些查询也很重要，如果被缓存的查询本身消耗巨大，那么即使缓存命中率低，对系统性能提升仍然是有好处的。
4、任何SELECT语句没有从查询缓存中返回都称为“缓存未命中”，以如下可能：
·查询语句无法被缓存，可能因为查询中包含一个不确定的函数，或者查询结果太大而无法缓存。
·MySQL从未处理这个查询，所以结果也从不曾被缓存过。
·虽然之前缓存了查询结果，但由于查询缓存的内存用完了，MySQL需要删除某些缓存，或者由于数据表被修改导致缓存失效。
如果服务器上有大量缓存缓存未命中，但是实际上绝大查询都被缓存了，那么一定是有如下情况发生：
查询缓存还没有完成预热，即MYSQL还没有机会将查询结果都缓存起来。
查询语句之前从未执行过。如果应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存未命中。
缓存失效操作太多，缓存碎片、内存不足、数据修改都会造成缓存失效。可以通过参数Com_*来查看数据修改的情况包括Com_update，Com_delete等，还可以通过Qcache_lowmem_prunes来查看有多少次失效是由于内存不足导致的。
5、有一个直观的方法能够反映查询缓存是否对系统有好处，推荐一个指标：”命中和写入“的比率，即Qcache_hits和Qcache_inserts的比值。根据经验来看，当这个比值大于3：1时通常查询缓存是有效的，如果能达到10：1最好。
6、通常可以通过观察查询缓存内存的实际使用情况Qcache_free_memory，来确定是否需要缩小或者扩大查询缓存。
1.5配置和维护查询缓存
MySQL提供了一些参数来控制查询缓存的行为，参数如下
·query_cache_limit
MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB)
如果某个查询的结果超出了这个值，Qcache_not_cached的值会加1，如果某个操作总是超出，可以考虑在SQL中加上SQL_NO_CACHE来避免额外的消耗。
·query_cache_min_res_unit
查询缓存分配的最小块的大小(字节)。 默认值是4096(4KB)。
·query_cache_size
为缓存查询结果分配的内存的数量，单位是字节，且数值必须是1024的整数倍。默认值是0，即禁用查询缓存。请注意即使query_cache_type设置为0也将分配此数量的内存。
·query_cache_type
设置查询缓存类型。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端可以设置SESSION值以影响他们自己对查询缓存的使用。下面的表显示了可能的值：
选项 | 描述 |
0或OFF | 不要缓存查询结果。请注意这样不会取消分配的查询缓存区。要想取消，你应将query_cache_size设置为0。 |
1或ON | 缓存除了以SELECT SQL_NO_CACHE开头的所有查询结果。 |
2或DEMAND | 只缓存以SELECT SQL_NO_CACHE开头的查询结果。 |
·query_cache_wlock_invalidate
一般情况，当客户端对MyISAM表进行WRITE锁定时，如果查询结果位于查询缓存中，则其它客户端未被锁定，可以对该表进行查询。将该变量设置为1，则可以对表进行WRITE锁定，使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时，可以强制其它试图访问表的客户端来等待。
1.6减少碎片
没有什么办法能够完全避免碎片，但是选择合适的query_cache_min_res_unit可以帮你减少由碎片导致的内存空间浪费。这个值太小，则浪费的空间更少，但是会导致频繁的内存块申请操作；如果设置得太大，那么碎片会很多。调整合适的值其实是在平衡内存浪费和CPU消耗。可以通过内存实际消耗query_cache_size_Qcache_free_memory除以Qcache_queries_in_cahce计算单个查询的平均缓存大小。可以通过Qcahce_free_blocks来观察碎片。
通过FLUSH_QUERY_CAHCE完成碎片整理，这个命令将所有的查询缓存重新排序，并将所有的空闲空间都聚焦到查询缓存的一块区域上。
1.7查询缓存的替代方案
MySQL查询缓存工作的原则是：执行查询最快的方式就是不去执行，但是查询仍然需要发送到服务器端，服务器也还需要做一点点工作，如果对于某些查询完全不需要与服务器通信效果会如何呢，这时客户端缓存可以很大程度上分担MySQL服务器的压力。