MySQL Interview Cheat sheet query cache

I am fat brother, an unprofessional interviewer!

I am embarrassed, a rookie who is actively looking for a job!

Embarrassed said: the most afraid of the interview is that the interviewer asked the knowledge point is too general, they can not quickly locate the key question point!!

This is the main interview question

What is the query caching mechanism?Copy the code

How do interviewers' queries hit the cache?Copy the code

In what scenarios are SQL and result sets not cached?Copy the code

Q: In what circumstances does the MySQL cache fail?Copy the code

How is the interviewer's query cache memory managed?Copy the code

MySQL allocates all memory at once.Copy the code

Memory fragmentation in the interviewers' test site cache is unavoidable, so is there any way to optimize it?Copy the code

Interviewer's test site MySQL4.0 introduces query caching, which query scenarios is it designed to speed up?Copy the code

MySQL5.6 is disabled by default and removed after 8.0. What is the reason for this change?Copy the code

Do you want to enable MySQL cache in your production environment?Copy the code

What is the query caching mechanism?

MySQL server is under high load, we need to take a measure to relieve the pressure on the server, a complex query is very expensive performance,

Disk IO occupies the main resources, and cache is an important means to optimize system performance.

The query caching mechanism is designed to fundamentally reduce disk I/O times. After MySQL enables caching, SQL and result sets are stored in memory in the form of key-value pair KV.

When the same SQL is entered again, MySQL recognizes the same query throat and directly returns the result set cached in memory.

Avoid going through a series of complex parsing optimizations and disk IO processes again.

How do interviewers’ queries hit the cache?

select id from user;

select id FROM user;
Copy the code

Can the above statement hit the cache?

The MySQL cache hit mechanism has strict requirements. Before determining a hit, MySQL does not parse the SQL.

Any character difference on SQL, such as case, Spaces, comments, and so on, will result in a cache missCopy the code

So the above query cannot hit the cache.

In what scenarios are SQL and result sets not cached?

Or what are the cache rules?

The first case: the query statement contains uncertain data

For example, query statements contain indeterminate functions: NOW(), CURRENT_DATE(), and so on. The result may be different each time a query with uncertain data is executed.Copy the code

Second case: the query_cache_LIMIT preset threshold is exceeded

Out of cache memory capacity, cache will be abandoned!Copy the code

Q: In what circumstances does the MySQL cache fail?

Any update to the table structure or table data will invalidate the data in the query cache, and all entries related to the value in the query cache will be cleared.

MySQL invalidates all query caches when it determines there is an update operation.

How is the interviewer’s query cache memory managed?

MySQL service starts, the caching mechanism creates a block of memory in memory,

An area is dedicated to managing and maintaining metadata for cached data.

Such as spatial memory, data tables and query result mapping, SQL and query result mapping

The MySQL cache mechanism divides the remaining free space into small data blocks for storing cached results.

Each chunk stores its own type, size, and query result data, as well as Pointers to the preceding and following chunks of memoryCopy the code

MySQL allocates all memory at once.

Because MySQL cannot predict the size of a query result, it cannot allocate exactly the size of the cache space for each query result.

MySQL cache mechanism is used to search and save, dynamically apply for cache memory.

How does an SQL query cache allocate memory?

When there are query results to cache, the MySQL cache mechanism allocates a chunk of memory (small data blocks) at the beginning of the SQL query. In the continuous query, if the memory is insufficient, the MySQL cache mechanism allocates a chunk of memory (small data blocks). If the memory is empty, the MySQL cache mechanism releases the excess memory space.

If the remaining space to be reclaimed is smaller than query_cache_MIN_res_unit and cannot be used again, memory fragmentation may occur, affecting query performance.

Memory fragmentation in the interviewers’ test site cache is unavoidable, so is there any way to optimize it?

There is no way to completely avoid memory fragmentation, but choosing the right one

query_cache_min_res_unit

You can reduce the amount of memory space wasted by fragmentation.

If the value is too small, less space is wasted, but it leads to frequent block requestsCopy the code

If you make it too big, you get a lot of fragmentationCopy the code

Adjusting the right values is a balancing act between memory waste and CPU consumption

So how do I determine this equilibrium?

You can calculate the average cache size for a single query based on actual memory consumption

(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cahceCopy the code

Fragments are observed by looking at the number of free memory blocks (Qcahce_free_blocks).

If too many fragments are generated, what method can be used to defragment?

The FLUSH_QUERY_CAHCE defragmentation command reorders all query caches and focuses all free space into one area of the query cache.Copy the code

Interviewer’s test site MySQL4.0 introduces query caching, which query scenarios is it designed to speed up?

1. Low concurrency and query QPS 2. The underlying data accessed is static or semi-static in nature 3

MySQL5.6 is disabled by default and removed after 8.0. What is the reason for this change?

Ideally, the above query scenario would be a good fit for query caching, but real business systems have CRUD operations.

In MySQL, QC is controlled by a global lock, and every update of QC memory block needs to be locked. Frequent data updates will result in continuous invalidation cache operations. Meanwhile, cache invalidation will cause a large number of query cache fragmentation, increase the server load, and affect the stability of the database.

Therefore, MySQL officials have decided to remove the query cache module.

Do you want to enable MySQL cache in your production environment?

Not recommended

According to MySQL official tests, if you perform a simple query against a table, set each query to be different, and turn on QC, the performance drops by about 13%Copy the code

Of course, in the real business, it will not all be such different requests, so the actual impact should be less than that.

The purpose of MySQL query caching is to improve query performance, but it has its own performance overhead.Copy the code

It needs to be used in the right business scenario (read/write stress model)

Inappropriate business scenarios will not improve query performance, but query caching will become the bottleneck of MySQL. For write-intensive applications, disabling caching can actually improve performance.Copy the code

Follow fate update, collation is not easy, welcome to contact xiaobai discussion, god baba please detour!

For more exciting content, please pay attention to wechat public account: Jiongmefeishi (or search: Jiongmefeishi)