【mysql性能优化】查询缓存

【最近在学习Mysql性能优化，以下是知识总结】

查询缓存

1.概念

当执行相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

2.使用缓存（相关命令）

SHOW VARIABLES LIKE 'query_cache%';
Variable_name                   Value
query_cache_limit               1048576 //MySQL能够缓存的最大查询结果
query_cache_min_res_unit        4096    //在查询缓存中分配内存块时的最小单位
query_cache_size                1048576 //查询缓存使用的总内存空间，单位是字节，这个值必须是1024的倍数
query_cache_type                OFF     //是否打开缓存，值可以是OFF,ON,DEMAND,其中DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。
query_cache_wlock_invalidate    OFF     //如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。参数默认是OFF,当为ON时，则不会从缓存中读取这类数据。


set session query_cache_type = demand   
select @@global.query_size                  //缓存大小
set @@global.query_cache_size = 1048576     //设置缓存大小
//指定查询是否使用缓存：
select SQL_CACHE post_title,post_content from p_posts;
select SQL_NO_CACHE post_title,post_content from p_posts;

show global status like 'Qcache_hits';              //缓存命中数
Com_select  com_update com_insert                   //统计信息
//锁定状态：show global status like '%lock%';    
Table_locks_waited/Table_locks_immediate            //值越大，表锁导致的阻塞比较严重
Innodb_row_lock_waits       
//临时表：Created_tmp_disk_tables/Created_tmp_tables 低于10%

3.查询缓存对性能的提升

3.1需要消耗大量资源的查询适合使用缓存，
1.汇总计算查询；如count();
2.复杂的SELECT语句；例如：1）多表JOIN后还要做排序和分页，这类查询执行消耗很大，但结果集很小，非常适合做查询缓存（注意：涉及的表上修改操作要很少才行）

查询命中率公式：Qcache_hits/(Qcache_hits+Com_select)

没有一个简单的规则可以判断查询缓存是否对系统有好处。
1.只要查询缓存带来的效率提升大于查询缓存带来的额外消耗，即使命中率低到30%也是对系统性能提升有好处的；
2.另外，缓存了哪些查询也是很重要，例如，被缓存查询本身消耗非常巨大，那么即使缓存命中率非常低，也仍然会对系统性能提升有好处。

3.2缓存没有被命中的原因Qcache_hits
1.查询语句不合法，查询语句无法被缓存，查询中可能有变量，自定义函数等（下面缓存失效的场景会有详细总结）
2.MYSQL从未处理过这个查询，所以结果也从不曾缓存过。
3.缓存内存空间不足，虽然之前缓存了查询结果，但是由于查询缓存的内存用完了，MySQL需要将某些缓存“逐出”，或者由于数据表被修改导致缓存失效。

3.3缓存失效的原因
1.缓存碎片
2.内存不足
3.数据修改
根据以上三大原因：
分析和配置查询缓存如下图

1）观看数据修改的情况：参数Com_*来查看数据修改的情况（如：Com_update、Com_delete等）
2）看看多少次失效是否由于内存不足：参数Qcache_lowmen_prunes

如果缓存的结果在失效前没有被任何其他的SELECT语句使用，那么这次缓存操作就是浪费时间和内存。
Qcache_inserts和Com_select的相对值可以看看是否一直有这种情况发生。
如果每次查询操作都是缓存未命中，然后需要将查询结果放到缓存中，那么Qcache_inserts的大小应该和Com_select相当，所以缓存完成预热后，我们总希望看到Qcache_inserts远小于Com_select。不过由于缓存和服务器内部的复杂和多样性，仍然很难说，这个比率是多少才是一个合适的值。

Qcache_hits/Qcache_inserts这个值达到10:1时，查询缓存才能带来性能提升（在查询缓存并没有成为系统的瓶颈时）

3.4减少缓存碎片
缓存碎片跟选择合适的query_cache_min_res_unit有关。
1.query_cache_min_res_unit设置的越小，浪费的空间就更少，但是会导致频繁的内存块申请操作；
2.query_cache_min_res_unit设置的越大，那么碎片就会越多。
这个值的最合适的大小跟应用程序的查询结果的平均大小直接相关
(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache

3.5FLUSH QUERY CACHE：完成碎片整理
这个命令会将所有的查询缓存重新排序，并将所有的空闲空间都聚集到查询缓存的一块区域上
注意:在这期间会访问所有的查询缓存，在这期间任何其他的连接都无法访问查询缓存，从而会导致服务器僵死一段时间，使用这个命令的时候需要特别小心这一点。另外，根据经验，建议保持查询缓存空间足够小，以便在维护时可以将服务器僵死控制在非常短的时间内。
3.6InnoDB和查询缓存
*等待……*

4.缓存失效的场景

1.请注意SQL语句编写规范：SQL大小写不同，任何字符上的不同，例如空格、注释（任何不同）都会导致缓存的不命中。【这个规则对Percona Server是个例外】
2.注意规范：关键词大写，保留关键字被当成字段或者条件的时候加上“ 反单引号，统一查询条件中变量的引号

1. 用户自定义函数、存储函数；
2. 临时表；
3. 子查询、视图
4. 预存储语句
5. 权限不足的用户查询数据不缓存
6. 事务隔离级别为Serializable的查询语句不缓存
7. 局部变量不缓存
8. 下面这些SQL语句：1）SELECT …. IS NULL
   2）SELECT … INTO OUTFILE(DUMPFILE)
   3）SELECT … FOR UPDATE
   4）SELECT …IN SHARE MODE

5.如何优化缓存

1. 通过分区表提高缓存命中率，设计数据库的时候使用多个小表
2. 尽量执行一次性写入操作
3. 对于写操作非常频繁的表不需要使用查询缓存（query_cache_size=0）
4. 使用demand(query_cache_type ),通过SQL_CACHE来决定是否使用缓存；少数查询不希望缓存，可以加入SQL_NO_CACHE
5. 因为对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用也会有好处。如果你希望提高系统的并发，那么最好做一个相关的测试，对比打开和关闭查询缓存时候的性能差异。

6.缓存如何利用内存

如图所示：
1.申请数据块，这个数据块需要大于参数query_cache_min_res_unit空间，即使查询结果远远小于此，仍需要至少申请query_cache_min_res_unit空间。
2.用内存块存储查询结果
3.释放空闲内存部分。如果申请的内存空间还有剩余，Mysql会将其释放。