MySQL Query cache Query_cache

Query Cache (querycache) saves the full result returned by the query. When the query hits the cache, MySQL returns the results immediately, skipping the parsing, optimization, and execution phases.

Official results in specific environmental tests (detailed in the official documentation):

1. If a simple query is made on a table, but each query condition is different, opening the query cache can cause performance degradation 13%.

2. If you query a table with only one row of data, you can promote 238%.

Therefore, the query cache is particularly suitable for scenarios where the update frequency is very low and the query frequency is very high.

Cache Hit Rule

The cache is stored in a reference table, referenced by a hash value, which contains the hash value (the query itself, the database of the current query, the version of the client protocol, and so on).

When deciding whether to hit , MySQL does not parse the statement, but uses the SQL statement and other raw information sent by the client directly. Any difference in character can cause a cache miss.

If a query statement contains any of the indeterminate functions, it will not be cached (like now ()) because MySQL disables caching of the query after the query is executed, so it is not possible to find the cached results in the query cache.

What happens when the query cache works

Not all cases of query caching can improve system performance. Opening the query cache has additional consumption for both read and write operations:

• A read query must first check whether the cache is hit before it starts.
• If the read query can be cached, then when execution is complete, the results need to be cached.
• Each write operation requires that all caches of the corresponding table be set to expire. If the cache is large or fragmented, it can be a significant drain.

Cache Hit Ratio:

The SHOW status provides a global performance indicator to calculate the cache hit ratio:

Each time the select query, either increase the Qcache_hits (query cache Hit count), or increase the com_select (no cached queries + Error query + permission check query), so the hit rate calculation formula: Qcache_hits/(Qcache_hits + Com_select).

Cache Configuration and Maintenance

Query_cache_type: Whether to open the query cache. can be set to (OFF, on, DEMAND). Demand indicates that only statements that explicitly mark Sql_cache in a query statement are placed into the query cache.

Query_cache_size: The query cache uses the total space.

Query_cache_min_res_unit: Allocates the smallest unit of memory block in the query cache.

Query_cache_limit:mysql The maximum query results that can be cached.

Query_cache_wlock_invalidate: If a data table is locked by another connection, the result is still returned from the query cache.

Specifies the size of the MySQL query buffer. You can observe this by executing the following commands in the MySQL console:

SHOW VARIABLES like '%query_cache% ';
SHOW STATUS like ' qcache% ';

MySQL Query cache Query_cache