MySQL的Query Cache原理分析
程序员文章站
2023-11-04 19:33:46
原理 querycache(下面简称qc)是根据sql语句来cache的。一个sql查询如果以select开头，那么mysql服务器将尝试对其使用qc。每个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文本，查询结果。
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文本，查询结果。