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

落日映苍穹つ 2022-05-12 22:03 181阅读 0赞

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

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

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”：QueryCache中目前还有多少剩余的blocks。如果该值显示较大，  
则说明QueryCache中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。  
●“Qcache\_free\_memory”：QueryCache中目前剩余的内存大小。通过这个参数我们可以较为准  
确的观察出当前系统中的QueryCache内存大小是否足够，是需要增加还是过多了；  
●“Qcache\_hits”：多少次命中。通过这个参数我们可以查看到QueryCache的基本效果；  
●“Qcache\_inserts”：多少次未命中然后插入。通过“Qcache\_hits”和“Qcache\_inserts”两  
个参数我们就可以算出QueryCache的命中率了：  
QueryCache命中率=Qcache\_hits/(Qcache\_hits+Qcache\_inserts)；  
●“Qcache\_lowmem\_prunes”：多少条Query因为内存不足而被清除出QueryCache。通过  
“Qcache\_lowmem\_prunes”和“Qcache\_free\_memory”相互结合，能够更清楚的了解到我们系  
统中QueryCache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换  
出  
●“Qcache\_not\_cached”：因为query\_cache\_type的设置或者不能被cache的Query的数量；  
●“Qcache\_queries\_in\_cache”：当前QueryCache中cache的Query数量；  
●“Qcache\_total\_blocks”：当前QueryCache中的block数量；  
  
QueryCache的限制  
QueryCache由于存放的都是逻辑结构的ResultSet，而不是物理的数据页，所以在性能提升的同  
时，也会受到一些特定的限制。  
a)5.1.17之前的版本不能Cache帮定变量的Query，但是从5.1.17版本开始，QueryCache已经  
开始支持帮定变量的Query了；  
b)所有子查询中的外部查询SQL不能被Cache；  
c)在Procedure，Function以及Trigger中的Query不能被Cache；  
d)包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。  
鉴于上面的这些限制，在使用QueryCache的过程中，建议通过精确设置的方式来使用，仅仅让合  
适的表的数据可以进入QueryCache，仅仅让某些Query的查询结果被Cache。

另外，如果Qcache\_free\_blocks值有点偏高,可以用flushquerycache来清理下。

**一个朋友的建议：**

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

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

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