sequence

Although MySQL 8.0 has removed the query cache, I believe that many companies are not that quick to upgrade, so it is worth knowing more about the query cache. This article expands on a number of issues and concepts by showing the reader some of the details of query caching with a memory structure diagram. This article will give you a deeper understanding of query caching.

First post a query flow chart:

Query the schematic diagram of cache memory structure

The MySQL query cache holds the complete results returned by the query. When a query hits the cache, MySQL checks the user’s rights once and returns the result immediately, skipping parsing, optimization, and execution.

What is the key

As shown in the figure above, the cache is stored in a reference table, referenced by a hash value that includes factors such as the query itself, the current database being queried, the version of the client protocol, and other information that might affect the results returned.

So when determining whether a cache hit is made, the SQL statement and other raw information sent by the client are used directly. Any character difference in the SQL statement will result in a cache miss.

Which data will not be cached

Queries that exceed the query_cache_limit limit will not be cached.

When there is some uncertainty in the query statement, the query result is not cached. If the query contains any user-defined functions, storage functions, user variables, temporary tables, system tables in the mysql library, or tables with column-level permissions, it will not be cached.

Example:

select * from employee where create_time > NOW();
select * from order where create_time >  DATE_SUB(CURRENT_DATE.INTERVAL 1 DAY); There are other things likeCURRENT_USERCONNECTION_ID(), and so on.Copy the code

Metadata management

Metadata management occupies approximately 40KB of memory resources and is used to specify which memory is currently available, which memory is used, which memory is used to store data tables and mappings prior to query results, and which memory is used to store query strings and query results

A block of data

Data blocks are variably long, and each data block stores its own type, size, and stored data itself, plus Pointers to the previous and next data blocks. The types of data blocks include: store query results, store query and data table mapping, store query text, and so on.

The block size must be greater than query_CACHE_MIN_res_unit. Even if the query result is much smaller than this, MySQL cannot allocate exactly the size of the cache space for each query result because it needs to be allocated as soon as the query results are returned.

Now, let’s take a look at the write process of the query cache:

Let's say I use this oneSQL ：SELECT * FROM employee LIMIT 2;

1.When you need to cache the first query result, select the smallest possible block of memory and store the result there.2.If the block is used up, MySQL will request a new block as small as possible because there is one more query result to store.3.When the query is complete, if the requested memory space is still available, MySQL will release it and put it into the free memory section.Copy the code

debris

Consider this: suppose the query results are very small, and the server returns results concurrently to two different connections. When MySQL reclaims the remaining block space after the return, it finds that the recovered block is smaller than query_CACHE_MIN_res_unit and therefore cannot be used directly in subsequent block allocations. That’s when you get debris.

On the other hand, when the cache fails, it may leave a chunk of data too small to be used in subsequent caches.

Fragmentation can be observed with the Qcache_free_blocks parameter, which reflects how many free blocks are in the query cache

Defragmentation can be accomplished by using the FLUSH QUERY CACHE command, reordering all QUERY caches and aggregating all free space into one area of the QUERY CACHE. However, this command accesses all query caches, during which time no other connections can access the query cache, causing the server to freeze for a while

Query result set

The query result set contains multiple query results, such as the return result of a range query. Therefore, in the schematic diagram, the query result set contains three data blocks, which are associated with each other using before and after Pointers. The query cache for an SQL statement corresponds to a query result set, which is one to more data blocks.

Remaining free space

Qcache_free_memory

When we find the average cache size for a single query, we use:


q u e r y _ c a c h e _ s i z e – Q c a c h e _ f r e e _ m e m o r y Q c a c h e _ q u e r i e s _ i n _ c a c h e {query\_cache\_size-Qcache\_free\_memory \over Qcache\_queries\_in\_cache}

Qcache_queries_in_cache Indicates the total number of times that the cache has been queried.

Some of the problems

Opening the query cache imposes additional overhead on both read and write operations

  • Read queries must be checked for cache hits before starting
  • If the read query can be cached, MySQL will store the result in the query cache if the query does not exist in the query cache after execution, resulting in additional system consumption.
  • This also has an impact on writes, because when writing to a table, MySQL must invalidate all caches for that table. If the query cache is very large or fragmented, this operation can be costly to the system (setting up a lot of memory for use by the query cache).
  • The query cache operation is a lock-exclusive operation, and this cost can be significant

Some advice

  1. Most of the time we still think that query caching should be turned off by default.
  2. If the query cache is large, configure a small query cache space (such as tens of megabytes).
  3. When query caching is enabled (query_cache_type = ON) can be used in a query statementSQL_NO_CACHETo avoid the additional cost of query caching
  4. You can set the query cache toquery_cache_type = DEMANDIs then used in the query statementSQL_CACHETo explicitly declare the use of the query cache and only use the query cache for specific queries.
  5. Do not use uncertain data in a query statement
  6. Use limit to control the size of query resultsquery_cache_limit
  7. Save the network request overhead to MySQL server by placing the query results in the client cache

other

This article’s illustrations were drawn using Processon. In this paper, the query cache memory structure diagram is according to the author’s own understanding of the appearance of the drawing, if there is something wrong (certainly wrong), the author does not assume any legal responsibility, but accept everyone’s criticism.

The author is not very good at writing articles, but this time he did the following:

  1. LateX was used for the first time to draw mathematical formula diagrams
  2. A written order
  3. The drawing with your heart

If you find this article helpful, please leave a red like before you go, or browse the author’s other articles if you are interested. If you think this article is a waste of your time, please leave your comments in the comments section. If you have any questions, please leave a message. The author is willing to spend time and energy to find answers and discuss them together.