高性能MySql进化论(十三):查询缓存机制

朱雀 2022-05-13 00:26 197阅读 0赞

对于很多的数据库系统都能够缓存执行计划，对于完全相同的sql, 可以使用已经已经存在的执行计划，从而跳过解析和生成执行计划的过程。MYSQL以及Oracle提供了更为高级的查询结果缓存功能，对于完全相同的SQL (字符串完全相同且大小写敏感) 可以执行返回查询结果。本文主要介绍MYSQL 查询缓存的一些特性，Oracle query cache可以参考[http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html][http_www.oracle.com_technetwork_articles_sql_11g-caching-pooling-088320.html]

如果你有一个不经常改变的表并且服务器收到该表的大量相同查询，查询缓存在这样的应用环境中十分有用。对于许多Web服务器来说存在这种典型情况，它根据数据库内容生成大量的动态页面。

# 1查询缓存的工作机制 #

Mysql 判断是否命中缓存的办法很简单，首先会将要缓存的结果放在引用表中，然后使用查询语句，数据库名称，客户端协议的版本等因素算出一个hash值，这个hash值与引用表中的结果相关联。如果在执行查询时，根据一些相关的条件算出的hash值能与引用表中的数据相关联，则表示查询命中

通过have\_query\_cache服务器系统变量指示查询缓存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

+------------------+-------+

为了监视查询缓存性能，使用SHOW STATUS查看缓存状态变量：

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

|变量名 |值 |

+-------------------------+--------+

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

+-------------------------+--------+

## 1.1 查询缓存机制失效的场景 ##

先不论查询缓存机制有利有弊，先看看哪些场景下会导致缓存机制失效

1. 如果查询语句中包含一些不确定因素时（例如包含 函数Current()），该查询不会被缓存,不确定因素主要包含以下情况

·引用了一些返回值不确定的函数

<table> 
 <tbody> 
 <tr> 
 <td> BENCHMARK() </td> 
 <td> CONNECTION_ID() </td> 
 <td> CURDATE() </td> 
 </tr> 
 <tr> 
 <td> CURRENT_DATE() </td> 
 <td> CURRENT_TIME() </td> 
 <td> CURRENT_TIMESTAMP() </td> 
 </tr> 
 <tr> 
 <td> CURTIME() </td> 
 <td> DATABASE() </td> 
 <td> 带一个参数的ENCRYPT() </td> 
 </tr> 
 <tr> 
 <td> FOUND_ROWS() </td> 
 <td> GET_LOCK() </td> 
 <td> LAST_INSERT_ID() </td> 
 </tr> 
 <tr> 
 <td> LOAD_FILE() </td> 
 <td> MASTER_POS_WAIT() </td> 
 <td> NOW() </td> 
 </tr> 
 <tr> 
 <td> RAND() </td> 
 <td> RELEASE_LOCK() </td> 
 <td> SYSDATE() </td> 
 </tr> 
 <tr> 
 <td> 不带参数的UNIX_TIMESTAMP() </td> 
 <td> USER() </td> 
 <td> </td> 
 </tr> 
 </tbody> 
</table>

·引用自定义函数(UDFs)。

·引用自定义变量。

·引用mysql系统数据库中的表。

·下面方式中的任何一种：

SELECT ...IN SHARE MODE

SELECT ...FOR UPDATE

SELECT ...INTO OUTFILE ...

SELECT ...INTO DUMPFILE ...

SELECT \* FROM ...WHERE autoincrement\_col IS NULL

·被作为编写好的语句，即使没有使用占位符。例如，下面使用的查询：

char *my_sql_stmt = "SELECT a，b FROM table_c";

/* ...*/

mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));

不被缓存。

·使用TEMPORARY表。

·不使用任何表。

·用户有某个表的列级别权限。

## 1.2 额外的消耗 ##

如果使用查询缓存，在进行读写操作时会带来额外的资源消耗，消耗主要体现在以下几个方面

·查询的时候会检查是否命中缓存，这个消耗相对较小

·如果没有命中查询缓存，MYSQL会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存

·如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

对于InnoDB而言，事物的一些特性还会限制查询缓存的使用。当在事物A中修改了B表时，因为在事物提交之前，对B表的修改对其他的事物而言是不可见的。为了保证缓存结果的正确性，InnoDB采取的措施让所有涉及到该B表的查询在事物A提交之前是不可缓存的。如果A事物长时间运行，会严重影响查询缓存的命中率

查询缓存的空间不要设置的太大。

因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况

## 1.3 配置参数 ##

MYSQL提供了一些参数来控制查询缓存的行为，参数如下

·**query\_cache\_limit**

MYSQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB)

如果某个查询的结果超出了这个值，Qcache\_not\_cached的值会加1，如果某个操作总是超出可以考虑在SQL中加上SQL\_NO\_CACHE来避免额外的消耗

·**query\_cache\_min\_res\_unit**

查询缓存分配的最小块的大小(字节)。 默认值是4096(4KB)。

·**query\_cache\_size**

为缓存查询结果分配的内存的数量，单位是字节，且数值必须是1024的整数倍。默认值是0，即禁用查询缓存。请注意即使query\_cache\_type设置为0也将分配此数量的内存。

·**query\_cache\_type**

设置查询缓存类型。设置GLOBAL值可以设置后面的所有客户端连接的类型。客户端可以设置SESSION值以影响他们自己对查询缓存的使用。下面的表显示了可能的值：

<table> 
 <tbody> 
 <tr> 
 <td> 选项 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> 0或OFF </td> 
 <td> 不要缓存或查询结果。请注意这样不会取消分配的查询缓存区。要想取消，你应将query_cache_size设置为0。 </td> 
 </tr> 
 <tr> 
 <td> 1或ON </td> 
 <td> 缓存除了以SELECT SQL_NO_CACHE开头的所有查询结果。 </td> 
 </tr> 
 <tr> 
 <td> 2或DEMAND </td> 
 <td> 只缓存以SELECT SQL_NO_CACHE开头的查询结果。 </td> 
 </tr> 
 </tbody> 
</table>

该变量默认设为ON。

·**query\_cache\_wlock\_invalidate**

一般情况，当客户端对MyISAM表进行WRITE锁定时，如果查询结果位于查询缓存中，则其它客户端未被锁定，可以对该表进行查询。将该变量设置为1，则可以对表进行WRITE锁定，使查询缓存内所有对该表进行的查询变得非法。这样当锁定生效时，可以强制其它试图访问表的客户端来等待。

# 2查询缓存的优化流程 #

当开启了查询缓存的功能后，可以通过一些参数以及状态值来观察查询缓存的使用情况。

流程以及涉及到的参数参见下图

![SouthEast][]

# 3查询缓存的优化 #

除了上图提到的一些优化策略外，还可以通过下面的措施来提高查询缓存的效率

·尽量用小表的简单替代大表的复杂查询

·尽量用批量写入取代单条写入

·控制query\_cache\_size的大小，甚至是禁用查询缓存

·通过DEMAND+SQL\_CACHE/SQL\_NO\_CACHE来灵活控制某个select是否需要进行缓存

·对于写密集型的应用，直接禁用查询缓存

# 4Reference #

更多信息可以参考：[http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html\#query-cache][http_dev.mysql.com_doc_refman_5.1_zh_database-administration.html_query-cache]

[http_www.oracle.com_technetwork_articles_sql_11g-caching-pooling-088320.html]: http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html
[SouthEast]: /images/20220513/8c6a21bfdb1148abab2380a834dc5812.png
[http_dev.mysql.com_doc_refman_5.1_zh_database-administration.html_query-cache]: http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache