mysql query cache详解

我不是女神ヾ 2022-08-18 11:39 208阅读 0赞

Query Cache 原理

当mysql接收到一条select类型的query时，mysql会对这条query进行hash计算而得到一个hash值，然后通过该hash值到query cache中去匹配，如果没有匹配中，则将这个hash值存放在一个hash链表中，同时将query的结果集存放进cache中，存放hash值的链表的每一个hash节点存放了相应query结果集在cache中的地址，以及该query所涉及到的一些table的相关信息；如果通过hash值匹配到了一样的query，则直接将cache中相应的query结果集返回给客户端。如果mysql任何一个表中的任何一条数据发生了变化，便会通知query cache需要与该table相关的query的cache全部失效，并释放占用的内存地址。

**query cache优缺点**

优点很明显，对于一些频繁select query，mysql直接从cache中返回相应的结果集，而不用再从表table中取出，减少了IO开销。

即使query cache的收益很明显，但是也不能忽略它所带来的一些缺点：

1. query语句的hash计算和hash查找带来的资源消耗。mysql会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在，虽然hash计算和查找的效率已经足够高了，一条query所带来的消耗可以忽略，但一旦涉及到高并发，有成千上万条query时，hash计算和查找所带来的开销就的重视了；

2. query cache的失效问题。如果表变更比较频繁，则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化，还包括结构或者索引的任何变化；

3.对于不同sql但同一结果集的query都会被缓存，这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同，缓存都是认为是不同的sql（因为他们的hash值会不同）；

4. 相关参数设置不合理会造成大量内存碎片，相关的参数设置会稍后介绍。

**合理利用query cache**

query cache有利有弊，合理的使用query cache可以使其发挥优势，并且有效的避开其劣势。

1. 并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更，那么就应该避免在变化频繁的table上使用query cache。

mysql中针对query cache有两个专用的sql hint：SQL\_NO\_CACHE和SQL\_CACHE，分别表示强制不使用和强制使用query cache，通过强制不使用query cache，可以让mysql在频繁变化的表上不使用query cache，这样减少了内存开销，也减少了hash计算和查找的开销。

例：

SELECT SQL\_CACHE id, name FROM customer;  
SELECT SQL\_NO\_CACHE id, name FROM customer;

**查询Query cache配置**

首先查看query cache的系统变量。

![1344743817_5552.png][]

其它几个系统变量控制查询缓存操作。当启动mysqld时，这些变量可以在选项文件或者命令行中设置。所有查询缓存系统变量名以query\_cache\_ 开头。  
为了设置查询缓存大小，设置query\_cache\_size系统变量。设置为0表示禁用查询缓存。默认缓存大小设置为0；也就是禁用查询缓存。

