【转载】MySQL Query Cache 小结

      最近经常有人问我 MySQL Query Cache 相关的问题，就整理一点 MySQL Query Cache 的内容，以供参考。

      顾名思义，MySQL Query Cache 就是用来缓存和 Query 相关的数据的。具体来说，Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集。大概来讲，就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中。

      在大部分的 MySQL 分发版本中，Query Cache 功能默认都是打开的，我们可以通过调整 MySQL Server 的参数选项打开该功能（mysql> show variables like "%query_cache%";）。主要由以下5个参数构成：

  • query_cache_limit：允许 Cache 的单条 Query 结果集的最大容量，默认是1MB，超过此参数设置的 Query 结果集将不会被 Cache
  • query_cache_min_res_unit：设置 Query Cache 中每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
  • query_cache_size：设置 Query Cache 所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到1024的倍数
  • query_cache_type：控制 Query Cache 功能的开关，可以设置为0(OFF),1(ON)和2(DEMAND)三种，意义分别如下：
    • 0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache
    • 1(ON)：开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用Query Cache
    • 2(DEMAND)：开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
  • query_cache_wlock_invalidate：控制当有写锁定发生在表上的时刻是否先失效该表相关的 Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有 Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的 Query Cache （类似脏读）。

Query Cache 如何处理子查询的？
      这是我遇到的最为常见的一个问题。其实 Query Cache 是以客户端请求提交的 Query 为对象来处理的，只要客户端请求的是一个 Query，无论这个 Query 是一个简单的单表查询还是多表 Join，亦或者是带有子查询的复杂 SQL，都被当作成一个 Query，不会被分拆成多个 Query 来进行 Cache。所以，存在子查询的复杂 Query 也只会产生一个Cache对象，子查询不会产生单独的Cache内容。UNION[ALL] 类型的语句也同样如此。

Query Cache 是以 block 的方式存储的数据块吗？
      不是，Query Cache 中缓存的内容仅仅只包含该 Query 所需要的结果数据，是结果集。当然，并不仅仅只是结果数据，还包含与该结果相关的其他信息，比如产生该 Cache 的客户端连接的字符集，数据的字符集，客户端连接的 Default Database等。

Query Cache 为什么效率会非常高，即使所有数据都可以 Cache 进内存的情况下，有些时候也不如使用 Query Cache 的效率高？
      Query Cache 的查找，是在 MySQL 接受到客户端请求后在对 Query 进行权限验证之后，SQL 解析之前。也就是说，当 MySQL 接受到客户端的SQL后，仅仅只需要对其进行相应的权限验证后就会通过 Query Cache 来查找结果，甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不许要发生任何存储引擎的交互，减少了大量的磁盘 IO 和 CPU 运算，所以效率非常高。

客户端提交的 SQL 语句大小写对 Query Cache 有影响吗？
      有，由于 Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以必须要整个 SQL 语句在字符级别完全一致，才能在 Query Cache 中命中，即使多一个空格也不行。

一个 SQL 语句在 Query Cache 中的内容，在什么情况下会失效？
      为了保证 Query Cache 中的内容与是实际数据绝对一致，当表中的数据有任何变化，包括新增，修改，删除等，都会使所有引用到该表的 SQL 的 Query Cache 失效。

为什么我的系统在开启了 Query Cache 之后整体性能反而下降了？
      当开启了 Query Cache 之后，尤其是当我们的 query_cache_type 参数设置为 1 以后，MySQL 会对每个 SELECT 语句都进行 Query Cache 查找，查找操作虽然比较简单，但仍然也是要消耗一些 CPU 运算资源的。而由于 Query Cache 的失效机制的特性，可能由于表上的数据变化比较频繁，大量的 Query Cache 频繁的被失效，所以 Query Cache 的命中率就可能比较低下。所以有些场景下，Query Cache 不仅不能提高效率，反而可能造成负面影响。

如何确认一个系统的 Query Cache 的运行是否健康，命中率如何，设置量是否足够？
MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态（mysql> show status like "%QCache%";），具体如下：

  • Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目
  • Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量
  • Qcache_hits：Query Cache 命中次数
  • Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
  • Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
  • Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
  • Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量
  • Qcache_total_blocks：Query Cache 中总的 Block 数量

      可以根据这几个状态计算出 Cache 命中率，计算出 Query Cache 大小设置是否足够，总的来说，我个人不建议将 Query Cache 的大小设置超过256MB，这也是业界比较常用的做法。

MySQL Cluster 是否可以使用 Query Cache？
      其实在我们的生产环境中也没有使用 MySQL Cluster，所以我也没有在 MySQL Cluster 环境中使用 Query Cache 的实际经验，只是 MySQL 文档中说明确实可以在 MySQL Cluster 中使用 Query Cache。从 MySQL Cluster 的原理来分析，也觉得应该可以使用，毕竟 SQL 节点和数据节点比较独立，各司其职，只是 Cache 的失效机制会要稍微复杂一点。

======== 我是分割线 ========= 

MySQL查询缓存变量解释

Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

Qcache_free_memory：缓存中的空闲内存。

Qcache_hits：每次查询在缓存中命中时就增大

Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)

Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。

Qcache_total_blocks：缓存中块的数量。

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小

query_cache_size：查询缓存大小

query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据 查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且 Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

引用一段前辈的话

优化提示：
如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。
如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache
根据我看的 《High Performance MySQL》中所述，关于query_cache_min_res_unit大小的调优，书中给出了一个计算公式，可以供调优设置参考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

======== 我是分割线 =========   

Query Cache，看上去很美  （2009年） 

      当你的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。

      在这个“Cache为王”的时代，我们总是通过不同的方式去缓存我们的结果从而提高响应效率，但一个缓存机制是否有效，效果如何，却是一个需要好好思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中，如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%[1]。但实际情况如何？Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除之。这里“数据表更改”包括：INSERT、UPDATE,DELETE、TRUNCATE、ALTER TABLE、DROP TABLE or DROP DATABASE等。举个例子，如果数据表posts访问频繁，那么意味着它的很多数据会被QC缓存起来，但是每一次posts数据表的更新，无论更新是不是影响到了cache的数据，都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。有实验表明，糟糕时，QC会降低系统13%[1]的处理能力。

      如果你的应用对数据库的更新很少，那么QC将会作用显著。比较典型的如博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时候QC的作用会比较明显。

再如，一个更新频繁的BBS系统。下面是一个实际运行的论坛数据库的状态参数：

QCache_hit 5280438
QCache_insert 8008948
Qcache_not_cache 95372
Com_select 8104159

      可以看到，数据库一共往QC中写入了约800W次缓存，但是实际命中的只有约500W次。也就是说，每一个缓存的使用率约为0.66次。很难说，该缓存的作用是否大于QC系统所带来的开销。但是有一点是很肯定的，QC缓存的作用是很微小的，如果应用层能够实现缓存，将可以忽略QC的效果。

-------------下面是关于QC的一些其他细节-----------------

一、Query Cache相关参数：

  • query_cache_size QC占用空间大小，通过将其设置为0关闭QC功能
  • query_cache_type 0表示关闭QC；1表示正常缓存；2表示SQL_CACHE才缓存
  • query_cache_limit 最大缓存结果集
  • query_cache_min_res_unit 手册上说，QC会按照这个值分配缓存block的大小。
  • Qcache_lowmem_prunes 这是一个状态变量(show status)，当缓存空间不够需要释放旧的缓存时，该值会自增。

二、Query Cache观察：

?


1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE t1(id INT,var1 varchar(10));

    //Com_select:8  Qcache_hits:1

INSERT INTO t1 VALUES(1,’WWW’);

    //Com_select:8  Qcache_hits:1

SELECT * FROM t1 WHERE id=1;

    //Com_select:9  Qcache_hits:1

SELECT * FROM t1 WHERE id=1;

    //Com_select:9  Qcache_hits:2 Qcache_queries_in_cache:1

INSERT INTO t1 VALUES(2,’RRRR’);

    //Com_select:9  Qcache_hits:2 Qcache_queries_in_cache:0

SELECT * FROM t1 WHERE id=1; //INSERT后Cache失效

    //Com_select:10  Qcache_hits:2 Qcache_queries_in_cache:1

参考：

  1. http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
  2. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
  3. http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

(全文完)

时间： 2024-11-03 06:40:55

【转载】MySQL Query Cache 小结的相关文章

MySQL Query Cache导入查询缓慢解决办法

进mysql:SHOW FULL PROCESSLIST; Command多为Sleep,客户端过来的连接倒也正常,重复执行上述命令发现有些Command的状态为"Coping to tmp table",果断打开my.cnf发现并无tmp_table_size的设置,MySQL环境是使用yum安装,这也是为什么要编译MySQL的缘故 mysql> SHOW VARIABLES LIKE 'tmp_table_size'; +----------------+----------

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

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缓存简单使用教程

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内部剖析

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

MySql如何计算Key Hitrate ,Key Buffer Used, Query Cache Hitrate??请问.

问题描述 我在MySQL数据库中的information_schema 库里的GLOBAL_VARIABLES. GLOBAL_STATUS表可以取出来好多数据,比如:•key_buffer_size•key_cache_block_size•query_cache_limit•query_cache_size...等等 可是我不知道怎么计算Key Hitrate ,Key Buffer Used, Query Cache Hitrate 希望有人可以帮到我. 解决方案 mysql> show

MySQL的Query Cache原理分析_Mysql

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

MySQL 缓存 Query Cache

过滤所有注释 去掉SQL文本前後的空格,TAB等字符.注意,是文本前面和後面的.中间的不会被去掉. 下面的三条SQL里,因 为SELECT大小写的关系,最後一条和其他两条在QC里肯定是用的不一样的存储位置.而第一条和第二条,区别在于後者有个注释,在不同客户端,会有不一 样的结果.所以,保险起见,请尽量不要使用动态的注释.在PHP的mysql教程扩展里,SQL的注释是不会被去掉的.也就是三条SQL会被存储在三个不同的 缓存里,虽然它们的结果都是一样的. select * FROM people w