第17章-MySQL查询缓存_mysql 开启查询缓存-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45086637/article/details/142514127

学习指引

MySQL查询缓存是非常重要的技术，查询缓存会存储一个SELECT查询的文本与被传送到客户端的相应结果。如果执行相同的一个SQL语句，MySQL数据库会将数据缓存起来以供下次直接使用，MySQL数据库以此优化查询缓存来提高缓存命中率。在MySQL服务器高负载的情况下，使用查询缓存可以减轻服务器的压力，减少服务器的I/O操作。通过本章的学习，能够初步了解MySQL的缓存机制，并且能够对MySQL的缓存机制进行有效的设置和使用。

重点内容

了解查询缓存的基本概念
熟悉查询缓存的工作原理
掌握查看MySQL缓存信息的方法
掌握配置查询缓存的方法
掌握监控和维护查询缓存的方法
掌握检查缓存命中的方法
掌握优化查询缓存的方法

17.1 MySQL的缓存机制

MySQL的缓存机制可以提高服务器性能，该功能是使用和管理MySQL的人员必须掌握的。本节主要学习MySQL的查询缓存（Query Cache）的基本概念。

17.1.1 查询缓存概述

MySQL服务器有一个重要特征是查询缓存。缓存机制简单地说就是缓存SQL语句和查询的结果，如果运行相同的SQL语句，服务器会直接从缓存中取到结果，而不需要再去解析和执行SQL语句。查询缓存会存储最新数据，而不会返回过期数据，当数据被修改后，在查询缓存中的任何相关数据均被清除。对于频繁更新的表，查询缓存是不适合的，而对于一些不经常改变数据且有大量相同SQL查询的表，查询缓存会提高很大的性能。

在MySQL的性能优化方面经常涉及缓冲区（Buffer）和缓存（Cache），MySQL通过在内存中建立缓冲区（Buffer）和缓存（Cache）来提升MySQL的性能。对于InnoDB数据库，MySQL采用缓冲池（Buffer Pool）的方式来缓存数据和索引；对于MyISAM数据库，MySQL采用缓存的方式来缓存数据和索引。

这些缓存能被所有的会话共享，一旦某个客户端建立了查询缓存，其他发送同样SQL语句的客户端也可以使用这些缓存。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。

查询必须是完全相同的（逐字节相同）才能够被认为是相同的，字符的大小写也被认为是不同的。另外，同样的查询字符串由于其他原因可能被认为是不同的。使用不同的数据库、不同的协议版本或者不同默认字符集的查询被认为是不同的查询并且分别进行缓存。

17.1.2 MySQL查询缓存的工作原理

需要特别注意的是，以下几种情况不会使用缓存数据。

1.大小写不同的查询语句

如果两条查询语句的大小写不一样，则它们的缓存不能共享。例如以下语句：

select price from fruit;
SELECT price FROM fruit;

由于上述两条语句的大小写不同，所以不会使用查询缓存。

2.子句不会被缓存

在MySQL中，SQL语句的子句不会被缓存。例如以下语句：

SELECT price FROM fruit WHERE did IN (SELECT did FROM fruit2);

在上述语句中，SELECT did FROM fruit2是该语句的子句，不会被缓存。

3.存储过程、触发器或者事件内部的一条语句

如果SQL语句是存储过程、触发器或者事件内部的一条语句，同样也不会被缓存。查询缓存也受到权限的影响，对于没有权限访问数据库中数据的用户，即使输入了同样的SQL语句，缓存中的数据也是无权访问的。由于InnoDB类型的表是支持事务操作的，当使用InnoDB类型的表时，包含在事务中的查询缓存也是会工作的。

4.包含视图的查询结果不会被缓存

从MySQL 5.6版本之后，包含在视图中的查询结果也是会被缓存的。

5.结果不确定的查询不会被缓存

查询缓存不会存储有不确定结果的查询，因此任何一个包含不确定函数（例如NOW()或CURRENT_DATE()）的查询不会被缓存。同样，CURRENT_USER()或CONNECTION_ID()这些由不同用户执行将会产生不同结果的查询也不会被缓存。事实上，查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表、MySQL数据库中的表、INFORMATION_SCHEMA数据库中的表、PERFORMANCE_SCHEMA数据库中的表或者任何一个有列级权限的表的查询。

17.1.3 查看MySQL的缓存信息

在MySQL数据库设置了查询缓存后，当服务器接收到一个和之前同样的查询时会从查询缓存中检索查询结果，而不是直接分析并检索查询。

在MySQL数据库中查看查询缓存功能是否已经开启的命令如下：

select @@query_cache_type;

开启查询缓存功能的方法如下：
设置query_cache_type为ON，命令如下：

set session query_cache_type=ON;

如果要禁用查询缓存功能，直接执行如下命令：

set session query_cache_type=OFF

接着查看系统变量have_query_cache是否为“YES”，该参数表示MySQL的查询缓存是否可用，查看命令如下：

show variables like 'have_query_cache';

如果SQL语句的结果被缓存，系统会修改MySQL的状态变量qcache_hits，并将其值增加1，可以运行语句来查看qcache_hits的值，命令如下：

show status like '%qcache_hits%';

从结果可以看出qcache_hits的值为0，表示查询缓存累计命中的数是0。

下面先输入以下语句：

SELECT * FROM fruit;

再次输入该语句：

SELECT * FROM fruit;

查询qcache_hits的值是否发生变化。

从结果可知，第二次查询后发现该缓存累计命中数已经发生了变化，此时查询出参数qcache_hits的值是1，表示查询直接从缓存中获取结果，不需要再去解析SQL语句。

17.2 MySQL查询缓存的配置和维护

本节主要讲解如何配置MySQL查询缓存的参数，以及如何维护和使用查询缓存。

17.2.1 配置查询缓存

设置系统变量query_cache_size的大小，命令如下：

set @@global.query_cache_size=999424;

查询系统变量query_cache_size设置后的大小，命令如下：

select @@global.query_cache_size;

如果需要将该参数永久修改，需要修改/etc/my.cnf配置文件，添加该参数的选项，添加如下：

[mysql]
port = 3306
query_cache_size = 1000000
...

如果查询结果很大，也可能缓存不了，需要设置query_cache_limit参数的值，该参数用来设置查询缓存的最大值。

查询该参数的值的命令如下：

select @@global.query_cache_limit;

设置query_cache_limit参数值的大小，命令如下：

set @@global.query_cache_limit = 2000000;

如果需要将该参数永久修改，需要修改/etc/my.cnf配置文件，添加该参数的选项，添加如下：

[mysql]
port = 3306
query_cache_size=1000000
query_cache_limit=2000000
...

通过以上步骤的设置，MySQL数据库已经成功开启查询缓存功能。

17.2.2监控和维护查询缓存

在日常工作中经常使用以下命令监控和维护查询缓存。

（1）flush query cache：该命令用于整理查询缓存，以便更好地利用查询缓存的内存，这个命令不会从缓存中移除任何查询结果。命令运行如下：

flush query cache;

（2）reset query cache：该命令用于移除查询缓存中所有的查询结果。命令运行如下：

reset query cache;

（3）show variables like ‘%query_cache%’：该命令可以监视查询缓存的使用状况，可以计算出缓存命中率。命令运行如下：

show variables like '%query_cache%';

下面具体介绍查询缓存功能相关参数的含义。

（1）have_query_cache：用来设置是否支持查询缓存区，“YES”表示支持查询缓存区。

（2）query_cache_limit：用来设置MySQL可以缓存的最大结果集，大于此值的结果集不会被缓存。

（3）query_cache_min_res_unit：用来设置分配内存块的最小体积。每次给查询缓存结果分配内存的大小，默认分配4096个字节。如果此值较小，那么会节省内存，但是这样会使系统频繁分配内存块。

（5）query_cache_type：用来设置是否启用查询缓存。如果设置为OFF，表示不进行缓存；如果设置为ON，表示除了SQL_NO_CACHE的查询以外，缓存所有的结果；如果设置为DEMAND，表示仅缓存SQL_CACHE的查询。

（6）query_cache_wlock_invalidate：用来设置是否允许在其他连接处于lock状态时使用缓存结果，默认是OFF，不会影响大部分应用。在默认情况下，一个查询中使用的表即使被LOCK TABLES命令锁住了，查询也能被缓存下来。用户可以通过设置该参数来关闭这个功能。

17.3 如何检查缓存命中

MySQL检查缓存命中的方式十分简单、快捷。缓存就是一个查找表（LookupTable），查找的键就是查询文本﹑当前数据库﹑客户端协议的版本，以及其他少数会影响实际查询结果的因素之哈希值。

下面主要学习MySQL数据库中缓存的管理技巧，以及如何合理配置MySQL数据库缓存，提高缓存命中率。

首先，在配置数据库客户端或者第三方工具与服务器连接时应该保证数据库客户端的字符集跟服务器的字符集保持一致。在实际工作中经常发现客户端配置的字符集和服务器字符集兼容没有完全一致，即使此时客户端没有出现乱码情况，查询数据可能就因为字符集不同的原因而没有被数据库缓存起来。

其次，为了提高数据库缓存的命中率，应该在客户端和服务器端采用一样的SQL语句。从数据库缓存的角度考虑，数据库查询的SQL语句是不区分大小写的，比如第一个查询语句采用大写语句，第二个查询语句采用小写语句，但对于缓存来讲，大小写不同的SQL语句会被当作不同的查询语句。

查询缓存只是发生在服务器第一次接收到SQL查询语句时，然后把查询结果缓存起来，对于查询中的子查询、视图查询和存储过程查询都不能缓存结果，对于预存储语句同样也不能使用缓存。

使用查询缓存有利也有弊，一方面，查询缓存可以使查询变得更加高效，改善了MySQL服务器的性能；另一方面，查询缓存本身也需要消耗系统I/O资源。所以说查询缓存增加了服务器额外的开销，主要体现在以下几个方面。

（1）MySQL服务器在进行查询之前首先会检测查询缓存是否存在相同的查询条目。

（2）MySQL服务器在进行查询操作时，如果缓存中没有相同的查询条目，会将查询的结果缓存到查询缓存，这个过程也需要消耗系统资源。

（3）如果数据库表发生增加操作，MySQL服务器查询缓存中相对应的查询结果将会无效，这时同样需要消耗系统资源。

除了注意以上问题可以提高查询缓存的命中率外，通过分区表也可以提高缓存的命中率。通常用户会遇到这样的问题，对于某张表某个时间段内的数据更新比较频繁，其他时间段查询和更新比较多，一旦数据表的数据执行更新操作，那么查询缓存中的信息将会清空，此时查询缓存的命中率不会很高。此时可以考虑采用分区表，把某个时间段的数据存放在一个单独的分区表中，这样可以提高服务器的查询缓存的命中率。