MySQL query high-speed buffer detailed

Query Caching Overview

The query cache stores the text of a select query and the corresponding results sent to the client. If you subsequently receive the same query, the server re-obtains the query results from the query cache, eliminating the need to parse and execute the query. If you have a table that does not change frequently and the server receives a large number of the same queries for that table, it is useful to have queries that are slow to exist in such an application environment. This is typical for many Web servers, which generate a large number of dynamic pages based on the contents of the database.
Note 1. The query cache does not return old data. When the table changes (such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database operation), the associated entry for the query cache value is emptied. 2. If you have many MYSQLD servers that update the same MyISAM table, the query cache does not work in this case. 3. The query cache does not apply to statements written by the server side. If you are using statements written by the server side, consider that the query cache will not be applied to these statements.

Querying how high-speed buffering works

When the MySQL server receives a query request, the MySQL server first checks the user's Select permissions on all related databases and tables, such as permission passes, The SQL literal is then used as the key to retrieve the same key from the query cache (because the data cache is stored as a key in SQL text), and if the corresponding key value is found from the query buffer, the server takes the Qcache_ Hits the value of the state variable plus one, without the need for the parser to parse the SQL statement, if the corresponding key value is not found, then perform SQL parsing, and then query, you can refer to.

Note:
1. The SQL statement that is the key value is case-sensitive, which is SELECT * FROM t1 select * from t1 not the same as the corresponding key value. Therefore, if query buffering is turned on, you should be aware of this when writing SQL statements.
2. If a table is changed, all buffered queries that use that table will no longer be valid and removed from the buffer, ensuring consistency in the data (i.e. data consistency in the table and in the buffer).

Querying the cache configuration

1. See if the query buffer can
In MySQL, you can indicate whether the query cache is available through the Have_query_cache server system variable. Even if query caching is disabled, this value is always yes when using standard MySQL binaries.

2. Other important configuration parameters
The query cache system variable name starts with Query_cache_, as we'll explain each one.

• Having_query_cache
  is not in detail, mainly to control the query buffer to open or not.
• Query_cache_limit
  The maximum number of specific query results that a single query can be cached.
• Query_cache_min_res_unit
  This parameter is the size of the memory block allocated when the buffer is controlled for storage queries. When the query is carried out, but if the result is larger than the value of Query_cache_min_res_unit, then MySQL will retrieve the results, while the results are saved, so sometimes not all the results are obtained and then saved once, Instead of allocating a chunk of query_cache_min_res_unit-sized memory space to save the result set, after use, then allocate one such block, if not enough, then allocate a block, and so on, that is, it is possible in a query, MySQL is going to do multiple memory allocations. The default value of Query_cache_min_res_unit is 4KB. If you have a large number of queries that return small result data, the default chunk size may cause memory fragmentation and display as a large chunk of free memory. Memory fragmentation forces the query cache to trim (delete) queries from cache memory due to lack of memory. At this point, you should reduce the value of the Query_cache_min_res_unit variable, and the number of free blocks and queries that are removed due to trimming is given by the values of the qcache_free_blocks and qcache_lowmem_prunes variables. Of course if a large number of queries return large results (check qcache_total_blocks and Qcache_queries_in_cache state variables), you can increase the sex by increasing the value of the Query_cache_min_res_unit variable.

• Query_cache_size
  Query cache size, set to 0 to disable query caching. The default cache size is set to 0, or the query cache is disabled. When setting the Query_cache_size variable to a value other than 0, remember that the query cache needs at least approximately 40KB to allocate its data structure. (The exact size depends on the system structure). If you set the value too small, you will get a warning and the Query_cache_size value will be set to 0.
  

• Query_cache_type
  The main parameter is to control the caching behavior. The global value of the Query_cache_type variable determines the caching behavior of all connected clients after the change. Under Linux, you can configure it in MY.CNF (under Win, in My.ini). Specific clients can control the caching behavior of their own connections by setting the session value of the Query_cache_type variable. For example, a customer can disable his own query cache. There are three values for this parameter:
  
  • 0 or off
    Cache or query cache results will be blocked
  • 1 or on
    will allow caching, except for query statements that start with select Sql_no_cache.
  • 2 or demand
    Enable caching only for those query statements starting with select Sql_cache

Querying for cache maintenance fragmentation cleanup

In the preceding, we can learn that using cache, unavoidable memory fragmentation, we can use the Flush query cache statement to clean up the query buffer fragmentation, to improve memory usage performance. The statement does not remove any queries from the cache. The RESET query cache statement removes all queries from the query buffer. The FLUSH Tables statement also performs the same work, distinguishing optimize table tableName, with reference to optimize (http://blog.csdn.net/hsd2012/article/details/51485250).

Query Cache Performance Monitoring

To monitor query cache performance, use Show status to view cache state variables:

Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. Executing flush QUERY Cache organizes the fragments in the cache, preserving only one free block.
Qcache_free_memory: Free memory in the cache.
Qcache_hits: Increases each time the query hits the cache
Qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.
Qcache_lowmem_prunes: The cache is out of memory and must be moved out of the query buffer to the number of free memory queries in order to cache the new query. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The Qcache_free_blocks and qcache_free_memory above can tell you which is the case). Removes the query buffer from the query buffer, using the least recently used (LRU) policy.
Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().
Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.
Qcache_total_blocks: The number of blocks in the cache.
Qcache_total_blocks and Qcache_free_blocks can display query cache memory fragmentation.

Note
The above narrative, the main reference MySQL manual.

MySQL query high-speed buffer detailed