Mysql的缓存原理

Mysql Query Cache

Mysql的缓存功能

对于一个并没有那么复杂的系统，但是某几个表特别大的时候，缓存是一个很实用的一个功能，是SQL优化和查询提速的常用操作，开启Mysql自带的此功能，实现查询结果缓存的同时，缓存失效的维护由数据库自己完成，而不像一般的Spring框架要通过代码或者过期自动超时来维护。当然，这个功能并不适合海量数据缓存，Mysql缓存还设计内存碎片的问题，需要定期手动维护。也不适合高性能数据缓存的业务场景，专业的缓存还是要用redis、ehcache来处理。

mysql缓存维护的方式简单暴力，只要查询涉及的表中有更新操作，就清空该表相关的所有缓存（多表关联查询就更严重，但凡有一个表更新了，该SQL缓存就失效了），基于这种逻辑就可以保证所有的数据是最新的，不用涉及超时时间的概念。当然使用缓存功能会增加一部分数据的开销，来实现热点数据的缓存快速查询，从而提升用户的体验。
结合业务场景，一般都是开启DEMAND模式，使用在不需要频繁变动的表上。频繁变更的表上如果开启缓存功能，会影响数据库的维护性能。

  1. MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
  2. 既然是缓存，肯定是保存在内存当中，重启后会失效。
  3. 如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，
  4. 对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能
  5. 缓存不可用的情况：
  • 使用缓存必须确保SQL完全一样，SQL字母大小写、空格、注释等都会导致缓存匹配失败
  • SQL中包含不确定的函数，则缓存功能不可用，比如 now()，CURRENT_DATE() ，比如 select * from wx_ib where war_exp_date=CURRENT_DATE();
  • 缓存要查询的表，正在被另外一个事务修改数据
  1. 注意事项：
  • 比较遗憾的是，缓存清空的机制是目标表有任何更新操作，都会导致该表的所有缓存失效，而不能实现行级别的缓存清空
  • 事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；

PK 其它缓存

  1. Mysql清除缓存的方式太过暴力，只要缓存查询的SQL查询，涉及到的表有更新操作，那么这个缓存查询就会失效，无法实现行级的缓存清除
  2. Spring集成第三方缓存工具和相关注解，可以实现更灵活的缓存处理，比如清除某个key的缓存，而不是整个表的缓存都清除
  3. 专业的缓存工具一般都支持，缓存数据持久化、FIFO策略等功能

Mysql缓存功能的开启

  1. 配置和启动：修改mysql安装目录下面的my.ini文件，添加query_cache_type=2(开启DEMAD模式)，然后重启mysql（windows重启的话，admin模式 net stop mysql ，net start mysql。 linux： service mysql restart）
    缓存开启的设置：query_cache_type
  2. OFF: 关闭，默认 值为0
  3. ON: 总是打开，值为1
  4. DEMAND: 只有明确写了SQL_CACHE的查询才会进入缓存，值为2
  • query_cache_type 这个参数windows下必须在my.ini文件里配置，默认是0 表示关闭缓存功能，设置为1 表示开启全部的缓存，设置为2表示DEMAND模式
  • query_cache_limit 限制查询缓存区最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置；，支持在线配置，立即生效
  • query_cache_min_res_unit 设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K
  • Qcache_hits 查看命中缓存查询的次数
  1. #my.ini里面配置
  2. query_cache_type=2
  3. set global query_cache_limit = 1024 * 1024 * 5;
  5. show status like '%qcache_hits%';
  1. 缓存的使用： SQL_NO_CACHE/ SQL_CACHE
  1. select  SQL_CACHE * from wx_ib where product_id=1246 and war_exp_date='2016-08-07';
  2. select  SQL_NO_CACHE * from wx_ib where product_id=1246 and war_exp_date='2016-08-07';

缓存开启的时机

  1. 缓存开启带来的必要开销：
  • 该表发生事务操作的时候，都要清空相关的缓存
  • 在query_cache_type为ON的模式下，该数据库全部的查询都会先尝试缓存，不管该查询是否会有热点数据，而实际的业务场景并不是这样。所以更建议使用DEMAND模式。
  • 开启缓存后，所有的Query完成之后，都要把查询结果插入到缓存当中

参考

https://www.jianshu.com/nb/33413483