初探查询缓存

作者: Tod_2021 | 来源:发表于2022-07-28 23:03 被阅读0次

初探查询缓存
MySQL学习——查询缓存
Mysql - 一条sql语句是如何执行的
Yii2缓存组件之数据库查询缓存
查询缓存
MySQL数据库性能优化
5. 查询缓存和索引
Mysql 查询优化
(8)MySQL性能优化的最佳21条经验（未完待续）
WEB缓存初探

0.MySQL基本架构

MySQL有客户端，也有服务端，服务端主要分为Server层和存储引擎层，Server层包括五个部分，分别为连接器、查询缓存、分析器、优化器和执行器。基本架构图如下：

MySQL基本架构.png

1.查询缓存作用

查询过程

当执行SQL查询时，MySQL会先到查询缓存中查看之前执行过该SQL语句，在查询缓存中之前执行过的语句及其结果会以key-value形式存放，key对应于查询语句，value对应于查询结果。如果在查询缓存中存在该key，则直接返回value，不需要执行后面操作；如果不存在该key，则进行分析器、优化器、执行器以及调用存储引擎操作，最后将对应结果以key-value形式写入查询缓存，返回value。
存在弊端

虽然命中查询缓存的语句效率非常高，但是查询缓存的失效非常频繁，只要有对一个表的更新，这个表所有的查询缓存就回被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
解决方案

好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type (ini配置文件)设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。

而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
```
mysql> select SQL_CACHE * from table_name where ID=10；
```
需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

同时以上查询缓存语句对于数据库系统表(information_schema/mysql/performance_schema/sys)无效

2.查询缓存模式

查询缓存模式

mysql>SHOW VARIABLES LIKE 'query_cache_type';
Variable_name     Value   
----------------  --------
query_cache_type  OFF

MySQL5.5默认开启(ON)

MySQL5.7默认关闭(OFF)

MySQL8.0不存在该变量

my.ini文件设置查询缓存模式

# 查询缓存模式：
# 0 ： OFF 关闭 
# 1 ： ON 缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存 
# 2 ： DEMAND 只缓存select语句中通过SQL_CACHE指定需要缓存的查询
query_cache_type=2

查询是否命中

mysql>SHOW STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              2            # 表示有多少次命中缓存 
Qcache_inserts           1            # 表示多少次未命中缓存然后插入，意思是新来的SQL请求如果在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。 
Qcache_lowmem_prunes     0         
Qcache_not_cached        11         # 表示因为query_cache_type的设置而没有被缓存的查询数量。 
Qcache_queries_in_cache  1          # 当前缓存中缓存的查询数量。 
Qcache_total_blocks      4        # 当前缓存的block数量。

3.验证查询缓存

以下试验以MySQL5.5版本为基础

3.1查询缓存模式为ON

查看数据库版本

mysql>SELECT VERSION();
version()  
-----------
5.5.40

查看默认查询缓存模式

mysql>SHOW VARIABLES LIKE 'query_cache_type';
Variable_name     Value   
----------------  --------
query_cache_type  ON

查询命中情况

mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15711208  
Qcache_hits              0         
Qcache_inserts           0         
Qcache_lowmem_prunes     0         
Qcache_not_cached        2         
Qcache_queries_in_cache  0         
Qcache_total_blocks      1

执行三次计数查询语句以及两次查询命中情况

①
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         1       
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              0         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        3         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4         
②
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         1
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              1         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        4         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4           
③
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         1       
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              2         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        5         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4

分析：第一次查询Qcache_inserts的值1，表示1次未命中缓存然后插入，意思是新来的SQL请求如果在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。

最后一次查询Qcache_hits为2，表示有2次命中缓存

更新当前表

mysql>INSERT INTO shop.`shop_user`(PASSWORD,telephone,username)VALUE('123456', '15552424710','Tod');
1 queries executed, 1 success, 0 errors, 0 warnings
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15711208  
Qcache_hits              2         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        5         
Qcache_queries_in_cache  0         
Qcache_total_blocks      1

执行两次次基数查询并查询命中情况

①
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2      
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              2         
Qcache_inserts           2         
Qcache_lowmem_prunes     0         
Qcache_not_cached        6         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4     
②
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2      
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              3         
Qcache_inserts           2         
Qcache_lowmem_prunes     0         
Qcache_not_cached        7         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4

分析：如果存在对该表插入，其查询缓存立即失效

更新其他表数据

mysql>INSERT INTO shop.`shop_product`(pname,pprice,stock) VALUE('魅族',4000.0,5000);
1 queries executed, 1 success, 0 errors, 0 warnings
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              3         
Qcache_inserts           3         
Qcache_lowmem_prunes     0         
Qcache_not_cached        7         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4

执行一次基数查询并查询命中情况

mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2      
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              4         
Qcache_inserts           3         
Qcache_lowmem_prunes     0         
Qcache_not_cached        8         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4

分析：其他表的更新对于原查询不会有影响

结论

查询缓存的失效只针对当前表的更新操作
如果当前表涉及更新操作，再此查询会执行缓存插入操作

3.2查询缓存模式为DEMAND

在my.ini文件中添加

# 查询缓存模式：
# 0 ： OFF 关闭 
# 1 ： ON 缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存 
# 2 ： DEMAND 只缓存select语句中通过SQL_CACHE指定需要缓存的查询
query_cache_type=2

重启数据库服务
```
cmd>net restart mysql
```

查看查询缓存模式

mysql>SHOW VARIABLES LIKE 'query_cache_type';
Variable_name     Value   
----------------  --------
query_cache_type  DEMAND

查看命中情况

mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15711208  
Qcache_hits              0         
Qcache_inserts           0         
Qcache_lowmem_prunes     0         
Qcache_not_cached        0         
Qcache_queries_in_cache  0         
Qcache_total_blocks      1

执行两次计数查询语句以及两次查询命中情况

①
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2       
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15711208  
Qcache_hits              0         
Qcache_inserts           0         
Qcache_lowmem_prunes     0         
Qcache_not_cached        2         
Qcache_queries_in_cache  0         
Qcache_total_blocks      1        
②
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15711208  
Qcache_hits              0         
Qcache_inserts           0         
Qcache_lowmem_prunes     0         
Qcache_not_cached        4         
Qcache_queries_in_cache  0         
Qcache_total_blocks      1

执行两次显示指定执行查询缓存模式，执行一次普通计数查询

①
mysql>SELECT SQL_CACHE COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              0         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        5         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4    
②
mysql>SELECT SQL_CACHE COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              1         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        6         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4       
③
mysql>SELECT COUNT(*) FROM shop.`shop_user`;
count(*)  
----------
         2
mysql>SHOW GLOBAL STATUS LIKE '%Qcache%';
Variable_name            Value     
-----------------------  ----------
Qcache_free_blocks       1         
Qcache_free_memory       15709672  
Qcache_hits              1         
Qcache_inserts           1         
Qcache_lowmem_prunes     0         
Qcache_not_cached        8         
Qcache_queries_in_cache  1         
Qcache_total_blocks      4

分析：如果没有显式使用SQL_CACHE，即使是同一查询语句也不走查询缓存模式

结论

显式使用SQL_CACHE能够有效控制查询语句是否走查询缓存模式，能够人为实现控制。
不走查询缓存模式能够减少一次写缓存操作。

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

初探查询缓存
0.MySQL基本架构 MySQL有客户端，也有服务端，服务端主要分为Server层和存储引擎层，Server层包...
MySQL学习——查询缓存
MySQL查询缓存简介 MySQL查询缓存是MySQL将查询返回的完整结果保存在缓存中。当查询命中该缓存，MySQ...
Mysql - 一条sql语句是如何执行的
1. 连接器长连接保持通信 2. 查询缓存每次查询都会缓存查询结果，相同语句再次查询时直接返回缓存结果，缓存结...
Yii2缓存组件之数据库查询缓存
查询缓存查询缓存是一个建立在数据缓存之上的特殊缓存特性。它用于缓存数据库查询的结果。（PS需配置好缓存配置）查...
查询缓存
查询缓存原理 mysql查询缓存保存完整的查询结果，当查询命中缓存时，立刻返回结果，跳过了解析，优化和执行阶段。 ...
MySQL数据库性能优化
MySQL架构查询缓存查询缓存（Query Cache）原理：缓存SELECT操作或预处理查询的结果集和SQ...
5. 查询缓存和索引
1. 查询缓存 查询缓存（ Query Cache ）原理缓存SELECT操作或预处理查询的结果集和SQL语句，...
Mysql 查询优化
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。缓存在第一次查询后，MySQL便将查询语句以及...
(8)MySQL性能优化的最佳21条经验（未完待续）
1.为查询缓存优化你的查询某些查询语句会让MySQL不使用缓存。 MySQL的查询缓存对这个函数不起作用。像...
WEB缓存初探
WEB缓存初探概念理解缓存——缓存就是数据交换的缓冲区（称作Cache）缓存的作用说白了就是用来就近获取东...