Entering the mysterious world of MySQL Query Cache

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 beyond 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 volume 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 a Query, whether the Query is a simple single-Table Query or multi-table Join, or complex SQL statements with subqueries are considered as one Query and won't be split into multiple queries for Cache. Therefore, complex queries with subqueries only generate one Cache object, and 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 result data required by the Query and is the result set. Of course, it is not only the result data, but also other information related to the result, such as the character set of the client connection that generates the Cache, the character set of the data, and the Default Database connected by the client.

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, after MySQL receives the SQL statement from the client, it only needs to verify the corresponding permissions and then searches for the result through the Query Cache, it does not even need to go through the Optimizer module to analyze and optimize the execution plan, so it does not require any storage engine interaction, which reduces a lot of disk IO 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 performs Query Cache lookup for each SELECT statement, although the Query operation is relatively simple, however, it still consumes some CPU computing resources. Due to the failure mechanism of the Query Cache, the table data may change frequently and a large number of Query Cache may become invalid frequently. Therefore, the hit rate of the Query Cache 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 idle
◆ Qcache_hits: Number of Query Cache hits
◆ Qcache_inserts: the number of times a new Query Cache is inserted into the Query Cache, that is, the number of times no hits are inserted.
◆ 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 no practical experience in using Query Cache in the MySQL Cluster environment, it is only described in the MySQL document that Query Cache can be used in the MySQL Cluster. From the analysis of the principles of MySQL Cluster, I think it should be usable. After all, SQL nodes and data nodes are relatively independent and perform their respective duties, but the Cache failure mechanism will be a little more complicated.