当前位置:首页 > mysql > 正文内容

mysql中QueryCache优化

phpmianshi4年前 (2017-04-24)mysql284

原理

 MySQL 的 Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query 语句(当然仅限于 SELECT 类型的 Query)通过一定的 hash 算法进行一个计算而得到一个 hash 值,存放 在一个 hash 桶中。同时将该 Query 的结果集(Result Set)也存放在一个内存 Cache 中的。存放 Query hash 值的链表中的每一个 hash 值所在的节点中同时还存放了该 Query 所对应的 Result Set 的 Cache 所 在的内存地址,以及该 Query 所涉及到的所有 Table 的标识等其他一些相关信息。系统接受到任何一个 SELECT 类型的 Query 的时候,首先计算出其 hash 值,然后通过该 hash 值到 Query Cache 中去匹配,如 果找到了完全相同的 Query,则直接将之前所 Cache 的 Result Set 返回给客户端而完全不需要进行后面 的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后,也会通知 Query Cache,需要将所有与该 Table 有关的 Query 的 Cache 全部失效,并释放出之前占用的内存地址,以便后面其他的 Query 能够使用



Query Cache 真的是“尚方宝剑”吗?

从上面的实现原理来看,Query Cache 确实是以比较简单的实现带来巨大性能收益的功能。但是很多 人可能都忽略了使用 QueryCache 之后所带来的负面影响:


a) Query 语句的 hash 运算以及 hash 查找资源消耗。当我们使用 Query Cache 之后,每条 SELECT 类型的 Query 在到达 MySQL 之后,都需要进行一个 hash 运算然后查找是否存在该 Query 的 Cache,虽然这个 hash 运算的算法可能已经非常高效了,hash 查找的过程也已经足够的优化 了,对于一条 Query 来说消耗的资源确实是非常非常的少,但是当我们每秒都有上千甚至几千 条 Query 的时候,我们就不能对产生的 CPU 的消耗完全忽视了。


b) Query Cache 的失效问题。如果我们的表变更比较频繁,则会造成 Query Cache 的失效率非常 高。这里的表变更不仅仅指表中数据的变更,还包括结构或者索引等的任何变更。也就是说我 们每次缓存到Query Cache中的Cache数据可能在刚存入后很快就会因为表中的数据被改变而被 清除,然后新的相同 Query 进来之后无法使用到之前的 Cache。


c) Query Cache 中缓存的是 Result Set ,而不是数据页,也就是说,存在同一条记录被 Cache 多 次的可能性存在。从而造成内存资源的过度消耗。当然,可能有人会说我们可以限定 Query Cache 的大小啊。是的,我们确实可以限定 Query Cache 的大小,但是这样,Query Cache 就很 容易造成因为内存不足而被换出,造成命中率的下降。


对于 Query Cache 的上面三个负面影响,如果单独拿出每一个影响来说都不会造成对整个系统多大 的问题,并不会让大家对使用 Query Cache 产生太多顾虑。但是,当综合这三个负面影响一起考虑的 话,恐怕 Query Cache 在很多人心目中就不再是以前的那把“尚方宝剑”了。


适度使用 Query Cache

虽然 Query Cache 的使用会存在一些负面影响,但是我们也应该相信其存在是必定有一定价值。我 们完全不用因为 Query Cache 的上面三个负面影响就完全失去对 Query Cache 的信心。只要我们理解了 Query Cache 的实现原理,那么我们就完全可以通过一定的手段在使用 Query Cache 的时候扬长避短,重 发发挥其优势,并有效的避开其劣势。


首先,我们需要根据 Query Cache 失效机制来判断哪些表适合使用 Query 哪些表不适合。由于 Query Cache 的失效主要是因为 Query 所依赖的 Table 的数据发生了变化,造成 Query 的 Result Set 可能已经 有所改变而造成相关的 Query Cache 全部失效,那么我们就应该避免在查询变化频繁的 Table 的 Query 上 使用,而应该在那些查询变化频率较小的 Table 的 Query 上面使用。MySQL 中针对 Query Cache 有两个专 用的 SQL Hint(提示):SQL_NO_CACHE 和 SQL_CACHE,分别代表强制不使用 Query Cache 和强制使用 Query Cache。我们完全可以利用这两个 SQL Hint,让 MySQL 知道我们希望哪些 SQL 使用 Query Cache 而 哪些 SQL 就不要使用了。这样不仅可以让变化频繁 Table 的 Query 浪费 Query Cache 的内存,同时还可以 减少 Query Cache 的检测量。


其次,对于那些变化非常小,大部分时候都是静态的数据,我们可以添加 SQL_CACHE 的 SQL Hint, 强制 MySQL 使用 Query Cache,从而提高该表的查询性能。


最后,有些 SQL 的 Result Set 很大,如果使用 Query Cache 很容易造成 Cache 内存的不足,或者将 之前一些老的 Cache 冲刷出去。对于这一类 Query 我们有两种方法可以解决,一是使用 SQL_NO_CACHE 参 数来强制他不使用 Query Cache 而每次都直接从实际数据中去查找,另一种方法是通过设定 “query_cache_limit”参数值来控制 Query Cache 中所 Cache 的最大 Result Set ,系统默认为 1M(1048576)。当某个 Query 的 Result Set 大于“query_cache_limit”所设定的值的时候,Query Cache 是不会 Cache 这个 Query 的。


Query Cache 的相关系统参数变量和状态变量

我们首先看看 Query Cache 的系统变量,可以通过执行如下命令获得 MySQL 中 Query Cache 相关的系 统参数变量:

show variables like '%query_cache%'

have_query_cache | YES       #该 MySQL 是否支持 Query Cache;

query_cache_limit | 1048576    #Query Cache 存放的单条 Query 最大 Result Set ,默认 1M;

query_cache_min_res_unit | 4096  #Query Cache 每个 Result Set 存放的最小内存大小,默认 4k;

query_cache_size | 268435456    #系统中用于 Query Cache 内存的大小;

query_cache_type | ON       #系统是否打开了 Query Cache 功能;

query_cache_wlock_invalidate | OFF #针对于 MyISAM 存储引擎,设置当有 WRITE LOCK 在某个 Table 上面的时候,读请求是要等待 WRITE LOCK 释放资源之后再查询还是允许直接从 Query Cache 中读取结果,默认为 FALSE(可以直接从 Query Cache 中取得结果)。


以上参数的设置主要是“query_cache_limit”和“query_cache_min_res_unit”两个参数的设置需 要做一些针对于应用的相关调整。如果我们需要 Cache 的 Result Set 一般都很小(小于 4k)的话,可 以 适 当 将 “ query_cache_min_res_unit ” 参 数 再 调 小 一 些 , 避 免 造 成 内 存 的 浪 费 , “query_cache_limit”参数则不用调整。而如果我们需要Cache 的 Result Set 大部分都大于4k 的话, 则最好将“query_cache_min_res_unit”调整到和 Result Set 大小差不多,“query_cache_limit”的 参数也应大于 Result Set 的大小。当然,可能有些时候我们比较难准确的估算 Result Set 的大小, 那么当 Result Set 较大的时候,我们也并不是非得将“query_cache_min_res_unit”设置的和每个 Result Set 差不多大,是每个结果集的一半或者四分之一大小都可以,要想非常完美的完全不浪费任何 内存确实也是不可能做到的。


如果我们要了解Query Cache的使用情况,则可以通过Query Cache 相关的状态变量来获取,如通过 如下命令:

show status like 'Qcache%';

Qcache_free_blocks Query Cache中目前还有多少剩余的 blocks。如果该值显示较大, 则说明 Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理

Qcache_free_memory Query Cache中目前剩余的内存大小。较为准确的观察出当前系统中的 Query Cache 内存大小是否足够

Qcache_hits    多少次命中。通过这个参数我们可以查看到 Query Cache 的基本效果

Qcache_inserts  多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出 Query Cache 的命中率了

Query Cache 命中率 = Qcache_hits / ( Qcache_hits + Qcache_inserts )

Qcache_lowmem_prunes 多少条 Query 因为内存不足而被清除出 Query Cache。通过 “Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系 统中Query Cache的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换

Qcache_not_cached 因为 query_cache_type 的设置或者不能被 cache 的 Query 的数量

Qcache_queries_in_cache 当前 Query Cache 中 cache 的 Query 数量

Qcache_total_blocks 当前 Query Cache 中的 block 数量


Query Cache 的限制

Query Cache 由于存放的都是逻辑结构的 Result Set,而不是物理的数据页,所以在性能提升的同时,也会受到一些特定的限制。

a) 5.1.17 之前的版本不能 Cache绑定变量的 Query,但是从 5.1.17 版本开始,Query Cache 已经 开始支持绑定变量的 Query 了;

b) 所有子查询中的外部查询 SQL 不能被 Cache;

c) 在 Procedure,Function 以及 Trigger 中的 Query 不能被 Cache;

d) 包含其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache。


鉴于上面的这些限制,在使用 Query Cache 的过程中,建议通过精确设置的方式来使用,仅仅让合适的表的数据可以进入 Query Cache,仅仅让某些 Query 的查询结果被 Cache。

版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

mysql中explain分析sql详解

Explain举例mysql> explain select * from event;  +—-+————-+——-+——+————...

mysql中行锁、两阶段锁协议、死锁以及死锁检测

mysql中行锁、两阶段锁协议、死锁以及死锁检测

行锁MySQL的行锁都是在引擎层实现的,但是 MyISAM 不支持行锁,意味着并发控制只能使用表锁,同一张表任何时刻只能被一个更新在执行,影响到业务并发度。InnoDB 是支持行锁的,这也是 MyIS...

mysql中优化器是如何选择索引的

一:概念  - 在 索引建立之后,一条语句可能会命中多个索引,这时,索引的选择,就会交由 优化器 来选择合适的索引。  - 优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执...

mysql中找出无用的索引或重复索引

背景MySQL允许在相同列上创建重复的索引,但这样做对数据库却是有害而无利的,需要定期检查此类重复索引以改善数据库性能。可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数...

mysql中performance_schema(三) 实践篇

背景前一篇文章我们分析了performance_schema中每个表的用途,以及主要字段的含义,比较侧重于理论的介绍。这篇文章我主要从DBA的角度出发,详细介绍如何通过performance_sche...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。