MySQL5.7.18-查询缓存参数优化-query_cache
MySQL Query Cache 会缓存select 查询，安装时默认是开启的，但是如果对表进行INSERT, UPDATE,DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等操作时，之前的缓存会无效并且删除。这样一定程度上也会影响我们数据库的性能。所以对一些频繁的变动表的情况开启缓存是不明智的。还有一种情况我们测试数据库性能的时候也要关闭缓存，避免缓存对我们测试数据的影响。下面介绍两种不使用缓存的方案：
MySQL的查询缓存是MySQL内置的一种缓存机制，可以针对sql进行缓存。比如我们发送select* from mysql.user这么一个查询，MySQL首先检索内存中是否有数据并且数据是否过期，如果没有数据或者数据已经过期就去数据库中查找，如果有数据并且没有过期就直接返回数据。对于sql的匹配规则非常简单，就是字符串的比较，只要字符串相同，那么就认为是同一个查询。这里的字符串相同并不是表示sql语义相同，而是查询的sql字符串相同，空格也不行。MySQL的缓存是对全部的sql有效的，也就是说一旦开启了查询缓存，那么对所有的sql查询默认都是开启的。我们会有很多变化的数据，其实是不希望开启查询缓存的，这个MySQL也给我们想到了，就是在sql中加入sql_no_cache 比如，select sql_no_cache *from mysql.user这样，MySQL就会绕过缓存直接从库里查找数据。另外还有一个需要注意的，比如我们在sql中使用了now()这样的函数，MySQL是不会给我们缓存的。
工作原理
查询缓存的工作原理，基本上可以用二句话概括：
l 缓存SELECT操作或预处理查询（注释：5.1.17开始支持）的结果集和SQL语句；
l 新的SELECT语句或预处理查询语句，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写；
查询缓存对什么样的查询语句，无法缓存其记录集，大致有以下几类：
l 查询语句中加了SQL_NO_CACHE参数；
l 查询语句中含有获得值的函数，包涵自定义函数，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；
l 对系统数据库的查询：mysql、information_schema
l 查询语句中使用SESSION级别变量或存储过程中的局部变量；
l 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句
l 查询语句中类似SELECT …INTO 导出数据的语句；
l 事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；
l 对临时表的查询操作；
l 存在警告信息的查询语句；
l 不涉及任何表或视图的查询语句；
l 某用户只有列级别权限的查询语句；
查询缓存的优缺点：
l 不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果；
l 查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
l Query Cache的起用，会增加检查和清理Query Cache中记录集的开销，而且存在SQL语句缓存的表，每一张表都只有一个对应的全局锁；
MySQL查询缓存
用于保存MySQL查询语句返回的完整结果，被命中时，
MySQL会立即返回结果,省去解析、优化和执行等阶段
并发查询量非常大、cup核心数非常多时缓存并不一定有效
(多个线程会争用存储缓存的这段内存空间)
如何检查缓存???
MySQL保存结果与缓存中：
把select语句本身做hash计算,计算的结果作为key,查询结果作为value
什么样的语句不会被缓存？
查询语句中有一些不确定数据时,不会缓存；例如now(),current_time();
一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存
缓存会带来额外开销
1、每个查询都会先检查是否命中
2、查询结果要先缓存(写缓存)
是否启用mysql查询缓存，可以通过2个参数：query_cache_type和query_cache_size，其中任何一个参数设置为0都意味着关闭查询缓存功能，但是正确的设置推荐query_cache_type=0。
l query_cache_type
值域为：0 -– 不启用查询缓存；
值域为：1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集斗可以
缓存起来，共其他客户端使用；
值域为：2 -– 启用查询缓存，只要查询语句中添加了参数：sql_cache，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，共其他客户端使用；
l query_cache_size
允许设置query_cache_size的值最小为40K，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大，查询缓存的命中率就越高，也不是对服务器负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置 为：64M；
l query_cache_limit
限制查询缓存区最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置；
l query_cache_min_res_unit
设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K
l query_cache_wlock_invalidate
该参数主要涉及MyISAM引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认设置为0，也即允许；
mysql> show global variables like"query_cache%";
Variable_name | Value | desc |
query_cache_limit | 1048576 | Mysql允许缓存的单个缓存对象的最大值，不过mysql只有在查询的所有结果都返回后才知道其是否超出此大小，但是在查询一开始便会尝试使用缓存存储查询结果，一旦发现超过可缓存最大值则会从缓存中将其删除，并增大Qcache_not_cached的值，因此，如果知道某查询的结果会超出可缓存的最大值的时候，应该在查询语句中使用SQL_NO_CACHE |
query_cache_min_res_unit | 4096 | 存储缓存的最小内存块，这个值过小，会减少空间浪费，但是会导致更频繁的内存块申请操作，设置的过大，会有着更高的碎片产生率，可以通过(query_cache_size - Qcache_free_memory) / Qcache_queryer_in_cache 来获得一个接近理想的值，同时，如果Qcache_free_blocks存在空闲块，但是Qcache_lowmem_prunes的值仍然在增长，则表明碎片过多导致了缓存结果会过早删除 |
query_cache_size | 33554432 | 查询缓存的内存总大小,其必须是1024的整数倍，单位为字节。Mysql启动时，一次性分配并且初始化这里指定大小的内存空间，改变其值，mysql会立刻删除所有的缓存对象并重新配置其大小及初始化，在性能较强的通用服务器上，查询缓存可能会成为影响服务器扩展的因素，因为它存在成为服务器资源竞争单点的可能性，在多核心的服务器上甚至还有可能导致服务进程宕机 |
query_cache_type | ON | 是否打开查询缓存,其可用值有OFF，ON和DEMAND，DEMAND仅在查询语句中显式使用SQL_CACHE时才会使用缓存 |
query_cache_wlock_invalidate | OFF | 如果某个数据表被其它的链接锁住，是否仍然从查询缓存中返回结果。OFF表示返回 |
MySql运行产生的状态值
mysql> show global status like'qcache%';
Variable_name | Value | desc |
Qcache_free_blocks | 1057 | 已经分配了块，但是尚未存储数据 , 如果该值显示较大，则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理。 |
Qcache_free_memory | 21667632 | 没有被申请划分为数据块的部分 |
Qcache_hits | 2379301 | 缓存命中次数 |
Qcache_inserts | 412811 | 执行向缓存中插入缓存对象的次数 |
Qcache_lowmem_prunes | 0 | 因为内存太小不能继续存储的对象个数, 如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。 |
Qcache_not_cached | 1248823 | 查询没被缓存的个数 |
Qcache_queries_in_cache | 1804 | 保存在缓存中的查询个数 |
Qcache_total_blocks | 5043 | 已经分配好的块个数 |
mysql> showglobal status like 'Qcache%';
+-------------------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------------------+----------+
| Qcache_free_blocks（空闲块数） | 1 |
| Qcache_free_memory（空闲空间） | 16759688 |
| Qcache_hits（命中次数） | 0 |
| Qcache_inserts (插入次数) | 2 |
| Qcache_lowmem_prunes（内存太小,修剪内存的次数） | 0 |
| Qcache_not_cached（没被缓存的个数） | 8 |
| Qcache_queries_in_cache（缓存中缓存的查询个数） | 0 |
| Qcache_total_blocks（总块数） | 1 |
+-------------------------+----------------------------------+
如何确定MySql中是否缓存了刚才执行的SQL语句
mysql中的缓存是 key-value hash hash区分大小写
MySql那些东西不会被缓存
1.不确定性的内容
2. 用户自定义函数
3. 用户自定义变量
4. 临时表
5. mysql用的系统表
6. 列级别的权限
7. 存储函数
8. 不确定数据
命中率估算
mysql> show global status wherevariable_name="Qcache_hits" or variable_name="Com_select";
Variable_name | Value |
Com_select | 1703848 |
Qcache_hits | 2421498 |
1. hits rate(命中率) = Qcache_hits/(Qcache_hits+Com_select)不过。这个未必能反应真实情况！命中率>30%就可以认为有效
2. 也应该经常查询另一个指标，命中和写入的比率，即Qcache_hits和Qcache_insert的比值，次比例大于3:1时通常查询缓存是有效的，能达到甚至大于10:1就更好了。
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%
示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 =1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。
优化提示：
如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache
根据我看的 《High PerformanceMySQL》中所述，关于query_cache_min_res_unit大小的调优
，书中给出了一个计算公式，可以供调优设置参考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache
性能监控
l 碎片率
查询缓存内存碎片率=Qcache_free_blocks /Qcache_total_blocks * 100%
l 命中率
查询缓存命中率=(Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
l 内存使用率
查询缓存内存使用率=(query_cache_size – Qcache_free_memory) / query_cache_size * 100%
l Qcache_lowmem_prunes
该参数值对于检测查询缓存区的内存大小设置是否，有非常关键性的作用，其代表的意义为：查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息，删除算法为LRU；
l query_cache_min_res_unit
内存块分配的最小单元非常重要，设置过大可能增加内存碎片的概率发生，太小又可能增加内存分配的消耗，为此在系统平稳运行一个阶段性后，可参考公式的计算值：
查询缓存最小内存块 = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
l query_cache_size
我们如何判断query_cache_size是否设置过小，依然也只有先预设置一个值，推荐为：32M~128M之间的区域，待系统平稳运行一个时间段（至少1周），并且观察这周内的相关状态值：
(1). Qcache_lowmem_prunes；
(2). 命中率；
(3). 内存使用率；
若整个平稳运行期监控获得的信息，为命中率高于80%，内存使用率超过80%，并且Qcache_lowmem_prunes的值不停地增加，而且增加的数值还较大，则说明我们为查询缓冲区分配的内存过小，可以适当地增加查询缓存区的内存大小；
若是整个平稳运行期监控获得的信息，为命中率低于40%，Qcache_lowmem_prunes的值也保持一个平稳状态，则说明我们的查询缓冲区的内存设置过大，或者说业务场景重复执行一样查询语句的概率低，同时若还监测到一定量的freeing items，那么必须考虑把查询缓存的内存条小，甚至关闭查询缓存功能；
缓存整理操作
1.FLUSH QUERY_CACHE, 命令可以用于完成碎片整理，但会导致服务器系统僵死一段时间
2. 要清空缓存，可以使用RESET QUERY_CACHE
FLUSH QUERY CACHE; // 清理查询缓存内存碎片。
RESET QUERY CACHE; // 从查询缓存中移出所有查询。
FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。
碎片整理 mysql >flush query_cache
清空缓存 mysql >reset query_cache
通用缓存优化思路
1.批量写入而非单个写入，批量写入仅一次性影响缓存
2. 过大的缓存空间会使的在大量缓存对象过期失效时导致服务器假死
3. 必要时，使用SQL_CACHE和SQL_NO_CACHE手动控制缓存动作。
4. 对写密集型的场景来说，禁用缓存可以提高性能。
5. 如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
.批量写入，而非多次单个写入
.缓存空间不宜过大，因为大量缓存同事失效时会导致服务器假死
.必要时,使用sql_CACHE和sql_no_cache手动控制缓存。
.对写密集型的应用场景来说，禁用缓存反而提高性能。
通过上述的知识梳理和分析，我们至少知道查询缓存的以下几点：
l 查询缓存能够加速已经存在缓存的查询语句的速度，可以不用重新解析和执行而获得正确得记录集；
l 查询缓存中涉及的表，每一个表对象都有一个属于自己的全局性质的锁；
l 表若是做DDL、FLUSH TABLES 等类似操作，触发相关表的查询缓存信息清空；
l 表对象的DML操作，必须优先判断是否需要清理相关查询缓存的记录信息，将不可避免地出现锁等待事件；
l 查询缓存的内存分配问题，不可避免地产生一些内存碎片；
l 查询缓存对是否是一样的查询语句，要求非常苛刻，而且还不智能；
我们再重新回到本节的重点上，查询缓存适合什么样的业务场景呢？只要是清楚了查询缓存的上述优缺点，就不难罗列出来，业务场景要求：
l 整个系统以读为主的业务，比如门户型、新闻类、报表型、论坛等网站；
l 查询语句操作的表对象，非频繁地进行DML操作，可以使用query_cache_type=2模式，然后SQL语句加SQL_CACHE参数指定；
