当前位置: 首页 > 技术与资源 > 技术分享 > 正文

Mysql查询缓存研究

2015-12-11 14:45:30

作者:李季鹏新炬网络高级技术专家。


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


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


1)工作流程:


A):服务器接收SQL,以SQL+DB+Query_cache_query_flags作为hash查找键;


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


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


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


2)相关参数及命令


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


变量名 说明
query_cache_min_res_unit 查询缓存分配块的最小尺寸
query_cache_size 为缓存查询结果分配的内存数量
query_cache_limit 不缓存大于该值的结果
query_cache_type 是否缓存查询及结果集
query_cache_wlock_invalidate  

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


状态名 说明
Qcache_queries_in_cache 当前缓存中的查询数
Qcache_inserts 插入到缓存中的查询总数
Qcache_hits 查询命中数
Qcache_lowmem_prunes 由于内存较小而从缓存删除的查询数量
Qcache_not_cached 没有被缓存的次数
Qcache_free_blocks 缓存空闲块

与缓存相关的命令如下:


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


(2)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前端的所有空闲块移到后端并合并成一个空闲块。合并空闲块的操作会扫描所有块,将非空闲块前移,合并前后对比如下图所示。


Mysql查询缓存研究-1

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


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


Mysql查询缓存研究-2

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


3.数据结构


3.1 Query_cache类


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


查询缓存(Query_cache类)的主要成员如下:


成员 含义
byte *cache 查询缓存池
HASH queries 查询语句构成的hash键,hash值指向queries_blocks
HASH tables 表名构成的hash键,hash值指向tables_blocks
Query_cache_block *first_block 第一个缓存块,链表结构
Query_cache_block *queries_blocks 查询块链表
Query_cache_block *tables_blocks 表块链表
Query_cache_memory_bin *bins 空闲块链表
Query_cache_memory_bin_step *steps 某区域内的空闲块信息,如空闲块的增量等

3.2缓存块


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


缓存块结构体
Block header
表信息
Table of database table lists
Type depended header   
(Query_cache_query, Query_cache_table, Query_cache_result)
数据

块头信息(Query_cache_block)由如下成员组成:


成员 含义
enum block_type FREE            空闲块
QUERY          查询块,存储SQL
RESULT      结果集块
RES_CONT    结果集后继块
RES_BEG      结果集的第一个块,还有后继块
RES_INCOMPLETE 所分配的结果集数据块
TABLE               表块
INCOMPLETE    废弃块,该块的内容失效
ulong length 块长度
ulong used 头部和数据的长度(所使用的内存长度)
Query_cache_block * pnext, pprev 物理顺序链表
Query_cache_block * next, prev 逻辑顺序链表。链接的是相同类型的块
uint n_tables 表个数

3.4查询、结果集块


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


查询块头信息
Query block
表信息
Table 0
Table 1
.........
数据头信息
query header
字符集和SQL
Charset encoding + query text

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


块头信息
Result block
 
数据头信息
Result header
结果集
Result data

一个查询对应一个结果集,同样一个查询块会对应一个或多个结果集块(因为查询结果       集可能分为多个包发往客户端);


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


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


3.5表链表


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


4.简单实验验证


Mysql查询缓存研究-32

Mysql查询缓存研究-4

Mysql查询缓存研究-5

第一次的sql语句执行过程


Mysql查询缓存研究-6

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


Mysql查询缓存研究-7

Mysql查询缓存研究-8

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


Mysql查询缓存研究-9

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的缓存对象如下:


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


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


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


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


Mysql> set @id=1;


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


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


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


f)非自动提交(显示使用BEGIN…END)事务中的SQL不会被缓存。


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


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


Mysql> select @id;


i)在下面的SELECT操作也不会被缓存:


SELECT ...IN SHARE MODE
SELECT ...FOR UPDATE
SELECT ...INTO OUTFILE ...
SELECT ...INTO DUMPFILE ...
SELECT * FROM ...WHERE autoincrement_col IS NULL

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


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


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

上一篇:说说航空产品测试那些事
下一篇:基于Emma的独立测试用例代码覆盖率统计