mysql缓存命中

学习MySQL缓存之前，我们首先得了解一下MySQL的一个架构，分析完架构之后，我们开始深入了解缓存。

一、 MySQL架构

MySQL 数据库是单进程多线程的架构，和 SQL Server 类似，和 Oracle 不一样，Oracle 是多进程架构。下面我来看一下MySQL具体的架构图：

MySQL的架构一共分为了连接层、服务层、存储引擎层、系统文件层，每一层都有各自的操作。下面我们来看一下每一层中具体做了哪些操作。

1.1 连接层

连接层主要负责连接管理、身份认证等，你输入的mysql -uxxx -pxxx之后，来到MySQL的第一件事就是校验你的身份，权限等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

1.2 服务层

认证成功过后，接下来就来到服务层了，服务层是 MySQL 的核心，MySQL 的核心服务层都在这一层，包括你执行的任何SQL语句，普通的DDL、DML、DQL、存储过程、视图、触发器等都需要经过这一层的一些操作，最终才可以被MySQL识别。主要经过有SQL解析器、SQL优化器等，最终还会经过查询缓存，总之，所有跨存储引擎的功能都在这一层实现。

1.3 存储引擎层

我们知道MySQL在存储引擎这方面和Oracle和SQL Server不一样，MySQL提供了可插拔式的存储引擎，即"插上什么存储引擎，就有什么功能"，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同。不同的存储引擎将直接决定了数据存储到磁盘的方式。

1.4 系统文件层

数据存储层主要是将数据存储在运行与设备的文件系统之上，并完成与存储引擎的交互。

1.5 SQL语句的执行流程

1）用户发送SQL语句来到MySQL服务端

2）首先查询缓存，如果缓存有数据直接响应，如果没有进行下一步

3）解析器进行解析SQL语句、预处理、经过优化器优化SQL语句等一系列操作，调用存储引擎

4）存储引擎去磁盘上读取数据，最终将数据以MySQL的方式（即行和表的方式）响应给客户端，并且存入查询缓存一份。

二、MySQL缓存机制

MySQL缓存机制就是缓存SQL文本及缓存结果，用KV形式保存再服务器内存中，如果运行相同的sql,服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行sql。 如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，显然，者对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能。

1.1 缓存命中条件

缓存是有命中条件的，并不是所有的SQL语句都会进入缓存查找。

缓存存在一个hash表中，通过查询SQL，查询数据库，客户端协议等作为key，在判断命中前，MySQL不会解析SQL，而是使用SQL去查询缓存，SQL上的任何字符的不同，如空格，注释，都会导致缓存不命中。如果查询有不确定的数据current_date()，那么查询完成后结果者不会被缓存，包含不确定的数的是不会放置到缓存中。

1.3 缓存工作流程

1）服务器接收SQL，以SQL和一些其他条件为key查找缓存。

2）如果找到了缓存，则直接返回缓存。

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

4）执行完SQL查询结果以后，将SQL查询结果放入查询缓存。

1.3 缓存说明

1.3.1 开启查询缓存

MySQL默认是将查询缓存关闭的，我们需要在配置文件中打开。

查询当前数据库缓存是否开启：

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                |
+--------------------+
1 row in set, 1 warning (0.00 sec)

修改配置文件：linux的是/etc/my.conf，Windows的是C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

在mysqld组下面增加：

query_cache_type=1

重启MySQL服务：

systemctl restart mysqld

再次查看：

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| on                 |
+--------------------+
1 row in set, 1 warning (0.00 sec)

1.3.2 缓存参数

• 输入如下命令查询缓存相关参数

show variables like "%query_cache%";

have_query_cache：当前数据库版本是否支持缓存 NO：不支持 YES：支持

query_cache_limit：查询缓存能缓存单个SQL语句产生的结果集的最大容量大小，超出此大小的SQL语句结果集则不放入缓存。

query_cache_min_res_unit：分配内存块时的最小单位大小

MySQL并不是一下子分配query_cache_size大小的内存作为缓存，而且将整个查询缓存的大小分成了若干个内存块，query_cache_min_res_unit正是决定这些块的大小，需要注意的是，即使缓存的数据没有达到一个缓存块大小也需要占用一个缓存块大小的空间。如果超出单个缓存块，那么需要申请多个缓存块，当查询完发现有缓存块内存多余，则会将多余的缓存块内存释放，造成缓存碎片。

query_cache_size：缓存使用的总内存空间大小，单位是字节，这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同

query_cache_type：是否打开缓存

OFF：关闭缓存（默认值）

ON：开启缓存

DEMAND：只有明确写了SQL_CACHE的查询才会写入缓存

select SQL_CACHE * from t_goods; -- 将查询结果放入缓存（前提缓存是开启的）

select SQL_NO_CACHE * from t_goods; -- 不缓存查询结果

query_cache_wlock_invalidate：如果某个数据表被锁住，是否仍然从缓存中返回数据，默认是OFF，表示仍然可以返回。

1.3.3 全局缓存状态

输入如下命令查询全局缓存状态：

show global status like '%Qcache%';

• Qcache_free_blocks：缓存池中空闲块的个数
• Qcache_free_memory：缓存中空闲内存量
• Qcache_hits：缓存命中次数
• Qcache_inserts：缓存写入次数
• Qcache_lowmen_prunes：因内存不足删除缓存次数
• Qcache_not_cached：查询未被缓存次数，例如查询结果超出缓存块大小，查询中包含可变函数等，或者未查询到数据的行、或者SQL语句中使用了SQL_NO_CACHE等。
• Qcache_queries_in_cache：当前缓存中缓存的SQL数量
• Qcache_total_blocks：缓存总block数

1.4 缓存测试

了解完了缓存的一系列参数之后，我们开始手动测试一下缓存的命中、写入，以及大数据量下缓存的效率如何。

1.4.1 缓存命中测试

• 创建数据库：

create database test;
use test;

• 创建一张测试表：

CREATE TABLE `goods`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

• 插入测试数据：

INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (1, '华为4G全面屏游戏手机', '华为手机', 5299.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (2, '神舟战神游戏本', '神舟笔记本', 4599.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (3, '小米5G全面屏手机', '小米手机', 2899.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (4, '小米4G游戏全面屏拍照手机', '小米手机', 1899.00);

• 查询当前缓存使用情况：

-- 首先执行一次查询语句
select * from goods;

-- 查看是否有写入到缓存
show global status like '%Qcache%';

发现缓存写入次数+1

我们再次执行SQL语句，查看缓存使用情况：

select*from goods;
showglobalstatuslike'%Qcache%';

发现缓存命中次数+1

我们执行多次SQL语句，查看缓存插入和命中情况：

-- 写入缓存+1
select * from goods g;				-- 注意仔细看SQL语句哦（取了个别名）

-- 写入缓存+1
select * from goods where id=1;

-- 写入缓存+1
select * from goods where id=2;

-- 写入缓存+1
select * from goods g where id=1;

-- 命中缓存+1
select * from goods g;

-- 命中缓存+1
select * from goods where id=1;

-- 命中缓存+1
select * from goods where id=2;

我们先计算一遍：

缓存写入数（Qcache_inserts）为1（原来的一次）+4=5

缓存命中数（Qcache_hits）为1（原来的一次）+3=4

查看缓存写入和命中情况：

show global status like '%Qcache%';

这里要注意一点，即使有的SQL查询不出来结果，也是会写入缓存的

• 我们做个测试：

发现即使有的SQL没有查询到结果集，也会写入缓存，并且再次查询也会命中缓存

1.4.2 缓存清空测试

我们上提到过，对表的任何DML操作都会导致缓存情况，包括insert,update,delete,truncate,alter table,drop table等。

为了方便测试，我重启MySQL服务器（缓存信息全部清空）：

systemctl restart mysqld

执行SQL语句观察缓存命中变化

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from goods where id=1;		# 写入缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |			# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> select * from goods where id=1;			# 命中缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 5399.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |					# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)
mysql> update goods set price=8999 where id=1;			# 清空缓存
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |				# 注意:这里显示的是你当前写入了多少次缓存
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 0       |				# 当前缓存了0条SQL
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.01 sec)

mysql> select * from goods where id=1;				# 写入缓存
+----+-------------------------------+--------------+---------+
| id | title                         | name         | price   |
+----+-------------------------------+--------------+---------+
|  1 | 华为4G全面屏游戏手机          | 华为手机     | 8999.00 |
+----+-------------------------------+--------------+---------+
1 row in set (0.00 sec)

mysql> show global status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1030296 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 2       |					# 发现缓存写入次数增加
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2       |
| Qcache_queries_in_cache | 1       |					# 当前缓存了一条SQL
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)
mysql>

通过观察Qcache_queries_in_cache参数可以发现，执行完update语句之后，有关于这张表的缓存全部清空，当再次执行SQL语句的时候，会重新写入缓存。

对MySQL表的任意DML操作都会导致有关于这张表的所有缓存全部清空。

1.4.3 使用SQL Hint选择缓存

我们知道MySQL的查询缓存一旦开启，会将本次SQL语句的结果集全部放入缓存中，这样其实是非常不友好的，因为我们知道，对于表的任何DML操作都会导致这张表的缓存全部清空。因此我们可以指定哪些SQL语句存入缓存，哪些不存。

• SQL_CACHE：将此次SQL语句的结果集存入缓存（前提是当前MySQL服务器时开启缓存的）
• SQL_NO_CACHE：此次SQL语句的结果集不存入缓存

执行如下SQL语句，分析缓存执行情况：

-- 缓存写入次数0
show global status like '%Qcache%';

-- 存入缓存
select * from goods;

-- 缓存写入次数1
show global status like '%Qcache%';

-- 不存入缓存
select SQL_NO_CACHE * from goods g;		-- 注意:我取了个别名

-- 缓存写入次数还是1
show global status like '%Qcache%';

1.5 大数据量缓存性能测试

我们已经明白缓存何时写入、何时清空，何时命中，接下来我们插入300W数据，来执行SQL语句，体验缓存给我们带来性能上的提升。

• 创建测试表：

CREATE TABLE `userinfo` (
  `id` int(10) NOT NULL COMMENT '用户id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
  `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男   ‘1’-女',
  `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
  `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
  `look` int(10) NULL DEFAULT NULL COMMENT '查看数',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

• 编写存储过程

create procedure test_insert(count int)
begin
		declare i int default 1;
		while i<=count do 
			INSERT INTO userinfo values(
			i,								-- id
			uuid(),							-- username
			CEILING(RAND()*90+10),			-- age
			FLOOR(RAND()*100000000000),		-- phone
			round(FORMAT(rand(),1)),		-- gender
			uuid(),							-- desc
			now(),							-- register_time
			now(),							-- login_time
			uuid(),							-- pic
			CEILING(RAND()*90+10)			-- look
			);
			set i=i+1;
		end while;
end;

• 执行脚本，批量插入300W数据：

-- 关闭唯一性校验,提高批量插入速度
set unique_checks=0;

-- 控制在一个事务,避免频繁开启/提交事务
start transaction;

call test_insert(3000000);			-- 模拟500W的数据

commit;

执行如下SQL测试缓存效率：

-- 第一次查询,将结果存入缓存
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 走缓存,效率快
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 对表进行修改,关于这张表的缓存全部清空
update userinfo set username='1' where id=1;

-- 再次查询,发现效率低,但又存入缓存了
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

-- 再次查询,走缓存,效率高
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';

可以自己再进行多次测试：

-- 写入缓存
select * from userinfo where age=1;

-- 命中缓存
select * from userinfo where age=1;

-- 写入缓存
select * from userinfo where phone='1';

-- 命中缓存
select * from userinfo where phone='1';

-- 命中缓存
select * from userinfo where phone='1';

-- 写入缓存
select * from userinfo where look=1;

-- 命中缓存
select * from userinfo where look=1;

-- 命中缓存
select * from userinfo where look=1;

发现命中缓存确实比没有命中缓存查询效率高多了。

1.5 缓存的利用率

计算命中率

缓存命中率 = Qcache_hits（缓存命中次

数） / Com_select（查询次数）

计算写入率

缓存写入率 = Qcache_inserts（缓存写入次数） / Com_select（查询次数）

示例：

show status like 'com_select';   		-- 显示当前会话的查询次数
show status like '%Qcache%';			-- 显示当前会话缓存使用情况

命中率：9 / 20 = 0.27 = 45%

写入率：13 / 20 = 0.18 = 65%

1.6 缓存失败情况

1、缓存碎片、内存不足、数据修改都会造缓存失败。如果配置了足够的缓存空间，而且query_cache_min_res_unit的大小也设置的合理。那么缓存失效应该主要是数据修改导致的。可以通过Qcache_lowmen_prunes参数来查看有多少次失效是由于内存不足导致的。

2、当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态

3、在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

1.7 减少缓存碎片

1）设置合适的query_cache_min_res_unit值

我们在分析缓存参的时候知道，MySQL并不是一下子分配query_cache_size内存作为缓存，而是将内存分为若干个query_cache_min_res_unit小内存，即使本次缓存没有达到一个缓存块大小也需要占用一个缓存块大小。但query_cache_min_res_unit也不能设置太小，设置太小会造成MySQL不断的分配很多个内存块来缓存本次SQL语句的结果集。

2）使用 FLUSH QUERY CACHE 命令整理缓存碎片

使用 FLUSH QUERY CACHE 命令可以有效的整理缓存碎片，来释放碎片占用的空间，但需要注意的是这个命令在整理缓存期间，会导致其他连接无法使用查询缓存。