query cache

 The MySQL Query Cache

  query cache缓存了select(查询)语句和它对应的结果集。当相同语句再次执行时，Mysql服务端会从query cache中取出结果返回给客户端，而不需要再去解析和执行这条sql语句了。query cache在会话间是共享的，通过一个会话缓存的结果集可以被另一个会话使用。

   每一个cached query需要最少两个block，一个存放sql text，一个存放query result。每个表也需要一个block；如果多个查询语句涉及到同一表，也只需为该表分配一个block

   query cache适合于更新频率低，并且查询语句严格一致的业务环境。

   query cache不会返回陈旧的数据。当表被更改时，所有涉及的行源都会被flush出query cache。

Note

The query cache does not work in an environment where you have multiple mysqld servers updating the sameMyISAM tables.

The query cache is used for prepared statements under the conditions described in Section 8.10.3.1, “How the Query Cache Operates”.

Note

As of MySQL 5.6.5, the query cache is not supported for partitioned tables, and is automatically disabled for queries involving partitioned tables. The query cache cannot be enabled for such queries. (Bug #53775)

来源： <http://dev.mysql.com/doc/refman/5.6/en/query-cache.html>

 

     To disable the query cache at server startup, set the  query_cache_size  system variable to 0. By disabling the query cache code, there is no noticeable overhead.

        在启动时指定 --query_cache_size=0，来禁用query cache

Query Cache相关参数：

　　query_cache_size                  QC占用空间大小，通过将其设置为0关闭QC功能

　　query_cache_type                              0或OFF将阻止缓存或查询缓存结果。  

                                        1或ON将允许缓存，以SELECT SQL_NO_CACHE开始的查询语句除外。 

                                        2或DEMAND，仅对以SELECT SQL_CACHE开始的那些查询语句启用缓存。

　　query_cache_limit                最大缓存结果集

　　query_cache_min_res_unit       手册上说，QC会按照这个值分配缓存block的大小。

　　Qcache_lowmem_prunes            这是一个状态变量(show status)，当缓存空间不够需要释放旧的缓存时，该值会自增。

    
    
(mysql@localhost) [(none)]> select @@query_cache_size;   --查看query cache大小
+--------------------+
| @@query_cache_size |
+--------------------+
|            1048576 |
+--------------------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> select @@query_cache_type;   --查看query cache是否开启
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                |
+--------------------+
1 row in set (0.00 sec)

    query cache提供了显著的性能改进的潜力，但不要以为它会在所有情况下这样做的。如果配置不当，性能可能会降低：

    慎重设置query cache的大小，如果设置过大，会增加管理query cache的开销，负面影响可能会超过query cache的收益。10M通常是有益的，100M可能不会(但是最小也要有40K)

    对于频繁  更改的 表，可以使用  SQL_NO_CACHE  option 组织语句进入query cache（以为进入后，一修改，就又会被flush out）

How the Query Cache Operates

    查询语句在解析前先要进行比较，下面的两个查询被认为是不同的语句

SELECT * FROM tbl_name
Select * from tbl_name

    查询语句必须完全一样(byte for byte)才会被认为是相同的。此外，由于一些原因，即便相同的查询语句也可能被认为是不同的。通过不同的database， 不同的协议版本,或不同的默认字符集的查询被认为是不同的，它们将被分开存储在query cache中

    在下面的情况下，query cache不会被使用 ：

• queries 是一个子查询
  
• queries在函数，触发器或event中执行
  

   在结果集从query cache中fetch前，Mysql会检查执行查询的用户是否有查询中涉及的database和tables的select权限，如果没有权限，cached result不可用

    如果一个查询结果是从query cache中获取的，   Qcache_hits  会增加，而Com_select不会

        com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询。

     
     
(mysql@localhost) [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 6     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> select count(*) from fan.ab
    -> ;
ERROR 1146 (42S02): Table 'fan.ab' doesn't exist
(mysql@localhost) [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 7     |
+---------------+-------+
1 row in set (0.00 sec)

      
      
(mysql@localhost) [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> show global status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> select * from fandb.ab;
+----+------+
| id | name |
+----+------+
|  5 | 5s   |
|  6 | 6s   |
|  7 | 7s   |
|  8 | 8s   |
|  9 | 9s   |
+----+------+
5 rows in set (0.00 sec)
(mysql@localhost) [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 4     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> show global status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> select * from fandb.ab;
+----+------+
| id | name |
+----+------+
|  5 | 5s   |
|  6 | 6s   |
|  7 | 7s   |
|  8 | 8s   |
|  9 | 9s   |
+----+------+
5 rows in set (0.00 sec)
(mysql@localhost) [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 4     |
+---------------+-------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> show global status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

    如果一个表发生了变化，所有被缓存在query cache中的涉及到此表的内容会被置为无效并被移出query cache，  This includes queries that use  MERGE  tables that map to the changed table。表会因下面的statements产生变化： INSERT ,  UPDATE ,  DELETE ,  TRUNCATE TABLE ,  ALTER TABLE ,  DROP TABLE , or  DROP DATABASE .

          The query cache also works within transactions when using  InnoDB  tables.

         从Mysql5.6开始，通过查询view获得的result也会被缓存

          The query cache works for  SELECT SQL_CALC_FOUND_ROWS ...  queries and stores a value that is returned by a following  SELECT FOUND_ROWS()  query.  FOUND_ROWS()  returns the correct value even if the preceding query was fetched from the cache because the number of found rows is also stored in the cache. The  SELECT FOUND_ROWS()  query itself cannot be cached.

        SELECT SQL_CALC_FOUND_ROWS

          SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding(忽略)  any limit clause. The number of rows can then be retrieved with select FOUND_ROWS().

    

    所有的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

     
     
(mysql@localhost) [(none)]> SELECT SQL_CALC_FOUND_ROWS * FROM fandb.ab limit 1;
+----+------+
| id | name |
+----+------+
|  5 | 5s   |
+----+------+
1 row in set (0.00 sec)
(mysql@localhost) [(none)]> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

说是分页的时候用？

A query cannot be cached if it contains any of the functions shown in the following table.

AES_DECRYPT() (as of 5.7.4)	AES_ENCRYPT() (as of 5.7.4)	BENCHMARK()
CONNECTION_ID()	CONVERT_TZ()	CURDATE()
CURRENT_DATE()	CURRENT_TIME()	CURRENT_TIMESTAMP()
CURTIME()	DATABASE()	ENCRYPT() with one parameter
FOUND_ROWS()	GET_LOCK()	LAST_INSERT_ID()
LOAD_FILE()	MASTER_POS_WAIT()	NOW()
PASSWORD()	RAND()	RANDOM_BYTES()
RELEASE_LOCK()	SLEEP()	SYSDATE()
UNIX_TIMESTAMP() with no parameters	USER()	UUID()
UUID_SHORT()




A query also is not cached under these conditions:

• It refers to user-defined functions (UDFs) or stored functions.

• It refers to user variables or local stored program variables.

• It refers to tables in the mysql, INFORMATION_SCHEMA, or performance_schema database.

• (MySQL 5.6.5 and later:) It refers to any partitioned tables.

• It is of any of the following forms:

  SELECT ... LOCK IN SHARE MODE
  SELECT ... FOR UPDATE
  SELECT ... INTO OUTFILE ...
  SELECT ... INTO DUMPFILE ...
  SELECT * FROM ... WHERE autoincrement_col IS NULL
  

  The last form is not cached because it is used as the ODBC workaround for obtaining the last insert ID value. See the Connector/ODBC section of Chapter 23, Connectors and APIs.

  Statements within transactions that use SERIALIZABLE isolation level also cannot be cached because they useLOCK IN SHARE MODE locking.

• It uses TEMPORARY tables.

• It does not use any tables.

• It generates warnings.

• The user has a column-level privilege for any of the involved tables.

Query Cache SELECT Options

Two query cache-related options may be specified in SELECT statements:

•  SQL_CACHE

  The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON orDEMAND.

• SQL_NO_CACHE

  The server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result. (Due to a limitation in the parser, a space character must precede and follow the SQL_NO_CACHE keyword; a nonspace such as a newline causes the server to check the query cache to see whether the result is already cached.)

Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

Query Cache Configuration

The have_query_cache server system variable indicates whether the query cache is available:

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

When using a standard MySQL binary, this value is always YES, even if query caching is disabled.

Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache system variables all have names that begin withquery_cache_. They are described briefly in Section 5.1.4, “Server System Variables”, with additional configuration information given here.

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0. By default, the query cache is disabled. This is achieved using a default size of 1M, with a default for query_cache_type of 0. (Before MySQL 5.6.8, the default size is 0, with a default query_cache_type of 1.)

To reduce overhead significantly, also start the server with query_cache_type=0 if you will not be using the query cache.

当你将query_cache_size设为非零值，要注意最小值为40K，用以存储其自身结构。 (The exact size depends on system architecture.) 

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

     
     
如何确认一个系统的 Query Cache 的运行是否健康，命中率如何，设置量是否足够？ 
MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态，具体如下： 
Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目 
Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量 
Qcache_hits：Query Cache 命中次数 
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数 
Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数 
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL 
Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量 
Qcache_total_blocks：Query Cache 中总的 Block 数量 
可以根据这几个状态计算出 Cache 命中率，计算出 Query Cache 大小设置是否足够，总的来说，我个人不建议将 Query Cache 的大小设置超过256MB，这也是业界比较常用的做法。 
Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；
来源： <http://jackyrong.iteye.com/blog/2173523>