8.10.3.1 How the Query Cache Operates

原文：https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

This section describes how the query cache works when it is operational. Section 8.10.3.3, “Query Cache Configuration”, describes how to control whether it is operational.

这一节介绍缓存（query cache)是如何工作的。在Section 8.10.3.3, “Query Cache Configuration”，介绍了如何控制查询缓存。

Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:

下面的查询语句在解析前会验证是否在缓存中存在，所以这两个查询语句会被缓存识别未不同的sql。

SELECT * FROM tbl_nameSelect * from tbl_name

Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

查询语句必须完全相同（字节相同），才会被认为是同一个查询语句。另外，还有其它因素影响是否是一个查询字符串在缓存中的唯一性。

查询语句使用不同的数据库，不同的协议版本，或者不同的字符集都会被识别为不同的查询语句，并分开缓存。

The cache is not used for queries of the following types:

下面的查询方式不会使用缓存：

• Queries that are a subquery of an outer query

• 使用了子查询，且子查询语句在最外层

• Queries executed within the body of a stored function, trigger, or event

• 查询语句在function,trigger或event里面。

Before a query result is fetched from the query cache, MySQL checks whether the user has SELECT privilege for all databases and tables involved. If this is not the case, the cached result is not used.

在从缓存获取结果之前，MYSQL会检查用户对查询语句涉及到的数据库和表是否有select权限，如有没有，将不使用缓存。

If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. See Section 8.10.3.4, “Query Cache Status and Maintenance”.

如果从缓存中查到了数据，server会增加Qcache_hits的值，而不是 Com_select。相关内容请查看Section 8.10.3.4, “Query Cache Status and Maintenance”。

If a table changes, all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

如果表的内容发生了变化，所有与该表关联的缓存将被清空，包括关联多表的查询语句。表的内容可以被很多语句修改，比如， INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

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

缓存可以InnoDB表的事务中使用。

The result from a SELECT query on a view is cached.

视图的select语句也会被缓存。

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 ...查询语句中工作，并在接下来的SELECT FOUND_ROWS()中使用缓存数据。这是因为查询匹配的行数也保存到了缓存中。SELECT FOUND_ROWS()查询不会被缓存。

Prepared statements that are issued using the binary protocol using mysql_stmt_prepare() and mysql_stmt_execute() (see Section 23.8.8, “C API Prepared Statements”), are subject to limitations on caching. Comparison with statements in the query cache is based on the text of the statement after expansion of ? parameter markers. The statement is compared only with other cached statements that were executed using the binary protocol. That is, for query cache purposes, prepared statements issued using the binary protocol are distinct from prepared statements issued using the text protocol (see Section 13.5, “Prepared SQL Statement Syntax”).

预编译语句在mysql_stmt_prepare() 和 mysql_stmt_execute()调用时使用二进制协议(see Section 23.8.8, “C API Prepared Statements”)，在缓存上受到一些限制。与普通语句相比，预编译的语句基于包含了？占位符的文本。普通语句仅仅使用二进制协议比较是否相同。也就是说，为了使用缓存，预编译的sql使用了二级制协议不同于预编译使用的文本协议（(see Section 13.5, “Prepared SQL Statement Syntax”）。

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

如果查询语句中使用了下表中的函数，将不能使用缓存。

BENCHMARK()CONNECTION_ID()CONVERT_TZ()CURDATE()CURRENT_DATE()CURRENT_TIME()CURRENT_TIMESTAMP()CURRENT_USER()CURTIME()DATABASE()ENCRYPT() with one parameterFOUND_ROWS()GET_LOCK()IS_FREE_LOCK()IS_USED_LOCK()LAST_INSERT_ID()LOAD_FILE()MASTER_POS_WAIT()NOW()RAND()RELEASE_ALL_LOCKS()RELEASE_LOCK()SLEEP()SYSDATE()UNIX_TIMESTAMP() with no parametersUSER()UUID()UUID_SHORT()  

A query also is not cached under these conditions:

以下情况也不会使用缓存：

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

• 使用了用户自定义的函数 (UDFs)或存储函数（ 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,INFORMATION_SCHEMA或 performance_schema数据库的表。

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

• (MySQL 5.5.23 and later:) 使用到了分区表
  

• It is of any of the following forms:
  使用了下面列出的sql样式将不使用缓存：

  SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATESELECT ... 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.
  最后一个样式不能使用缓存是因为它使用ODBC workaround来获取最后插入的ID。Connector/ODBC内容请查看Chapter 23, Connectors and APIs。

  Statements within transactions that use SERIALIZABLE isolation level also cannot be cached because they use LOCK IN SHARE MODE locking.
  使用SERIALIZABLE隔离级别的事务也不会使用缓存，因为该级别下使用的是共享锁。

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

• 登录用户在sql关联的任意表中有列级别粒度的权限。