MySQL query cache Summary

Recently, I have been asked questions about MySQL query cache. I just want to sort out some MySQL query cache content for your reference.

As the name suggests, MySQL query cache is used to cache query-related data. Specifically, the query cache caches the SELECT statement submitted by the client to MySQL and the result set of the statement. In general, a hash ing relationship is established between the SELECT statement and the statement result and is saved in a certain memory area.

In most MySQL distribution versions, the query cache function is enabled by default. You can enable this function by adjusting the MySQL server Parameter options. It consists of the following five parameters:

• Query_cache_limit: Maximum capacity of a single query result set that can be cached. The default value is 1 MB. The query result set that exceeds this parameter will not be cached.
• Query_cache_min_res_unit: sets the minimum memory size allocated for each query cache, that is, the minimum memory space occupied by each query cache.
• Query_cache_size: Set the memory size used by the query cache. The default value is 0 and the size must be an integer multiple of 1024. If it is not an integer multiple, MySQL automatically adjusts and reduces the minimum size to a multiple of 1024.
• Query_cache_type: the switch that controls the query cache function. It can be set to 0 (off), 1 (on), and 2 (Demand). The meanings are as follows:
  • 0 (off): disables the query cache function. query cache is not used in any situation.
  • 1 (on): Enable the query cache function. However, when the SQL _no_cache prompt is used in the SELECT statement, the query cache is not used.
  • 2 (demand): Enable the query cache function, but the query cache is used only when the SQL _cache prompt is used in the SELECT statement.
• Query_cache_wlock_invalidate: controls whether to invalidate the query cache related to the table when a write lock occurs on the table. If it is set to 1 (true ), when the write lock is performed, all query cache related to the table will be invalidated. If it is set to 0 (false), the query cache related to the table can still be read at the lock time.

How does query cache process subqueries?
This is the most common problem I have encountered. In fact, query cache is used to process the query submitted by the client, as long as the client requests
Query, whether the query is a simple single-Table query, multi-table join, or a complex SQL statement with subqueries, is considered as
Query will not be split into multiple queries for cache. Therefore, complex queries with subqueries exist.
Only one cache object is generated. subqueries do not generate separate cache content. The same applies to Union [all] statements.

Is query cache a block-based data block?
No. The cached content in the query cache only contains the query
The required result data is the result set. Of course, it is not only the result data, but also other information related to the result, such
Client Connection Character Set, data character set, client connection default database, etc.

Why is query cache very efficient? In some cases, even if all data can be cached in the memory, it is not as efficient as using query cache?
Query cache lookup is before SQL parsing after MySQL accepts the client request and verifies the query permission. That is to say, when
After MySQL receives the SQL statement from the client, it only needs to perform the corresponding permission verification and then searches for the result through the query cache.
The optimizer module analyzes and optimizes the execution plan, and does not require any storage engine interaction. It reduces a lot of disk I/O and CPU operations, so the efficiency is very high.

Does the case sensitivity of SQL statements submitted by the client affect query cache?
Yes. Because the query cache is mapped by hash structure in the memory, the hash algorithm is based on the characters that constitute SQL statements. Therefore, the entire SQL statement must be completely consistent at the character level, in order to hit the query cache, even if there is one more space.

When will the content of an SQL statement in the query cache become invalid?
To ensure that the content in the query cache is absolutely consistent with the actual data, when the data in the table has any changes, including addition, modification, and deletion, it will invalidate all query cache statements that reference the table.

Why does my system's overall performance decrease after I enable the query cache?
After the query cache is enabled, especially when the query_cache_type parameter is set to 1, MySQL
The SELECT statement performs query cache lookup. Although the query operation is relatively simple, it still consumes some CPU computing resources. Because Query
The cache invalidation mechanism may be caused by frequent changes in table data and frequent invalidation of a large number of query caches.
The hit rate may be relatively low. Therefore, in some scenarios, the query cache does not improve the efficiency, but may have a negative impact.

How can I check whether the query cache of a system is healthy, how is the hit rate, and whether the set quantity is sufficient?
MySQL provides a series of global statuses to record the current status of the query cache, as follows:

• Qcache_free_blocks: Number of memory blocks in the query cache that are currently in idle state
• Qcache_free_memory: total query cache memory that is currently in idle state
• Qcache_hits: Number of query cache hits
• Qcache_inserts: the number of times that a new query cache is inserted into the query cache, that is, the number of times that a new query cache is not hit.
• Qcache_lowmem_prunes: the number of times the old query cache needs to be deleted from the query cache to be used for new cache objects when the query cache memory capacity is insufficient.
• Qcache_not_cached: Number of SQL statements not cached, including SQL statements that cannot be cached and SQL statements that are not cached due to query_cache_type
• Qcache_queries_in_cache: current number of SQL statements in the query Cache
• Qcache_total_blocks: Total number of blocks in query Cache

The cache hit rate can be calculated based on these statuses to determine whether the query cache size setting is sufficient. In general, I personally do not recommend setting the query cache size to exceed 256 MB, this is also a common practice in the industry.

Does MySQL cluster support query cache?
In fact, MySQL cluster is not used in our production environment, so I have not used query cache in the MySQL cluster environment.
The actual experience is that the query cache can be used in the MySQL cluster. From MySQL Cluster
After all, the SQL node and data node are relatively independent and perform their respective duties, but the cache failure mechanism will be slightly more complex.

Original article: MySQL query cache Summary