MySQL 缓存 Query Cache

过滤所有注释

去掉SQL文本前後的空格,TAB等字符。注意,是文本前面和後面的。中间的不会被去掉。

下面的三条SQL里,因 为SELECT大小写的关系,最後一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条,区别在于後者有个注释,在不同客户端,会有不一 样的结果。所以,保险起见,请尽量不要使用动态的注释。在PHP的mysql教程扩展里,SQL的注释是不会被去掉的。也就是三条SQL会被存储在三个不同的 缓存里,虽然它们的结果都是一样的。

select * FROM people where name='surfchen';

select * FROM people where /*hey~*/name='surfchen';

SELECT * FROM people where name='surfchen';

目前只有select语句会被cache,其他类似show,use的语句则不会被cache。

因为QC是如此前端,如此简单的一个缓存系统,所以如果一个表被更新,那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了表A和表B,如果表A或者表B的其中一个被更新(update或者delete),这个查询的QC将会失效。

也就是说,如果一个表被频繁更新,那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC,可能会加重数 据库的负担,而不是减轻负担。我一般的做法是默认打开QC,而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用 CACHE。这样可以尽可能避免不必要的内存操作,尽可能保持内存的连续性。

那些查询很分散的SQL语句,也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name='surfchen'”。这样的语句,在一个系统里,很有可能只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询,都是不一样的SQL 文本,QC在这里就几乎不起作用了,因为缓存的数据几乎是不会被用到的,它们只会在内存里占地方。

存储块

在本节里“存储块”和“block”是同一个意思

QC缓存一个查询结果的时候,一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询结果获得的过程中,逐块存储。当一个存储块被填满之 後,一个新的存储块将会被创建,并分配内存(allocate)。单个存储块的内存分配大小通过query_cache_min_res_unit参数控 制,默认为4KB。最後一个存储块,如果不能被全部利用,那么没使用的内存将会被释放。如果被缓存的结果很大,那么会可能会导致分配内存操作太频繁,系统 系能也随之下降;而如果被缓存的结果都很小,那么可能会导致内存碎片过多,这些碎片如果太小,就很有可能不能再被分配使用。

除了查询结果需要存储块之外,每个SQL文本也需要一个存储块,而涉及到的表也需要一个存储块(表的存储块是所有线程共享的,每个表只需要一个存储 块)。存储块总数量=查询结果数量*2+涉及的数据库教程表数量。也就是说,第一个缓存生成的时候,至少需要三个存储块:表信息存储块,SQL文本存储块,查 询结果存储块。而第二个查询如果用的是同一个表,那么最少只需要两个存储块:SQL文本存储块,查询结果存储块。

通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储块。它们的 比例如果接近1:2,则说明当前的query_cache_min_res_unit参数已经足够大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多,则需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit(sql文本和表信息所在的block占用的 内存很小,可以忽略)如果远远大于query_cache_size-Qcache_free_memory,那么可以尝试减小 query_cache_min_res_unit的值。

调整大小

如果Qcache_lowmem_prunes增长迅速,意味着很多缓存因为内存不够而被释放,而不是因为相关表被更新。尝试加大query_cache_size,尽量使Qcache_lowmem_prunes零增长。

启动参数

show variables like 'query_cache%'可以看到这些信息。

query_cache_limit

如果单个查询结果大于这个值,则不Cache

query_cache_size

分 配给QC的内存。如果设为0,则相当于禁用QC。要注意QC必须使用大约40KB来存储它的结构,如果设定小于40KB,则相当于禁用QC。QC存储的最 小单位是1024 byte,所以如果你设定了一个不是1024的倍数的值,这个值会被四舍五入到最接近当前值的等于1024的倍数的值。

query_cache_type

0 完全禁止QC,不受SQL语句控制(另外可能要注意的是,即使这里禁用,上面一个参数所设定的内存大小还是会被分配);1启用QC,可以在SQL语句使用SQL_NO_CACHE禁用;2可以在SQL语句使用SQL_CACHE启用。

query_cache_min_res_unit

每次给QC结果分配内存的大小

状态

show status like 'Qcache%'可以看到这些信息。

Qcache_free_blocks

当一个表被更新之後,和它相关的cache blocks将被free。但是这个block依然可能存在队列中,除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。

Qcache_free_memory

可用内存,如果很小,考虑增加query_cache_size

Qcache_hits

自mysql进程启动起,cache的命中数量

Qcache_inserts

自mysql进程启动起,被增加进QC的数量

Qcache_lowmem_prunes

由于内存过少而导致QC被删除的条数。加大query_cache_size,尽可能保持这个值0增长。

Qcache_not_cached

自mysql进程启动起,没有被cache的只读查询数量(包括select,show,use,desc等)

Qcache_queries_in_cache

当前被cache的SQL数量

Qcache_total_blocks

在 QC中的blocks数。一个query可能被多个blocks存储,而这几个blocks中的最後一个,未用满的内存将会被释放掉。例如一个QC结果要 占6KB内存,如果query_cache_min_res_unit是4KB,则最後将会生成3个blocks,第一个block用来存储sql语句文 本,这个不会被统计到query+cache_size里,第二个block为4KB,第三个block为2KB(先allocate4KB,然後释放多 馀的2KB)。每个表,当第一个和它有关的SQL查询被CACHE的时候,会使用一个block来存储表信息。也就是说,block会被用在三处地方:表 信息,SQL文本,查询结果。

排序缓冲

当一个查询需要对结果进行排序的时候,MySQL会分配一定的内存用来排序。这个内存大小由sort_buffer_size来控制。记得,这个参数是针对每个查询的,而不是所有查询总共可分配的量。

如果sort_buffer_size不够大,排序的结果将会被分段写入临时文件里。每次结束之後再把文件中的排序结果拿出来合并,进行再次排序, 直到得出最後结果。sort_buffer_size越小,合并的次数就越多。合并次数可以通过状态变量Sort_merge_passes获得。理论 上,Sort_merge_passes越小,排序越快。但是在实际应用中可能并非如此。sort_buffer_size如何设置需要根据实际运行环境 来进行测试。如果实在不知道如何测试,那么就设到使Sort_merge_passes为0吧。

read_buffer_size read_rnd_buffer_size join_buffer_size thread_cache

字段选择

从二进制角度考虑

select col1,col2 from table PROCEDURE ANALYSE();,这条语句可以根据当前表的内容来给出一个字段类型的推荐。

MyISAM

key_buffer_size

CACHE INDEX Syntax

innodb

innodb_buffer_pool_size

这是和innodb有关的最重要的一个参数。这个参数指定了innodb缓存池的大小。这个缓存池被用来存储

innodb_file_per_table innodb_additional_mem_pool_size=80M innodb_log_file_size=1G innodb_log_buffer_size=16M innodb_flush_method=O_DIRECT

(max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size)) + key_buffer + innodb_bufer_pool_size + query_cache + tmp_table_size

系统相关

linux:

echo -n 0 >/proc/sys/vm/swappiness

huge page

memlock

优化工具

mysqltuner

时间: 2024-11-08 14:22:52

MySQL 缓存 Query Cache的相关文章

MySQL使用query cache缓存简单使用教程

1. query cache的key是sql字符串,并且要求精确匹配:大小写不一样或者多了一个空格,都会导致cache miss 2. query cache直接把用户输入的原始sql作为key, 不是把解析后或优化后的sql作为key,也不会把其中的子查询独立缓存 2. 存储过程中的sql结果不会被缓存 3. 如果一个sql会产生不确定的结果(即使表中数据没有变化也会不确定),比如select now(), MySQL就不会缓存它的结果 4. 只要表中数据有任何变化,就会导致本表相关的所有qu

MySQL的Query Cache原理分析_Mysql

原理 QueryCache(下面简称QC)是根据SQL语句来cache的.一个SQL查询如果以select开头,那么MySQL服务器将尝试对其使用QC.每个Cache都是以SQL文本作为key来存的.在应用QC之前,SQL文本不会被作任何处理.也就是说,两个SQL语句,只要相差哪怕是一个字符(例如大小写不一样:多一个空格等),那么这两个SQL将使用不同的一个CACHE. 不过SQL文本有可能会被客户端做一些处理.例如在官方的命令行客户端里,在发送SQL给服务器之前,会做如下处理:  过滤所有注释

RDS for MySQL查询缓存 (Query Cache) 的设置和使用

功能和适用范围 原理 限制 设置 验证效果 1. 功能和适用范围 功能: 降低 CPU 使用率 降低 IOPS 使用率(某些情况下) 减少查询响应时间,提高系统的吞吐量 适用范围: 表数据修改不频繁.数据较静态 查询(Select)重复度高 查询结果集小于 1 MB 注: 查询缓存并不一定带来性能上的提升,在某些情况下(比如查询数量大,但重复的查询很少)开启查询缓存会带来性能的下降. 2. 原理 RDS for MySQL 对来自客户端的查询(Select)进行 Hash 计算得到该查询的Has

mysql query cache用法与性能详细介绍

mysql Query Cache 默认为打开.从某种程度可以提高查询的效果,但是未必是最优的解决方案,如果有的大量的修改和查询时,由于修改造成的cache失效,会给服务器造成很大的开销,可以通过query_cache_type[0(OFF)1(ON)2(DEMAND)]来控制缓存的开关. 需要注意的是mysql query cache 是对大小写敏感的,因为Query Cache 在内存中是以 HASH 结构来进行映射,HASH 算法基础就是组成 SQL 语句的字符,所以 任何sql语句的改变

转贴一个有关MYSQL的文章.E文的.MySQLs Query Cache

cache|mysql A typical scenarioBoss: Our new website is crawling! How can it be, we have four state-of-the-art web servers - what's the problem?You: Well, the web servers are fine - it's the database server that's struggling.Boss: What? You told me th

pgpool-II 3.2 coming soon, get cool feature with it, on memory query cache and Built in HA

Almost one year worth of development, pgpool-II 3.2 release is getting closer. This version has two major new features: On memory query cache Built in HA On memory query cache is similar to MySQL's query cache.  Caching query SELECT query results by

MySQL SQL 分析 - 参数化查询 vs query cache 功能

query cache,  mysql 5 开始附带的一个功能, 与引擎无关, 只与数据查询语法相关.   测试描述: 当前使用中是 MySQL-5.6.14 Linux RHEL6  64 位系统产生环境,  使用 INNODB 引擎, 分配 innodb 2g 内存空间   [root@TiYanPlat ~]# uname -a Linux TiYanPlat 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64

【转载】MySQL Query Cache 小结

      最近经常有人问我 MySQL Query Cache 相关的问题,就整理一点 MySQL Query Cache 的内容,以供参考.       顾名思义,MySQL Query Cache 就是用来缓存和 Query 相关的数据的.具体来说,Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集.大概来讲,就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中.       在大部分的 MySQL

MySQL · 源码分析 · Query Cache内部剖析

Query Cache背景 Query Cache在其他数据库里面也称为结果集缓存.顾名思义,它的目的是将SELECT语句与其返回结果缓存到Query Cache中,如果重复执行相同的SELECT语句的话,我们可以跳过MySQL的解析.优化.执行阶段,将SELECT的查询结果直接返回到客户端,加速SELECT语句的执行. Query Cache中的主要数据结构 Query_cache 对整个Query Cache进行管理,负责提供接口供Server调用. Query_cache_block Qu