关于MySQL Query Cache的一些交流心得

 

今天线上MySQL 出现内存使用率报警，就去查了下mysql内存使用的参数，重点是缓存，关于innodb_buffer_pool_size以及query cache的使用。

query_cache_type  默认是打开的，而且缓存区query_cache_size默认大小是32M，通常建议不超过256M大小，可以用过查询cache参数来看具体值：

[sql] 
mysql> show variables like '%cache%';  
+------------------------------+----------------------+  
| Variable_name                | Value                |  
+------------------------------+----------------------+  
| binlog_cache_size            | 32768                |  
| binlog_stmt_cache_size       | 32768                |  
| have_query_cache             | YES                  |  
| key_cache_age_threshold      | 300                  |  
| key_cache_block_size         | 1024                 |  
| key_cache_division_limit     | 100                  |  
| max_binlog_cache_size        | 18446744073709547520 |  
| max_binlog_stmt_cache_size   | 18446744073709547520 |  
| metadata_locks_cache_size    | 1024                 |  
| query_cache_limit            | 1048576              |  
| query_cache_min_res_unit     | 4096                 |  
| query_cache_size             | 33554432             |  
| query_cache_type             | ON                   |  
| query_cache_wlock_invalidate | OFF                  |  
| stored_program_cache         | 256                  |  
| table_definition_cache       | 400                  |  
| table_open_cache             | 512                  |  
| thread_cache_size            | 8                    |  
+------------------------------+----------------------+  
18 rows in set (0.00 sec)  
  
  
mysql>  

 

 

“Qcache_free_blocks”：Query Cache 中目前还有多少剩余的blocks。如果该值显示较大，

则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。

● “Qcache_free_memory”：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准

确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了；

● “Qcache_hits”：多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果；

● “Qcache_inserts”：多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两

个参数我们就可以算出Query Cache 的命中率了：

Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；

● “Qcache_lowmem_prunes”：多少条Query 因为内存不足而被清除出Query Cache。通过

“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系

统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换

出

● “Qcache_not_cached”：因为query_cache_type 的设置或者不能被cache 的Query 的数量；

● “Qcache_queries_in_cache”：当前Query Cache 中cache 的Query 数量；

● “Qcache_total_blocks”：当前Query Cache 中的block 数量；

 

Query Cache 的限制

Query Cache 由于存放的都是逻辑结构的Result Set，而不是物理的数据页，所以在性能提升的同

时，也会受到一些特定的限制。

a) 5.1.17 之前的版本不能Cache 帮定变量的Query，但是从5.1.17 版本开始，Query Cache 已经

开始支持帮定变量的Query 了；

b) 所有子查询中的外部查询SQL 不能被Cache；

c) 在Procedure，Function 以及Trigger 中的Query 不能被Cache；

d) 包含其他很多每次执行可能得到不一样结果的函数的Query 不能被Cache。

鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合

适的表的数据可以进入Query Cache，仅仅让某些Query 的查询结果被Cache。

 

另外，如果Qcache_free_blocks值有点偏高,可以用flush query cache 来清理下。

一个朋友的建议：

 第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。

 

 第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。

 

第三个：小网站或者没有高并发的无所谓，高并发下，会看到 很多 qcache 锁 等待，所以一般高并发下，不建议打开query cache