MySQL 4.1.0 Chinese reference manual---6.9 mysql query cache

mysql| Reference | Reference Manual | cache | Chinese MySQL 4.1.0 chinese reference manual---dog (heart sail) Translate MySQL Reference Manual for version 4.1.0-alpha.

6.9 MySQL Query Cache


Starting with MySQL 4.0.1, MySQL server has an important feature: Query Cache. When in use, the query cache stores the text of a SELECT query and the corresponding results that are delivered to the client. If you receive a similar query later, the server retrieves the results from the query cache instead of parsing and executing the same query again.

Note: The query cache never returns expired data. When the data is modified, any related entries in the query cache are dumped and purged.

Query caching is useful when some tables do not change frequently, and you perform a large number of identical queries against it. This is a typical scenario for many WEB servers using a lot of dynamic information.

The following is a performance data for the query cache. (These results are generated by executing the MySQL benchmark suite and to the on a Linux Alpha 2 x MHz, 2GB RAM, and 64MB query cache):
If all of the queries you execute are simple (such as selecting one row from the table), but still different, the query cannot be buffered and the query cache is active with a cost of 13%. This can be seen as the worst case scenario. In practice, however, the query is much more complex than our simple example, so the overhead is usually significantly lower. After searching for a row in only one row of records, the search will be 238% faster. This can be considered to be close to the minimum acceleration expected for a buffered query. If you want to disable query caching, set query_cache_size=0. The query cache is disabled and there is no significant overhead. (with the help of the configuration option--without-query-cache, the query cache can be excluded from the outside code) 6.9.1 how the query cache works
The query is compared before parsing, so

SELECT * from Tbl_name

And

Select * from Tbl_name

The query cache is treated as a different query, so the query needs to be strictly consistent (byte to byte) before it is considered the same. In addition, if a client uses a new connection protocol format or another character set that differs from other clients, a query is considered different.

Queries using different databases, using different protocol versions, or using different default strings will be considered different queries and will be buffered separately.

High-speed buffering does not SELECT calc_rows ... and SELECT found_rows () ... The query for the type works because the number of rows found is also stored in the buffer.

If the query result is returned from the query cache, then the state variable com_select will not be incremented, but the qcache_hits will increase. View Chapter 6.9.4 Query cache status and maintenance.

If a table changes (INSERT, UPDATE, DELETE, TRUNCATE, Alter, or DROP table| DATABASE), then all this table uses the buffered query (possibly through a mrg_myisam table!). will be invalidated and removed from the buffer.

Changes to the InnoDB table's transactions invalidate the data when a COMMIT is completed.

If a query includes the following function, it will not be buffered: function function function user-defined functions connection_id found_rows get_lock release_lock load_file Master_po S_wait now sysdate current_timestamp curdate current_date curtime current_time DATABASE ENCRYPT (only one parameter invocation) last_insert_id RAND unix_timestamp (no parameter calls) USER BENCHMARK


If a query contains user variables, refer to the MySQL system database, or one of the following formats, SELECT ... In SHARE MODE, SELECT ... Into outfile ..., SELECT ... Into DumpFile ... or SELECT * from Autoincrement_field is NULL (retrieves the last insert ID-ODBC statement), and the query cannot be cached.

However, FOUND ROWS () will return the correct value even if the previous query was read from the cache.

If a query does not use any tables, or if a temporary table is used, or if the user has a column permission on any related table, the query will not be cached.

Before a query is read from the query cache, MySQL checks that the user has SELECT permissions on all related databases and tables. If this is not the case, the cached results will not be used.
6.9.2 Query Cache Settings
Query caching adds several MySQL system variables for mysqld, which can be set in the configuration file or on the command line when the mysqld is started.
Query_cache_limit does not cache results that are greater than this value. (Default is 1M)

Query_cache_min_res_unit This variable was introduced from 4.1. The results of the query (data that has been routed to the client) are stored in the query cache during the result retrieval. Thus, the data will not be processed in a large way. The query cache allocates blocks to handle this data when needed, so when a block is populated, a new block is allocated. Very memory allocation operations are expensive, and query caching allocates blocks query_cache_min_res_unit the smallest size. When a query completes, the final result block is trimmed to the size of the actual data so that unused memory is freed. The default value for Query_cache_min_res_unit is 4 KB, which is sufficient in most cases. If you have many queries that return a smaller result, the default block size may cause memory fragmentation (shown as a large number of free blocks (qcache_free_blocks), which will cause the query cache to suffer from a lack of memory (Qcache_lowmem_prunes) and delete the query from the cache. In this case, you should reduce the query_cache_min_res_unit. If your main query returns a large result set (see Qcache_total_blocks and Qcache_queries_in_cache), you can increase the performance by adding query_cache_min_res_unit. However, be careful not to set it too large.

Query_cache_size The amount of memory (in bytes) allocated to store old query results. If it is set to 0, the query buffer is blocked (the default is 0). Query_cache_type This can be set to (only a number) option meaning 0 (off, do not cache or regain results) 1 (on, cache all results except SELECT sql_no_cache ...) Query) 2 (DEMAND, cache only SELECT Sql_cache ... Query
Within a thread (connection), the behavior of the query cache can be changed. The syntax looks like this:

Query_cache_type = Off | On | DEMAND Query_cache_type = 0 | 1 | 2
Option meaning 0 or off does not cache or regain results 1 or on cache all results except SELECT Sql_no_cache ... Query 2 or DEMAND only cache SELECT Sql_cache ... Query 6.9.3 query caching options in SELECT
There are two possible query cache-related parameters that can be specified in a SELECT query:


option meaning Sql_cache if Query_cache_type is DEMAND, allow the query to be cached. If Query_cache_type is on, this is the default. If Query_cache_type is off, it does nothing sql_no_cache so that the query is not cached and the query is not allowed to be stored in the cache 6.9.4 the state and maintenance of the query cache
Using the FLUSH Query cache command, you can organize query caching to better utilize its memory. This command does not remove any queries from the cache. FLUSH TABLES dumps the purge query cache.

RESET Query Cache mission removes all query results from the query buffer.

You can check whether the query cache is introduced in your MySQL:

Mysql> show VARIABLES like ' have_query_cache '; +------------------+-------+| variable_name | Value |+------------------+-------+| Have_query_cache | YES |+------------------+-------+1 row in Set (0.00 sec)

In Show STATUS, you can monitor the performance of query caching:
Variable meaning qcache_queries_in_cache number of queries registered in the cache qcache_inserts number of queries added to the cache qcache_hits number of cached samples Qcache_lowmem_prunes The number of queries removed from the cache because of lack of memory qcache_not_cached the number of queries not cached (which cannot be cached, or due to Query_cache_type) qcache_free_memory The total amount of free memory for the query cache qcache_ Free_blocks the number of free memory blocks in the query cache Qcache_total_blocks The total number of blocks in the query cache
Total number of queries = Qcache_inserts + qcache_hits + qcache_not_cached.

The query cache uses variable-length blocks, so qcache_total_blocks and qcache_free_blocks may display fragments of the query cache. After FLUSH QUERY CACHE, only a single (large) free block remains.

Note: Each query requires a minimum of two blocks (one for storing the query text and another or more for storing the query results). Similarly, each table used by a query requires a block, but if two or more queries use the same table, just allocate a block.

You can use the state variable qcache_lowmem_prunes to harmonize the query cache size. It counts the queries that were removed from the cache, and the query was removed to free memory to cache the newly created query. The query cache uses a least recently used (LRU) policy to determine which query is removed from the cache.