查询mysql的缓存优化详解

本文主要给大家介绍查询mysql的缓存优化详解，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在亿速云行业资讯里面关注我的更新文章的。

写在前面：查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除。
Query cache 作用于整个 MySQL，主要用来缓存 MySQL 中的查询结果集，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，如果命中缓存，也就是说所需结果集已经在缓存中了，后面一系列步骤都不用再执行，直接从缓存拿到结果集返回给客户端，可以极大的提高查询性能！
sql 查询数据库 执行步骤如下：

从上图可以看出，当select 命中缓存后，后面一系列的sql解析，预处理，优化器处理都不用进行！

数据库缓存打开方式：

query_cache_size参数可以根据自己数据库查询运行状况进行自行调整，调整原则稍后介绍！

query_cache_type可以设置为0，1或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）

可以用如下命令show variables like '%have_query_cache%';查看是否开启，其中have_query_cache为是否开启，query_cache_limit 指定单个查询能够使用的缓冲区大小，缺省为1M；query_cache_min_res_unit为系统分配的最小缓存块大小，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据 查询，就容易造成内存碎片和浪费；

通过以下命令查看缓存相关变量 show variables like '%query_cache%';

通过以下命令查看查询缓存状态 show status like '%Qcache%';

Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache的次数，也就是没有命中的次数
Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache以给新的 Cache 对象使用的次数
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于query_cache_type 设置的不会被 Cache 的 SQL
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
Qcache_total_blocks：Query Cache 中总的 Block 数量
检查换成命中情况
查询缓存命中率的计算公式是 Qcache_hits/(Qcache_hits+Com_select)。
query_cache_min_res_unit默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERYCACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) /query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes >50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

优化提示：
如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache

高性能mysql中关于query_cache_min_res_unit大小调优给出的计算公式 query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache

mysql在判断是否命中缓存时，任何字符的不同 ，不确定的函数 都将导致缓存无法命中，子查询，存储过程也将导致查询缓存无法命中；
mysql查询缓存在带来查询性能提升的同时，也会带来其他的一些问题：首先、打开查询缓存，对读对写都会带来额外的消耗
1）首先在读查询之前，必须先去检查是否命中缓存
2）如果命中缓存，那么直接返回结果集给用户，不在执行后续一系列操作，如果没有命中缓存，那么在查询完成后，还需要将结果集存入查询缓存，这本身也是一个消耗
3）对写操作也有很大影响，因为我们在对表进行时，也需要先将该表的查询缓存设置为失效，如果这个表的缓存很大，那么这也是一个相当大的消耗。
对查询缓存的写和设置失效操作都是一个排他锁操作
1）对于innodb 来说，事务特性会限制查询的使用，事务中修改了某个表，那么，需要将这个表的查询缓存设置为失效，RR，RC 模式下，事物特性会隔离这个修改对其他事务不可见！在这个事务提交前，这个表的其他相关查询都是无法缓存的，只有在这个表修改事物提交后，这个表的相关查询才可以缓存，因此，长时间的大的更新事务将导致查询缓存效率大大下降！
2）如果我们开启了一个很大的查询缓存，在缓存失效操作时，可能导致整个数据库系统都出现性能问题，因为缓存失效操作是一个全局锁，其他
所有需要做缓存失效操作的查询都要等待这个锁，而且无论缓存命中检测，还是缓存失效检测都需要等待这个锁。缓存过大时，这将导致严重的性能瓶颈。
因此我们在开启查询缓存时，一定是根据业务需要，开启一个大小比较合理的查询缓存，在线上避免大的事务！

看了以上关于查询mysql的缓存优化详解，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。