1. 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全部失效,并释放占用的内存地址。

2. query cache优缺点

优点很明显,对于一些频繁select query,MySQL直接从cache中返回相应的结果集,而不用再从表table中取出,减少了IO开销。
即使query cache的收益很明显,但是也不能忽略它所带来的一些缺点:
① query语句的hash计算和hash查找带来的资源消耗。MySQL会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在,虽然hash计算和查找的效率已经足够高了,一条query所带来的消耗可以忽略,但一旦涉及到高并发,有成千上万条query时,hash计算和查找所带来的开销就的重视了。
② query cache的失效问题。如果表变更比较频繁,则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化,还包括结构或者索引的任何变化。
③ 对于不同sql但同一结果集的query都会被缓存,这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同,缓存都是认为是不同的sql(因为他们的hash值会不同)。
④ 相关参数设置不合理会造成大量内存碎片,相关的参数设置会稍后介绍。

3. 合理利用query cache

query cache有利有弊,合理的使用query cache可以使其发挥优势,并且有效的避开其劣势。
① 并不是所有表都适合使用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计算和查找的开销。
② 设置合理的参数变量和状态变量。
wKioL1cggeDSLvdyAAAb3hWshUI314.png
⊙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的内存大小,单位为byte,这里是18M;
⊙query_cache_type:系统是否打开了query cache功能,在my.ini文件中取值从0到2。0为OFF,表示关闭缓存;1为ON,表示打开缓存,单使用select sql_no_cache...则强制不使用缓存;2为DEMAND,表示必须使用select sql_cache...强制使用缓存时才会放入缓存;
⊙query_cache_wlock_invalidate:针对myisam存储引擎(对innodb引擎无效,还是会阻塞等待),设置当有write lock在某个table上面的时候,读请求是要等待write lock释放资源之后再查询还是允许直接从query cache中读取结果,默认是OFF,可以直接从query cache中取得结果。下面就是例子,我们在session1中锁定了myisam_table,然后再查询里面所有数据,最后在session2里面也进行全表查询,可以看到是可以查询到的。但我们在session2中再按id查询就会阻塞,因为sql语句不一样,没有对应缓存。
session1:
wKioL1cghVeSv6g4AAAUWgyC4zE609.png
session2:
wKiom1cghRfjAzhNAAASY0dqzKs961.png
以上参数经常需要调整的是“query_cache_limit”和“query_cache_min_res_unit,都需要根据业务做一些调整,比如cache的结果集大都小于4k的话,可以适当的调整"query_cache_min_res_unit"的值,以避免造成内存的浪费。如果结果集的大小又都大于1M时,就得调整"query_cache_limit"的值,避免因为结果集大小超过限制而不被MySQL缓存。
③ 查看query cache相关的状态变量。
wKioL1cgmPCg_DF4AAAiuqs-XkM269.png
⊙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数量。

4. query cache的限制
① 5.1.17之前的版本不能cache绑定变量的query,但是从5.1.17版本开始,query cache已经开始支持绑定变量的query了;
② 所有子查询中的内部查询sql不能被cache,只会cache最终的结果集;
③ 在procedure,function以及trigger中的query不能被cache;
④ 包含其他很多每次执行可能得到不一样结果的函数的query不能被cache。
鉴于上面的这些限制,在使用Query Cache的过程中,建议通过精确设置的方式来使用,仅仅让合适的表的数据可以进入Query Cache,仅仅让某些Query 的查询结果被Cache。

本文大部分文字资料来自于:http://blog.csdn.net/tonyxf121/article/details/7856361