MySQL Study之--MySQL查询缓存

﹏ヽ暗。殇╰゛Y 2022-08-18 12:13 155阅读 0赞

[**MySQL Study之--MySQL查询缓存**][MySQL Study_--MySQL]

转自：http://www.cnblogs.com/zemliu/archive/2013/08/03/3234372.html（感谢作者）

MySQL查询缓存可以跳过SQL解析优化查询等阶段,直接返回缓存结果给用户,查询缓存的工作流程如下:

**命中条件**

缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key.在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.

如果查询中有不确定数据,例如CURRENT\_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的

**工作流程**

1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)

2. 如果找到了缓存,则直接返回缓存(性能提升)

3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.

4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)

**缓存失效**

当某个表正在写入数据,则这个表的缓存(命中检查,缓存写入等)将会处于失效状态.在Innodb中,如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态,在这个事务提交前,这个表的相关查询都无法被缓存

**缓存的内存管理**

缓存会在内存中开辟一块内存(query\_cache\_size)来维护缓存数据,其中有大概40K的空间是用来维护缓存的元数据的,例如空间内存,数据表和查询结果的映射,SQL和查询结果的映射等.

MySQL将这个大内存块分为小的内存块(query\_cache\_min\_res\_unit),每个小块中存储自身的类型,大小和查询结果数据,还有指向前后内存块的指针.

MySQL需要设置单个小存储块的大小,在SQL查询开始(还未得到结果)时就去申请一块空间,所以即使你的缓存数据没有达到这个大小,也需要用这个大小的数据块去存(这点跟Linux文件系统的Block一样).如果结果超出这个内存块的大小,则需要再去申请一个内存块.当查询完成发现申请的内存块有富余,则会将富余的空间释放掉,这就会造成内存碎片问题,见下图

![03092910-347ffd9a878b42459427033bf18d2d35.png][]

此处查询1和查询2之间的空白部分就是内存碎片,这部分空闲内存是有查询1查询完以后释放的,假设这个空间大小小于MySQL设定的内存块大小,则无法再被使用,造成碎片问题

在查询开始时申请分配内存Block需要锁住整个空闲内存区,所以分配内存块是非常消耗资源的.注意这里所说的分配内存是在MySQL初始化时就开辟的那块内存上分配的.

**缓存的使用时机**

衡量打开缓存是否对系统有性能提升是一个很难的话题

1. 通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache\_hits) / 查询次数 (Com\_select)

2. 通过缓存写入率, 写入率 = 缓存写入次数 (Qcache\_inserts) / 查询次数 (Qcache\_inserts)

3. 通过 命中-写入率 判断, 比率 = 命中次数 (Qcache\_hits) / 写入次数 (Qcache\_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般来说达到3:1则算是查询缓存有效,而最好能够达到10:1

**缓存配置参数**

1. query\_cache\_type: 是否打开缓存

可选项

1) OFF: 关闭

2) ON: 总是打开

3) DEMAND: 只有明确写了SQL\_CACHE的查询才会吸入缓存

2. query\_cache\_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)

3. query\_cache\_min\_res\_unit: 分配内存块时的最小单位大小

4. query\_cache\_limit: MySQL能够缓存的最大结果,如果超出,则增加 Qcache\_not\_cached的值,并删除查询结果

5. query\_cache\_wlock\_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回

GLOBAL STAUS 中 关于 缓存的参数解释:

Qcache\_free\_blocks: 缓存池中空闲块的个数

Qcache\_free\_memory: 缓存中空闲内存量

Qcache\_hits: 缓存命中次数

Qcache\_inserts: 缓存写入次数

Qcache\_lowmen\_prunes: 因内存不足删除缓存次数

Qcache\_not\_cached: 查询未被缓存次数,例如查询结果超出缓存块大小,查询中包含可变函数等

Qcache\_queries\_in\_cache: 当前缓存中缓存的SQL数量

Qcache\_total\_blocks: 缓存总block数

**减少碎片策略**

1. 选择合适的block大小

2. 使用 FLUSH QUERY CACHE 命令整理碎片.这个命令在整理缓存期间,会导致其他连接无法使用查询缓存

PS: 清空缓存的命令式 RESET QUERY CACHE

**查询缓存问题分析**

![03104207-9ece9e9864564dab9f7e41e13cf4b6ea.png][]

**InnoDB与查询缓存**

Innodb会对每个表设置一个事务计数器,里面存储当前最大的事务ID.当一个事务提交时,InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器.

只有比这个最大ID大的事务能使用查询缓存,其他比这个ID小的事务则不能使用查询缓存.

另外,在InnoDB中,所有有加锁操作的事务都不使用任何查询缓存

# MySQL查询缓存总结 #

转自：http://www.chinaitlab.com/linux/MYSQL/925457.html

可以通过下面的SQL查看当前查询缓存相关参数状态：

SHOW VARIABLES LIKE '%query\_cache%';

输出结果类似下面：

![20130506152822782.png][]

query\_cache\_type

查询缓存类型，有0、1、2三个取值。0则不使用查询缓存。1表示始终使用查询缓存。2表示按需使用查询缓存。

如果query\_cache\_type为1而又不想利用查询缓存中的数据，可以用下面的SQL：

SELECT SQL\_NO\_CACHE \* FROM my\_table WHERE condition;

如果值为2，要使用缓存的话，需要使用SQL\_CACHE开关参数：

SELECT SQL\_CACHE \* FROM my\_table WHERE condition;

query\_cache\_size

默认情况下query\_cache\_size为0，表示为查询缓存预留的内存为0，则无法使用查询缓存。所以我们需要设置query\_cache\_size的值：

SET GLOBAL query\_cache\_size = 134217728;

注意上面的值如果设得太小不会生效。比如我用下面的SQL设置query\_cache\_size大小：

SET GLOBAL query\_cache\_size = 4000;

SHOW WARNINGS;

会返回下面的结果：

![20130506152823724.png][]

缓存条件

查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果，可以通过下面的SQL来查看缓存命中次数（是个累加值）：

SHOW STATUS LIKE 'Qcache\_hits';

另外即使完全相同的SQL，如果使用不同的字符集、不同的 [协议][Link 1]等也会被认为是不同的查询而分别进行缓存。

缓存数据失效时机

在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

清理查询缓存

可以使用下面三个SQL来清理查询缓存：

1.FLUSH QUERY\_CACHE;

2.RESET QUERY\_CACHE;

3.FLUSH TABLES;<span></span>

第一个SQL会清理查询缓存内存碎片。第二个SQL会从查询缓存中移出所有查询。第三个SQL会关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

[MySQL Study_--MySQL]: http://www.cnblogs.com/zemliu/p/3234372.html
[03092910-347ffd9a878b42459427033bf18d2d35.png]: /images/20220731/ad793948549d47c4872c4e5652a70d48.png
[03104207-9ece9e9864564dab9f7e41e13cf4b6ea.png]: /images/20220731/f5c4d2c9313e4616b6e467c74dc3a460.png
[20130506152822782.png]: http://linux.chinaitlab.com/UploadFiles_7565/201305/20130506152822782.png
[20130506152823724.png]: http://linux.chinaitlab.com/UploadFiles_7565/201305/20130506152823724.png
[Link 1]: http://cisco.chinaitlab.com/List_11.html