Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what are the basics of query caching in MySQL", which is easy to understand and well organized. I hope it can help you solve your doubts. Let me lead you to study and learn "what are the basics of query caching in MySQL".

What is query caching?

Although the MySQL query cache is deprecated in MySQL 5.7 (and deleted in 8.0), it stores statements that were previously run in memory: in other words, the query cache typically stores SELECT statements in the memory of the database. Therefore, if we run a query and then run the exact same query again after a period of time, the results will be returned faster because they will be retrieved from memory rather than from disk.

Query cache cache query and result set. Therefore, when we run the same query, the results of the query cache are returned immediately. The query cache size can be controlled by setting the query_cache_size system variable, but here is a warning: if you want queries to use query caching, they must, must be the same, byte by byte. This means that even if you think the two queries should be cached in exactly the same way:

SELECT * FROM demo_table WHERE column = 'Demo'

Select * from demo_table where column = 'Demo'

Actually, they're not. They are not because the MySQL query cache requires all its queries to be the same and does not return any results even if they differ by a byte.

So, to sum up, when MySQL executes a statement, the first thing it does is check to see if query caching is enabled. (if you need to review, go back to our last blog post on slow MySQL queries. If query caching is enabled, MySQL will first check for any relevant matches to the query; if there is no match, MySQL will proceed to the next step However, if the same match exists, MySQL returns the result from its query cache.

Query caching within MySQL

Before returning to the game, MySQL's query cache requires MySQL to confirm: d OES the user has the necessary permissions to complete such an action? Should I refuse to execute the query?

The following is a list of permissions for MySQL inspection:

privilege

Briefly describes that ALL gives all permissions to a particular MySQL user. SELECT grants specific MySQL users permission to select rows from the specified database. UPDATE grants specific MySQL users permission to update existing rows in the specified table. SHOW DATABASES enables specific MySQL users to obtain a list of all MySQL databases that exist in a particular MySQL instance. USAGE gives the user the right to use only MySQL, which means that the user cannot run any queries in it. Is essentially a synonym for unprivileged.

There are more privileges, but you get it. Permissions are important for query caching because MySQL also stores table-related information with cached queries. Permissions are also important because it is one of the first stages of the MySQL results checking process. This is how everything works:

Priority identification

MYSQL statu

Explain that 1MySQL is checking permissions in the query cache. First, MySQL checks whether a particular user has access to a specific result. 2MySQL is checking the query cache of the query itself. Next, MySQL starts checking to see if the same query exists in the query cache. If there is a match, MySQL returns; if not, MySQL proceeds to the next step. 3MySQL marks entries 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 entries in the query cache as invalid. 4 send the result. MySQL sends the cached result and displays it. 5 store the results in the cache. MySQL saves the query results in the query cache. 6 the query cache is locked. The caching process now ends-- MySQL locks the query cache. "MySql does not have a cache!" Depends on whether the query cache is corrupted

When using functions or partitions, if we use any variables in the query, the query cache will also be invalid. For example, if we are dealing with big data and we use SELECT. If INTO OUTFILE loads the big data set into MySQL, the result is also not cached. Typically, query caches SELECT... [LOCK | FOR | INTO] does not work when using a similar query. If we are checking the AUTO_ index value in a column with a null value, the query cache cannot be used, which means that if we have a column AUTO_INCREMENT with an increment, and we run such a query, so:

SELECT * FROM demo_table WHERE increment IS NULL

The query cache will not take effect either.

All of these features may cause some of you to shout, "it doesn't work!" -you're right. Query caching is not always valid: like everything in MySQL, it has limitations. In addition to the above, it refuses to work when statements generate warnings or statements are run on tables with the TEMPORARY storage engine, and in other extreme cases. The query cache is also emptied every time someone writes anything to the table.

The function of the query cache also depends on the query_cache_size parameter. The larger this parameter, the better, but note that this parameter depends largely on your memory. Keep in mind that the basic structure of the query cache requires at least the storage space of 40kB by default, and its default value may be between 1MB and 16MB. However, if your database is very busy reading data, a larger value may help find the best solution, and you must try it again.

You can also delete everything in the query cache by running the RESET QUERY CACHE statement. If you use a similar statement FLUSH TABLES, all values will also be removed from the query cache.

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

SHOW STATUS LIKE 'Qcache_queries_in_cache%'

You will get the results shown below:

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 similar statement, FLUSH TABLES, and all values will disappear.

These are all the contents of the article "what are the basics of query caching in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.