登录 |  注册 |  繁體中文


mysql查询缓存

分类: 数据库 颜色:橙色 默认  字号: 阅读(5623) | 评论(0)

一、MySQL查询缓存(Query Cache)

The MySQL Query Cache

从 MySQL4开始,出现了QueryCache查询缓存,如果使用了QueryCache,当查询接收到一个和之前同样的查询,服务器将会从查询缓存中检索结果,而不是再次分析和执行上次的查询。这样就大大提高了性能,节省时间,非常有用。

MySQL查询缓存保留了查询返回给客户端的完整结果。当缓存命中的时候,服务器马上返回保存的结果,并跳过解析、优化和执行步骤。

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

查询缓存不会存储有不确定结果的查询。因此,任何一个包含不确定函数(比如NOW()或CURRENT_DATE()) 的查询不会被缓存。查询缓存不会缓存引用了用户自定义函数、用户自定义变量、临时表、mysql数据库中的表或者任何一个有列级权限的表的查询。请参阅 MySQL手册了解所有不会被缓存的查询类型。

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

QueryCache是根据SQL语句来cache的。一个SQL查询如果以select开头,那么MySQL服务器将尝试对其使用QueryCache。每个Cache都是以SQL文本作为key来存的。在应用QueryCache之前,SQL文本不会被作任何处理。也就是说,两个SQL语句,只要相差哪怕是一个字符(例如大小写不一样;多一个空格等),那么这两个SQL将使用不同的一个CACHE。

不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客户端里,在发送SQL给服务器之前,会做如下处理:

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

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

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

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


二、缓存设置

打开查询缓存,是通过几个步骤来设置的,例如:虽然你设置Mysql允许查询缓存,但

是如果你设置的查询缓存大小为了0,这和没有允许没什么区别。

所以必须是几个步骤的设置才能真正打开查询缓存这个功能。

下面我用 mysql6.0 最为演示最常用的设置查询缓存

第一: query_cache_type 使用查询缓存的方式

一般,我们会把 query_cache_type 设置为 ON,默认情况下应该是ON

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON |
+--------------------+
这样 当我们执行 select id,name from tableName; 这样就会用到查询缓存。

在 query_cache_type 打开的情况下,如果你不想使用缓存,需要指明
select sql_no_cache id,name from tableName;

当然也可以禁用查询缓存: mysql> set session query_cache_type=off;
这里我们不讨论这个,我们演示常用的设置。

第二: 系统变量 have_query_cache 设置查询缓存是否可用

mysql> show variables like ’have_query_cache’;

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
上面的显示,表示设置查询缓存是可用的。

第三: 系统变量 query_cache_size

表示查询缓存大小,也就是分配内存大小给查询缓存,如果你分配大小为0,

那么 第一步 和 第二步 起不到作用,还是没有任何效果。

mysql> select @@global.query_cache_size;

+---------------------------+
| @@global.query_cache_size |
+---------------------------+
| 16777216 |
+---------------------------+
上面是 mysql6.0设置默认的,之前的版本好像默认是0的,那么就要自己设置下。

设置 set @@global.query_cache_size=1000000; 这里是设置1M左右,900多K。

再次查看下 select @@global.query_cache_size;

+---------------------------+
| @@global.query_cache_size |
+---------------------------+
| 999424 |
+---------------------------+
显示我们设置新的大小,表示设置成功。

第四: query_cache_limit 控制缓存查询结果的最大值

例如: 如果查询结果很大, 也缓存?这个明显是不可能的。

MySql 可以设置一个最大的缓存值,当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M,也就是超过了1M查询结果就不会缓存。

mysql> select @@global.query_cache_limit;

+----------------------------+
| @@global.query_cache_limit |
+----------------------------+
| 1048576 |
+----------------------------+

这个是默认的数值,如果需要修改,就像设置缓存大小一样设置,使用set
重新指定大小。

三、变量

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

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

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

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

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

四、状态

show status like ’QueryCacheache%’可以看到这些信息。

QueryCacheache_free_blocks
当一个表被更新之後,和它相关的cache blocks将被free。但是这个block依然可能存在队列中,除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。
QueryCacheache_free_memory
可用内存,如果很小,考虑增加query_cache_size
QueryCacheache_hits
自mysql进程启动起,cache的命中数量
QueryCacheache_inserts
自mysql进程启动起,被增加进QueryCache的数量
QueryCacheache_lowmem_prunes
由于内存过少而导致QueryCache被删除的条数。加大query_cache_size,尽可能保持这个值0增长。
QueryCacheache_not_cached
自mysql进程启动起,没有被cache的只读查询数量(包括select,show,use,desc等)
QueryCacheache_queries_in_cache
当前被cache的SQL数量
QueryCacheache_total_blocks
在 QueryCache中的blocks数。一个query可能被多个blocks存储,而这几个blocks中的最後一个,未用满的内存将会被释放掉。例如一个QueryCache结果要占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文本,查询结果。

五、存储块(block)

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

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

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

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

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

参考 http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache




姓 名: *
邮 箱:
内 容: *
验证码: 点击刷新 *   

回到顶部