MySQL的Query Cache原理分析

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

过滤所有注释 
去掉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文本,查询结果。

(0)

相关推荐

  • 对于mysql的query_cache认识的误区

    其实,这一种说法是不完全正确的.首先第一点,mysql的query_cache的键值并不是简单的query,而是query加databasename加flag.这个从源码中就可以看出.在这里不做重点描述,后续可以针对于这一点再具体分析.重要的是第二点,是不是加了空格,mysql就认为是不同的查询呢?实际上这个是要分情况而言的,要看这个空格加在哪. 如果空格是加在query之前,比如是在query的起始处加了空格,这样是丝毫不影响query cache的结果的,mysql认为这是一条query,

  • MySQL的Query Cache原理分析

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

  • MySQL的Query Cache图文详解

    目录 一.原理概述 二.Query Cache系统变量 1. have_query_cache 2. query_cache_limit 3. query_cache_min_res_unit 4. query_cache_size 5. query_cache_type 6. query_cache_wlock_invalidate 三.Query Cache状态变量 1. Qcache_free_blocks 2. Qcache_free_memory 3. Qcache_hits 4. Q

  • 解读mysql主从配置及其原理分析(Master-Slave)

    1.在主数据库服务器为从服务器添加一个拥有权限访问主库的用户:GRANT REPLICATION SLAVE ON *.* TO ' test'@'%' IDENTIFIED BY 'test'; (%表示允许所有IP,可设置指定从服务器IP)添加用户后:可在从服务器上用mysql -h127.0.0.1 -utest -ptest;  来测试是否有权限访问主数据库 2.在主据库配置文件加上:#master configserver-id       = 1log-bin          =

  • mysql 数据库中索引原理分析说明

    下面,我们举例来说明一下聚集索引和非聚集索引的区别:其实,我们的汉语字典的正文本身就是一个聚集索引.比如,我们要查"安"字,就会很自然地翻开字典的前几页,因为"安"的拼音是"an",而按照拼音排序汉字的字典是以英文字母"a"开头并以"z"结尾的,那么"安"字就自然地排在字典的前部.如果您翻完了所有以"a"开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字:同样

  • MYSQL主从不同步延迟原理分析及解决方案

    1. MySQL数据库主从同步延迟原理.要说延时原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率很比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施.DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能可slave上的其他查询产生lock争

  • MySQL取消了Query Cache的原因

    MySQL之前有一个查询缓存Query Cache,从8.0开始,不再使用这个查询缓存,那么放弃它的原因是什么呢?在这一篇里将为您介绍. MySQL查询缓存是查询结果缓存.它将以SEL开头的查询与哈希表进行比较,如果匹配,则返回上一次查询的结果.进行匹配时,查询必须逐字节匹配,例如 SELECT * FROM t1; 不等于select * from t1;,此外,一些不确定的查询结果无法被缓存,任何对表的修改都会导致这些表的所有缓存无效.因此,适用于查询缓存的最理想的方案是只读,特别是需要检查

  • mysql滑动订单问题原理与解决方法实例分析

    本文实例讲述了mysql滑动订单问题原理与解决方法.分享给大家供大家参考,具体如下: 先根据以下代码来创建表MonthlyOrders并导入一定的数据 CREATE TABLE MonthlyOrders( ordermonth DATE, ordernum INT UNSIGNED, PRIMARY KEY (ordermonth) ); INSERT INTO MonthlyOrders SELECT '2010-02-01',23; INSERT INTO MonthlyOrders SE

  • PDO防注入原理分析以及使用PDO的注意事项总结

    本文详细讲述了PDO防注入原理分析以及使用PDO的注意事项,分享给大家供大家参考.具体分析如下: 我们都知道,只要合理正确使用PDO,可以基本上防止SQL注入的产生,本文主要回答以下两个问题: 为什么要使用PDO而不是mysql_connect? 为何PDO能防注入? 使用PDO防注入的时候应该特别注意什么?   一.为何要优先使用PDO? PHP手册上说得很清楚: Prepared statements and stored procedures Many of the more mature

  • MySQL执行状态的查看与分析

    当感觉mysql性能出现问题时,通常会先看下当前mysql的执行状态,使用 show processlist 来查看,例如: 其中state状态列信息非常重要,先看下各列含义,然后看下state常用状态 各列的含义 1.id 一个标识,你要kill一个语句的时候使用,例如 mysql> kill 207; 2.user 显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句 3.host 显示这个语句是从哪个ip 的哪个端口上发出的,可用来追踪出问题语句的用户 4.db 显示这

  • MySQL的join buffer原理

    一.MySQL的join buffer 在MySQL对于join操作的处理过程中,join buffer是一个重要的概念,也是MySQL对于table join的一个重要的优化手段.虽然这个概念实现并不复杂,但是这个是实现MySQL join连接优化的一个重要方法,在"暴力"连接的时候可以极大提高join查询的效率. 关于这个概念的权威说明当然是来自MySQL文档中对于这个概念的说明,说明的文字不多,但是言简意赅,说明了这个优化的主要实现思想: Assume you have the

随机推荐