MySQL加速查询速度的独门武器:查询缓存

2
MySQL SQL 数据库 C/C++ 14432 次浏览
    与朋友或同事谈到mysql查询缓存功能的时候,个人喜欢把Query Cache比作荔枝,是非常营养的东西,但是一次性吃太多了,就容易导致上火而流鼻血,虽然不是特别恰当的比喻,但是有很多相似的地方,另外Query Cache有其特殊的业务场景,也不像其他数据库产品,缓存查询语句的执行计划等信息,而是直接缓存查询语句的记录集和对应的SQL语句。本文就给大家介 绍下查询缓存的相关知识,希望可以引导大家正确地使用Query Cache这个独门武器。
对mysql查询缓存从五个角度进行详细的分析:Query Cache的工作原理、如何配置、如何维护、如何判断查询缓存的性能、适合的业务场景分析。
n  工作原理
查询缓存的工作原理,基本上可以用二句话概括:
l  缓存SELECT操作或预处理查询(注释:5.1.17开始支持)的结果集和SQL语句;
l  新的SELECT语句或预处理查询语句,先去查询缓存,判断是否存在可用的记录集,判断标准:与缓存的SQL语句,是否完全一样,区分大小写;
查询缓存对什么样的查询语句,无法缓存其记录集,大致有以下几类:
l  查询语句中加了SQL_NO_CACHE参数;
l  查询语句中含有获得值的函数,包涵自定义函数,如:CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等;
l  对系统数据库的查询:mysql、information_schema
l  查询语句中使用SESSION级别变量或存储过程中的局部变量;
l  查询语句中使用了LOCK  IN SHARE MODE、FOR UPDATE的语句
l  查询语句中类似SELECT …INTO 导出数据的语句;
l  事务隔离级别为:Serializable情况下,所有查询语句都不能缓存;
l  对临时表的查询操作;
l  存在警告信息的查询语句;
l  不涉及任何表或视图的查询语句;
l  某用户只有列级别权限的查询语句;
查询缓存的优缺点:
l  不需要对SQL语句做任何解析和执行,当然语法解析必须通过在先,直接从Query  Cache中获得查询结果;
l  查询缓存的判断规则,不够智能,也即提高了查询缓存的使用门槛,降低其效率;
l  Query Cache的起用,会增加检查和清理Query Cache中记录集的开销,而且存在SQL语句缓存的表,每一张表都只有一个对应的全局锁;
n  配置
        是否启用mysql查询缓存,可以通过2个参数:query_cache_type和query_cache_size,其中任何一个参数设置为0都意味着关闭查询缓存功能,但是正确的设置推荐query_cache_type=0。
l  query_cache_type
值域为:0 -– 不启用查询缓存;
值域为:1 -– 启用查询缓存,只要符合查询缓存的要求,客户端的查询语句和记录集斗可以缓存起来,共其他客户端使用;
值域为:2 -– 启用查询缓存,只要查询语句中添加了参数:SQL_CACHE,且符合查询缓存的要求,客户端的查询语句和记录集,则可以缓存起来,共其他客户端使用;
l  query_cache_size
允许设置query_cache_size的值最小为40K,对于最大值则可以几乎认为无限制,实际生产环境的应用经验告诉我们,该值并不是越大,查询缓 存的命中率就越高,也不是对服务器负载下降贡献大,反而可能抵消其带来的好处,甚至增加服务器的负载,至于该如何设置,下面的章节讲述,推荐设置 为:64M;
l  query_cache_limit
限制查询缓存区最大能缓存的查询记录集,可以避免一个大的查询记录集占去大量的内存区域,而且往往小查询记录集是最有效的缓存记录集,默认设置为1M,建议修改为16k~1024k之间的值域,不过最重要的是根据自己应用的实际情况进行分析、预估来设置;
l  query_cache_min_res_unit
设置查询缓存分配内存的最小单位,要适当地设置此参数,可以做到为减少内存块的申请和分配次数,但是设置过大可能导致内存碎片数值上升。默认值为4K,建议设置为1k~16K
l  query_cache_wlock_invalidate
该参数主要涉及MyISAM引擎,若一个客户端对某表加了写锁,其他客户端发起的查询请求,且查询语句有对应的查询缓存记录,是否允许直接读取查询缓存的记录集信息,还是等待写锁的释放。默认设置为0,也即允许;
n  维护
l  查询缓存区的碎片整理
    查询缓存使用一段时间之后,一般都会出现内存碎片,为此需要监控相关状态值,并且定期进行内存碎片的整理,碎片整理的操作语句:FLUSH QUERY CACHE;
l  清空查询缓存的数据
那些操作操作可能触发查询缓存,把所有缓存的信息清空,以避免触发或需要的时候,知道如何做,二类可触发查询缓存数据全部清空的命令:
(1).     RESET QUERY CACHE;
(2).     FLUSH TABLES;
n  性能监控
l  碎片率
查询缓存内存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%
l  命中率
查询缓存命中率=Qcache_hits/(Qcache_hits + Qcache_inserts)  * 100%
l  内存使用率
查询缓存内存使用率=(query_cache_size – Qcache_free_memory) / query_cache_size * 100%
l  Qcache_lowmem_prunes
该参数值对于检测查询缓存区的内存大小设置是否,有非常关键性的作用,其代表的意义为:查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息,删除算法为LRU;
l  query_cache_min_res_unit
    内存块分配的最小单元非常重要,设置过大可能增加内存碎片的概率发生,太小又可能增加内存分配的消耗,为此在系统平稳运行一个阶段性后,可参考公式的计算值:
查询缓存最小内存块 = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
l  query_cache_size
我们如何判断query_cache_size是否设置过小,依然也只有先预设置一个值,推荐为:32M~128M之间的区域,待系统平稳运行一个时间段(至少1周),并且观察这周内的相关状态值:
(1).     Qcache_lowmem_prunes;
(2).     命中率;
(3).     内存使用率;
    若整个平稳运行期监控获得的信息,为命中率高于80%,内存使用率超过80%,并且Qcache_lowmem_prunes的值不停地增加,而且增加的数值还较大,则说明我们为查询缓冲区分配的内存过小,可以适当地增加查询缓存区的内存大小;
    若是整个平稳运行期监控获得的信息,为命中率低于40%,Qcache_lowmem_prunes的值也保持一个平稳状态,则说明我们的查询缓冲区的内 存设置过大,或者说业务场景重复执行一样查询语句的概率低,同时若还监测到一定量的freeing items,那么必须考虑把查询缓存的内存条小,甚至关闭查询缓存功能;
n  业务场景
通过上述的知识梳理和分析,我们至少知道查询缓存的以下几点:
l  查询缓存能够加速已经存在缓存的查询语句的速度,可以不用重新解析和执行而获得正确得记录集;
l  查询缓存中涉及的表,每一个表对象都有一个属于自己的全局性质的锁;
l  表若是做DDL、FLUSH TABLES 等类似操作,触发相关表的查询缓存信息清空;
l  表对象的DML操作,必须优先判断是否需要清理相关查询缓存的记录信息,将不可避免地出现锁等待事件;
l  查询缓存的内存分配问题,不可避免地产生一些内存碎片;
l  查询缓存对是否是一样的查询语句,要求非常苛刻,而且还不智能;
    我们再重新回到本节的重点上,查询缓存适合什么样的业务场景呢?只要是清楚了查询缓存的上述优缺点,就不难罗列出来,业务场景要求:
l  整个系统以读为主的业务,比如门户型、新闻类、报表型、论坛等网站;
l  查询语句操作的表对象,非频繁地进行DML操作,可以使用query_cache_type=2模式,然后SQL语句加SQL_CACHE参数指定;
请尽量让自己的答案能够对别人有帮助

12个答案

默认排序 按投票排序
0

Make your dream villa a reality with Construction Bali. We are the best contractor in Bali and we are focus in villa construction in Bali. Jalan Patih Jelantik, Kuta Istana Galeria, Ring 11, Kuta, 80361, Badung, Bali, Indonesia Phone Number : +62818108808 bali contractor

0

ncredible articles and awesome design. Your blog entry merits the greater part of the positive input it"s been getting. Full Circle Health reviews

0

Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. There tend to be not many people who can certainly write not so simple posts that artistically. Continue the nice writing lowes kronos server

0

Man's lives, such as uncontrolled huge amounts, definitely not while countries furthermore reefs, challenging to seismic disturbance upward perfect apply. Pinterest

0

Your work here on this blog has been top notch from day 1. You've been continously providing amazing articles for us all to read and I just hope that you keep it going on in the future as well. Cheers! mieszkania nowe kielce

0

Your post is very helpful to get some effective tips to reduce weight properly. You have shared various nice photos of the same. I would like to thank you for sharing these tips. Surely I will try this at home. Keep updating more simple tips like this. perfect ten condo showflat

0

Extremely helpful post. This is my first time i visit here. I discovered such a large number of intriguing stuff in your blog particularly its exchange. Truly its extraordinary article. Keep it up. u30 pill

0

Man's lives, such as uncontrolled huge amounts, definitely not while countries furthermore reefs, challenging to seismic disturbance upward perfect apply. parc clematis

0

I think this is definitely an amazing project here. So much good will be coming from this project. The ideas and the work behind this will pay off so much. https://gamebnat.net/mtsite04/

0
不错