What are the basic knowledge of query caching in MySQL?

What is query cache?

The MySQL query cache, although deprecated in MySQL 5.7 (and removed in 8.0), stores previously run statements in memory: in other words, the query cache typically stores SELECT statements in the database's memory middle. So if we run a query and then run the exact same query again some time later, the results will be returned faster because they will be retrieved from memory rather than from disk.

Query cache caches queries and result sets. So when we run the same query, the query cached results are returned immediately. Query cache size can be controlled by setting the query_cache_size system variable, but here's a caveat: if you want queries to use the query cache, they must, must, be identical, byte for byte. This means that even if you think these two queries should be cached exactly the same way:

SELECT * FROM demo_table WHERE column = 'Demo';
select * from demo_table where column = 'Demo';

In fact, they are not. If the MySQL query cache requires that all queries be identical, then no results will be returned even if they differ by just one byte.

So, to summarize, when MySQL executes a statement, the first thing it does is check to see if query caching is enabled (if you need a refresher, go back to our previous blog on slow MySQL queries article.) If query caching is enabled, MySQL will first check for any relevant matches to the query; if there is no match, MySQL will continue to the next step. If there are duplicate matches, MySQL will return the results from the query cache.

MySQL Internal Query Cache

Before returning to the match, MySQL's query cache requires MySQL's confirmation: d OES Does this user have the necessary permissions to complete such an action? Should I refuse to execute the query?

The following is a list of permissions that MySQL checks:

Privileges	Brief description
ALL	Give all permissions to a specific MySQL user.
SELECT	Grants a specific MySQL user permission to select rows from the specified database.
UPDATE	Grants a specific MySQL user permission to update existing rows in the specified table.
SHOW DATABASES	Enables a specific MySQL user to obtain a list of all MySQL databases that exist in a specific MySQL instance.
USAGE	Gives the user permission to use MySQL only, which means the user cannot run any queries in it. Essentially a synonym for unprivileged.

has more privileges, but you get the idea. MySQL stores table-related information along with cached queries, so permissions are critical for query caching. Permissions are also important because they are one of the first stages of the MySQL result checking process. Here's how it all works:

PRIORITY IDENTIFICATION	MYSQL STATUS	Explanation
1	MySQL is checking permissions in the query cache.	First, MySQL checks whether a specific user has permission to access a specific result.
2	MySQL is checking the query cache for the query itself.	Next, MySQL starts checking whether the same query exists in the query cache. If there is a match, MySQL returns; if not, MySQL continues to the next step.
3	MySQL marked the entry in the query cache as invalid.	As the table changes, the query cache needs to be updated. So in this step, MySQL decides to mark the entry in the query cache as invalid.
4	Send the result.	MySQL sends the cached result and displays it.
5	Store the results in cache.	MySQL saves query results in the query cache.
6	The query cache is locked.	The caching process is now complete - MySQL locks the query cache.

"MySql has no cache!": Depends on whether the query cache is damaged?

If we use any variable in the query, whether in function or partition state, the query cache will become invalid. For example, if we are dealing with big data and we use SELECT ... INTO OUTFILE to load a large data set into MySQL, the results will not be cached either. Normally the query cache doesn't work when using queries like SELECT ... [LOCK | FOR | INTO], also the query cache doesn't work if we are checking for AUTO_INCREMENT value in a column with NULL value, that means if we There is an increment with a column AUTO_INCREMENT, and we run a query like this so:

SELECT * FROM demo_table WHERE increment IS NULL;

The query cache will not take effect.

You are right, all these features may make some people feel that they are useless. Although like other features in MySQL the query cache has its own limitations and therefore does not always work. In addition to those mentioned above, it refuses to work when the statement generates warnings or when the statement is run on a table with TEMPORARY storage engine and in some other corner cases. Every time someone writes anything to the table, the query cache is also cleared.

The function of query cache also depends on the query_cache_size parameter. The larger this parameter is, the better, but be aware that this parameter is highly dependent on your memory. Keep in mind that the basic structure of the query cache requires at least 40kB of storage by default, and its default value may be between 1MB and 16MB. If your database reads data frequently, increasing the parameter value may help find the best solution, but experiment again.

You can use the "RESET QUERY CACHE" statement to clear all contents in the query cache. If you use a statement like FLUSH TABLES, all query cache values ​​will also be deleted.

MySQL even allows you to see how many queries are stored in the query cache by running the query:

SHOW STATUS LIKE 'Qcache_queries_in_cache%';

You will get results that look like this:

MySQL:

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_queries_in_cache | 1      |
+-------------------------+--------+

In this case, we can see that our query cache currently caches a result. Execute a statement like FLUSH TABLES and all values ​​will disappear.

The above is the detailed content of What are the basic knowledge of query caching in MySQL?. For more information, please follow other related articles on the PHP Chinese website!