【MySQL】聊一聊MySQL的缓存机制

曾经终败给现在 2023-10-14 20:14 49阅读 0赞

#### 聊一聊MySQL的缓存机制 ####

*  一、MySQL有缓存吗？是什么？在哪？
 *  二、MySQL缓存机制
 *   *   *  1.缓存规则
         *  2.缓存失效
         *  3.缓存机制中的内存管理
 *  三、缓存管理和配置
 *  四、MySQL缓存优缺点
 *  五、生产环境如何设置MySQL Query Cache

## 一、MySQL有缓存吗？是什么？在哪？ ##

首先，**MySQL一定是有缓存的**。  
聊一聊我最初的认识。因为使用SQL客户端工具进行查询操作，每次建立临时表，关闭会话session之后，再次打开会话，临时表就会被释放，不存在，因此我粗略认为缓存就保持在session中。  
直到有一次的大数据量查询操作时：第一次查询1000万数据用时600s，而之后每次查询也就30s，关闭会话后，再次查询还是30s，本以为是sql代码优化了，心里还挺高兴，结果 有位前辈说是因为有，我立即反问道：缓存不在会话吗？他说：缓存保持在MySQL服务端！！！

> 经过总结发现：MySQL Query Cache(MySQL查询缓存)在MySQL Server中是默认打开的，但是网上各种资料以及有经验的DBA都建议生产环境中把MySQL Query Cache关闭。按道理，MySQL Server默认打开，是鼓励用户使用缓存，但是大拿们却建议关闭此功能，并且国内各个云厂商提供的MySQL云服务中默认都是关闭这个功能。

**MySQL查询缓存介绍：**

MySQL查询缓存是MySQL中比较独特的一个缓存区域，用来缓存特定Query的整个结果集信息，且共享给所有客户端。为了提高完全相同的Query语句的响应速度，**MySQL Server会对查询语句进行Hash计算后，把得到的hash值与Query查询的结果集对应存放在Query Cache中**。

当MySQL Server打开Query Cache之后，MySQL Server会对接收到的每一个SELECT 语句通过特定的Hash算法计算该Query的Hash值，然后**通过该hash值到Query Cache中去匹配。**  
 **①如果没有匹配，将这个hash值存放在一个hash链表中，并将Query的结果集存放到cache中，存放hash值链表的每个hash节点存放了相应Quey结果集在cache中的地址，以及该query所涉及到一些table相关信息；  
②如果通过hash值匹配到了一样的Query，则直接将cache中相应的Query结果集返回给客户端。**

## 二、MySQL缓存机制 ##

MySQL缓存机制其实就是 缓存sql文本以及查询结果集。通过将sql文本进行hash计算，hash值作为key，结果集作为value，进行存储。当运行相同的sql，服务端直接从缓存中取到结果，而不需要再去解析和执行SQL。进而提升查询性能。  
查询必须是完全相同(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。**使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。**

#### 1.缓存规则 ####

*  开启了缓存，MySQL Server会自动将查询语句和结果集返回到内存，下次再查直接从内存中取；
 *  缓存的结果是通过sessions共享的，所以一个client查询的缓存结果，另一个client也可以使用
 *  MySQL Query Cache内容为 select 的结果集, cache 使用完整的SQL字符串做 key, 并区分大小写，空格等。即两个SQL必须完全一致才会导致cache命中。即检查查询缓存时，MySQL Server不会对SQL做任何处理，它精确的使用客户端传来的查询，只要字符大小写或注释有点不同，查询缓存就认为是不同的查询；
 *  prepared statement永远不会cache到结果，即使参数完全一样。在 5.1 之后会得到改善。
 *  where条件中如包含任何一个不确定的函数将永远不会被cache, 比如current\_date, now等。
 *  date 之类的函数如果返回是以小时或天级别的，最好先算出来再传进去。
 *  太大的result set不会被cache (< query\_cache\_limit)
 *  MySQL缓存在分库分表环境下是不起作用的
 *  执行SQL里有触发器,自定义函数时，MySQL缓存也是不起作用的

#### 2.缓存失效 ####

1.  表的结构或数据发生改变时，查询缓存中的数据不再有效。如INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。
2.  一旦表数据进行任何一行的修改，基于该表相关cache立即全部失效。
3.  手动清理缓存  
    相关sql语句：
    
     *  FLUSH QUERY CACHE； \#清理查询缓存内存碎片
     *  RESET QUERY CACHE；\#从查询缓存中移除所有查询
     *  FLUSH TABLES； \#关闭所有打开的表，同时该操作会清空查询缓存中的内容。

**注意**：MySQL Query Cache 是对大小写敏感的，因为Query Cache 在内存中是以 HASH 结构来进行映射，HASH 算法基础就是组成 SQL 语句的字符，所以 任何SQL语句的改变重新cache.

#### 3.缓存机制中的内存管理 ####

MySQL Query Cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的block, 用来存储类型、大小、数据等信息；一个result set的cache通过链表把这些block串起来。block最短长度为query\_cache\_min\_res\_unit。

当服务器启动的时候，会初始化缓存需要的内存，是一个完整的空闲块。当查询结果需要缓存的时候，先从空闲块中申请一个数据块为参数query\_cache\_min\_res\_unit配置的空间，即使缓存数据很小，申请数据块也是这个，因为查询开始返回结果的时候就分配空间，此时无法预知结果多大。

分配内存块需要先锁住空间块，所以操作很慢，MySQL会尽量避免这个操作，选择尽可能小的内存块，如果不够，继续申请，如果存储完时有空余则释放多余的。

但是如果并发的操作，余下的需要回收的空间很小，小于query\_cache\_min\_res\_unit，不能再次被使用，就会产生碎片。

## 三、缓存管理和配置 ##

MySQL缓存相关的配置参数：

*  have\_query\_cache  
    该MySQL Server是否支持Query Cache。
 *  query\_cache\_limit  
    MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB)如果某个查询的结果超出了这个值，Qcache\_not\_cached的值会加1，如果某个操作总是超出，可以考虑在SQL中加上SQL\_NO\_CACHE来避免额外的消耗。
 *  query\_cache\_min\_res\_unit  
    查询缓存分配的最小块的大小(字节)。 默认值是4096(4KB)。当查询进行的时候，MySQL把查询结果保存在qurey cache中，但如果要保存的结果比较大，超过query\_cache\_min\_res\_unit的值 ，这时候mysql将一边检索结果，一边进行保存结果，所以，有时候并不是把所有结果全部得到后再进行一次性保存，而是每次分配一块query\_cache\_min\_res\_unit大小的内存空间保存结果集，使用完后，接着再分配一个这样的块，如果还不不够，接着再分配一个块，依此类推，也就是说，有可能在一次查询中，mysql要进行多次内存分配的操作。适当的调节query\_cache\_min\_res\_unit可以优化内存如果你的查询结果都是一些small result,默认的query\_cache\_min\_res\_unit可能会造成大量的内存碎片如果你的查询结果都是一些larger resule，你可以适当的把query\_cache\_min\_res\_unit调大
 *  query\_cache\_size  
    为缓存查询结果分配的内存的数量，单位是字节，且数值必须是1024的整数倍。默认值是0，即禁用查询缓存。请注意如果设置了该值，即使query\_cache\_type设置为0也将分配此数量的内存。
 *  query\_cache\_type  
    设置查询缓存类型，默认为ON。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端可以设置SESSION值以影响他们自己对查询缓存的使用。
 *  query\_cache\_wlock\_invalidate  
    如果某个表被锁住，是否返回缓存中的数据，默认关闭，也是建议的。一般情况，当客户端对MyISAM表进行WRITE锁定时，如果查询结果位于查询缓存中，则其它客户端未被锁定，可以对该表进行查询。将该变量设置为1，则可以对表进行WRITE锁定，使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时，可以强制其它试图访问表的客户端来等待。

**开启缓存**

mysql> set global query_cache_size = 600000; --设置缓存内存大小
    mysql> set global query_cache_type = ON;     --开启查询缓存

**关闭缓存**

mysql> set global query_cache_size = 0; --设置缓存内存大小为0， 即初始化是不分配缓存内存
    mysql> set global query_cache_type = OFF;     --关闭查询缓存

## 四、MySQL缓存优缺点 ##

**优点**  
Query Cache的查询，发生在MySQL接收到客户端的查询请求、查询权限验证之后和查询SQL解析之前。也就是说，当MySQL接收到客户端的查询SQL之后，仅仅只需要对其进行相应的权限验证之后，就会通过Query Cache来查找结果，甚至都不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互。由于Query Cache是基于内存的，直接从内存中返回相应的查询结果，因此减少了大量的磁盘I/O和CPU计算，导致效率非常高。

**缺点**  
Query Cache的优点很明显，但是也不能忽略它所带来的一些缺点：

查询语句的hash计算和hash查找带来的资源消耗。如果将query\_cache\_type设置为1（也就是ON），那么MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。虽然hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了。  
Query Cache的失效问题。如果表的变更比较频繁，则会造成Query Cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。  
查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询（因为他们的hash值会不同）。  
相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存。

## 五、生产环境如何设置MySQL Query Cache ##

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

**所以，如果经常有更新的系统，想要获得较高tps的话，建议一开始就关闭Query Cache**。

> 查询缓存的替代方案MySQL查询缓存工作的原则是：执行查询最快的方式就是不去执行，但是查询仍然需要发送到服务器端，服务器也还需要做一点点工作，如果对于某些查询完全不需要与服务器通信效果会如何呢，这时客户端缓存可以很大程度上分担MySQL服务器的压力。

参考：[\[玩转MySQL之四\]MySQL缓存机制][MySQL_MySQL]

[MySQL_MySQL]: https://zhuanlan.zhihu.com/p/55947158#:~:text=1%20FLUSH%20QUERY%20CACHE%EF%BC%9B%20#%E6%B8%85%E7%90%86%E6%9F%A5%E8%AF%A2%E7%BC%93%E5%AD%98%E5%86%85%E5%AD%98%E7%A2%8E%E7%89%87%202%20RESET,QUERY%20CACHE%EF%BC%9B%20#%E4%BB%8E%E6%9F%A5%E8%AF%A2%E7%BC%93%E5%AD%98%E4%B8%AD%E7%A7%BB%E9%99%A4%E6%89%80%E6%9C%89%E6%9F%A5%E8%AF%A2%203%20FLUSH%20TABLES%EF%BC%9B%20#%E5%85%B3%E9%97%AD%E6%89%80%E6%9C%89%E6%89%93%E5%BC%80%E7%9A%84%E8%A1%A8%EF%BC%8C%E5%90%8C%E6%97%B6%E8%AF%A5%E6%93%8D%E4%BD%9C%E4%BC%9A%E6%B8%85%E7%A9%BA%E6%9F%A5%E8%AF%A2%E7%BC%93%E5%AD%98%E4%B8%AD%E7%9A%84%E5%86%85%E5%AE%B9