当设置query\_cache\_size变量为非零值时，应记住查询缓存至少大约需要40KB来分配其数据结构。(具体大小取决于系统结构）。如果你把该值设置的太小，  
将会得到一个警告，如本例所示：  
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 |  
\+------------------+-------+

如果查询缓存大小设置为大于0，query\_cache\_type变量影响其工作方式。这个变量可以设置为下面的值：  
· 0或OFF将阻止缓存或查询缓存结果。  
· 1或ON将允许缓存，以SELECT SQL\_NO\_CACHE开始的查询语句除外。  
· 2或DEMAND，仅对以SELECT SQL\_CACHE开始的那些查询语句启用缓存。

设置query\_cache\_type变量的GLOBAL值将决定更改后所有连接客户端的缓存行为。具体客户端可以通过设置query\_cache\_type变量的会话值控制它们本身连  
接的缓存行为。例如，一个客户可以禁用自己的查询缓存，方法如下：

mysql> SET SESSION query\_cache\_type = OFF;  
要控制可以被缓存的具体查询结果的最大值，应设置query\_cache\_limit变量。默认值是1MB。  
当一个查询结果（返回给客户端的数据）从查询缓冲中提取期间，它在查询缓存中排序。因此，数据通常不在大的数据块中处理。查询缓存根据数据排序要  
求分配数据块，因此，当一个数据块用完后分配一个新的数据块。因为内存分配操作是昂贵的(费时的)，所以通过query\_cache\_min\_res\_unit系统变量给查询  
缓存分配最小值。当查询执行时，最新的结果数据块根据实际数据大小来确定，因此可以释放不使用的内存。根据你的服务器执行查询的类型，你会发现调  
整query\_cache\_min\_res\_unit变量的值是有用的：  
· query\_cache\_min\_res\_unit默认值是4KB。这应该适合大部分情况。  
· 如果你有大量返回小结果数据的查询，默认数据块大小可能会导致内存碎片，显示为大量空闲内存块。由于缺少内存，内存碎片会强制查询缓存从缓  
存内存中修整（删除）查询。这时，你应该减少query\_cache\_min\_res\_unit变量的值。空闲块和由于修整而移出的查询的数量通  
过Qcache\_free\_blocks和Qcache\_lowmem\_prunes变量的值给出。  
· 如果大量查询返回大结果（检查 Qcache\_total\_blocks和Qcache\_queries\_in\_cache状态变量），你可以通过增加query\_cache\_min\_res\_unit变量的值  
来提高性能。但是，注意不要使它变得太大（参见前面的条目）。

以上参数经常需要调整的是“query\_cache\_limit”和“query\_cache\_min\_res\_unit”，都需要根据业务做一些调整，比如cache的结果集大都小于4k的话，可以适当的调整"query\_cache\_min\_res\_unit"的值，以避免造成内存的浪费。如果结果集的大小又都大于1M时，就得调整"query\_cache\_limit"的值，避免因为结果集大小超过限制而不被mysql cache住。

**MySQL 查询缓存变量**

<table style="word-wrap:break-word; color:rgb(102,102,102); font-family:宋体,Arial; font-size:16px; line-height:26px; border-collapse:collapse; padding:0pt 5.4pt"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> 变量名 </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 说明 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_free_blocks </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH&nbsp;QUERY&nbsp;CACHE&nbsp;会对缓存中的碎片进行整理，从而得到一个空闲块。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_free_memory </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 缓存中的空闲内存。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_hits </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 每次查询在缓存中命中时就增大。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_inserts </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用&nbsp;1&nbsp;减去这个值就是命中率。在上面这个例子中，大约有&nbsp;87%&nbsp;的查询都在缓存中命中。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_lowmem_prunes </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的&nbsp;free_blocks&nbsp;和&nbsp;free_memory&nbsp;可以告诉您属于哪种情况）。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_not_cached </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 不适合进行缓存的查询的数量，通常是由于这些查询不是&nbsp;SELECT&nbsp;语句。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_queries_in_cache </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 当前缓存的查询（和响应）的数量。 </td> 
 </tr> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:117.1pt"> Qcache_total_blocks </td> 
 <td style="word-wrap:break-word; margin:0px; padding:0pt 5.4pt; width:315.25pt"> 缓存中块的数量。 </td> 
 </tr> 
 </tbody> 
</table>

![1344760080_2359.png][]

通过上述这些状态变量可以了解到query cache的运行状况，从而可以调整相应的系统参数的值。

**检查查询缓存使用情况**

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率

当服务器收到SELECT 语句的时候，Qcache\_hits 和Com\_select 这两个变量会根据查询缓存

的情况进行递增

**查询缓存命中率的计算公式是：Qcache\_hits/(Qcache\_hits + Com\_select)。**

mysql> show status like '%Com\_select%';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| Com\_select    | 1     |

\+---------------+-------+

1 row in set (0.00 sec)

此时的查询缓存命中率：3/（3+1）=75%；由于个人的测试数据库，查询较少，更行更少，命中率颇高。

**query cache的限制**

1. 5.1.17之前的版本不能cache绑定变量的query，但是从5.1.17版本开始，query cache已经开始支持绑定变量的query了；

2. 所有子查询中的外部查询sql不能被cache，只会cache最终的结果集；

3. 在procedure，function以及trigger中的query不能被cache；

4. 包含其他很多每次执行可能得到不一样结果的函数的query不能被cache。

5.语句中出现以下任何一个函数该语句都不会被缓存。

![20151214193656040][]

6.引用自定义函数(UDFs)。  
7.引用自定义变量。  
8. 引用mysql系统数据库中的表。  
9.下面方式中的任何一种：  
    SELECT ...IN SHARE MODE  
    SELECT ...FOR UPDATE  
    SELECT ...INTO OUTFILE ...  
    SELECT ...INTO DUMPFILE ...  
    SELECT \* FROM ...WHERE autoincrement\_col IS NULL  
    最后一种方式不能被缓存是因为它被用作为ODBC工作区来获取最近插入的ID值

10.被作为编写好的语句，即使没有使用占位符。例如，下面使用的查询：  
     char \*my\_sql\_stmt = "SELECT a，b FROM table\_c";  
     /\* ...\*/  
     mysql\_stmt\_prepare(stmt，my\_sql\_stmt，strlen(my\_sql\_stmt));  
不被缓存。  
11.使用TEMPORARY表。  
12.不使用任何表。  
13. 用户有某个表的列级权限。

鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query 的查询结果被Cache。

参考文章：

http://blog.chinaunix.net/uid-25266990-id-3426963.html

http://blog.csdn.net/tonyxf121/article/details/7856361

mysql 5.6官方文档

[1344743817_5552.png]: https://img-my.csdn.net/uploads/201208/12/1344743817_5552.png
[1344760080_2359.png]: https://img-my.csdn.net/uploads/201208/12/1344760080_2359.png
[20151214193656040]: /images/20220731/324cd91f710c447a92c48915356f4a60.png