How to cache queries in MySQL and how to solve the failure

Time:2022-5-6

We all know that before using the function, we need to find out the properties of the parameters, so that we can have a better understanding of the use of the function. After learning the query cache, some small partners directly carry out the next practical operation. Here, I want to remind you that you must set the parameters before starting the operation, otherwise there will be problems. Let’s talk about the steps of MySQL query caching and the analysis of cache invalidation.

1. Configuration parameters

(1) have_ query_ Cache indicates whether query caching is supported, and yes indicates yes

(2) query_ cache_ Type indicates the cache type, off indicates to turn off the query cache, on indicates to turn on the query cache, and demand indicates user-defined query cache

(3) query_ cache_ Limit indicates the maximum amount of SQL data supported for a single query

(4) query_ cache_ min_ res_ Unit represents the smallest unit of query cache

(5) query_ cache_ Size indicates the size of query cache space

(6) query_ cache_ wlock_ Invalidate indicates whether the query cache supports write lock. Off indicates no support, that is, the write lock is not considered when reading data. On indicates support, that is, the read data will be blocked by the write lock

MySQL’s query cache is turned off by default. You need to manually configure the parameter query cache type to turn on the query cache. Query cache type this parameter can take three values:

1) Off or 0: query cache function is off;

2) On or 1: when the query caching function is turned on, the selected results will be cached if they meet the caching conditions. Otherwise, they will not be cached and the specified SQL will be displayed_ NO_ Cache, no cache

3) Demand or 2: the query cache function is performed on demand, and the specified SQL is displayed_ Only the select statement of cache will be cached; Others are not cached.

At / usr / my In CNF configuration, add the following configuration:

2. Enable the query cache of MySQL


query_cache_type=1

After configuration, restart the service to take effect;

Then you can execute the SQL statement on the command line for verification, execute a time-consuming SQL statement, and then execute it several more times to check the execution time of the next few times; Gets the number of cache hits in the query cache to determine whether to use the query cache.

Query cache usage

(1) Only SQL queries with equal strings use the same cache, that is, select name from city and select name from city do not use the same cache.

(2) In query_ cache_ When type is on, all queries are cached by default. We can use SQL_ no_ Cache display specifies that a query does not use cache


select sql_no_cache name from city;

(3) In query_ cache_ When the type is demand, you need to use SQL_ Cache specifies that a query uses the cache


select sql_cache name from city;

3. Cache failure:

When the structure or data of the table changes, the data in the query cache is no longer valid. For example, insert, update, delete, truncate, alter table, drop table or drop database will invalidate the cached data. Therefore, query cache is suitable for applications with a large number of the same queries, not for applications with a large number of data updates.

Once any row of table data is modified, all relevant caches based on the table will be invalidated immediately.

The above are the steps we need to take in MySQL query caching. I remind you again in terms of parameter settings. If the cache fails, don’t worry, and find a way to analyze the corresponding Xiaobian.