Mysql Query cache description _ MySQL

Mysql Query cache description bitsCN.com

Perform a detailed analysis on mysql Query Cache from five perspectives: Query Cache working principle, how to configure, how to maintain, how to determine the Query Cache performance, and suitable business scenario analysis.

Working Principle

The working principle of the query cache can basically be summarized:
Cache the result set and SQL statements of SELECT operations or pre-processing queries (supported at the beginning of 5.1.17;
The new SELECT statement or pre-processing query statement first queries the cache to determine whether there is an available record set. the criteria are: whether it is exactly the same as the cached SQL statement, case sensitive;

The query statements of the cache cannot be cached. There are roughly the following types:
The SQL _NO_CACHE parameter is added to the query statement;
The query statement contains the functions for obtaining values, including custom functions, such as CURDATE (), GET_LOCK (), RAND (), and CONVERT_TZ;
Query System databases: mysql and information_schema
The query statement uses SESSION-level variables or local variables in the stored procedure;
The lock in share mode and for update statements are used IN the query statement.
The query statement is similar to SELECT... INTO statement for data export;
When the transaction isolation level is Serializable, all query statements cannot be cached;
Query temporary tables;
Query statement with warning information;
Query statements that do not involve any tables or views;
A user can only query statements with column-level permissions;

Advantages and disadvantages of querying cache:

SQL statements do not need to be parsed or executed. of course, syntax parsing must be performed first and Query results can be obtained directly from the Query Cache;
The judgment rules of the query cache are not intelligent enough, which increases the threshold for using the query cache and reduces its efficiency;
When Query Cache is used, the overhead of checking and clearing record sets in Query Cache is increased, and tables cached by SQL statements exist. Each table has only one corresponding global lock;

Configuration

Whether to enable mysql Query cache. Two parameters can be used: query_cache_type and query_cache_size. if any one of the parameters is set to 0, the query cache function is disabled. However, query_cache_type = 0 is recommended for correct settings.

Query_cache_type
Value range: 0-