How to perform query caching in MySQL and how to solve failures

We all know that we need to understand the properties of the parameters before using a function, so that we can have a better understanding of the use of the function. After learning about query caching, some friends directly proceed to the next step of practical operations. Here the editor would like to remind everyone that you must set the parameters before starting the operation, otherwise problems will arise. Let's take a complete look at the steps of MySQL query caching and the analysis of cache invalidation.

1. Configuration parameters

(1) have_query_cache indicates whether query cache is supported. YES indicates support

(2) query_cache_type indicates the cache type. OFF means turning off the query cache, ON means turning on the query cache, and DEMAND means user-defined query cache

(3) query_cache_limit indicates the maximum amount of data supported by a single query SQL statement

(4) query_cache_min_res_unit indicates the minimum unit of query cache

(5) query_cache_size indicates the size of the query cache space

(6) query_cache_wlock_invalidate indicates whether the query cache supports write locks. OFF means it does not support, that is, reading data does not consider write locks. ON means it supports, that is, reading data will be blocked by write locks.

The query cache of MySQL is disabled by default. You need to manually configure the parameter query cache type to enable the query cache. The query cache type parameter has three possible values:

1) OFF or 0: query cache function is disabled;

2) ON or 1: The query cache function is turned on. The SELECT result will be cached if it meets the cache conditions. Otherwise, it will not be cached. If SQL_NO_CACHE is specified, it will not be cached.

3) DEMAND or 2: The query cache function is performed on demand. Only SELECT statements that explicitly specify SQL_CACHE are cached; others are not cached.

In the /usr/my.cnf configuration, add the following configuration:

2. Enable MySQL query cache

query_cache_type=1

After the configuration is complete, restart the service to take effect;

Then you can execute SQL statements on the command line to verify. Execute a time-consuming SQL statement, and then execute it several times to check the execution time of the subsequent times. Get the cache hit count of the query cache to determine whether to use the query cache.

Query cache usage

(1) Only query SQL statements with the same string equality use the same cache, that is, select name from city and SELECT name FROM city do not use the same cache.

(2) When query_cache_type is ON, all queries use the cache by default. We can use sql_no_cache to explicitly specify that a query does not use the cache.

select sql_no_cache name from city;

(3) When query_cache_type is DEMAND, you need to use sql_cache to specify that a query uses the cache.

select sql_cache name from city;

3. Cache invalidation:

When the structure or data of a table changes, the data in the query cache is no longer valid. Operations such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE will cause cached data to become invalid. Therefore, query cache is suitable for applications with a large number of identical queries, but not for applications with a large amount of data updates.

Once any row of table data is modified, all caches related to the table will become invalid immediately.

The above are the steps we need to take to query cache in MySQL. I would like to remind you again about the parameter settings. If the cache is invalid, don't worry, just look for solutions according to the editor's analysis.