MySQL Query Cache

MySQL Query execution process

Query Process :

1. The client sends a query to the server;

2. The server checks the query cache first, and returns the results stored in the cache immediately if the cache is hit; otherwise, go to the next stage;

3. The server performs SQL parsing, preprocessing, and then generates the corresponding execution plan by the optimizer;

4. MySQL invokes the API of the storage engine to execute the query based on the execution plan generated by the optimizer;

5. Return the results to the client;

Query cache

• Used to save the full result returned by the MySQL query statement, when hit, MySQL will immediately return the results, eliminating the parsing, optimization and execution phases;

• MySQL saves the results in the cache, the SELECT statement itself to do hash calculation, the results of the calculation as key, query results as value;

• The case of the query statement affects the storage and hit of the cache, so it is necessary to keep the case consistency of the query statement;

What statements are not cached

• When there are some indeterminate data in the query statement, it will not be cached, such as now (), current_time (), etc.

• If the query contains user-defined functions, stored functions, user variables, temporary tables, system tables in the MySQL library, or any tables that contain permissions, it is generally not cached

caching brings additional overhead , because:

• The Read query must first check whether the cache is hit before it starts;

• If a read query can be cached and not cached, MySQL will store its results in the query cache when execution is complete;

• Also has an impact on write operations, because when writing data, MySQL must set all cache of the corresponding table invalid, which will result in large system consumption when the cache memory is large;

• Therefore, the query cache is not necessary, and its efficiency depends on whether the query can be cached by the large-cost queries;

Cache correlation Variable Query

How to determine cache hit ratio

Cache hit ratio-related variables

Calculate cache Hit Ratio

The idea of cache optimization

• bulk write instead of multiple individual writes;

• cache space should not be too large, Because a large number of cache failures can cause the server to feign death;

• For write-intensive scenarios, disabling caching can instead raise high performance