MySQL Query cache optimizer

Query Cache principle

When MySQL receives a select type of query, MySQL hashes the query to get a hash value and then matches the hash value to the query cache, if there is no match, The hash value is stored in a hash list, and the result set of query is stored in the cache, and each hash node of the linked list that holds the hash value holds the address of the corresponding query result set in the cache. And some table related information that is involved in the query, and if the hash value matches the same query, the corresponding query result set in the cache is returned to the client directly. If any of the data in any of the tables in MySQL changes, the query cache is notified that the cache for the query associated with the table is invalidated and frees the memory address that is occupied.

Query Cache pros and cons

The advantage is obvious that for some frequent select Query,mysql to return the corresponding result set directly from the cache without having to remove it from the table tables, the IO overhead is reduced.

Even though the benefits of query cache are obvious, you cannot overlook some of the drawbacks that it brings:

1. The hash calculation of the query statement and the resource consumption resulting from the hash lookup. MySQL will hash the query for each received select type and then find out if the query's cache exists, although the hash calculation and lookup efficiency is high enough, the cost of a query can be ignored, but when it comes to high concurrency, When there are thousands of query, the cost of hash calculation and lookup is paid attention to;

2. The failure of the query cache. If the table changes more frequently, it will result in a very high loss of query cache. Table changes not only refer to changes in the data in the table, but also any changes in structure or index;

3. Query for different SQL but the same result set is cached, which results in a transition in memory resource consumption. SQL is different from character case, space, or comment, the cache is considered different SQL (because their hash value will be different);

4. The relevant parameter setting is unreasonable and will cause a lot of memory fragmentation, the related parameter setting will be introduced later.

Make reasonable use of query cache

With the pros and cons of query cache, using query cache wisely can make it an advantage and effectively avoid its disadvantages.

1. Not all tables are suitable for use with query cache. The reason why the query cache is invalidated is that the corresponding table has changed, so you should avoid using query cache on a table that is changing frequently. There are two dedicated SQL Hint:sql_no_cache and Sql_cache for query cache in MySQL, respectively, to force the use of query cache without using and forcing, by forcing the query cache to not be used, It allows MySQL to not use the query cache on frequently changing tables, which reduces memory overhead and reduces the cost of hashing and finding;

2. Set reasonable parameter variables and state variables.

First look at the system variables of query cache.

650) this.width=650; "src=" Http://my.csdn.net/uploads/201208/12/1344743817_5552.png "/>

• Have_query_cache Indicates if query cache is supported;

• Query_cache_limit indicates that query cache holds a single query maximum result set, the default value is 1M, the result set larger than the value of the query will not be the cache;

• Query_cache_min_res_unit indicates the minimum memory size for each result set stored by query cache, default 4k;

• Query_cache_size indicates the memory size used in the system for query cache;

• Does the Query_cache_type system open the query cache function;

• Query_cache_wlock_invalidate for the MyISAM storage engine, set when write lock is on a table, the read request waits for the write lock to release the resource before querying or allowing direct from the query The cache reads the result, which is off by default and can be obtained directly from the query cache.

The above parameters often need to adjust the "query_cache_limit" and "Query_cache_min_res_unit", all need to do some adjustments according to the business, such as the cache result set is mostly less than 4k, can be adjusted appropriately "Query_ Cache_min_res_unit "value to avoid the waste of memory. If the result set is larger than 1M, you have to adjust the value of "query_cache_limit" to avoid the MySQL cache because the result set size exceeds the limit.

View query Cache-related state variables

650) this.width=650; "src=" Http://my.csdn.net/uploads/201208/12/1344760080_2359.png "/>

• Qcache_free_blocks indicates that the query cache currently has more than kill the remaining blocks, if the value is larger, then the memory fragments in query cache are more, need to be organized;

• Qcache_free_memory indicates the current memory size of query cache;

• Qcache_hits indicates how many hits the query cache has;

• Qcache_inserts indicates the number of times the result set has been written to the cache after the cache has been hit;

• Qcache_lowmem_prunes indicates how many query is cleared out of query_cache because of insufficient memory;

• Qcache_not_cached indicates the number of query that cannot be deleted because of the Query_cache_type setting or the cache.

• Qcache_queries_in_cache indicates the number of query for the current cache;

• Qcache_total_blocks the number of blocks in the current query cache.

These state variables allow you to understand the health of the query cache, allowing you to adjust the values of the corresponding system parameters.

Limitations of Query Cache

1. The previous version of 5.1.17 cannot cache the query of the variable, but starting with the 5.1.17 version, query cache has started to support the binding variable of query;

2. External query SQL in all subqueries cannot be cache, only caches the final result set;

3. Query in Procedure,function and trigger cannot be cache;

4. A query that contains many other functions that may have different results each time it is executed cannot be deleted by the cache.

Given these limitations, in the process of using the query cache, it is recommended to use the exact setup method, so that only the data of the appropriate table can be entered into the query cache, so that some query results are only allowed by the cache.

This article is from the "Fire" blog, so be sure to keep this source http://fire7758.blog.51cto.com/993821/1587979

MySQL Query cache optimizer