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

This article will give you a detailed explanation of the principle of query caching in mysql database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The principle of mysql database query cache is: 1, cache the result set of SELECT operation and SQL statement, key is sql,value for query result set; 2, if the new SELECT statement arrives, use this sql as key to cache the query, and if it matches, return the cached result set.

The principle of mysql database query caching is as follows:

Overview

Query cache (Query Cache, QC), which stores SELECT statements and their resulting data results. Have nothing to do, make a summary of this piece, and also make a memo!

Ultra-detailed summary of mysql database query cache, worth collecting

working principle

Cache the result set and SQL statement of SELECT operation. Key is sql,value and query result set.

If the new SELECT statement arrives, use this sql as the key to cache the query. If it matches, the cached result set will be returned.

Matching criteria: whether it is exactly the same as the cached SQL statement, the letters in sql are case-sensitive and the spaces in the middle are simply understood as storing a key-value structure, key is sql,value is the sql query result, and the equals () of Java's String is used for matching, for example:

Select age from user and select AGE from user will not match because the case is different

Select age from use and select age from user will not match because the spaces are different

The spaces on both sides of the sql are negligible and can be thought of as a trim operation on the key followed by an equals comparison.

View mysql setting parameters

Execution

Show variables like'% query_cache%'

You can see the relevant parameters:

Query_cache_type:0- does not enable query caching; 1-enable, 2-enable, default is 0

Query_cache_size: set the total size of the cache. The minimum query_cache_size allowed is 40K. The default is 1m. It is recommended to set it to: 64M/128M.

Query_cache_limit: limits the maximum size of a single query recordset that can be cached in the cache. Default is 1m.

When query_cache_type is 1, client query statements and recordsets can be cached as long as the query cache requirements are met. If SQL_NO_CACHE is added to the SQL, it will not be cached.

When query_cache_type is 2, as long as the parameter: SQL_CACHE is added to the SQL and meets the requirements of the query cache, the client query statements and recordsets can be cached.

View cache usage

Show status like'% Qcache%%'

You can see the relevant parameters:

Qcache_hits: number of cache hits

Qcache_inserts: the number of inserts in the cache, adding 1 for each cache. Note that this is not the number of caches.

Turn on query caching

Set the option query_cache_type = 1 and set query_cache_size = 67108864

Note: the value of query_cache_size can be set within 100MB. In MySQL, the query cache is controlled by a global lock, and the memory block of the query cache needs to be locked every time it is updated.

Turn off query caching

Set the option query_cache_type = 0, and also set query_cache_size = 0.

Applicable scenario

Used for scenarios where the same statement is submitted frequently and the table data changes infrequently, such as static pages, or information that changes infrequently in a piece of the page.

Because the query cache needs to cache the latest data results, any change in table data (insert, update, delete, or other operations that may cause data changes) will cause the query cache to be flushed. Therefore, for a scenario where the update frequency is very low and the read-only query frequency is very high, it is advantageous to turn on the query cache.

Does not apply to scenarios

Query cache strictly requires that the two SQL requests should be exactly the same, including SQL statements, connected database, protocol version, character set and other factors. Here are a few scenarios where query caching is not applicable:

Subquery

Procedures, functions, triggers, SQL called in event, or references to these results

When some special functions are involved in the query, such as BENCHMARK (), CURDATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), NOW (), SLEEP (), CONNECTION_ID (), CURRENT_DATE (), CURRENT_USER (), PASSWORD (), RAND (), UUID (), ENCRYPT (), LAST_INSERT_ID (), etc.

The query involves mysql,information_schema or performance_schema.

Similar to SELECT... LOCK IN SHARE MODE, SELECT... FOR UPDATE, SELECT..INTO OUTFILE/DUMPFILE, SELECT * FROM... Query of WHERE autoincrement_col IS NULL

Temporary tables are used in SELECT execution plan

Queries that do not refer to any tables, such as SELECT 1 / 2

The query generated an alarm (warnings)

The SQL_NO_CACHE keyword exists in the SELECT statement

It involves the partition table.

This is the end of the detailed explanation of the principle of query caching in mysql database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.