MySQL-QueryCache查询缓存

最新推荐文章于 2021-01-27 14:57:15 发布

weixin_34185560

最新推荐文章于 2021-01-27 14:57:15 发布

阅读量143

点赞数

文章标签：数据库 python 操作系统

原文链接：https://my.oschina.net/hanheheda/blog/651681

版权

2019独角兽企业重金招聘Python工程师标准>>>

###ps:本文主要内容是官方文档上关于5.6版本的查询缓存的内容，本人仅是先做了个翻译，内容方面还有很多地方需要推敲斟酌，可能读起来会发现很多地方不通顺，算是个半成品吧。 #一、简介

查询缓存中存储了被发送到客户端的SELECT语句和相匹配的结果的文本内容。如果服务器之后收到了相同的查询语句，就会会从查询缓存中获取结果而非解析并执行这个语句。查询缓存在sessions之间共享，所以一个客户端产生的结果集，可以用来作为另一个客户端产生的相同查询的结果。

查询缓存在表内容很少改变并且客户端收到许多相同的查询请求的情况下，是十分有用的。这是许多基于数据库内容的web服务的典型案例。

查询缓存不会返回过期失效的数据。当表中数据被修改时，相关的查询结果会被更新。

-Note 
查询缓存在你拥有多个mysqld服务下更新相同的MyISAM表格时是不会工作的。

查询缓存被用于Section 8.10.3.1, “How the Query Cache Operates”所描述的预处理语句上。

-Note
在mysql 5.6.5中，查询缓存不支持分区表, 并且对包含分区表的查询语句是自动禁用的。在这样的查询中查询缓存是无法被启用的。 (Bug #53775)

一些查询缓存的工作特性如下所示。下面的结论是由一台Linux Alpha 2×500MHz system with 2GB RAM and a 64MB query cache的服务器运行mysql服务所产得出的。

1.如果你的查询请求很简单(例如只从单排表表中查询一条记录),但是查询内容始终不同的话，那么查询结果无法被缓存,查询缓存的开销只有13%. 这可以被看作是最差的情况.在真实生活中, 查询通常要复杂的多,所以开销会显著更低. 

2.用查询缓存从单排表中搜索一个记录比没有查询缓存快238%。这可以看成是查询被缓存后的最小加速。

如果要在启动服务时禁用查询缓存，只要设置query_cache_size这个系统变量为0. 通过这条禁用命令，服务器就没有显著的性能开销。

查询缓存提供了潜在的显著性能改善，但是不要认为它在所有情况下都会生效。在某些查询缓存配置和服务器工作负载下，你可能会看到性能下降：

1.小心数值过大的查询缓存，会增加维持这个缓存的开销，可能会超过它所带来的好处。通常几十MB的查询缓存是有益的，几百MB的查询缓存可能不是。 

2.服务器工作负载对查询缓存的效率有显著的影响。 一个查询包含了几乎一组固定的SELECT语句的，而不是包含频繁INSERT语句导致缓存中的结果不断失效的情况下，启动查询缓存通常是有益的。在某些情况下，一种临时解决办法是使用SQL_NO_CACHE选项来防止一个使用频繁更新的表的SELECT语句来使用查询缓存。

为了确定启用查询缓存是有效的，最好在你的mysql服务器上分别测试使用和禁用查询缓存。最好定期重新测试以防止服务器工作负载影响查询缓存的效率。

#二、查询缓存是如何运行的

这个小节描述了查询缓存在它运行的时候是如何起作用的。Section 8.10.3.3, “Query Cache Configuration”, 描述了如何去控制查询缓存是否在运行。

接收到的查询将会被与在缓存中的进行比较，所以下面这两种查询将会被认为是与在缓存中不同的两种查询：

SELECT * FROM tbl_name
Select * from tbl_name

查询必须是完全一致的(byte for byte)才会被识别。另外查询字符串还会因为其他原因被识别为不同的查询。查询不同的databases,不同的版本协议或者不同的默认字符集都会被认为是不同的查询并且分别被加载到缓存中。

查询缓存不适用与下面两种情况:

1.其它外查询的子查询 
2.在存储函数，触发器或者事件内执行的查询

在从查询缓存中提取查询结果前，mysql 会检查用户是否有所有相关的database和table的SELECT权限。如果没有权限，缓存的查询结果不会被使用。

如果一个查询结果是从查询缓存中提取的，服务器会增加Qcache_hits这个状态变量，而不是Com_select。详细请看Section 8.10.3.4, “Query Cache Status and Maintenance”。

如果一个表格被变更，所有使用这个表格的被缓存的查询将会失效并且从缓存中被移除。这包括使用映射到变动的表格的MERGE表格。（:scream:不明标注）一个表格可以被许多种语句变更，例如INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

在使用Innodb表格的事务中，查询缓存同样生效。

SELECT视图查询的结果会被缓存。

查询缓存对SELECT SQL_FOUND_ROWS...等查询和存储由SELECT FOUND_ROWS()返回的数值是有用的。因为查到的行记录的数量也被存储在缓存中，尽管之前的查询被从缓存中提取，FOUND_ROWS()仍会返回正确的值。SELECT FOUND_ROWS()查询本身无法被缓存。

由mysql_stmt_prepare()和mysql_stmt_execute()生成的使用二进制协议的编译语句受制于对缓存的限制，参考23.8.8节 C API预编译语句）。与之不同的是，查询缓存中的语句是基于填充了问号符之后的文本的。语句是基于二进制协议与缓存中的语句比较的。就是，对查询缓存的目标，基于二进制协议的预处理语句与基于文本协议的预处理语句不通。(see Section 13.5, “SQL Syntax for Prepared Statements”).

如果一个查询语句包含下面表格中的任意函数就无法被缓存：

+--------------------+--------------------+----------------------+  
| BENCHMARK()        |CONNECTION_ID()     |  CONVERT_TZ()        |  
+--------------------+--------------------+----------------------+    
| CURDATE()          | CURRENT_DATE()     |  CURRENT_TIME()      |  
+--------------------+--------------------+----------------------+     
| CURRENT_TIMESTAMP()| CURRENT_USER()     |  CURTIME()           |  
+--------------------+--------------------+----------------------+  
| DATABASE()         | ENCRYPT()          |  FOUND_ROWS()        |  
+--------------------+--------------------+----------------------+  
| GET_LOCK()         | IS_FREE_LOCK()     |  IS_USED_LOCK()      |  
+--------------------+--------------------+----------------------+  
| LAST_INSERT_ID()   | LOAD_FILE()	      |  MASTER_POS_WAIT()   |  
+--------------------+--------------------+----------------------+    
| NOW()              | PASSWORD()	      |  RAND()              |  
+--------------------+--------------------+----------------------+  
| RANDOM_BYTES()     | RELEASE_ALL_LOCKS()|  RELEASE_LOCK()      |  
+--------------------+--------------------+----------------------+  
| SLEEP()            | SYSDATE()	      |  UNIX_TIMESTAMP()    |  
|                    |                    | with no parameters   |  
+--------------------+--------------------+----------------------+  
| USER()             | UUID()             |  UUID_SHORT()        |  
+--------------------+--------------------+----------------------+

下面这几种情况也同样无法被缓存：

1. 引用了用户自定义函数(UDFs)或者存储函数。 
2. 引用了用户变量或者本地存储的程序变量。
3. 引用了 mysql, INFORMATION_SCHEMA, or performance_schema等数据库中的表格。 
4. (MySQL 5.6.5 或者更新版本:)引用了任何分区表格。  
5. 下面任意一种形式的: 
    SELECT ... LOCK IN SHARE MODE
    SELECT ... FOR UPDATE
    SELECT ... INTO OUTFILE ...
    SELECT ... INTO DUMPFILE ...
    SELECT * FROM ... WHERE autoincrement_col IS NULL
    最后一种形式无法被缓存是因为它被用来当成建立最后一个insert ID值的临时解决办法。详情参考Chapter 23, Connectors and APIs. 
在事务内使用可串行化读的隔离级别的语句无法被缓存因为它们使用了共享锁。
6. 使用了临时表 
7. 不使用任何表 
8. 会产生warnings
9. 用户拥有所有相关表column-level权限

#三、查询缓存SELECT选项

SELECT语句中有两个查询缓存相关的的具体选项：

1.SQL_CACHE
如果查询结果是可缓存的并且query_cache_type这个系统变量的值为ON或者DEMAND，那么就会被缓存。 
2.SQL_NO_CACHE
服务器禁用查询缓存。它既不会去检查查询缓存中结果是否被缓存，也不会缓存查询结果。（由于解析器的局限性，在SQL_NO_CACHE关键词后必须有一个空格；一个非空格符号例如换行符将会使服务器去检查查询缓存中结果是否被缓存，即没有空格SQL_NO_CACHE这个选线就不会生效）

例如: SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;

#四、查询缓存配置

have_query_cache这个系统变量表示查询缓存是否被支持：

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

当使用标准的mysql二进制版本，这个值总是YES，尽管查询缓存被禁用的情况下。

还有一些其它的系统变量控制查询缓存的运行。这些可以在配置文件中设置或者在启动mysqld服务的时候在命令行中设置。这些系统变量名称全都以query_cache_开头。它们在Section 5.1.4, “Server System Variables”有详细描述，还包括额外的配置信息。

要设置缓存变量的大小，需要设置query_cache_size这个系统变量。设置query_cache_size=0是禁用查询缓存。默认情况下，查询缓存是禁用的。通过默认大小为1M，query_cache_type值为0来实现。（在mysql5.6.8之前的版本，默认大小是0，query_cache_type默认值是1）

如果不使用查询缓存的话，启动时候配置query_cache_type值为0将会显著降低负载。

Note 
如果使用windows配置向导来安装或配置mysql的话，默认的query_cache_size将会根据不同的配置类型为你自动设置。当使用windows配置向导，根据选择的配置查询缓存可能会被启用（即设置为一个非零值）。query_cache_type这个变量也可以控制查询缓存。记住在已经生效的配置文件my.ini中检查这些变量的值。

当你设置query_cache_size为非零时，记住查询缓存需要一个最小的大约40KB的大小来分配它的结构。（具体值取决于系统架构）如果你将这个值设置的太小，你会得到一个如下的warning:

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
         new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+

为了保证查询缓存可以保存任意查询结果，它应该被设置得更大些:

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)

query_cache_size的值将会被定向到最近的1024字节块。所以报告的值可能会与你设置的不同。

如果查询缓存的大小大于0，那么query_cache_type这个变量的值将会影响它如何工作。这个变量可以设为为如下值：

1. 0或者OFF禁止缓存或者提取缓存结果
2. 1或者ON将会启用缓存除非SQL语句以SELECT SQL_NO_CACHE开头
3. 2或者DEMAND只有在语句以SELECT SQL_CACHE开头的情况才使用查询缓存

如果query_cache_size为0，你应该设置query_cache_type值也为0.在这种情况下，服务器不会产生查询缓存互斥，也就是查询缓存无法被启用并且在执行查询的时候可以降低负载。

设置全局query_cache_type值决定了所有变更后的链接服务器的查询缓存特性。个人用户可以通过设置会话query_cache_type值来控制自己的查询缓存特性。例如，用户可以通过如下命令禁用查询缓存：

mysql> SET SESSION query_cache_type = OFF;

如果你在启动服务的时候设置query_cache_type值（而不是在运行的时候通过SET语句），只有数字类型值才可以使用。

如果想要控制单个被缓存的查询结果的最大单位，设置query_cache_limit系统变量。默认值为1MB。

注意不要将查询缓存的大小设置的过大。由于在更新时需要线程去锁住缓存，在缓存很大时你可能会发现锁争用问题。

Note
你可以在启动的时候通过在命令中或者配置文件中用SET语句：--maxmum-query_cache_size=32M明确设置查询缓存的最大值。

当查询被缓存时，在结果（发送到客户端的数据）被提取时将会被存储到查询缓存中。因此数据并不会以一个大块处理。查询缓存会根据需求分配一些块来存储这些数据，所以当这些块被填充时，一个新块将会被分配。因为内存的分配操作开销（时间上）很大，查询缓存会根据 query_cache_min_res_unit这个系统变量来分配块。当一个查询被执行后，最后结果的块将会被分割成数据实际大小，所以剩余的内存是可用的。根据你的服务器执行的查询的类型，你会发现适当地调整query_cache_min_res_unit是有益的：

1.默认的query_cache_min_res_unit值是4KB.这适用于大多数情况。
2.如果你有许多较小结果的查询，由于大量的自由块，默认的块大小将会导致许多的内存碎片。由于内存紧缺，碎片将会强制查询缓存删除（delete）缓存中的查询。在这种情况下，应当减小query_cache_min_res_unit的值。Qcache_free_blocks 和 Qcache_lowmem_prunes另个状态变量表示了自由块的数量和由于删除（delete）被移除缓存的查询数量。
3.如果你的查询大多数都是较大的结果（检查 Qcache_total_blocks 和 Qcache_queries_in_cache状态变量），你可以通过加大query_cache_min_res_unit的值来提高性能，注意不要调整得过大。

#五、查询缓存状态与维护

通过下面的语句来检查你的mysql服务器中查询缓存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

你可以利用FLUSH_QUERY_CACHE语句来整理查询缓存的碎片。这个语句不会从缓存中移除任何查询结果。

RESET QUERY CACHE会此处查询缓存中的所有查询结果。FLUSH TABLES语句也是一样。

想要监控查询缓存的性能，可以如下所示使用SHOW STATUS语句：

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+

这些变量的详细说明可参考 Section 5.1.6, “Server Status Variables”。里面有这些变量的详细用法。

SELECT查询的统计数量可以用下面公式算出：

  Com_select
+ Qcache_hits
+ queries with errors found by parser

Com_select 的值可以用下面公式算出：

  Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during the column-privileges check

查询缓存使用了变长度块，所以 Qcache_total_blocks 和 Qcache_free_blocks可以表示查询缓存的内存碎片。在执行FLUSH QUERY CACHE语句后，只会剩下一个空闲块。

每个被缓存的查询需要至少两个内存块（一个用来存储查询文本一个或多个用来存储查询结果）。同样，每个被查询使用的表格需要一个内存块。但是，如果两个或多个查询使用了相同的表格，也只会被分配一个内存块。

由Qcache_lowmem_prunes状态变量所提供的信息可以帮助你调整查询缓存的大小。它包含为了给新查询腾出内存空间而所被移除的查询数量。查询缓存使用最近最少使用（LRU）策略来决定从缓存中移除那些查询。调整信息在Section 8.10.3.3, “Query Cache Configuration”中。

参考文章：http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

转载于:https://my.oschina.net/hanheheda/blog/651681