High-performance MySQL (7) Query Cache technology Detailed

The MySQL query cache saves the full results of the query return. When the query hits the cache, the result is immediately returned, skipping the parsing, optimization, and execution phases.

The query cache tracks each table involved in the query, and if the table changes, all caches associated with the table will fail.

However, as the server function is powerful, query caching can also become a resource contention point for the entire server.

How to judge open query cache is good or bad, is the content of this article ~ ~ ~

First, how to determine the hit cache

The cache is stored in a reference table, referenced by a hash value, which includes the query itself, the database, the version of the client protocol, and so on, any character differences, such as spaces, can result in cache misses.

When there are some uncertain data in the query, it will not be cached, for example now (), Current_date (), custom functions, stored functions, user variables, word queries, etc. So such a query will not hit the cache, but will also detect the cache, because the query cache before parsing SQL, so MySQL does not know whether the query contains the class function, but does not cache, naturally does not hit.

Opening Qcache brings additional consumption to both read and write:

A, read the query must check whether to hit the cache before starting.

b, if the read query can be cached, then the cache is written after execution.

C, when you write data to a table, you must set all the cache of this table to be invalidated, if the cache space is large, the consumption will be very large, may make the system zombie for a period of time, because this operation is by the global lock operation to protect.

On the InnoDB table, cache invalidation is set when a table is modified, but the multiple versioning feature temporarily masks the modification to other transactions, and until the transaction is committed, all queries cannot use the cache until the transaction is committed, so a lengthy transaction can significantly reduce the query cache hit.

Second, how to use memory Qcache

The cached memory used by MySQL for querying is divided into variable-length blocks of data to store information such as type, size, and data.

When the server starts, it initializes the memory required by the cache and is a complete free block. When the query results need to cache, first from the free block to request a block of data is larger than the parameter query_cache_min_res_unit configuration, even if the cache data is small, the application block is this, because the query began to return the results when the allocation of space, at this time can not predict how large the results.

Allocate memory blocks need to lock the space block first, so the operation is very slow, MySQL will try to avoid this operation, choose as small as possible memory block, if not enough, continue to apply, if the store is free to release redundant.

But if the concurrent operation, the remaining need to reclaim the space is very small, less than query_cache_min_res_unit, can not be used again, will produce fragments. As shown in figure: