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;

MySQL Query cache optimizer