MySQL Query cache

MySQL Query cache MySQL Query execution process

Query process:

1. The client sends a query to the server;

2. The server first checks the query cache. if the cache is hit, the results stored in the cache are immediately returned. Otherwise, the server enters the next stage;

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

4. MySQL calls the storage engine API to execute queries based on the execution plan generated by the optimizer;

5. Return the result to the client;

Query cache

• It is used to save the complete results returned by the MySQL Query statement. when hit, MySQL immediately returns the results, saving the parsing, optimization, and execution stages;

• MySQL stores the results in the cache. The select statement itself is used for hash calculation. the calculation result is used as the key, and the query result is used as the value;

• The case sensitivity of the query statement affects the cache storage and hit. Therefore, the case sensitivity of the query statement must be consistent;

Which statements will not be cached?

• The query statement does not cache some uncertain data, such as now () and current_time ().

• If a query contains user-defined functions, storage functions, user variables, temporary tables, system tables in the mysql database, or any tables containing permissions

Cache brings additional overheadBecause:

• Before starting a read query, you must first check whether it hits the cache;

• If a read query can be cached but not cached, MySQL stores the result in the query cache after execution;

• This also affects write operations, because when writing data, MySQL must set all the caches of the corresponding table to fail, which will cause a large amount of system consumption when the cache memory is large;

• Therefore, the query cache is not required. its efficiency depends on whether a large number of queries with large sales in all queries can be hit by the cache;

Cache-related variable query

How to determine cache hit rate

Cache hit rate variables

Calculate cache hit rate

Thoughts on cache optimization

• Batch writing instead of multiple single writes;

• The cache space should not be too large, because a large number of caches are invalid at the same time, causing the server to be suspended;

• If necessary, use SQL _cache and SQL _no_cache to manually control the cache;

• For write-intensive application scenarios, disabling caching improves performance.