High-performance MySql evolution (13): Query Cache Mechanism

Many database systems can cache execution plans. For identical SQL statements, you can use existing execution plans to skip the process of parsing and generating execution plans. MYSQL and Oracle provide more advanced query result caching functions. For SQL statements with identical strings and Case sensitivity, You can execute the return query statement.

Many database systems can cache execution plans. For identical SQL statements, you can use existing execution plans to skip the process of parsing and generating execution plans. MYSQL and Oracle provide more advanced query result caching functions. For SQL statements with identical strings and Case sensitivity, You can execute the return query statement.

Many database systems can cache execution plans. For identical SQL statements, you can use existing execution plans to skip the process of parsing and generating execution plans. MYSQL and Oracle provide more advanced query result caching functions. For SQL statements with identical strings and Case sensitivity, the query results can be returned. This article mainly introduces some features of MYSQL query cache, Oracle query cache can refer to http://www.oracle.com/technetwork/articles/ SQL /11g-caching-pooling-088320.html

If you have a table that does not change frequently and the server receives a large number of identical queries for the table, the query cache is very useful in such an application environment. This is a typical case for many Web servers. It generates a large number of dynamic pages based on the database content.

1. query cache Working Mechanism

The method for Mysql to determine whether to hit the cache is very simple. First, the results to be cached are placed in the reference table, and then a hash value is calculated using the query statement, database name, client Protocol version, and other factors, the hash value is associated with the result in the referenced table. If the hash value calculated based on some related conditions can be associated with the data in the referenced table during query execution, it indicates that the query hits

The system variables of the have_query_cache server indicate whether the cache is available:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

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

| Variable_name    | Value |

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

| have_query_cache | YES   |

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

To monitor query cache performance, use show status to View cache STATUS variables:

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    |

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

1.1 query cache failure scenarios

No matter whether the query cache mechanism has advantages or disadvantages, first check which scenarios will cause the cache mechanism to fail.

1. If the query statement contains some uncertainties (such as the function Current (), the query will not be cached. the uncertain factors mainly include the following situations:

· Referenced some functions with uncertain return values

BENCHMARK ()	CONNECTION_ID ()	CURDATE ()
CURRENT_DATE ()	CURRENT_TIME ()	CURRENT_TIMESTAMP ()
CURTIME ()	DATABASE ()	ENCRYPT () with a parameter ()
FOUND_ROWS ()	GET_LOCK ()	LAST_INSERT_ID ()
LOAD_FILE ()	MASTER_POS_WAIT ()	NOW ()
RAND ()	RELEASE_LOCK ()	SYSDATE ()
UNIX_TIMESTAMP () without Parameters ()	USER ()

· Reference UDFs ).

· Reference custom variables.

· Reference tables in the mysql System database.

· Any of the following methods:

SELECT... IN SHARE MODE

SELECT... FOR UPDATE

SELECT... into outfile...

SELECT... into dumpfile...

SELECT * FROM... WHERE autoincrement_col IS NULL

· Compiled statements, even if no Placeholders are used. For example, the following query is used:

char *my_sql_stmt = "SELECT a，b FROM table_c";

   /* ...*/

mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));

Not cached.

· Use the TEMPORARY table.

· No tables are used.

· You have column-level permissions for a table.

1.2 Additional consumption

If query cache is used, additional resource consumption will occur during read/write operations. The consumption is mainly reflected in the following aspects:

· Check whether the cache is hit during query, which consumes a relatively small amount.

· If the query cache is not hit, MYSQL will determine whether the query can be cached and the system does not have the corresponding cache. Then, the results will be written to the query cache.

· If a table is changed, all buffered queries using that table will no longer be valid and will be removed from the buffer. This includes queries for tables mapped to changed tables that use MERGE tables. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database.

For InnoDB, some features of transactions also limit the use of query cache. When table B is modified in thing A, because modification to table B is invisible to other things before the transaction is committed. To ensure the correctness of the cache results, InnoDB takes measures so that all queries involving the B table cannot be cached before transaction A is committed. If transaction A runs for A long time, it will seriously affect the query cache hit rate.

Do not set the cache space to too large.

Because the query cache is protected by a global Lock operation, if the memory configured for the query cache is large and contains a large number of query results, when the query cache fails, hold the global lock for a long time. Because the query cache hit detection operations and cache failure detection depend on this global lock, the system may be frozen.

1.3 configure Parameters

MYSQL provides some parameters to control the query cache behavior. The parameters are as follows:

·Query_cache_limit

The maximum query result that MYSQL can cache. If the query result is greater than this value, it is not cached. The default value is 1048576 (1 MB)

If the result of a query exceeds this value, the value of Qcache_not_cached is incremented by 1. If an operation is always exceeded, you can add SQL _NO_CACHE to SQL to avoid additional consumption.

·Query_cache_min_res_unit

Query the size (in bytes) of the minimum block allocated by the cache ). The default value is 4096 (4 kb ).

·Query_cache_size

The number of memories allocated to cache query results. The unit is byte and the value must be an integer multiple of 1024. The default value is 0, indicating that the query cache is disabled. Please note that this amount of memory will be allocated even if query_cache_type is set to 0.

·Query_cache_type

Set the query cache type. You can set the GLOBAL value to set the connection types of all clients. The client can set the SESSION value to affect their use of the query cache. The following table shows possible values:

Option	Description
0 or OFF	Do not cache or query results. Note that this will not cancel the allocated query cache. To cancel the task, set query_cache_size to 0.
1 or ON	Cache all query results starting with SELECT SQL _NO_CACHE.
2 or DEMAND	Only query results starting with SELECT SQL _NO_CACHE are cached.

This variable is set to ON by default.

·Query_cache_wlock_invalidate

Generally, when the client locks the MyISAM table, if the query result is in the query cache, other clients are not locked and can query the table. If this variable is set to 1, the WRITE lock can be performed on the table to make all queries on the table in the query cache invalid. In this way, when the lock takes effect, you can force other clients trying to access the table to wait.

2. cache query optimization process

After the cache query function is enabled, you can use some parameters and Status values to check the Cache Usage.

For procedures and parameters, see

3 query Cache Optimization

In addition to some of the optimization policies mentioned, you can also use the following measures to improve the query cache efficiency.

· Try to replace large tables with complex queries with small tables

· Try to replace a single write with batch write

· Control the query_cache_size, or even disable the query cache.

· Use DEMAND + SQL _CACHE/SQL _NO_CACHE to flexibly control whether a select statement needs to be cached.

· Directly disable query cache for write-intensive applications

4 Reference

For more information, see: http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache