MySQL Query Cache

The realization principle of 1,querycache;

1, currently only the SELECT statement will be the cache, other similar show,use statements will not be the cache.

2, two SQL statements, as long as the difference between a character (such as the case is not the same, more than one space, etc.), then the two SQL will use a different cache.

A frequently updated table if the QC is applied, it may aggravate the burden on the database, rather than lighten the burden. My general practice is to turn on QC by default, and add the sql_no_cache keyword to the SQL statements that involve frequently updated tables to disable CACHE for them. This allows you to avoid unnecessary memory operations as much as possible and to maintain memory continuity as much as possible. Those SQL statements that query very scattered, and should not use QC. For example, the statement used to query the user and password--"Select pass from user where name= ' surfchen '". Such statements, in a system, are most likely to be used only when a user logs on. Each user login to use the query, are not the same SQL text, QC here is almost useless, because the cached data is almost not used, they will only occupy a place in memory.

Negative effects of 2,querycache:
1,query hash performance problem and hit rate problem;
2, the query cache and its easy invalidation, when the table content changes or the table structure changes, the corresponding query cache content will be invalidated;
3, the results in the query cache are prone to duplication, because the query cache is cached in the query results, so the results of different queries are easy to duplicate;

Use of the 3,query cache:
1, set Query_cache_limit as the query cache size, if 0, the query cache is not used;
2, use Sql_cache or Sql_no_cache to enforce whether query caching is used;
3, query query cache settings: Show variables like '%query_cache% ';
1, "Have_query_cache": whether the MySQL supports query cache;
2, "Query_cache_limit": Query cache to store a single query maximum result Set, the default 1M;
3, "Query_cache_min_res_unit": Query cache each result Set to store the minimum memory size, default 4k;
4, "query_cache_size": the size of the system for query cache memory;
5, "Query_cache_type": Whether the system has opened the query cache function;

4, query Query cache usage: Show status like ' qcache% ';
1, "qcache_free_blocks": How many remaining blocks are currently in Query Cache. If the value is large, it means that there is more memory fragmentation in query Cache
2, "qcache_free_memory": The amount of memory currently remaining in Query Cache;
3, "qcache_hits": how many times hit;
4, "Qcache_inserts": How many misses are then inserted; Query Cache hit rate = qcache_hits/(qcache_hits + qcache_inserts);
5, "Qcache_lowmem_prunes": How many query Cache is cleared out because of insufficient memory;
6, "qcache_not_cached": Because of the query_cache_type settings or the number of query can not be cache;
7, "Qcache_queries_in_cache": The number of query cache in the current query cache;
8, "Qcache_total_blocks": the number of blocks in the current query Cache;

5,query Cache Usage Restrictions:
1,mysql Query cache content is the result set of select, the cache uses the full SQL string to do key, and distinguishes between case, space, and so on. That is, two SQL must be fully consistent to cause a cache hit.
2,prepared statement will never cache to the result, even if the parameters are exactly the same,
3,where conditions include some functions that will never be cache, such as Current_date, now, and so on.
4, too large result set will not be cache (< Query_cache_limit)

How to use the 6,query cache:
1, if there is no absolute use of certainty, you can close the query cache;
2, if you want to use the query cache, it is best to control those table content to the query cache, which tables do not query cache;

Block description of storage blocks

In this section "Storage Block" and "block" are the same meaning

When QC caches a query result, it is generally not a one-time allocation of enough memory to cache the results. Instead, it is stored in chunks as the results of the query are obtained. When a block of storage is filled, a new block of storage is created and allocated memory (allocate). The memory allocation size of a single storage block is controlled by the query_cache_min_res_unit parameter, which defaults to 4KB. The last storage block, if not fully exploited, will be freed from unused memory. If the result of the cache is large, it can cause the allocated memory operations to be too frequent, the system can also decline, and if the cached results are very small, it may lead to too much memory fragmentation, if the fragments are too small, it is likely to be no longer allocated to use.

In addition to the query results requiring a block of storage, each SQL text also requires a block of storage, and the table involved requires a block of storage (the table's storage block is shared by all threads, and each table requires only one storage block). Total number of storage blocks = number of database tables involved in the number of query results *2+. That is, when the first cache is generated, at least three storage blocks are required: The table Information store block, the SQL text storage block, and the query result store block. The second query, if it uses the same table, requires a minimum of two storage blocks: The SQL Text store block, and the query result store block.

By observing Qcache_queries_in_cache and qcache_total_blocks, you can know the average storage block that each cache result occupies.

If their proportions are close to 1:2, the current query_cache_min_res_unit parameter is large enough.

If Qcache_total_blocks is much larger than qcache_queries_in_cache, you need to increase the size of the query_cache_min_res_unit.

Qcache_queries_in_cache*query_cache_min_res_unit (The block of SQL text and table information occupies a small amount of memory that can be ignored) if it is much larger than query_cache_size-qcache_ Free_memory, you can try to reduce the value of query_cache_min_res_unit.

Reference from: Http://blog.csdn.net/iris_xuting/article/details/50495928#t0

MySQL Query Cache