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高性能第三版，http://blog.csdn.net/truelove12358/article/details/51956356