Mysql查询缓存研究

Mysql查询缓存研究

2015-12-20  李季鹏  DBAplus社群

转载声明：本文为DBA+社群原创文章，转载必须连同本订阅号二维码全文转载，并注明作者名字及来源：DBA+社群（dbaplus）。

专家简介

李季鹏

【DBA+社群•开源数据库用户组】联合发起人

5年+MySQL经验，主导运营商去IOE技术落地，精通MySQL数据库及相关解决方案，对MySQL集群架构，高可用方案有深入的研究。

MySQL的查询缓存并非缓存执行计划，而是查询及其结果集，这就意味着只有相同的查询操作才能命中缓存，因此MySQL的查询缓存命中率很低，另一方面，对于大结果集的查询，其查询结果可以从cache中直接读取，有效的提升了查询效率。

1

工作流程和相关参数及命令

1.1 工作流程

A)：服务器接收SQL，以SQL+DB+Query_cache_query_flags作为hash查找键；

B)：找到了相关的结果集就将其返回给客户端；

C)：如果没有找到缓存则执行权限验证、SQL解析、SQL优化等一些列的操作；

D)：执行完SQL之后，将结果集保存到缓存

1.2 相关参数及命令

与缓存相关的主要参数如下表所示。可以使用命令SHOW VARIABLES LIKE '%query_cache%'查看

与缓存相关的状态变量如下表所示。可以使用命令SHOW STATUS LIKE '%Qcache%'查看

与缓存相关的命令如下：

• FLUSH QUERY CACHE。用于清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。

• RESET QUERY CACHE。用于清空查询缓存所有内容。

2

查询缓存策略

2.1 在查询缓存中查找结果

当服务器端接收到查询包后，系统就会检查查询缓存中是否有该查询，因此利用查询缓存可以省去SQL解析和处理操作，该步骤被封装在query_cache_send_result_to_client()函数，位于sql/sql_parse.cc 的mysql_parse()函数中，而query_cache_send_result_to_client()函数则宏定义于sql/sql_cache.h，详细过程则在sql/sql_cache.cc的send_result_to_client()函数中。

MySQL使用SQL + DB + Query_cache_query_flags作为hash查找键，从缓存中查找SQL的结果集，而SQL语句的这一部分会先去掉首尾的空格，所以首尾有无空格不会被认为是不同的SQL，该过程也在send_result_to_client()函数中。

2.2何时插入查询缓存

MySQL得到结果集后，将结果集以包的形式发送到客户端，在将包发送到客户端之前会将包保存到查询缓存中。是否将结果集插入到查询缓存取决于查询SQL，能够插入到查询缓存的对象如前所述。

该过程封装在query_cache_insert ()函数中，该函数位于sql/sql_cache.cc中

2.3查找空闲块

查询缓存初始化时，整个查询缓存被视为1个空闲块。当有新的查询需要缓存时，就需要分配一个新的缓存块。MySQL会尝试从所有空闲块中找出最适合大小的内存块（即大于所需块大小的最小缓存块）分配给新的查询。如果没找到这种块，则查找小于所需块大小的最大缓存块。如果没有空闲块，就将最老的查询移出缓存，而后再次分配内存，重复之前的步骤，直到找出合适的块。

2.4空闲块分割与合并

如果找到了合适大小的缓存块，并且该块大于所需大小，则将它分割为两个缓存块。新块不能小于min_allocation_unit_bytes。每当产生一个空闲块，系统会检查其临近块中是否包含空闲块，如果包含则将它们合并为一个空闲块。

2.5空闲块存储

根据空闲块的大小，将其存储到不同的区域中，各区域按照存储块的大小以降序排列，每个空闲块按照大小成近似对数分布。

空闲块存储步骤如下所述，query_cache_size为用户设定的查询缓存大小。

区域1：第一个区域中存储的空闲块大小为size <= query_cache_size >> 4，即小于等于query_cache_size/16；

区域2：第二个区域中存储(1 + 1) *1.2个空闲块。该区域中最小空闲块的大小为query_cache_size>>4 >> 2。

区域N：存储(N + 1) *1.2个空闲块。每个区域中的空闲块大小都会递减2^2倍，存储的空闲块数会增加。最小空闲块的大小接近min_allocation_unit字节。

查询缓存空闲块及空闲区域只会在初始化缓存大小时计算。

当要查出恰当的空闲块时，首先需要找到适当的区域，然后计算该区域中的空闲块数。空闲块大小是递增的，因为较小的空闲块会被经常使用。

2.6缓存整理（FLUSH QUERY CACHE）

随着查询缓存内容的增加，缓存中会产生内存碎片，MySQL虽然有碎片合并的机制，但仍不能完全保证碎片的生成，因此在必要的时候需要手工输入命令整理缓存。

缓存整理操作对应于FLUSH QUERY CACHE命令，其内部实现分为两种操作：合并空闲块、合并结果集。

（1）合并空闲块就是将cache前端的所有空闲块移到后端并合并成一个空闲块。合并空闲块的操作会扫描所有块，将非空闲块前移，合并前后对比如下图所示。

该操作会移除找到的所有空闲块，所删除的空闲块的长度会被记录，所有非空闲块会被上移到删除的位置。

（2）结果集合并操作会扫描查询缓存中的所有查询，如果查询结果未被存储在相同的块中，系统就会尝试将其移到同一块中。合并前后对比如下图所示。

如果结果集合并的操作中分配了新块，那么就需要再次执行空闲块合并操作。

3

数据结构

3.1 Query_cache类

查询缓存（Query_cache类）是MySQL查询缓存的入口，query_cache是该类的一个全局实例，用于描述查询缓存。

查询缓存（Query_cache类）的主要成员如下：

缓存块是缓存内容组织的基本单位，每个缓存块的结构如下：

块头信息（Query_cache_block）由如下成员组成：

3.4查询、结果集块

查询块用于存储查询SQL，其内存结构如下图所示：

结果集用于存储某SQL的查询结果，它与查询块相关联，结构如下：

一个查询对应一个结果集，同样一个查询块会对应一个或多个结果集块（因为查询结果 集可能分为多个包发往客户端）；

结果集块是一个双向链表，查找某SQL的结果集可以遍历该链表。

query_block链表的next顺序表示该query的新旧。最新被命中的query被放到链表的最后。缓存的替换策略是替换最旧的查询块。

3.5表链表

当表内容被修改时，缓存中所有该表的查询块及其结果集块都会被移除缓存。为了快速完成该操作，缓存中维护表链表，每个表块都会指向其相关的查询块。

4

简单实验验证

第一次的sql语句执行过程

第二次的执行过程，与第一次相比，很明显地看到少了很多的过程

通过源码调式跟踪发现，当执行一个insert/update/delete或其他使表数据变更的操作时，在返回信息给客户端之前，会执行free_query_internal和free_memory_block操作（该函数位于sql/sql_cache.cc中），这里会把相关表的缓存给清掉。而再一次执行前面已经验证被缓存的语句时，就会发现该语句和结果集在缓存中已经没有了，mysql缓存机制就会再一次将该sql和结果集缓存；

5

总结

（1）MySQL的查询缓存利用率很低，原因是每当有修改表内容操作时，缓存中所有与该表相关的内容全部要被清空。

（2）查询缓存是一次申请query_cache_size大小的内存，而不是随查询的插入动态申请，这样提升了系统性能，因为申请、释放内存的操作很慢。

（3）查询缓存的空闲块是有序的，因为较小的块会被经常使用，同样为了性能考虑。

（4）为充分利用内存，某缓存块填充数据后，如果还有空闲空间，则将空闲空间回收。

（5）缓存替换策略是，当缓存没有空闲块时，系统将最老（最近没有被使用）的查询块剔除。

（6）缓存命中率的计算：Qcache_hits/(Qcache_hits+Com_select)

（7）完全相同的SQL才会命中缓存。在查询缓存中搜索结果前，MySQL不会对SQL进行解析，因此，查询缓存的查找方式是字节匹配。也就是说，如果SQL中包含不确定内容(即大小写不同、注释不同)、多余的空格都会被认为是不同的SQL。

（8）MySQL的缓存对象如下：

• 只缓存SELECT语句。SHOW命令和存储程序不会被缓存。

• 不能缓存预编译语句（prepared statement）和游标。查询缓存中保存的是查询语句和结果集，而预编译语句中存在替代符和额外的参数，游标从块中读取结果，因此上述两种情况不能被缓存。

• 查询语句不能包含动态内容。多次执行某SQL，必须能够返回相同的结果集，因此查询中不能包含像UUID(), RAND(), CONNECTION_ID()这样的函数。

• SQL中包含定义函数和自定义变量不会被缓存。

  Mysql> set @id=1;

  Mysql> select * from test where id=@id 像这种语句也不会缓存

• 对系统表的查询不会被缓存。

  Mysql> select * from mysql.user where user=’root’

• 非自动提交（显示使用BEGIN…END）事务中的SQL不会被缓存。

• 使用TEMPORARY表的SQL不会被缓存。

• 不使用任何表的SQL不会被缓存。

  Mysql> select @id;

• 在下面的SELECT操作也不会被缓存：

（9）表内容更改时缓存失效。修改表的所有操作（DELETE/INSERT/UPDATE等等），都会导致缓存中该表的所有内容（SQL和结果集）被一次清空。很有可能这些操作并没有改变SQL的结果集，但MySQL无法验证哪些SQL会影响缓存而哪些SQL不会影响。也是由于这点，MySQL的缓存利用率不是很高。对于写操作频繁的应用，查询缓存的命中率会相当的低。如果缓存中存在某表的大量SQL，多少也会降低该表的更新速度。

（10）缓存碎片。随着缓存量的增多，查询缓存会产生碎片，这将降低缓存性能。状态变量Qcache_free_blocks描述了缓存中的空闲块，该值越大表示碎片越多。可以用FLUSH QUERY CACHE命令来整理碎片，而对于大缓存，该操作会长时间阻塞查询缓存。

所以，开启query_cachel查询缓存功能对于读多写少的应用来说，会带来一定性能的提高，而对很多写入任务的应用，关闭查询缓存功能也许能够改善一定的性能。

即日起，凡是推送在【DBAplus社群】平台的文章，阅读量超过1000，该文章作者可获得赠书一本。大家如有好的干货文章也可以向我们的订阅号投稿，投稿邮箱：1017465571@qq.com。近期赠书有：白鳝《思想的天空》、杨志洪《Oracle核心技术》……

小编精心为大家挑选了近日最受欢迎的几篇热文:

回复001，看杨志洪《【职场心路】一个老DBA的自白》；

回复002，看丁俊的《【重磅干货】看了此文，Oracle SQL优化文章不必再看！》；

回复003，看胡怡文《PG，一道横跨oltp到olap的梦想之桥》；

回复004，看陈科《memcached&redis等分布式缓存的实现原理》；

回复005，看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》；

回复006，看郑晓辉《存储和数据库不得不说的故事》；

回复007，看袁伟翔《揭秘Oracle数据库truncate原理》；

回复008，看李海翔《MySQL优化案例：半连接（semi join）优化方式导致的查询性能低下》；

回复009，看丁启良《LINUX类主机JAVA应用程序占用CPU、内存过高分析手段》；

回复010，看徐桂林《以应用为中心的企业混合云管理》。