8.10.3 The MySQL Query Cache MySQL查询缓存

8 Optimization优化

8.10 Buffering and Caching 缓冲和缓存

8.10.1 InnoDB Buffer Pool Optimization

8.10.2 The MyISAM Key Cache

8.10.3 The MySQL Query Cache MySQL查询缓存

The querycache stores the text of a SELECT statement together with the corresponding resultthat was sent to the client. If an identical statement is received later, theserver retrieves the results from the query cache rather than parsing andexecuting the statement again. The query cache is shared among sessions, so aresult set generated by one client can be sent in response to the same queryissued by another client.

查询缓存将SELECT语句的文本与发送给客户端的相应结果存储在一起。如果稍后接收到相同的语句，服务器将从查询缓存中检索结果，而不是重新解析和执行语句。查询缓存是在会话之间共享的，因此由一个客户机生成的结果集可以根据另一个客户机发出的相同查询来发送。

The querycache can be useful in an environment where you have tables that do not changevery often and for which the server receives many identical queries. This is atypical situation for many Web servers that generate many dynamic pages basedon database content.

在表不会经常更改和高耦合的查询环境中非常适合查询缓存。对于许多基于数据库内容生成许多动态页面的Web服务器来说，这是一个典型的情况。

The querycache does not return stale data. When tables are modified, any relevantentries in the query cache are flushed.

查询缓存不会返回过时的数据。当修改表时，查询缓存中的任何相关条目都将被刷新。

Note

The query cachedoes not work in an environment where you have multiple mysqld serversupdating the same MyISAM tables.

请注意

在有多个mysqld服务器更新相同的MyISAM表的环境中，查询缓存无法工作。

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

查询缓存是根据在8.10.3.1节中描述的条件下的准备语句使用的，“查询缓存是如何操作的”。

Note

The query cache isnot supported for partitioned tables, and is automatically disabled for queriesinvolving partitioned tables. The query cache cannot be enabled for suchqueries.

请注意

对分区表不支持查询缓存，对于涉及分区表的查询，自动禁用该查询。

查询缓存不能用于这样的查询。

Someperformance data for the query cache follows. These results were generated byrunning the MySQL benchmark suite on a Linux Alpha 2×500MHz system with 2GB RAMand a 64MB query cache.

下面是查询缓存的一些性能数据。

这些结果是通过运行在Linux Alpha 2 500MHz系统上的MySQL基准测试套件而生成的，它有2GB RAM和64MB的查询缓存。

If all thequeries you are performing are simple (such as selecting a row from a tablewith one row), but still differ so that the queries cannot be cached, theoverhead for having the query cache active is 13%. This could be regarded asthe worst case scenario. In real life, queries tend to be much morecomplicated, so the overhead normally is significantly lower.

如果您所执行的所有查询都很简单(例如从一个表中选择一行)，但是仍然存在差异，因此查询不能缓存，那么查询缓存活动的开销是13%。

这可能被认为是最糟糕的情况。

在现实生活中，查询往往要复杂得多，所以开销通常要低得多。

Searches fora single row in a single-row table are 238% faster with the query cache thanwithout it. This can be regarded as close to the minimum speedup to be expectedfor a query that is cached.

在单行表中搜索单个行比不使用查询缓存快了238%。

这可以被看作是对被缓存的查询所期望的最小加速速度。

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

要在服务器启动时禁用查询缓存，请将querycachesize系统变量设置为0。

通过禁用查询缓存代码，就没有明显的开销。

The querycache offers the potential for substantial performance improvement, but do notassume that it will do so under all circumstances. With some query cacheconfigurations or server workloads, you might actually see a performancedecrease:

查询缓存提供了潜在的性能改进的潜力，但是不要假设它在所有情况下都会这样做。

使用一些查询缓存配置或服务器工作负载，您可能会看到性能下降:

• 
  Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

要谨慎地对查询缓存进行过大的调整，这会增加维护缓存所需的开销，这可能超出了启用缓存的好处。几十兆字节的大小通常是有益的。几百兆字节的大小可能不是。

• Server workload has a significant effect on query cache efficiency. A query mix consisting almost entirely of a fixed set of SELECT statements is much more likely to benefit from enabling the cache than a mix in which frequent INSERT statements cause continual invalidation of results in the cache. In some cases, a workaround is to use the SQL_NO_CACHE option to prevent results from even entering the cache for SELECTstatements that use frequently modified tables. (See Section 8.10.3.2, “Query Cache SELECT Options”.)

服务器工作负载对查询缓存效率有显著影响。几乎完全由一组固定的SELECT语句组成的查询混合，比使用频繁的INSERT语句在缓存中导致结果连续失效的组合更有可能从启用缓存中获益。在某些情况下，一个解决方案是使用SQL_NO_CACHE选项来防止结果甚至进入使用频繁修改的表的SELECT语句。(参见8.10.3.2，“查询缓存选择选项”)。

To verify thatenabling the query cache is beneficial, test the operation of your MySQL serverwith the cache enabled and disabled. Then retest periodically because querycache efficiency may change as server workload changes.

要验证启用查询缓存是有好处的，可以使用启用和禁用的缓存来测试MySQL服务器的操作。然后周期性地进行重新测试，因为随着服务器工作负载的变化，查询缓存效率可能会发生变化。

8.10.3.1 How the Query Cache Operates如何操作查询缓存

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

本节描述查询缓存在运行时是如何工作的。第8.10.3.3，“查询高速缓存配置”，描述了如何控制它是否可以运行。

Incoming queries are comparedto those in the query cache before parsing, so the following two queries areregarded as different by the query cache:

在解析之前，将传入查询与查询缓存中的查询进行比较，因此以下两个查询在查询缓存中被认为是不同的:

SELECT * FROM tbl_name

Select * from tbl_name

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

查询必须完全相同(字节为字节)，才能被视为相同。此外，由于其他原因，相同的查询字符串可能被视为不同的。使用不同的数据库、不同的协议版本或不同的默认字符集的查询被视为不同的查询，并且被单独缓存。

The cache is not used forqueries of the following types:

缓存不用于以下类型的查询:

·        Queries that are a subqueryof an outer query

·        Queries executed within thebody of a stored function, trigger, or event

查询是外部查询的子查询

在存储函数、触发器或事件体中执行的查询

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

在从查询缓存获取查询结果之前，MySQL检查用户是否为所涉及的所有数据库和表的select权限。如果没有权限，则不会使用缓存的结果。

If a query result is returnedfrom query cache, the server increments the Qcache_hits statusvariable, notCom_select. See Section 8.10.3.4, “Query Cache Status andMaintenance”.

如果查询结果从查询缓存返回，服务器将增加Qcache_hits，而不是Com_select。参见8.10.3.4，“查询缓存状态和维护”。

If a table changes, allcached queries that use the table become invalid and are removed from thecache. This includes queries that use MERGE tables that map to thechanged 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.

The query cache also workswithin 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() returnsthe correct value even if the preceding query was fetched from the cachebecause the number of found rows is also stored in the cache. The SELECT FOUND_ROWS() query itself cannot becached.

查询缓存适用于SELECTSQL_CALC_FOUND_ROWS ...查询并存储由以下SELECT FOUND_ROWS()查询返回的值。FOUND_ROWS()返回正确的值，即使前面的查询是从缓存中获取的，因为已找到的行数也存储在缓存中。SELECT FOUND_ROWS()查询本身不能被缓存。

Prepared statements that areissued using the binary protocol using mysql_stmt_prepare() andmysql_stmt_execute() (see Section 27.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 thestatement after expansion of? parameter markers. The statement is compared only with othercached statements that were executed using the binary protocol. That is, forquery cache purposes, prepared statements issued using the binary protocol aredistinct from prepared statements issued using the text protocol (see Section 13.5,“Prepared SQL Statement Syntax”).

使用mysql_stmt_prepare()和mysql_stmt_execute()使用二进制协议发出的准备语句(见第27.8.8，  “CAPI Prepared Statements”)，在缓存上有限制。与查询缓存中的语句的比较是基于扩展后的语句的文本?参数标记。该语句只与使用二进制协议执行的其他缓存语句进行比较。也就是说，对于查询缓存的目的，使用二进制协议发出的准备语句与使用文本协议发出的准备语句不同(参见第13.5节“准备好的SQL语句语法”)。

A query cannot be cached ifit 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()
CURRENT_USER()	CURTIME()	DATABASE()
ENCRYPT() with one parameter	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()

A query also is not cachedunder these conditions:

在以下条件下，查询也不会被缓存:

·        It refers to user-definedfunctions (UDFs) or stored functions.

·        它是指用户定义的函数(udf)或存储函数。

·        It refers to user variablesor local stored program variables.

·        它指的是用户变量或本地存储程序变量

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

·        它指的是mysql、信息模式或性能模式数据库中的表。

·        It refers to any partitionedtables.

·        It is of any of the followingforms:

·         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 ODBCworkaround for obtaining the last insert ID value. See the Connector/ODBCsection of Chapter 27, Connectors and APIs.

最后一个没有被缓存，因为它被用作获取最后一个insert ID值的ODBC工作区。参见Chapter 27, Connectors and APIs.的the Connector/ODBC部分。

Statements within transactions that use SERIALIZABLE isolationlevel also cannot be cached because they use LOCK IN SHARE MODE locking.

使用可序列化隔离级别的事务中的语句也不能缓存，因为它们使用锁在共享模式锁定中

·        It uses TEMPORARY tables.

·        It does not use any tables.

·        It generates warnings.

·        The user has a column-levelprivilege for any of the involved tables.

8.10.3.2 Query Cache SELECT Options查询缓存的select选项

Two querycache-related options may be specified in SELECT statements:

两个查询缓存相关的选项可以在SELECT语句中指定:

•  SQL_CACHE

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

如果查询结果是可缓存的，那么查询结果就会被缓存，而query_cache_type 系统变量的值是ON或DEMAND。

• SQL_NO_CACHE

The server does not use the query cache. Itneither checks the query cache to see whether the result is already cached, nordoes it cache the query result.

服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已经缓存，也不缓存查询结果。

Examples:

SELECT SQL_CACHEid, name FROM customer;

SELECTSQL_NO_CACHE id, name FROM customer;

8.10.3.3 Query Cache Configuration查询缓存配置

The have_query_cache server system variable indicateswhether the query cache is available:

有have_query_cache服务器系统变量表示查询缓存是否可用:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

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

|Variable_name    | Value |

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

| have_query_cache| YES   |

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

When using astandard MySQL binary, this value is always YES, even if querycaching is disabled.

当使用标准的MySQL二进制时，这个值总是YES，即使禁用了查询缓存。

Several othersystem variables control query cache operation. These can be set in an optionfile or on the command line when starting mysqld.The query cache system variables all have names that begin withquery_cache_. They aredescribed briefly in Section 5.1.5,“Server System Variables”, with additional configuration informationgiven here.

其他几个系统变量控制查询缓存操作。在启动mysqld时，可以在选项文件或命令行中设置这些选项。查询缓存系统变量都有以query_cache开头的名称。在5.1.5节，“服务器系统变量”中简要描述了它们，并给出了额外的配置信息。

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

要设置查询缓存的大小，设置query_cache_size系统变量。将其设置为0将禁用查询缓存，设置 query_cache_type=0也是如此。默认情况下，查询缓存是禁用的。这是使用默认大小为100万的默认值，默认为0的query_cache_type。

To reduce overheadsignificantly, start the server with query_cache_type=0 if you will not be using thequery cache.

为了显著减少开销，如果您不使用查询高速缓存，可以使用query_cache_type=0启动服务器。

Note

When using theWindows Configuration Wizard to install or configure MySQL, the default valueforquery_cache_size will be configured automaticallyfor you based on the different configuration types available. When using theWindows Configuration Wizard, the query cache may be enabled (that is, set to anonzero value) due to the selected configuration. The query cache is alsocontrolled by the setting of the query_cache_type variable. Check the values of thesevariables as set in your my.ini file after configuration has takenplace.

请注意

当使用Windows配置向导来安装或配置MySQL时，将根据可用的不同配置类型为您自动配置query_cache_size的默认值。当使用Windows配置向导时，可以启用查询缓存(也就是说，设置为非零值)，这是因为所选的配置。查询缓存也由query_cache_type 变量的设置控制。检查这些变量的值，就像我的设置一样。配置完成后的 my.ini文件。

When you set query_cache_size to a nonzero value, keep in mindthat the query cache needs a minimum size of about 40KB to allocate itsstructures. (The exact size depends on system architecture.) If you set thevalue too small, you'll get a warning, as in this example:

当您将 query_cache_size设置为非零值时，请记住，查询缓存需要大约40KB的最小大小来分配它的结构。(具体的大小取决于系统架构。)如果设置的值太小，就会得到警告，如本例中所示:

mysql> SET GLOBAL query_cache_size = 40000;

Query OK, 0 rowsaffected, 1 warning (0.00 sec)

 

mysql> SHOW WARNINGS\G

***************************1. row ***************************

  Level: Warning

   Code: 1282

Message: Querycache failed to set size 39936;

         new query cache size is 0

 

mysql> SET GLOBAL query_cache_size = 41984;

Query OK, 0 rowsaffected (0.00 sec)

 

mysql> SHOW VARIABLES LIKE 'query_cache_size';

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

|Variable_name    | Value |

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

| query_cache_size| 41984 |

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

For the querycache to actually be able to hold any query results, its size must be setlarger:

对于查询缓存实际上能够保存任何查询结果，它的大小必须设置得更大:

mysql> SET GLOBAL query_cache_size = 1000000;

Query OK, 0 rowsaffected (0.04 sec)

 

mysql> SHOW VARIABLES LIKE 'query_cache_size';

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

|Variable_name    | Value  |

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

| query_cache_size| 999424 |

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

1 row in set (0.00sec)

The query_cache_size value is aligned to the nearest1024 byte block. The value reported may therefore be different from the valuethat you assign.

query_cache_size值与最近的1024字节块对齐。因此，所报告的值可能与您分配的值不同。

If the query cachesize is greater than 0, the query_cache_type variable influences how it works.This variable can be set to the following values:

如果查询缓存的大小大于0，那么query_cache_type变量会影响它的工作方式。该变量可以设置为以下值:

• A value of 0 or OFF prevents caching or retrieval of cached results.
• 值为0或OFF，可以防止缓存或检索缓存结果。
• A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.
• 除了从SELECT SQL_NO_CACHE.开始的那些语句之外，值1或ON可以启用缓存。
• A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
• 一个值为2或需求的值只会导致那些从SELECT SQL_CACHE.开始的语句的缓存。

If query_cache_size is 0, you should also set query_cache_type variable to 0. In this case, theserver does not acquire the query cache mutex at all, which means that thequery cache cannot be enabled at runtime and there is reduced overhead in queryexecution.

如果query_cache_size是0，那么您也应该将query_cache_type变量设置为0。在这种情况下，服务器根本没有获得查询缓存互斥锁，这意味着查询缓存不能在运行时启用，并且在查询执行中减少了开销。

Setting the GLOBAL query_cache_type value determines query cachebehavior for all clients that connect after the change is made. Individualclients can control cache behavior for their own connection by setting theSESSION query_cache_type value. For example, a client candisable use of the query cache for its own queries like this:

设置GLOBAL query_cache_type值将确定在更改之后连接的所有客户机的查询缓存行为。单独的客户机可以通过设置SESSION query_cache_type值来控制其自身连接的缓存行为。例如，客户端可以禁用查询缓存的使用，例如:

mysql> SET SESSION query_cache_type = OFF;

If you set query_cache_type at server startup (rather than atruntime with a SET statement), only the numeric values arepermitted.

如果您在服务器启动时设置query_cache_type (而不是在运行时使用SET语句)，那么只允许数值值。

To control themaximum size of individual query results that can be cached, set the query_cache_limit system variable. The default valueis 1MB.

为了控制可以缓存的单个查询结果的最大大小，设置query_cache_limit系统变量。默认值是1MB。

Be careful not toset the size of the cache too large. Due to the need for threads to lock thecache during updates, you may see lock contention issues with a very largecache.

注意不要将缓存的大小设置得太大。由于在更新期间需要线程来锁定缓存，所以您可能会看到一个非常大的缓存的锁争用问题。

Note

You can set themaximum size that can be specified for the query cache at runtime withthe SETstatement by using the --maximum-query_cache_size=32M option onthe command line or in the configuration file.

请注意

您可以在运行时使用set语句在命令行或配置文件中使用 --maximum-query_cache_size=32M选项来设置可为查询缓存指定的最大大小。

When a query is tobe cached, its result (the data sent to the client) is stored in the querycache during result retrieval. Therefore the data usually is not handled in onebig chunk. The query cache allocates blocks for storing this data on demand, sowhen one block is filled, a new block is allocated. Because memory allocationoperation is costly (timewise), the query cache allocates blocks with a minimumsize given by thequery_cache_min_res_unit system variable. When aquery is executed, the last result block is trimmed to the actual data size sothat unused memory is freed. Depending on the types of queries your serverexecutes, you might find it helpful to tune the value of query_cache_min_res_unit:

当一个查询被缓存时，它的结果(发送给客户机的数据)在结果检索期间被存储在查询缓存中。因此，数据通常不会在一个大数据块中处理。查询缓存为按需存储此数据而分配块，因此当一个块被填满时，就会分配一个新的块。因为内存分配操作是代价高昂的(时间)，查询缓存分配由query_cache_min_res_unit系统变量提供的最小大小的块。当执行一个查询时，最后一个结果块会被缩减到实际的数据大小，这样就可以释放未使用的内存。取决于您的服务器执行的查询类型，您可能会发现调整query_cache_min_res_unit的值是有帮助的:

• The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.

·        query_cache_min_res_unit 的默认值是4KB。对于大多数情况，这应该足够了。

• If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value ofquery_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.
• 如果您有许多小的查询，那么默认的块大小可能会导致内存碎片，这是由大量的空闲块所指示的。由于缺乏内存，碎片化可能迫使查询缓存从缓存中删除(删除)查询。在这种情况下，减少query_cache_min_res_unit的值。由Qcache_free_blocks和Qcache_lowmem_prunes修剪的状态变量的值给出了删除的空闲块和查询的数量。
• If most of your queries have large results (check the Qcache_total_blocks andQcache_queries_in_cache status variables), you can increase performance by increasingquery_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

如果您的大多数查询都有较大的结果(检查Qcache_total_blocks和Qcache_queries_in_cache状态变量)，您可以通过增加query_cache_min_res_unit来提高性能。但是，要小心不要使它太大(请参阅前面的项目)。

8.10.3.4 Query Cache Status and Maintenance查询缓存的状态和维护

To check whether the querycache is present in your MySQL server, use the following statement:

要检查查询缓存是否存在于MySQL服务器中，请使用以下语句:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

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

| Variable_name    | Value |

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

| have_query_cache | YES   |

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

Youcan defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does notremove any queries from the cache.

您可以对查询缓存进行碎片整理，以更好地利用刷新查询缓存FLUSH QUERY CACHE 语句来使用它的内存。该语句不会删除缓存中的任何查询。

The RESET QUERY CACHE statement removes all queryresults from the query cache. The FLUSH TABLESstatement also doesthis.

RESET QUERY CACHE语句将从查询缓存中删除所有查询结果。FLUSH TABLES语句也是这样做的。

Tomonitor query cache performance, use SHOW STATUS to view the cache status variables:

为了监视查询缓存的性能，使用 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    |

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

Descriptionsof each of these variables are given in Section 5.1.7,“Server Status Variables”. Some uses for them are described here.

每个变量的描述都在 Section 5.1.7,“Server Status Variables”。这里描述了它们的一些用途。

Thetotal number of SELECT queries is given by this formula:

SELECT查询的总数是由这个公式给出的:

  Com_select

+ Qcache_hits

+ queries with errors found by parser

The Com_select value is given by thisformula:

这个公式给出了Com_select值:

  Qcache_inserts

+ Qcache_not_cached

+ queries with errors found during the column-privileges check

Thequery cache uses variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memoryfragmentation. After FLUSH QUERY CACHE, only a singlefree block remains.

查询缓存使用变长块，因此 Qcache_total_blocks和 Qcache_free_blocks可能指示查询缓存内存碎片。在FLUSH QUERY CACHE之后，只剩下一个空闲块。

Everycached query requires a minimum of two blocks (one for the query text and oneor more for the query results). Also, every table that is used by a queryrequires one block. However, if two or more queries use the same table, onlyone table block needs to be allocated.

每个缓存的查询至少需要两个块(一个用于查询文本，一个或多个用于查询结果)。此外，查询使用的每个表都需要一个块。但是，如果两个或多个查询使用相同的表，那么只需要分配一个表块。

Theinformation provided by the Qcache_lowmem_prunes status variable can help you tune thequery cache size. It counts the number of queries that have been removed fromthe cache to free up memory for caching new queries. The query cache uses aleast recently used (LRU) strategy to decide which queries to remove from thecache. Tuning information is given in Section 8.10.3.3,“Query Cache Configuration”.

Qcache_lowmem_prunes状态变量提供的信息可以帮助您调优查询缓存大小。它计算从缓存中删除的查询数量，以释放用于缓存新查询的内存。查询缓存使用最近使用的(LRU)策略来决定从缓存中删除哪些查询。在Section 8.10.3.3,“Query Cache Configuration”中给出了调优信息。

8.10.4 Caching of Prepared Statements and Stored Programs