《MySQL中级系列教程-第三章-MySQL查询缓存优化》

逃离我推掉我的手 2023-01-02 11:28 167阅读 0赞

# 《MySQL中级系列教程-第三章-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

![在这里插入图片描述][20200529155504871.png]  
重启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%";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]

*  **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%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]

*  **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%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]

> 发现缓存写入次数+1

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

select * from goods;
    
    show global status like '%Qcache%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]

> 发现缓存命中次数+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%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]

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

*  我们做个测试：

-- 即使查询不到结果集,也会写入缓存
    select * from goods where id=10;
    
    -- 即使查询不到结果集也会命中缓存
    select * from goods where id=10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]

> 发现即使有的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%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 6]

## 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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 7]

*  执行如下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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 8]

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

-- 写入缓存
    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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 9]  
发现命中缓存确实比没有命中缓存查询效率高多了。

## 1.5 缓存的利用率 ##

计算命中率

缓存命中率 = `Qcache_hits`（缓存命中次  
数） / `Com_select`（查询次数）

计算写入率

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

*  示例：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 10]

> 命中率：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` 命令可以有效的整理缓存碎片，来释放碎片占用的空间，但需要注意的是这个命令在整理缓存期间，会导致其他连接无法使用查询缓存。

# 二、缓存这么好，为什么很少用他？ #

既然缓存这么好，为什么咱们公司实际开发不使用它？而且官网上已经明确说明，在MySQL8.0及以上版本，MySQL的缓存功能已经被删除了（其实就光这一点，很多人就已经失去学习他的动力了）。  
![在这里插入图片描述][20200601154649968.png]

## 2.1 缓存失效很频繁 ##

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。**因此读写非常频繁的业务场景，缓存开启还不如关闭效率高。**

我们来做一下实际测试。

**待会测试的时候速度会比较慢，我把数据量切换为100W测试（原来是300W）。**

start transaction;
    
    delete from userinfo where id>1000000;
    
    commit;

后面我们需要把查询结果集到缓存中（这个结果集非常大，100W数据），因此我们需要把缓存的参数调一下，修改`my.ini`配置文件，添加如下配置：

query_cache_limit=250M
    query_cache_min_res_unit=512KB
    query_cache_size=800M
    query_cache_type=1

> query\_cache\_limit：修改缓存单个最大结果集上限为250M  
> query\_cache\_min\_res\_unit：修改分配内存块时的最小单位为512KB（不宜太大，容易造成缓存碎片）  
> query\_cache\_size：修改查询缓存最大占用内存为800MB  
> query\_cache\_type：开启缓存  
> ![在这里插入图片描述][20200601165321857.png]

*  重启MySQL服务器：

systemctl restart mysqld

*  查询缓存相关参数：

show variables like "%query_cache%";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 11]

> 查询出来的值是以字节显示的，你们可以算一下是否和上面设置的值能够对的上。

*  **开启缓存测试：**

-- 写入缓存
    select * from userinfo;								-- 2.66s
    
    -- 清空缓存
    update userinfo set username='1' where id=1;		-- 0.01s
    
    -- 写入缓存
    select * from userinfo;								-- 2.15s
    
    -- 清空缓存
    update userinfo set username='2' where id=1;  		-- 0.01s
    	
    -- 写入缓存
    select * from userinfo;								-- 2.22s

*  **关闭缓存测试（将query\_cache\_type=0，并且重启服务器）：**

select * from userinfo;								-- 1.90s
    
    -- 清空缓存
    update userinfo set username='1' where id=1;		-- 0.01s
    
    -- 写入缓存
    select * from userinfo;								-- 1.51s
    
    -- 清空缓存
    update userinfo set username='2' where id=1;  		-- 0.01s
    	
    -- 写入缓存
    select * from userinfo;								-- 1.72s

> 发现开启缓存第一次查询明显比关闭缓存查询效率低多了，因为**当缓存开启后，MySQL需要将本次查询的结果集全部放入缓存中，这个过程是需要时间的**，如果后期能用上还好，如果用不上，或者创建了缓存里面又给情况了，那么无疑是在做无用功。

## 2.2 缓存会占用我们的内存 ##

一般来说，我们的数据库都是单独部署在一台服务器中，我们应该尽可能的减少这台服务器的压力，必要时还要进行扩容（搭建集群、读写分离、数据分片等），这些操作都是来提高我们单台MySQL的处理能力的，而不是把缓存和数据库放在一起，增加MySQL服务器的压力。**如果真的需要缓存来提高响应速度，那么应该把缓存和数据库独立分开部署。**

## 2.3 市面上已经有非常成熟的缓存中间件 ##

在大多数情况下，我们都是采用市面上比较成熟的软件来进行缓存，如redis、memcache等，这些软件不管是性能、功能上都比MySQL缓存要强大的多，而且利于我们进行性能扩容（如搭建缓存集群）操作起来也非常方便。

# 三、缓存并发性能测试 #

我们在上面的测试结果可以看到，如果需要缓存比较大的数据情况下，第一次查询的效率会稍微变慢（因为需要放到查询缓存中）。**但是各位请记住，是缓存大数据量的情况下！！！！在开发中，一张表存储几百万数据，而且是一股脑全部查询出来，再而且还需要一下子全部放入缓存中！！！！**

好吧，这样的场景我没遇到过。

我们在开发中，一般的查询结果都是根据条件赛选出来的数据，一般不会太大。如果是这样的情况，**使用MySQL的查询缓存是没有问题的，就算是频繁修改，性能上也不会带来太大的折扣。**

我们做个测试，把表的数据量降低到10W（一般开发中，根据某些条件筛选出10W的数据，然后全部放入到缓存的场景也挺少吧！即使有非常多的这样的需求，那也会把缓存独立出来（redis））

start transcation;
    
    delete from userinfo where id>100000;
    
    commit;

*  开启缓存测试：

select * from userinfo;								-- 0.14
    
    update userinfo set username='1' where id=1;		
    
    select * from userinfo;								-- 0.15
    
    update userinfo set username='2' where id=1;
    
    select * from userinfo;								-- 0.15

*  关闭缓存测试：

select * from userinfo;				-- 0.14s
    
    select * from userinfo;				-- 0.14s
    
    select * from userinfo;				-- 0.15s

> 可以看出来在缓存10W数据的情况下，开启缓存/关闭缓存 第一次查询的效率几乎平等

我们上面是针对于一个用户操作，如果是多个用户同时来访问（并发），效率又当如何呢？

这里要使用到一个MySQL自带的并发压力测试工具：`mysqlsalp`（如果没用过也没关系，你直接看我操作，so easy的）

输入如下命令：

mysqlslap -uroot -padmin --concurrency=10 --create-schema=test --query=/root/query.sql

*  `-u`：你MySQL的用户名
 *  `-p`：你MySQL的密码
 *  `--concurrency`：模拟的并发数
 *  `--create-schema`：测试的数据库
 *  `--query`：执行的SQL语句脚本

测试的脚本内容：

-- 写入缓存
    select * from userinfo;
    
    -- 清空缓存
    update userinfo set username='1' where id=1; 
    
    -- 写入缓存
    select * from userinfo;
    
    -- 清空缓存
    update userinfo set username='2' where id=1; 
    
    -- 写入缓存
    select * from userinfo

分别在开启缓存和关闭缓存环境下测试：

*  开启缓存，模拟10个并发一起请求（如果你想搞多个也行，时间会比较久，我不想等）：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 12]
 *  关闭缓存，模拟10个并发一起请求：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 13]

> 我们发现，在10个并发量一起请求的时候，开启缓存/关闭缓存 第一次查询的效率也是几乎平等（注意看执行的SQL语句脚本）

就像我刚刚说的，其实在开发中，**表里面有大量的数据是很正常，查询的SQL语句需要筛选大量的数据也很正常，但是经过SQL语句条件筛选出来的结果集数据量还是很大的情况并不多。****因此这样的情况下是可以使用查询缓存的。**当然咯，前提是你们公司没有搭建Redis或者其他的缓存服务器，如果有搭建，还是建议使用Redis。

因为查询缓存正是把哪些通过SQL语句条件筛选过后的数据存入结果集，因此数据量不会太大，超过数10W的场景更是少。

但是会出现一种情况，**通过SQL语句筛选出来的结果集少，会存入缓存，但是并发量非常高，加上又经常修改。**

我们针对这种情况做一下测试：

*  我们把数据量降到1000（**假设某SQL语句查询出来的结果集为1000条记录，需要存入缓存，而且还是经常修改**）：

truncate userinfo;
    
    start transcation;
    
    call test_insert(1000);
    
    commit;

待会开启需要开启1000并发去连接MySQL服务器，**MySQL默认能够处理的最大连接数为151**，因此我们需要修改MySQL最大能够处理的连接数，在配置文件中添加：

max_connections=1024

*  修改前：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 14]
 *  修改后：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 15]
 *  关闭缓存，测试1000并发：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 16]
 *  开启缓存测试：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 17]

> 测试的值波动有点大，但整体看起来也是差不多的，我们计算平均值。  
>   
>   
> 关闭缓存：（35.053+26.813+23.016+20.931+33.739）/5=27.9104‬  
> 开启缓存：（16.155+22.617+35.167+33.971+20.484）/5=25.6788‬  
>   
>   
> 执行SQL脚本里面都是创建一次缓存然后里面就修改了数据（清空了缓存），因此理论上来说关闭缓存要比开启缓存查询速度要高一些的，因为关闭缓存的情况下，不需要把查询出来的结果集放入查询缓存。当然了，**这是测试值，实际生产中肯定会有偏差，大家私下也可以多测试一下**。  
>   
>   
> 有人会说我这里是不是搞反了（其实我在下面测试了N多次，得出的结果是关闭缓存和开启缓存的效率差不多，各有胜负）

从上面可以反映出来，在处理小的结果集存入查询缓存的时间是可以忽略不计的，即使是**小规模的高并发**情况下。

> 再说明一点：我这里的并发量是1000，如果是超高并发量（过万、上十万、百万），我觉得此时根本没有必要使用MySQL的查询缓存了。具体为什么，参考第三章节的3.2和3.3。

# 四、总结 #

MySQL的查询缓存缓存的是SQL语句筛选过后的结果集，而筛选过后的结果集通常来说不会太大，**此时是可以存入缓存来提高我们的查询效率的**，**如果查询的结果集太大，你完全可以选择不存入查询缓存**（使用`SQL_NO_CACHE`），**因为查询缓存真的很容易失效！**

但是如果你们老板明确说明，需要这100W数据全部存入缓存，而且这100W数据中有些需要频繁修改怎么办？？

**答：存入redis吧！这个时候查询缓存真的不适合你！**

在某些场景下，查询缓存我个人认为是可以使用的。

*  场景举例：

select * from xxx age > 18 and age <25;
    
    -- 在大量数据中，筛选出几条数据（这样的SQL语句最适合存入查询缓存）
    select * from xxx hobby in (1,2,3,4);
    
    select * from xxx limit 1000000,100;
    
    -- 在大量数据中，做分组操作（这样的SQL语句最适合存入查询缓存）
    select avg(age) from xxx group dept_id;

> 尽量缓存结果集不会太大的SQL语句（建立缓存时间短，就算缓存失效对我影响也不大），但是表中数据量大（表中数据量大，意味着查询速度慢），这样的SQL语句下次查询缓存速度上就能得到明显的提升。

什么情况下不适合使用查询缓存？

*  场景举例：

-- 整表缓存
    select SQL_NO_CACHE * from xxx;
    
    -- 多表缓存
    select SQL_NO_CACHE * from xxx x1 inner join xxx x2 on x1.x=x2.x;
    
    -- 大范围缓存
    select SQL_NO_CACHE * from xxx where age>18;

*  **为什么很多公司不用查询缓存？**

在这里发表一下我个人的看法：我觉得更偏向与咱们第三章节讨论的后面两点。第一点虽然说有点影响，但是不大。而且我们知道，**查询缓存缓存的是SQL语句筛选过后的结果集，并不是缓存此次SQL所检索的所有数据**，我们在开发中，一般都是根据条件筛选，得到的结果集一般不会很大。如果你是做数据分析的那就另说了。我只是说一般场景下。

如果你迫切需要缓存给你带来的强大功能，**而不是有或没有都行**（这才是互联网公司的需求），那么建议你使用市面上专门的缓存中间件吧（Redis），MySQL的查询缓存不适合你。

[20200529155504871.png]: /images/20221119/b8ccd97bc5664783bbb7dde577a54de6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: /images/20221119/6d3d09e993f6414c9d20f9c43adf17a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: /images/20221119/f0361b25693f49078368eab291b82f67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: /images/20221119/e06cdeecabb9432e86e086b9c14cf2fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]: /images/20221119/ecf81d71de854397aeea19881e655671.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]: /images/20221119/e387200015c548feb727f920916e829c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]: /images/20221119/2cde647fb0344941b99b6f01a668af38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 6]: /images/20221119/b3bc7e7cd10c441b8ec2592e565f5ccf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 7]: /images/20221119/fab54366fab242f0a48d117447ac697e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 8]: /images/20221119/44af065bf69c4039b06937ed43ce6471.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 9]: /images/20221119/c19c70eac82f4c4d8a92b24fdd4238d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 10]: /images/20221119/fb3394e0854f48f58e2cf0ea08551e07.png
[20200601154649968.png]: /images/20221119/4c203a3c5a354c5c8d827326ad9ec655.png
[20200601165321857.png]: /images/20221119/7b0d5dd162c04580879bd56c10dfb3df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 11]: /images/20221119/9950675cabd3499db6da65eaf34de7eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 12]: /images/20221119/a700d1a61e594b108e68408fd0b31560.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 13]: /images/20221119/3075d657dc094eb6939f4be67a6eaa73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 14]: /images/20221119/cdd5eca0a7cd49f79d4f0b6d0214b9b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 15]: /images/20221119/48765e4be37f4a509a03d6515e2526c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 16]: /images/20221119/a970b9143624483cab2939a0e62a6ae1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 17]: /images/20221119/127a36a3edfe4c838743b4199513418e.png