欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

Query Cache的陷阱

事情背景:
公司在使用MySQL作为线上业务的生产库,某一天,同事说,怪不得select有时候很慢,原来查询缓存(Query Cache)没有打开,昨晚找了个空闲时间就打开了~

发现问题:
在例行检查status的时候,想起了Qcache的事情,因为以前都是关着的,所以各项统计都是0,正好同事打开了Qcache,就打算去看看Qcache的运行状况
然而结果真是亮瞎了
注:测试使用的MySQL 5.7.7-rc,文档采用的是MySQL 5.6 不要问我为什么文档和数据库版本不一致,真是懒得下5.7的了 _(:з」∠)_ 



简单的翻了一下MySQL的官方文档,看了一些这几行数据代表的意义,

Qcache_free_blocks : QueryCache中的空闲块
Qcache_lowmem_prunes : 当QueryCache的空间不够用时,从QueryCache移除的查询缓存结果数
Qcache_not_cached : 没用到缓存的查询数(无法被缓存,或者是依据MySQL的设置不被缓存的查询)
Qcache_queries_in_cache : 注册进QueryCache的查询数(其实就是当前保存的查询结果数,一个结果对应一个“注册”的SQL语句,后文有验证)
Qcache_hits : 缓存命中数
Qcache_inserts  : 向QueryCache中添加缓存结果的次数

很明显能看出,hits/inserts<0.1,这说明有大量的查询结果写进了QueryCache,但是实际上缓存的利用率确非常低,只有10%不到的结果再次被使用,效果突出一个惨
not_cached>>>>>>queries_in_cache,这说明运行在这个库的业务SQL语句,绝大多数都没有放进缓存里面or绝大多数没有理用到缓存
Qcache_lowmem_prunes的数据说明当前的QueryCache的大小还可以增加一点,防止缓存的结果被刷出去
在10%都不到的利用率面前,初步判断QueryCache的大小并不是主要问题......

寻找原因:继续在官方文档里面找有关QueryCache的资料,发现如下描述



直译过来:只有当查询的TEXT完全一致(byte级别的一致),才会利用查询缓存,这意味着大小写的不同,SQL语句内部的空格数,制表符,参数等地方的不一致都会导致无法使用QueryCache,官方也举了一个不能使用QueryCache的例子。
            
之所以用TEXT来描述SQL语句,是因为如下描述



直译过来:查询的语句以TEXT的格式保存在QueryCache里面,并且占位符?已经被实际的参数所取代,一个具体的SQL语句对应一个具体的结果集(验证结果在最后,直接贴结论)。
直白一点的理解,这个QueryCache以一种类似于Key-Value的形式,把查询语句的TEXT作为key,实际执行结果作为Value保存起来

QueryCache的实现策略相对比较简单粗暴,并没有去“智能”的多缓存一些最近查询结果相邻的一些额外数据。
而且最重要的,任何改变了表,或者表数据的操作,都会使得这个表相关的所有缓存全部失效!
官方文档列举了一些例子,懒,不翻译了......如下图
    


那么,显而易见,MySQL提供的这个QueryCache并不算是一个很实用的东西,所以推荐同事找个时间把这个东西给关闭,使得数据库层面进行查询的时候,减少一个查询QueryCache的步骤,提升高并发,大访问量下的数据库效率。
关闭的方法很多,网上可以搜索到

思考:
看上去这个东西这么坑爹,为什么Oracle还要加入这个特性?看看官方的解释,感觉挺苍白的,大致意思就是最多提高13%的查询消耗,但是能最多提升238%的查询性能。
从写学术论文的角度来看,这个文字游戏也是玩的溜,不过关于这个QueryCache,官方文档还提到了另外一点,


如红字标出:如果查询缓存能够命中,那么Server会跳过SQL解析和SQL执行这两个阶段,直接返回缓存的结果
这意味着,这个QueryCache是处于SQL解析之前的一层判断逻辑
相比较于Oracle,Oracle至少也要在PGA里面解析了SQL,再去SGA取数据,即便是都走的缓存,在逻辑操作上面,MySQL的QueryCache是“更少”的。
所以虽然现在QueryCache不好用,不实用,不过在将来,官方/第三方也许能开发出一套足够高效的算法来改善QueryCache的缓存策略,从而能达到“不解析也能得出结果”的目标。

-------------------------------------------------------------------------------------------验证-----------------------------------------------------------------------------------------------
SQL用的占位符?是被替换成实际参数再保存在QueryCache里面的么?
来验证一下:
1.重启数据库,开启QueryCache  show variables like '%query%'; 

2.查看Qcache的统计数量 show status like 'Qcache%'

上来就是2,其实是执行了use和show status like 'Qcache%',没想到这种语句也会计算进去╮(╯_╰)╭

3.正戏开始,先执行一个select语句,然后再执行一次,看看计数器的变化

可以看到,第一次查询在QueryCache中找不到,所以insert了进去,之后再执行一模一样的查询

可以看到查询缓存命中了,hits数+1,那么假设前端使用的?占位符替代的10000,当?代表的值变换之后,结果如何?

可以看到查询条件的值变换之后,QueryCache再次出现了miss,把新结果放进了QueryCache
然后reset query cache,顺便看一下Qcache_queries_in_cache  show status like 'Qcache%' 的意义

显而易见,这个属性代表放在QueryCache中的查询结果数

来自 
http://blog.itpub.net/29510932/viewspace-1694922/
普通分类: