MySQL query_cache_type parameter and usage details

The purpose of setting up MySQL query cache is:

Cache the query results so that you can directly retrieve them from the result set the next time you execute the same query; this is much faster than searching again.

The end result of query caching is that it backfires:

There are two objective reasons why query cache does not improve performance:

1. Use the hash value of the SQL statement as the key and the result set of the SQL statement as the value; this raises a problem such as select user from mysql.user and SELECT user FROM mysql.user

These two will be treated as different SQL statements. At this time, even if the result set already exists, it will not be used.

2. When the lower-level table on which the query is based is modified, the query cache related to this table will be invalidated. If the system has a large degree of concurrency, this overhead is considerable; the operation of invalidating the result set also requires concurrency.

Access control means there will also be locks. When the concurrency is large, Waiting for query cache lock will occur.

3. Whether to use it or not depends on the business model.

How to configure the query cache:

The query_cache_type system variable controls whether the query cache feature is enabled or disabled.

When query_cache_type=0, it means closed, 1 means open, and 2 means caching only when SQL_CACHE is explicitly specified in the select.

The setting of this parameter is a bit strange. 1. If the query cache is closed beforehand, but set @@global.query_cache_type=1; an error will be reported

ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

2. If it was opened beforehand and you try to close it, the closure is incomplete and the query will still try to find the cache.

The best way to turn off query cache is to set query_cache_type=0 in my.cnf and then restart mysql.

Query cache-related system variables:

have_query_cache indicates whether this MySQL version supports query cache.

query_cache_limit indicates the maximum value allowed to cache a single result set.

query_cache_min_res_unit The minimum memory that each cached result set should occupy.

query_cache_size The memory size used for the query cache.

How to monitor the query cache hit rate:

Qcache_free_memory The current remaining space size of the query cache.

Qcache_hits The number of query cache hits.

Qcache_inserts The number of query cache insertions.

That is to say, the cache hit rate is Qcache_hits/(Qcache_hits+Qcache_inserts)

Generally, it is not possible to set this alone. You have to combine it. It is recommended to read the following article