query cache优化总结

Query Cache 原理

QueryCache(下面简称QC)是根据SQL语句来cache的。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用QC。每个Cache都是以SQL文本作为key来存的。在应用QC之前，SQL文本不会被作任何处理。也就是说，两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。

不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客户端里，在发送SQL给服务器之前，会做如下处理：

• 过滤所有注释
• 去掉SQL文本前后的空格,TAB等字符。注意，是文本前面和后面的。中间的不会被去掉。

下面的三条SQL里，因为SELECT大小写的关系，最后一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条，区别在于后者有个 注释，在不同客户端，会有不一样的结果。所以，保险起见，请尽量不要使用动态的注释。在PHP的mysql扩展里，SQL的注释是不会被去掉的。也就是三 条SQL会被存储在三个不同的缓存里，虽然它们的结果都是一样的。

select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';

目前只有select语句会被cache，其他类似show,use的语句则不会被cache。

因为QC是如此前端，如此简单的一个缓存系统，所以如果一个表被更新，那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了表A和表B，如果表A或者表B的其中一个被更新（update或者delete），这个查询的QC将会失效。

也就是说，如果一个表被频繁更新，那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC，可能会加重数据库的负担，而不是减轻负担。我一般的做法是默认打开QC，而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用CACHE。这样可以尽可能避免不必要的内存操作，尽可能保持内存的连续性。

那些查询很分散的SQL语句，也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name='surfchen'”。这样的语句，在一个系统里，很有可能只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询，都是不一样的SQL 文本，QC在这里就几乎不起作用了，因为缓存的数据几乎是不会被用到的，它们只会在内存里占地方。

存储块

在本节里“存储块”和“block”是同一个意思

QC缓存一个查询结果的时候，一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询结果获得的过程中，逐块存储。当一个存储块被填满之后，一个新的存储块将会被创建，并分配内存（allocate）。单个存储块的内存分配大小通过query_cache_min_res_unit参数控制，默认为4KB。最后一个存储块，如果不能被全部利用，那么没使用的内存将会被释放。如果被缓存的结果很大，那么会可能会导致分配内存操作太频繁，系统系能也随之下降；而如果被缓存的结果都很小，那么可能会导致内存碎片过多，这些碎片如果太小，就很有可能不能再被分配使用。

除了查询结果需要存储块之外，每个SQL文本也需要一个存储块，而涉及到的表也需要一个存储块（表的存储块是所有线程共享的，每个表只需要 一个存储块）。存储块总数量=查询结果数量*2+涉及的数据库表数量。也就是说，第一个缓存生成的时候，至少需要三个存储块：表信息存储块，SQL文本存 储块，查询结果存储块。而第二个查询如果用的是同一个表，那么最少只需要两个存储块：SQL文本存储块，查询结果存储块。

通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储 块。它们的比例如果接近1:2，则说明当前的query_cache_min_res_unit参数已经足够大了。如果 Qcache_total_blocks比Qcache_queries_in_cache多很多，则需要增加 query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的 block占用的内存很小，可以忽略）如果远远大于query_cache_size-Qcache_free_memory，那么可以尝试减小 query_cache_min_res_unit的值。

query cache的命中率：Qcache_hits/(Qcache_hits + Com_select).

任何一个不在缓存中的查询都是缓存未命中。缓存未命中可能是因为下面的原因：

• 查询不可缓存。原因可能是含有不确定函数，比如CURRENT_DATE，也有可能是结果太大，无法缓存。状态变量Qcache_not_cached会因为这两种无法缓存的查询而增加。

• 服务器从前从来没见过这个缓存，所以它根本就没有机会缓存自身结果。

• 查询的结果以前被缓存过，但是服务器把它移除了。发生移除的原因可能是内存空间不够，所以被从服务器上删除了，也可能是缓存失效了。

    缓存可能会因为碎片、内存不足或数据改变而失效。如果已经给缓存分配了足够的内存，并且把query_cache_min_res_unit调整到了合适的值，那么大部分缓存失效都应该是数据改变引起的，可以通过检查Com_*(Com_update,Com_delete等)的值知道了多少查询修改了数据。也可以通过Qcache_lowem_prunes的值了解有多少查询因为内存不足而失效。

调整大小

如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大query_cache_size，尽量使Qcache_lowmem_prunes零增长。

启动参数

show variables like 'query_cache%'可以看到这些信息。

query_cache_limit如果单个查询结果大于这个值，则不Cache query_cache_size分配给QC的内存。如果设为0，则相当于禁用QC。要注意QC必须使用大约 40KB来存储它的结构，如果设定小于40KB,则相当于禁用QC。QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。 query_cache_type0 完全禁止QC，不受SQL语句控制（另外可能要注意的是，即使这里禁用，上面一个参数所设定的内存大小还是会被分配）；1启用QC，可以在SQL语句使用SQL_NO_CACHE禁用；2可以在SQL语句使用SQL_CACHE启用。 query_cache_min_res_unit每次给QC结果分配内存的大小，默认是4k, 这个根据实际常用返回结果的大小来确定,设为常用的1/2或1/4 状态

show status like 'Qcache%'可以看到这些信息。

Qcache_free_blocks当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。 Qcache_free_memory可用内存，如果很小，考虑增加query_cache_size Qcache_hits自mysql进程启动起，cache的命中数量 Qcache_inserts自mysql进程启动起，被增加进QC的数量 Qcache_lowmem_prunes由于内存过少而导致QC被删除的条数。加大query_cache_size，尽可能保持这个值0增长。 Qcache_not_cached自mysql进程启动起，没有被cache的只读查询数量（包括select,show,use,desc等） Qcache_queries_in_cache当前被cache的SQL数量 Qcache_total_blocks在QC中的blocks数。一个query可能被多个blocks存储，而 这几个blocks中的最后一个，未用满的内存将会被释放掉。例如一个QC结果要占6KB内存，如果query_cache_min_res_unit是 4KB，则最后将会生成3个blocks，第一个block用来存储sql语句文本，这个不会被统计到query+cache_size里，第二个 block为4KB，第三个block为2KB（先allocate4KB，然后释放多余的2KB）。每个表，当第一个和它有关的SQL查询被CACHE 的时候，会使用一个block来存储表信息。也就是说，block会被用在三处地方：表信息，SQL文本，查询结果。
减少碎片   
    没有办法避免所有的碎片，但是仔细地选择query_cache_min_res_unit可以避免在查询缓存中造成大量的内存浪费。关键在于每一个新块和服务器已分配给存储结果的块的数量直接找平衡。如果值过小，服务器将会浪费较少的内存，但会更频繁地分配块，这对服务器意味着更多的工作，如果值过大，碎片将会很多。合适的折中是在浪费内存和增加CPU处理时间上取得平衡。   最佳设置根据典型查询结果而定。可以使用的内存（大致等于query_cache_size-Qcache_free_memory）除以Qcache_queries_in_cache得到查询的平均大小。如果缓存由大结果和小结果混合而成，那么就很难找到一个合适的大小，既能避免碎片，也能避免过多的分配内存。但是有理由相信缓存大结果没有太大的益处（这通常是真的）。可以通过降低query_cache_limit的值阻止缓存大结果，它有时有助于在碎片和在缓存中保存结果的开销中得到平衡。    可以通过检查Qcache_free_blocks的值来探测缓存中碎片的情况，它可以显示缓存中有多少内存块处于free状态。碎片最严重的情况就是在每两个存储了数据的块之间都有一个比最小值稍小的可用块。这样的话，每隔一个存储块就有一个自由块。因此，如果Qcache_free_blocks大致等于Qcache_total_blocks/2，则说明碎片非常严重。如果Qcache_lowem_prunes的值正在增加，并且有大量的自由块，这意味着碎片导致查询正被从缓存中永久删除。可以使用flush query cache命令移除碎片。这个命令会把所有的存储块向上移动，并把自由块移到底部。当它运行的时候，它会阻止访问查询缓存，这锁定了整个服务器，但它通常都很快，除非缓存非常大。和名字相反，它不会从缓存中移除缓存，reset query cache才会这么做。
提高查询缓存的可用性   
    如果缓存没有碎片，但是命中率却不高，那么就应该给缓存分配较少的内存。如果服务器找不到足够大小的块来存储结果，那么就应该从缓存中清理掉一些查询。    当服务器清理查询的时候，Qcache_lowmem_prunes的值会增加。如果它的值增加的很快，那么可能有两个原因：    如果有很多自由块，那么问题可能是由碎片引起的；    如果自由块比较少，那么这可能意味着工作负载使用的内存大小超过了所分配的内存。可以检查Qcache_free_memory知道未使用的内存数量。    如果有很多自由块，碎片很少，由于内存不足引起的清理工作也很少，但是命中率仍然不高，这说明工作负载也许不能从缓存中受益。肯定有什么东西阻止查询使用缓存，很多update语句可能是原因，另外一个可能的原因是查询是不可缓存的。如果已经估算过缓存命中率，但是还不确定服务器是否从缓存中受益，此时可以禁止使用缓存并且监控性能，然后重新开启缓存并观察性能变化。为了禁用缓存，可以将query_cache_size设置为0（改变query_cache_type不会从全局上影响已经打开了的链接，而且不会把内存归还给服务器）。也可以做基准测试，但是有时候很难得到包含了可缓存的查询、不可缓存的查询，以及更新语句的测试样例。
通用查询缓存优化方案   
   架构、查询以及应用程序设计会影响查询缓存。除了上节讨论的内容，下面还有一些需要注意的观点： 

• 使用多个较小的表，而不是用一个大表，对查询缓存由帮助。这种设计方式使失效策略工作在一个较好的颗粒度上。但是不要让这个想法过度影响架构设计，因为其他的因素能轻易抵消它的好处。

• 成批地进行写入操作，而不是逐个执行，会有效率很多。因为这种方法只会引起一次失效操作。

• 我们已经注意到在让缓存失效或清理一个大型缓存的时候，服务器可能会挂起相当长时间。至少在MySQL5.1之前的版本中是这样。一个容易的解决办法就是不要让query_cache_size太大，256MB已经足够大了

• 不能在数据库或表的基础上控制查询缓存，但是可以使用SQL_CACHE和SQL_NO_CACHE决定是否缓存查询。也可以基于某个连接来运行或禁止缓存，可以通过用适当的值设定query_cache_size来开启或关闭对某个连接的缓存。
• 对于很多写入任务的应用程序，关闭查询缓存也许能改进性能。这样做可以消除缓存那些很快就会失效的查询带来的开销。记住在禁用的时候把query_cache_size设置到0,这样就不会消耗任何内存。

如果想让大多数缓存都不使用缓存，但是有少部分查询能从缓存中极大地受益，这时可以将全局变量query_cache_type设置为DEMAND,然后在想使用缓存的查询后面添加SQL_CACHE，尽管这会造成很多的工作，但可以控制缓存，相应地，如果想缓存大部分查询，只排除一小部分，就可以使用SQL_NO_CACHE.