MySQL Query Cache Graphical Explanation

1. Principle Overview

Query Cache is to cache the "results returned by the query". However, it is not accurate to simply cache "the results returned by the query". Query Cache also caches query statements and maps "query statements" and "the results returned by the query" in memory.
When MySQL receives a SELECT query statement, it performs hash calculation on the query statement to obtain a hash value. Then, the hash value is used to match the query cache. If no matching result is obtained, the hash value will be stored in a hash linked list, and the result of the query will be stored in the cache. Each node of the linked list storing hash values ​​will store the address of the corresponding query return result in the cache, as well as relevant information about some tables involved in the query. If the same query is matched through the hash value, the corresponding query result in the cache will be directly returned to the client. If any data in any table of MySQL changes, the Query Cache will be notified, all query caches related to this table will be invalidated, and the occupied memory space will be released. Operations that modify table data include: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, and DROP DATABASE, etc.

The working principle of Query Cache is: the fastest way to execute a query is not to execute it. The component diagram and flow chart of Query Cache are as follows:

Query Cache system variables

The main configurable system variables for Query Cache are as follows:

1. have_query_cache

Indicates whether mysqld supports Query Cache.

2. query_cache_limit

Indicates the maximum result set size of a single query that can be cached by Query Cache. The default value is 1MB. If the result set size of a query exceeds the value of this system variable, Query Cache will not cache the result set of this query.

3. query_cache_min_res_unit

Indicates the minimum size of memory that MySQL allocates for Query Cache each time, that is, the minimum size of memory space used to cache query results. The default value is 4KB.

4. query_cache_size

Indicates the maximum memory size that can be used by Query Cache. The default value is 1MB. The value must be an integer multiple of 1024. If it is not an integer multiple, MySQL will automatically adjust it down to the maximum value that is a multiple of 1024.

5. query_cache_type

Indicates the working mode of Query Cache, and also turns the Query Cache function on and off. It can be set to 0 (OFF), 1 (ON), and 2 (DEMAND):

• 0 (OFF): Disable the Query Cache function. The Query Cache will not be used under any circumstances.

• 1 (ON): Enable the Query Cache feature. However, if the SQL_NO_CACHE option is used in the SELECT statement, the Query Cache will not be used.

• 2 (DEMAND): Enable the Query Cache feature, but use the Query Cache only when the SQL_CACHE option is used in the SELECT statement.

6. query_cache_wlock_invalidate

Controls whether to invalidate the Query Cache associated with the table first when a write lock is added to the table. It has two values: 1 (ON) and 0 (OFF):

• 1 (ON): All query caches related to the table will be invalidated while writing the lock.

• 0 (OFF): Reading the Query Cache associated with the table is still allowed while writing the lock.

The environment variables that often need to be adjusted are query_cache_limit and query_cache_min_res_unit. They both need to be adjusted accordingly based on actual business needs. For example, if most cached query result sets are smaller than 4KB, you can adjust the value of query_cache_min_res_unit appropriately to avoid wasting memory. If the size of the query result set is greater than 1MB, you need to adjust the value of query_cache_limit to prevent the result set from being cached because the size exceeds the limit.

Query Cache Status Variables

MySQL provides a series of status variables to record the current status of the Query Cache, allowing you to confirm whether the Query Cache is running healthily, what the hit rate is, whether the memory space is sufficient, and so on. The status variables of Query Cache are as follows:

1. Qcache_free_blocks

Indicates how many free memory blocks are currently in the Query Cache. If this value is large, it means that there may be a lot of memory fragmentation in the Query Cache. FLUSH QUERY CACHE will defragment the cache to create a larger free memory block.

2. Qcache_free_memory

Indicates the current free memory size of the Query Cache.

3. Qcache_hits

Indicates how many queries hit the Query Cache.

4. Qcache_inserts

The number of times new records were inserted into the Query Cache, that is, the number of times the query did not hit.

5. Qcache_lowmem_prunes

Indicates the number of query results that were removed from the cache due to low memory in the Query Cache. If this value is constantly increasing, it usually means that the free memory of the Query Cache is insufficient (judged by Qcache_free_memory) or the memory is severely fragmented (judged by Qcache_free_blocks).

6. Qcache_not_cached

Indicates the number of queries that are not cached. There are three situations where query results will not be cached: first, due to the setting of query_cache_type; second, the query is not a SELECT statement; third, a function such as now() is used, causing the query statement to keep changing.

7. Qcache_queries_in_cache

Indicates the number of query results currently contained in the Query Cache.

8. Qcache_total_blocks

Indicates the total number of memory blocks in the Query Cache.

4. Advantages and Disadvantages

1. Advantages

Query Cache queries occur after MySQL receives the client's query request, verifies query permissions, and before parsing the query SQL. That is to say, when MySQL receives the query SQL from the client, it only needs to verify the corresponding permissions and then search for the results through the Query Cache. It does not even need to analyze and optimize the execution plan through the Optimizer module, and does not require any interaction with the storage engine. Since Query Cache is memory-based and returns the corresponding query results directly from the memory, it reduces a lot of disk I/O and CPU calculations, resulting in very high efficiency.

2. Disadvantages

Even though the advantages of Query Cache are obvious, some of its disadvantages cannot be ignored:

• Resource consumption caused by hash calculation and hash search of query statements. If query_cache_type is set to 1 (that is, ON), MySQL will perform hash calculations on each SELECT type query received, and then check whether the cached result of this query exists. Although the efficiency of hash calculation and search is high enough and the overhead of a query statement can be ignored, once high concurrency is involved and there are tens of thousands of query statements, the overhead of hash calculation and search must be taken seriously.

• Query Cache expiration issue. If the table changes frequently, the failure rate of Query Cache will be very high. Table changes not only refer to changes in the data in the table, but also include any changes in the table structure or indexes.

• Queries with different query statements but the same query results will be cached, which will cause excessive consumption of memory resources. If the query statements have different capitalization, spaces, or comments, Query Cache will consider them to be different queries (because their hash values ​​will be different).

• Improper settings of related system variables will cause a large amount of memory fragmentation, which will lead to frequent memory clearing of Query Cache.

5. Frequently Asked Questions

1. Under what conditions should Query Cache be used?

In fact, not all tables are suitable for using Query Cache. The main reason for Query Cache failure is that the corresponding table has changed. Therefore, you should avoid using Query Cache on tables that change frequently. MySQL has two dedicated SQL options for Query Cache: SQL_NO_CACHE and SQL_CACHE. If query_cache_type is set to 1 (ON), the SQL_NO_CACHE option can be used to force Query Cache not to be used; if query_cache_type is set to 2 (DEMAND), the SQL_CACHE option can be used to force Query Cache to be used. By forcing Query Cache not to be used, MySQL can prevent Query Cache from being used on frequently changing tables, which reduces memory overhead as well as hash calculation and lookup overhead.

2. What is the relationship between Query Cache and query statements?

Regardless of whether the query statement received by MySQL is a single-table or multi-table SQL or contains a subquery, it is treated as a query and will not be split into multiple queries for caching, including Union statements.

3. Does the case of the query statement submitted by the client affect the Query Cache?

influential. Since the Query Cache is mapped in memory using a hash structure, the basis of the hash algorithm is the characters that make up the query statement. Therefore, the entire query statement must be completely consistent at the character level in order to hit the Query Cache.

4. Under what circumstances will the query results cached in the Query Cache of a query statement become invalid?

In order to ensure that the content in the Query Cache is absolutely consistent with the actual data, when there is any change in the data in the table, including addition, modification, deletion, etc., all Query Cache cached data that references the table will be invalidated.

5. What is the Query Cache fragmentation rate? What's the use?

Query Cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%
If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the memory; if your queries are all small amounts of data, you can try to reduce query_cache_min_res_unit.

6. What is the Query Cache utilization? What's the use?

Query Cache utilization = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

If the Query Cache utilization is below 25%, it means that the query_cache_size is set too large and can be appropriately reduced; if the Query Cache utilization is above 80% and Qcache_lowmem_prunes > 50, it means that the query_cache_size may be a bit small, or there is too much memory fragmentation.

7. What is the Query Cache hit rate? What's the use?

① Query Cache hit rate of cacheable queries = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
② Query Cache hit rate covering all queries = Qcache_hits / (Qcache_hits + Com_select) * 100%

If the hit rate is within the range of 50-70%, it indicates that the cache efficiency of Query Cache is high. If the hit rate is significantly less than 50%, it is recommended to disable (set query_cache_type to 0 (OFF)) or use (set query_cache_type to 2 (DEMAND)) Query Cache on demand. The saved memory can be used as the buffer pool of InnoDB.

8. How to determine whether the Query Cache has insufficient free memory or too much memory fragmentation?

If the Qcache_lowmem_prunes value is large, it means that the memory size of the Query Cache is too small and needs to be increased.

If the Qcache_free_blocks value is relatively large, it means that there is a lot of memory fragmentation, and you need to use the FLUSH QUERY CACHE statement to clean up the memory fragmentation.

9. How large should the query_cache_min_res_unit system variable be set to?

The calculation formula for query_cache_min_res_unit is as follows:

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

It is generally not recommended to set the Query Cache size (that is, the query_cache_size system variable) to more than 256MB.

The function of MySQL query cache Query_cache

MySQL's query cache does not cache execution plans, but queries and their result sets. This means that only identical query operations can hit the cache, so MySQL's query cache hit rate is very low. On the other hand, for queries with large result sets, the query results can be read directly from the cache, effectively improving query efficiency.

So how do you set the cache size? Let’s take a look:

1. In the MySQL client command line, we can check whether the cache is enabled and the size of the cache setting:

mysql> show variables like '%query_cache%';
+------------------------------+-------------+
| Variable_name | Value |
+------------------------------+-------------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16106127360 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-------------+
6 rows in set (0.01 sec)

in:

query_cache_type: Whether to enable the cache function, the value is ON, OFF, DEMAND, the default value is ON
- When the value is OFF or 0, the query cache function is disabled;
- When the value is ON or 1, the query cache function is turned on. The SELECT result will be cached if it meets the cache conditions. Otherwise, it will not be cached. If SQL_NO_CACHE is explicitly specified, it will not be cached.
- When the value is DEMAND or 2, query caching is performed on demand, and only SELECT statements that explicitly specify SQL_CACHE are cached; others are not cached

query_cache_wlock_invalidate: Indicates whether to return cached results or wait for the write operation to complete before reading the table to obtain the results if the query is in the query cache when other clients are writing to the MyISAM table.

query_cache_limit specifies the buffer size that can be used by a single query, the default is 1M;

query_cache_min_res_unit is the minimum cache block size allocated by the system. The default value is 4KB. Setting a large value is good for large data queries, but if your queries are all small data queries, it is easy to cause memory fragmentation and waste; query_cache_size: indicates the size of the cache.

After understanding the above indicators, we can set them in the MySQL configuration file my.cnf. Then restart the mysl server. Add the parameters under [mysqld]. Generally, query_cache_size and query_cache_type are set.

2. The above view is our cache configuration, which generally reads the value from the configuration file, but sometimes we need to view the current data cache size in MySQL in real time.

mysql> show status like '%qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 16489053 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)

explain:

Qcache_free_memory: Free memory in the cache.

Qcache_total_blocks: The number of blocks in the cache.

Qcache_lowmem_prunes: The number of times the cache ran out of memory and had to be pruned to make room for more queries. This number is best viewed over time; if it is growing, it could indicate very severe fragmentation or low memory. To determine this, you need to look at the two indicators Qcache_free_blocks and Qcache_free_memory.

3. Clear the cache:

flush query cache command

Increasing the cache will help improve the efficiency of our queries:

Here is an example:

mysql> use mob_adn
Database changed
mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
|87151154|
+----------+
1 row in set (3 min 18.29 sec)

mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
|87151154|
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from creative_output;
+----------+
| count(*) |
+----------+
|87151154|
+----------+
1 row in set (0.00 sec)

You can see that caching is really powerful.

Some students may think of the parameter innodb_buffer_pool. I will continue to talk about the functions and differences between the two.

By the way, here are some SQL optimizations (very good):

https://www.cnblogs.com/L-dongf/p/9163848.html

This is the end of this article about MySQL Query Cache with pictures and text. For more relevant MySQL Query Cache content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!