【十二】MySQL查询缓存

一、缓存条件，原理

MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集，MySql在实现Query Cache的具体技术细节上类似典型的KV存储，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。

当客户端发起SQL查询时，Query Cache的查找逻辑是，先对SQL进行相应的权限验证，接着就通过Query Cache来查找结果（注意必须是完全相同，即使多一个空格或者大小写不 同都认为不同，即使完全相同的SQL，如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存）。

它不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生同任何存储引擎的交互，减少了大量的磁盘IO和CPU运 算，所以有时候效率非常高。

MySQL执行一个查询过程

（1） MySQL客户端/服务器通信协议

MySQL客户端和服务器之的通信协议是“半双工”的，这就意味着，在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。所以我们无法也无须将一个消息切成小块独立来发送。

优缺点：
这种协议让MySQL通信简单快速,但是也从很多地方限制了 MySQL。
一个明显的限制是,这意味着没法进行流量控制。一旦一端开始发送消息,另一端要接收完整个消息才能响应它。这就像采回抛球的游戏:在任何时刻,只有一个人能控制球,而且只有控制球的人才能将球抛回去(发送消息)。

（2）.连接器

MySQL客户端和服务端建立连接,获取当前连接用户的权限

（3）查询缓存

在解析一个查询语句之前,如果查询缓存是打开的，MySQL会检查这个缓存，是否命中查询缓存中的数据。这个检查是通过一个大小写敏感的哈希查找实现的。
查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果,这种情况下查询
就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前 MySQL会检查一次用
户权限。这仍然是无须解析查询SQL语句的,因为在查询缓存中已经存放了当前查询需
要访问的表信息。如果权限没有问题, MySQL会跳过所有其他阶段,直接从缓存中拿
到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行.

ps:注意在 mysql8 后已经没有查询缓存这个功能了，因为这个缓存非常容易被清空掉，命中率比较低。

(4).分析器

既然没有查到缓存，就需要开始执行 sql 语句了，在执行之前肯定需要先对 sql 语句进行解析。
分析器主要对 sql 语句进行语法和语义分析，检查单词是否拼写错误，还有检查要查询的表或字段是否存在

(5)查询优化

查询的生命周期的下一步是将一个SQL转换成一个执行计划, MySQL再依照这个执行
计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQ执行计划。
这个过程中任何错误(例如语法错误)都可能终止查询。

1.缓存命中条件

缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key，该该查询语句的结果作为value

1.1 在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.

1.2 如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的

1.3 即使完全相同的SQL，如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存。

1.4 使用 FLUSH QUERY CACHE 命令整理碎片.这个命令在整理缓存期间,会导致其他连接无法使用查询缓存。

InnoDB与查询缓存

Innodb会对每个表设置一个事务计数器,里面存储当前最大的事务ID.

当一个事务提交时,InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器.

1.5 只有比这个最大ID大的事务能使用查询缓存,其他比这个ID小的事务则不能使用查询缓存.

1.6 在InnoDB中,所有有加锁操作的事务都不使用任何查询缓存

2.工作流程

1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)

2. 如果找到了缓存,则直接返回缓存(性能提升)

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

4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)

二、SQL命令查询缓存相关信息

查看当前查询缓存相关参数状态

SHOW VARIABLES LIKE '%query_cache%';

输出结果类似下面： 

输出说明： 

have_query_cache ： 查询缓存是否可用

query_cache_limit：能够缓存的最大查询结果，对有较大结果的查询语句，建议在select中使用SQL_NO_CACHE

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

query_cache_size ：查询缓存总共可用的内存空间，单位是字节，必须是1024整数倍`

设置query_cache_size的值： 

SET GLOBAL query_cache_size = 134217728;

注意上面的值如果设得太小不会生效。比如我用下面的SQL设置query_cache_size大小： 

SET GLOBAL query_cache_size = 4000;

query_cache_type ：是否打开缓存

值含义说明：

1) OFF: 关闭

2) ON: 总是打开

3) DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存

如果query_cache_type为ON而又不想利用查询缓存中的数据，可以用下面的SQL： 

SELECT SQL_NO_CACHE * FROM my_table WHERE condition;

开启查询缓存 

set session query_cache_type = ON; --开启查询缓存 

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

 

查询缓存性能监控

SHOW STATUS LIKE 'Qcache%'

输出说明： 

Qcache_free_blocks：查询缓存中的空闲内存块

Qcache_free_memory：查询缓存的空闲内存数量

Qcache_hits：查询缓存命中数量

Qcache_inserts：添加到查询缓存的查询的数量(不是表示没被缓存而进行的读，而是缓存失效而进行的读)

Qcache_lowmen_prunes：因内存太低，从缓存查询中删除的查询的数量

Qcache_not_chached：未缓存查询的数量(未被缓存、因为querey_cache_type设置没被缓存)

Qcache_queries_in_cache：缓存查询中注册的查询的数量

Qcache_total_blocks：查询缓存中的内存块总数

SELECT查询总数

Com_select+Qcache_hits+ 解析错误的查询数(queries with errors found by parser)

其中，Com_select表示未命中缓存数，Qcache_hits表示缓存命中数

Com_select计算公式：

Qcache_inserts+Qcache_not_cached+权限检查错误数(queries with errors found during the column-privileges check)

索引监控

SHOW STATUS LIKE 'handler_read%';

 输出说明：

Handler_read_first

The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed

索引中的第一项(the first entry in an index)被读取的次数，如果该值很高，那表明服务器正在执行很多全索引扫描，例如 SELECT col1 FROM foo, 假设col1上建立了索引

Handler_read_key

The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

基于某个键读取一行的请求次数。如果该值很高，那很好的说明了，对于执行的请求，表采用了适当的索引。

Handler_read_next

The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.

根据键顺序，读取下一行的请求次数。如果你正在查询一个带一系列约束的索引列或者正在执行索引扫描时，该值会增加

Handler_read_prev

The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC

根据键的顺序，请求读取前一行的次数。该读取方法主要用于优化 ORDER BY ... DESC

Handler_read_rnd

The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.

在固定位置读取一行的请求次数。该值如果很高，那么说明正在执行许多要求对结果集排序的查询。可能在执行有许多要求全表扫描的查询，或没使用适合键的联合查询。

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

读取数据文件中下一行的请求次数。该值很高，表明正在执行很多全表扫描。通常表明表没使用适当的索引或者查询请求没利用现成的索引。

三、缓存数据失效时机 

在表的结构或数据发生改变时，查询缓存中的数据不再有效。

有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。

所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

可以使用下面三个SQL来清理查询缓存 

1、FLUSH QUERY CACHE; // 清理查询缓存内存碎片。

2、RESET QUERY CACHE; // 从查询缓存中移出所有查询。

3、FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

 四、性能监控

碎片率
查询缓存内存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%

命中率
查询缓存命中率=(Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

内存使用率
查询缓存内存使用率=(query_cache_size – Qcache_free_memory) / query_cache_size * 100%

Qcache_lowmem_prunes

该参数值对于检测查询缓存区的内存大小设置是否，有非常关键性的作用，其代表的意义为：查询缓存去因内存不足而不得不从查询缓存区删除的查询缓存信息，删除算法为LRU；

query_cache_min_res_unit

内存块分配的最小单元非常重要，设置过大可能增加内存碎片的概率发生，太小又可能增加内存分配的消耗，为此在系统平稳运行一个阶段性后，可参考公式的计算值：
查询缓存最小内存块 = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

query_cache_size

我们如何判断query_cache_size是否设置过小，依然也只有先预设置一个值，推荐为：32M~128M之间的区域，待系统平稳运行一个时间段（至少1周），并且观察这周内的相关状态值：
(1).Qcache_lowmem_prunes；
(2).命中率；
(3).内存使用率；

若整个平稳运行期监控获得的信息，为命中率高于80%，内存使用率超过80%，并且Qcache_lowmem_prunes的值不停地增加，而且增加的数值还较大，则说明我们为查询缓冲区分配的内存过小，可以适当地增加查询缓存区的内存大小；

若是整个平稳运行期监控获得的信息，为命中率低于40%，Qcache_lowmem_prunes的值也保持一个平稳状态，则说明我们的查询缓冲区的内 存设置过大，或者说业务场景重复执行一样查询语句的概率低，同时若还监测到一定量的freeing items，那么必须考虑把查询缓存的内存条小，甚至关闭查询缓存功能；