mysql数据库性能优化 - 查询缓存

查询缓存

      缓存机制简单的说就是缓存sql文本和查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要去解析和执行sql，如果表更改了，那么使用这个表的所有缓存查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括insert、update、delete、truncate、alter table、drop table或drop database等。这对频繁更新的表，查询缓存是不适合的，而对一些不常改变数据且大量相同的sql查询的表，查询缓存会节约很大的性能。

     查询必须是完全相同的才能够被认为是相同的。另外，同样的查询字符串由于其他原因可能认为是不同的，使用不同的数据库、不同的协议版本或者不同的默认字符集的查询被认为是不同的查询并且分别进行缓存。

 1、query cache开启方法

编辑mysql的my.cnf，添加如下内容，然后重启mysql：

• query_cache_size = 268435456   --设置query cache所使用的内存大小，默认为0，大小必须是1024的整数部
• query_cache_type = 1   --给所有的查询做cache
• query_cache_limit = 1048576  --允许cache的单条query结果集的最大容量，默认是1MB，超过此参数设置的query结果集将不会被cache

 2、query cache运行状态分析

      show status like ‘Qcache%‘; 

• Qcache_free_blocks：数目大说明可能有碎片
• Qcache_free_memory：缓存中的空闲内存
• Qcache_hits：每次查询在缓存中命中时就增大
• Qcache_insert：每次插入一个查询时就增大
• Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看，如果不断增长，标示可能碎片非常严重，或者内存很少。（结合Qcache_free_blocks和Qcache_free_memory分析属于哪种情况）
• Qcache_total_blocks：缓存中块的总数量
• Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

 3、query cache设置状态分析

       show  variables like ‘%query_cache%‘;

• query_cache_limit：允许cache的单条query结果集的最大容量，默认是1MB，超过此参数设置的query结果集将不会被cache
• query_cache_size：设置query cache所使用的内存大小，默认为0，大小必须是1024的整数倍
• query_cache_type：控制query cache功能的开关，可设置为：0（OFF，关闭query cache功能，如何情况下都不会使用query cache）、1（ON，开启query cache功能，只要select语句中不使用sql_no_cache提示，都会使用query cache）、2（DEMAND，开启query cache功能，但是只有当select语句中使用了sql_cache提示后，才使用query cache）
• query_cache_min_res_unit：缓存块的最小大小。是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

4、几个率

query cache命中率=Qcache_hits / （Qcache_hits+Qcache_inserts）

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

--如果查询缓存碎片率超过20%，可以使用flush query cache; 整理缓存碎片

查询缓存利用率=（query_cache_size - query_free_memory）/ query_cache_size * 100%  

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