query cache 的内存使用和调优

mysql 的query cache 大小设置（ query_cache_size）最小只能设置为40k, 且bytes数最好设置为1024的倍数，并且要注意 当 query_cache_type为0时,mysql也会为query cache分配 query_cache_size大小的内存，这个就可能不是你想要的了。
mysql 的query cache 是完全存放在内存中的，当mysql 重起的时候，cache 中的内容会完全丢失。
在mysql 启动初始化的时候，会分配一整块连续的（ query_cache_size大小的）内存.在系统运行过程中，缓存数据时，会从这块内存中分配最小为query_cache_min_res_unit大小的，且每次分配的都会是这个参数的整数倍大小的内存区块。
除了这些存放缓存数据的block外，还有两个hash表，一个hash表存放hash过的 sql语句，另一个存放着cache过的这些语句所引用到的 数据库表，当数据库中有表改动的时候，就需要check一下这个hash表看是否需要使 query cache中相关的缓存失效（query cache invalidation）
关于一些 query cache相关的 系统变量可以参见以下（从相关书籍文档中直接copy过来的没有翻译）：
Query Cache System Variables:
query_cache_limit: Result sets larger than this are not cached. The default is 1 Mb.
query_alloc_block_size: Specifies the size of memory blocks allocated by the
query cache during query processing. The default is 8 Kb.
query_cache_min_res_unit:Specifies the minimum size for blocks in the query
cache. The default value is 4 Kb.
query_cache_size: The total memory allocated by mysqld for the query
cache. The default is 0, which disables the query cache.
query_cache_type: Determines the mode of operation of the query cache. Options are OFF,
ON, and DEMAND. The default is ON.
query_cache_wlock_invalidate: If set to TRUE, queries referencing MyISAM tables are invalidated
when a write lock for that table is obtained, even if none of the data
is changed when the write lock is released. The default is FALSE.
query_cache_prealloc_size: Specifies the size of the buffer used for query parsing
by the cache. Defaults to 8 Kb.
还有一些状态信息的变量（Status Variable）：
Query Cache Status Variables:
Qcache_free_blocks： Number of memory blocks free in the query cache
Qcache_free_memory ： Total bytes of memory free in the query cache
Qcache_hits： Number of times a query matched the query cache
Qcache_inserts： Number of times a query and result set were inserted into the query cache
Qcache_lowmem_prunes： Number of times a query was removed due to query pruning
Qcache_not_cached： Number of queries that could not be cached
Qcache_queries_in_cache： Number of queries currently stored in the query cache
Qcache_total_blocks： Total number of memory blocks in cache
一般query cache tuning 所做的就是要针对这些 status variables 调整那些system variables,这些变量和状态信息都可用 SHOW GLOBAL Variables或SHOW GLOBAL STATUS 查看，在INFORMATION_SCHEMA中也可以查看。
下面我们就用以上的状态信息看看一些指标（我会列举某个系统中的一些实际数据，版本为 5.0.45）。
1：缓存命中率(query cache hit ratio)。这个指标的计算公式是缓存命中次数 除以 命中次数与未命中次数（Com_select即为 未从cache中找到缓存并执行查询计划的语句）的总和
Qcache_hits / (Qcache_hits + Com_select)
223254326 /(223254326 +38402673) * 100%= 85.32%
2：缓存的 插入率 （insert ratio）
Qcache_inserts / Com_select
37878146/38402673 * 100% = 98.63%
这个百分比比较高，说明大部分select语句都放入到了query cache, 再来看一下Qcache_lowmem_prunes这个数值的大小 为 70013，看来情况还是很好的，在98.63%的语句都放入了query cache里面的情况下 ，pruning次数还是很少的。
3: 空缓存所占百分比，可以分 字节 和 块计算。
Qcache_free_memory / query_cache_size * 100%
Qcache_free_blocks / Qcache_total_blocks * 100%
如果空缓存率很高，那么就应该减少 query_cache_size ， 或者 cache更多的查询。
如果这个值较低，而同时 Qcache_queries_in_cache 也比较低，那么就有可能是以下几种情况了：
1）query_cache_size需要增大来缓存更多的查询。
2）query_cache_limit 需要减少，来减少打数据结果集的语句。
3）query cache 中出现了很多碎片，需要清理了。

我们下面先看看怎么cache更多的查询，然后讨论一下碎片的问题。
当你认为你的query cache 的memory 利用率不够的话，可以采用以下几种方式cache更多查询。
1）尽少利用 SELECT SQL_NO_CACHE （query_cache_type 如果是ON的话）
2）尽多地利用SELECT SQL_CACHE （query_cache_type 如果是DEMAND的话）
3）Qcache_not_cached是不能被缓存的查询，如果这个数值很大的话，加大query_cache_limit的值来让有更大结果集的查询放入到缓存。
4）如果Qcache_lowmem_prunes 和Qcache_free_memory 值都很高的话，那很有可能是出现碎片了，那么就需要清理query cache了。

在mysql中主要有两方面导致了碎片的出现：
1）前面说过，query cache在使用时是按照block分配的，那么结果集大小不可能正好合适。query cache的管理程序一开始是不知道结果集大小的，因为他接受数据是row by row的，当所有结果集都存放到缓存中后，管理程序就会裁减一下一开始分配的内存而使其正好和结果集大小一致。
2）其次就是由于query cache的失效（invalidation ）。缓存失效时，缓存的数据会从query cache 中删除，这样就出现了query cache的空洞（hole）。
怎么样来避免query cache 碎片化呢？
一个方法就是调整query cache 的block 大小，将其设置为所有结果集大小的平均值：
(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache
当然这只是一般的方法，有些时候是不是用的，比如结果集有很多很大的也有很多很小的。
另外一个方法就是减小query cache 的prune次数 和 invalidation的次数。怎么减小就是一个比较复杂的问题了。当不能减少UPDATE, INSERT和 DELETE语句的频度时，可以将大表分成小表，也可以用merge table或者partitioning。
当很难避免碎片时，我们可以把query_cache_type设置为DEMAND ， 用SQL_CACHE缓存你想缓存的查询。出现了碎片后，我们可以用 FLUSH QUERY CACHE 语句整理一下query cache， 这条语句不会使query cache里的数据丢失，它只是整理内存去掉碎片（defragment），但是要注意如果 query_cache_size设置得比较大的话 ，这段时间将会比较久，而这段时间内几乎所有的查询语句和别的DML 、DDL语句都会等待defragment的完成，所以做这个操作的时候请be careful！
最后，如果要清除掉query cache里面的内容的话可以用 RESET QUERY CACHE 。