`
t641339564
  • 浏览: 27661 次
  • 性别: Icon_minigender_1
  • 来自: 贵阳
社区版块
存档分类
最新评论

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

阅读更多

导读

    与朋友或同事谈到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参数指定;

分享到:
评论

相关推荐

    释放查询潜力:深度配置MySQL查询缓存

    ### 释放查询潜力:深度配置 MySQL 查询缓存 #### MySQL 概览 MySQL 是一个流行的开源关系型数据库管理系统(RDBMS),广泛应用于 Web 应用程序的后端数据存储。它基于结构化查询语言(SQL)进行数据管理,是 LAMP...

    mysql缓存查询和设置

    mysql缓存查询和设置global_query_cache_size

    mysql查询缓存

    MySQL的查询缓存并非缓存执行计划,而是查询及其结果集,这就意味着只有相同的查询操作才能命中缓存,因此MySQL的查询缓存命中率很低,另一方面,对于大结果集的查询,其查询结果可以从cache中直接读取,有效的提升...

    MysqL缓存的解决方案

    "MySQL缓存的解决方案" MySQL 缓存是提高数据库性能的一种重要手段,它可以减少数据库的读写次数,从而提高系统的整体性能。以下将对 MySQL 缓存的解决方案进行详细的介绍。 缓存的必要性 缓存的主要目的是减少...

    高效MySQL查询加速指南:索引策略、查询优化、性能调优,助力数据库管理员和开发者突破性能瓶颈

    ### 高效MySQL查询加速指南:索引策略、查询优化、性能调优 在数据库管理和开发领域中,MySQL因其灵活性和高效性而受到广泛欢迎。然而,在面对大规模数据集时,即使是性能强大的MySQL也可能遇到查询速度慢的问题。...

    MySQL缓存研究

    MySQL 缓存是数据库管理系统中至关重要的一部分,它主要目的是为了提高数据库的性能和响应速度。在数据库系统中,缓存扮演着数据高速存取的角色,通过临时存储频繁访问的数据,减少对磁盘I/O操作,从而显著提升...

    mysql缓存优化

    ### MySQL缓存优化详解 #### 一、MySQL存储引擎概览与选择 ##### 1.1 存储引擎类型 ...通过对存储引擎的选择和合理配置查询缓存,可以有效地提高 MySQL 数据库的性能和响应速度,满足不同业务场景的需求。

    MySQL 执行过程与查询缓存1

    **查询缓存**虽然在新版本中已不再使用,但在早期版本中,它可以提高查询速度,但需注意的是,任何对查询中涉及的表的修改都会使相关缓存失效,因此命中率不高。**分析器**用于解析SQL语句,检查语法和语义,确保...

    清空mysql 查询缓存的可行方法

    在MySQL数据库系统中,查询缓存是一个非常重要的性能优化机制,它允许服务器存储先前执行的SQL查询及其结果,以便在后续请求相同查询时快速返回结果,而无需再次解析和执行查询。然而,这也可能导致某些情况下优化...

    mysql高级优化查询

    根据提供的文件信息,我们可以深入探讨 MySQL 的高级优化查询这一主题,包括其查询缓存原理以及 MySQL 的内部机制等核心知识点。 ### 一、MySQL 查询缓存原理 #### 1.1 什么是查询缓存 查询缓存是 MySQL 中用于...

    Mysql 缓存

    MySQL数据库系统在处理查询时,为了提高性能,使用了多种缓存机制,其中最常见的是查询缓存(Query Cache)。本文将深入探讨MySQL的查询缓存功能,以及如何通过全局变量`global_query_cache_size`来调整其大小。 ...

    释放MySQL潜能:深入配置缓存机制

    MySQL支持多种缓存机制,包括查询缓存、InnoDB缓存、表缓存和键缓存。 ##### 1. 查询缓存 - **定义**:查询缓存存储了SELECT查询的结果集,当相同的查询再次执行时,可以直接从缓存中获取结果,无需重新执行查询。 ...

    Mysql查询流程分析

    如果之后有相同的查询再次执行,MySQL可以直接从缓存中获取结果而无需重新执行查询,从而大大提高了查询速度。然而,需要注意的是,查询缓存的使用可能会受到某些因素的影响,例如缓存空间大小限制以及缓存失效策略...

    MysqlHelper.cs带注释带缓存带MySql.Data.dll

    - 缓存机制:`MysqlHelper`可能会包含一个缓存层,如使用`System.Runtime.Caching`命名空间中的`MemoryCache`类,来存储频繁查询的结果,减少对数据库的访问,提高性能。 - 缓存策略:缓存的添加、过期和清理策略...

    mysql 设置查询缓存

    可将如下语句 query_cache_size = 268435456 query_cache_type = 1 query_cache_limit = 1048576 存放到/etc/my.cnf文件的[mysqld]下 然后重启mysql数据库 service mysqld restart 就会启动mysql的缓存机制Query ...

    10倍以上提高Pentaho Kettle的MySQL写入速度

    2. **索引优化**: 创建合适的索引可以加速查询,尤其是对于写操作,避免全表扫描。在插入数据前分析并创建针对写操作的索引可能有助于提升速度。 3. **并发执行**: 利用Kettle的工作线程机制,可以将数据加载任务...

    MySQL优化之缓存优化(续)

    MySQL缓存优化是数据库性能提升的关键一环,尤其在高并发和大数据量的场景下,合理利用缓存能显著减少磁盘I/O操作,提高查询速度。本文将深入探讨MySQL中的各种缓存机制及其优化策略。 首先,我们关注的是查询缓存...

    mysql查询速度优化从3万秒到0.001秒共14页.pd

    MySQL查询速度优化是数据库管理中的核心议题,尤其在大数据量的场景下,高效的查询性能直接影响着系统的整体性能和用户体验。本资料"mysql查询速度优化从3万秒到0.001秒共14页.pdf"揭示了如何将一个原本耗时长达3万...

    基于MySQL的数据库查询性能优化.pdf

    通过大量的实验测试,我们发现,适当的数据表索引、SQL 查询语句中的 "limit" 和 "*" 优化、id 限定分页查询和查询缓存技术可以大大提高数据库的响应速度。 结论 本文总结了数据库查询性能优化的重要性和影响...

Global site tag (gtag.js) - Google Analytics