小谈MySql查询缓存

原文地址:https://blog.csdn.net/ClementAD/article/details/46806469
http://imysql.com/2014/09/05/mysql-faq-why-close-query-cache.shtml

Query Cache(查询缓存,以下简称QC)存储SELECT语句及其产生的数据结果,特别适用于:频繁提交同一个语句,并且该表数据变化不是很频繁的场景,例如一些静态页面,或者页面中的某块不经常发生变化的信息。QC有可能会从InnoDB Buffer Pool或者MyISAM key buffer里读取结果。

由于QC需要缓存最新数据结果,因此表数据发生任何变化(INSERT、UPDATE、DELETE或其他可能产生数据变化的操作),都会导致QC被刷新。

根据MySQL官方的测试,QC的优劣分别是:

1、如果对一个表执行简单的查询,但每次查询都不一样的话,打开QC后,性能反而下降了13%左右。但通常实际业务中,通常不会只有这种请求,因此实际影响应该比这个小一些。

2、如果对一个只有一行数据的表进行查询,则可以提升238%,这个效果还是非常不错的。

因此,如果是在一个更新频率非常低而只读查询频率非常高的场景下,打开QC还是比较有优势的,其他场景下,则不建议使用。而且,QC一般也维持在100MB以内就够了,没必要设置超过数百MB。

启用查询缓存

查看查询缓存情况:
mysql> show variables like 『%query_cache%』; 
(query_cache_type 为 ON 表示已经开启)
+——————————+———-+
| Variable_name      | Value    |
+——————————+———-+
| have_query_cache     | YES      |
| query_cache_limit       | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size       | 20971520 |
| query_cache_type         | ON       |
| query_cache_wlock_invalidate | OFF      |
+——————————+———-+

如果不是ON,修改配置文件以开启查询缓存:

mysql.cnf 中添加:
query_cache_size = 20M
query_cache_type = ON

重启mysql服务:

service mysql restart

查看缓存使用情况:
mysql> show status like 『qcache%』;  
+————————-+———-+
| Variable_name           | Value    |
+————————-+———-+
| Qcache_free_blocks      | 83       |
| Qcache_free_memory      | 19811040 |
| Qcache_hits             | 3108196  |
| Qcache_inserts          | 757254   |
| Qcache_lowmem_prunes    | 20720    |
| Qcache_not_cached       | 47219    |
| Qcache_queries_in_cache | 47       |
| Qcache_total_blocks     | 276      |
+————————-+———-+

其中各个参数的意义如下:
Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory:缓存中的空闲内存。
Qcache_hits:每次查询在缓存中命中时就增大
Qcache_inserts:每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes:缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache:当前缓存的查询(和响应)的数量。
Qcache_total_blocks:缓存中块的数量。

此外,QC也不适用于下面几个场景:

  1. 子查询或者外层查询;
  2. 存储过程、存储函数、触发器、event中调用的SQL,或者引用到这些结果的;
  3. 包含一些特殊函数时,例如:BENCHMARK()、CURDATE()、CURRENT_TIMESTAMP()、NOW()、RAND()、UUID()等等;
  4. 读取mysql、INFORMATION_SCHEMA、performance_schema 库数据的;
  5. 类似SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE、SELECT..INTO OUTFILE/DUMPFILE、SELECT..WHRE…IS NULL等语句;
  6. SELECT执行计划用到临时表(TEMPORARY TABLE);
  7. 未引用任何表的查询,例如 SELECT 1+1 这种;
  8. 产生了 warnings 的查询;
  9. SELECT语句里加了 SQL_NO_CACHE 关键字;

更加奇葩的是,MySQL在从QC中取回结果前,会先判断执行SQL的用户是否有全部库、表的SELECT权限,如果没有,则也不会使用QC。

相比下面这个,其实上面所说的都不重要。

最为重要的是,在MySQL里QC是由一个全局锁在控制,每次更新QC的内存块都需要进行锁定。
例如,一次查询结果是20KB,当前 query_cache_min_res_unit 值设置为 4KB(默认值就是4KB,可调整),那么么本次查询结果共需要分为5次写入QC,每次都要锁定,可见其成本有多高。

我们可以通过 PROFILING 功能来查看 QC 相关的一些锁竞争,例如像下面这样的:

• Waiting for query cache lock
• Waiting on query cache mutex

或者,也可以通过执行 SHOW PROCESSLIST 来看线程的状态,例如:

• checking privileges on cached query
检查用户是否有权限读取QC中的结果集

• checking query cache for query
检查本次查询结果是否已经存储在QC中

• invalidating query cache entries
由于相关表数据已经修改了,因此将QC中的内存记录被标记为失效

• sending cached result to client
从QC中,将缓存后的结果返回给客户程序

• storing result in query cache
将查询结果缓存到QC中

如果可以频繁看到上述几种状态,那么说明当前QC基本存在比较重的竞争。

说了这么多废话,其实核心要点就一个:
如果线上环境中99%以上都是只读,很少有更新,再考虑开启QC吧,否则,就别开了。
关闭方法很简单,有两种:

1、同时设置选项 query_cache_type = 0 和 query_cache_size = 0;
2、如果用源码编译MySQL的话,编译时增加参数 –without-query-cache 即可;

延伸阅读:
http://www.dbasquare.com/kb/how-query-cache-can-cause-performance-problems/
http://www.percona.com/blog/2012/09/05/write-contentions-on-the-query-cache/
http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

2019.04.01

今晚去西乡找表哥,三十几平的屋里三个男人喝着茶烧着烟坐聊了两个多钟。表哥比我年长五岁,我上小学的时候他就已经出来社会工作了。另外一个是表哥的同事,四五十岁人。

从来没想过有这么一天,我和表哥都以社会人的身份在异乡聚聊。在他们面前我永远是那个长不大的小孩,可时光就是这么神奇,那个小孩永远地长大了。

和“长辈”们聊生活的难,感觉很特别。一直以为自己和兄长们没有共同的话题,但聊及生活和现实的时候,发现并非如此,我们都是在迷茫人生道路上奋力前行的普通人。

今早微博上看到个视频,一小伙骑单车逆行,被交警拦下后,接到了一个电话,没想到接完电话后,小伙子怒刷手机,彻底奔溃,嚎啕大哭起来,整个人情绪像失控一样跑向桥边,幸好在交警大哥的开导下才冷静下来。以前不管工作多辛苦,累了躺个觉就完了。但看到这个视频后,眼泪哗的一下就出来了。生活让人遍体鳞伤,但很多时候我们都是咬着牙麻木的坚持着,突然有一天伤疤被揭开,痛苦便加以千万倍汹涌而上,心理脆弱的人可能一下子就承受不住,像紧绷的弦突然断了。人是需要发泄和被理解的,特别在这个绝望的时代,活着就先得学会不定时清理精神和心理垃圾,憋气憋太久人会窒息。

这又让我想起了近年来身边发生的一幕幕悲剧————大学时期一个同学传闻感情不顺从宿舍十五楼一跃而下;前年一个师弟的舍友因为抑郁同样选择了跳楼轻生;去年和我同届的一个即将研究生毕业并且拿到了大疆offer的校友也选择了告别生命;还有去年中兴大厦一个四十岁左右的员工因为被离职也跳楼轻生了…而今天,是哥哥张国荣告别我们的第十六个年头。以前看到这类事件总觉得他们太傻,对生活对家庭不负责任,但现在好像能理解他们的选择。人是利己动物,每个人做的每一个选择都是最有利于自己的。一个人连生活都放弃了,可见生活对他们来说只是一种累赘和负担。相对于痛苦的活着,死亡对他们来说可能是一种解脱。

工作三四年了,我也时常被周围左右我的思想,以别人定义的成功去衡量自己的生活。但越来越发觉,既然来到这个世界,我们就要尽最大的努力去生活,把生命当作一场修行。在这个唯利是图金钱至上的时代,我们可能忽略了内心的诉求,束缚于外界条条框框。每个人都是独立的个体,应该有独立的思想,为什么一定要活成别人期待的样子。一个人倘若能倾听自己的内心,了解自己内心的诉求,以愉悦自己为导向去生活,或许活着并没有那么不堪。

黑塞《德米安:彷徨少年时》

鸟要挣脱出壳。蛋就是世界。人要诞于世上,就得摧毁这个世界。

世上并没有偶然,如果一个人务必要得到什么,并最终得到了,这就不是偶然,而是他自己的功劳,他的意愿将他领向了那里。

对每个人而言,真正的职责只有一个:找到自我。无论他的归宿是诗人还是疯子,是先知还是罪犯——这些其实和他无关,毫不重要。他的职责指使找到自己的命运—— 而不是他人的命运——然后在心中坚守其一生,全心全意,永不停息。所有其它的路都是不完整的,是人的逃避方式,是对大众理想的懦弱回归,是随波逐流,是对内心的恐惧。
『而自我是多元而丰富的。寻找自我时,跳出自我,并认识自我的能力,极其重要。不然必然会走入歧途。』

我不能自诩洞明世事。从过去到今天,我一直是一个寻觅者,但我已不再寻求于星辰和书本之间,而是开始聆听自己血液的簌簌低语。我的故事并不令人畅怀,也不像杜撰的故事那样甜美和谐,它味如痴语、混乱、癫狂和梦幻,就像所有那些不愿再自欺欺人的生活一样。

每个人的生命都是通向自我的征途,是对一条道路的尝试,是一条小径的悄然召唤。人们从来都无法以绝对的自我之相存在,每一个人都在努力变成绝对自我,有人迟钝,有人更洞明,但无一不是自己的方式。我们可以彼此理解,然而能解读自己的人只有自己。

我就像一棵秋天的树,树叶从它身边飘落,但它毫无知觉,雨水从它一旁滴落,还有太阳和严寒,生命已缓缓缩进了它内部最私密幽深之处。它没有死,它在等待。

渴望,对世界最热烈的接触,以狂野的方式与世界再度分离,对自我黑暗灵魂的热切聆听,对奉献的陶醉,对奇妙之物的深深好奇!

没有任何人去爱她, 没有任何人亲近她。 虽然她是个好人, 却也不愿走进任何人的内心。

我过去是,现在仍然是一个探索者,但是我不再占星问道,我开始倾听内心深处的低语。

“我们看到的事物,”皮斯托琉斯轻声道,“同时也是自己心中之物。真实无非就是心中的真实。因此,大多数人的生活都是不真实的,因为他们只将外界的景象当成真实,压抑了自己内心的世界。那样他们会幸福。可是,一旦人们了解了事情的另一面,他们就不能再选择庸人的路了。辛克莱,庸人的道路很轻松,我们的道路却很艰险——但我们愿意走。”

我没法赤裸裸、孤单单地站在世上,我也就是一条可怜巴巴的狗,需要一些温暖和食物,有时也希望有同类相伴。如果有人真的只追随自己的命运,那他就不再有同伴,他会完全孤立,身边是冷漠的世界。

一切都变了。我的童年已成废墟。父母看我的目光多了一层尴尬。姊妹们和我已变得非常疏远。一种豁然醒悟的感受让我所熟识的那些情感和乐趣都变得了无生趣,我闻不到花园的芬芳,对森林也毫不好奇,世界就像一堆廉价待售的旧货围绕着我,乏味无趣,书变成了纸,音乐则是噪音。

今天少有人懂得什么是人。很多人感觉到了这一点,因此死得更从容,当我写完这个故事之后,我也会同样从容地死去。

觉醒的人只有一项义务,找到自我,固守自我。沿着自己的路向前走,不管它通向哪里。

上帝借由各种途径使人变得孤独,好让我们可以走向自己。

恐惧中,我感到,他已经完全进入了自身中。我从来没有感到如此孤独,我不是他的一分子,无法触及他,天涯海角也没有他离我的距离那般遥远。

我做自己不情愿的事,是因为完全不知如何面对自己。我恐惧长久的孤独,害怕心绪的各种细微、羞涩和热切的波动,害怕那常常泛起的爱的柔情。

爱不必请求,也不可要求。爱必须成为自己明确肯定的力量。它便不再是被牵引,而是去牵引。

要是我知道爱是什么,那就是为了你的缘故。我在众人之中只爱你,你无法猜测这是什么意思。 这是表示沙漠中的泉源,荒野中开花的树,我的心不枯干,能够得神的恩宠,都是要感谢你的。

我常常考虑着未来的图景,我梦到可能会赠给我的角色,也许去做诗人或者去做先知,或者去当画家,或者随便什么。这一切都是一场空。我不是专门来作诗、布道、绘画的,无论我还是其他什么人都不能做到这些。这一切都只是顺便产生的。每个人的真正职业只有一个:走向自身。他可以以诗人或者疯子,以先知或者罪犯告终一一这不是他的事情,这终究是无关紧要的。他的事情是找到自己的命运,不是随便哪一个命运,并在内心充分享受它,完整而不间断地享受它。其他一切都是不完整的,都是企图逃脱,都是逃回中庸的理想,都是适应,是害怕自己的内心。

我一生中的一半是在对你求爱的,我知道你也喜欢我,可是我从不希望你会把这件事告诉我,因为你是个高傲的人。 现在你对我说了,在这一瞬间我什么都没有了,流浪与自由,世界与女人都舍我远去了。我要因此而感谢你。