mysql query cache优化

query cache原理

当mysql接收到一条select类型的query时，mysql会对这条query进行hash计算而得到一个hash值，然后通过该hash值到query cache中去匹配，如果没有匹配中，则将这个hash值存放在一个hash链表中，同时将query的结果集存放进cache中，存放hash值的链表的每一个hash节点存放了相应query结果集在cache中的地址，以及该query所涉及到的一些table的相关信息；如果通过hash值匹配到了一样的query，则直接将cache中相应的query结果集返回给客户端。如果mysql任何一个表中的任何一条数据发生了变化，便会通知query cache需要与该table相关的query的cache全部失效，并释放占用的内存地址。

query cache优缺点

优点很明显，对于一些频繁select query，mysql直接从cache中返回相应的结果集，而不用再从表table中取出，减少了IO开销。

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

1. query语句的hash计算和hash查找带来的资源消耗。mysql会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在，虽然hash计算和查找的效率已经足够高了，一条query所带来的消耗可以忽略，但一旦涉及到高并发，有成千上万条query时，hash计算和查找所带来的开销就的重视了；

2. query cache的失效问题。如果表变更比较频繁，则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化，还包括结构或者索引的任何变化；

3.对于不同sql但同一结果集的query都会被缓存，这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同，缓存都是认为是不同的sql（因为他们的hash值会不同）；

4. 相关参数设置不合理会造成大量内存碎片，相关的参数设置会稍后介绍。

合理利用query cache

query cache有利有弊，合理的使用query cache可以使其发挥优势，并且有效的避开其劣势。

1. 并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更，那么就应该避免在变化频繁的table上使用query cache。mysql中针对query cache有两个专用的sql hint：SQL_NO_CACHE和SQL_CACHE，分别表示强制不使用和强制使用query cache，通过强制不使用query cache，可以让mysql在频繁变化的表上不使用query cache，这样减少了内存开销，也减少了hash计算和查找的开销；

2. 设置合理的参数变量和状态变量。

首先查看query cache的系统变量。

• have_query_cache 表示是否支持query cache；

• query_cache_limit 表示query cache存放的单条query最大结果集，默认值为1M，结果集大小超过该值的query不会被cache；

• query_cache_min_res_unit 表示query cache 每个结果集存放的最小内存大小，默认4k；

• query_cache_size 表示系统中用于query cache的内存大小；

• query_cache_type 系统是否打开了query cache功能；

• query_cache_wlock_invalidate 针对myisam存储引擎，设置当有write lock在某个table上面的时候，读请求是要等待write lock释放资源之后再查询还是允许直接从query cache中读取结果，默认是OFF，可以直接从query cache中取得结果。

以上参数经常需要调整的是“query_cache_limit”和“query_cache_min_res_unit”，都需要根据业务做一些调整，比如cache的结果集大都小于4k的话，可以适当的调整"query_cache_min_res_unit"的值，以避免造成内存的浪费。如果结果集的大小又都大于1M时，就得调整"query_cache_limit"的值，避免因为结果集大小超过限制而不被mysql cache住。

查看query cache相关的状态变量

• Qcache_free_blocks 表示query cache中目前还有多杀剩余的blocks，如果该值显示较大，则说明query cache中的内存碎片较多，需要进行整理了；

• Qcache_free_memory 表示query cache目前剩余的内存大小；

• Qcache_hits 表示query cache有多少次命中；

• Qcache_inserts 表示未命中cache后将结果集再写入到cache中的次数；

• Qcache_lowmem_prunes 表示多少条query因为内存不足而被清除出query_cache；

• Qcache_not_cached 表示因为query_cache_type的设置或者不能被cache的query的数量；

• Qcache_queries_in_cache 表示当前cache的query的数量；

• Qcache_total_blocks 当前query cache中的block数量。

通过上述这些状态变量可以了解到query cache的运行状况，从而可以调整相应的系统参数的值。

query cache的限制

1. 5.1.17之前的版本不能cache绑定变量的query，但是从5.1.17版本开始，query cache已经开始支持绑定变量的query了；

2. 所有子查询中的外部查询sql不能被cache，只会cache最终的结果集；

3. 在procedure，function以及trigger中的query不能被cache；

4. 包含其他很多每次执行可能得到不一样结果的函数的query不能被cache。

鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query 的查询结果被Cache。

本文出自 “fire” 博客，请务必保留此出处http://fire7758.blog.51cto.com/993821/1587979