What is the principle of mysql database query caching

The principle of mysql database query caching is: 1. Cache the result set and SQL statement of the SELECT operation, the key is sql, and the value is the query result set; 2. If a new SELECT statement arrives, use this sql as the key. Query in the cache, and if there is a match, return the cached result set.

Mysql database query caching principle is:

Overview

Query Cache (QC for short) stores SELECT statements and the data results they generate. I have nothing to do, so I would like to summarize this topic and make a memo!

Super detailed mysql database query cache summary, worth collecting

Working principle

• Cache the result set and SQL statement of the SELECT operation, the key is sql, and the value is the query result set;

• If a new SELECT statement arrives, use this sql as the key to query in the cache , if it matches, the cached result set will be returned;

• Matching criteria: Whether it is exactly the same as the cached SQL statement. Letters in SQL are case-sensitive and spaces in between are simply understood as A key-value structure is stored. The key is sql and the value is the sql query result. Java's String equals() is used when 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 sql can be ignored. It can be considered that the key is trimmed and then compared with equals.

View mysql setting parameters

Execution

show variables like '%query_cache%';

You can see the relevant parameters:

• query_cache_type: 0-Do not enable query cache; 1-Enable, 2-Enable, the default value is 0;

• query_cache_size: Set the total size of the cache area. The minimum value allowed to set query_cache_size is 40K. The default is 1M. The recommended setting is: 64M/128M;

• query_cache_limit: Limits the maximum size of a single query record set that can be cached in the cache area. The default setting is 1M

• When query_cache_type is 1, as long as the query cache requirements are met, the client's query statements and records All sets can be cached. If SQL_NO_CACHE is added to SQL, it will not be cached;

• query_cache_type is 2, as long as the parameter: SQL_CACHE is added to SQL and meets the requirements of query caching, the customer The query statements and record sets on the client can be cached.

View cache usage

show status like '%Qcache%%';

##You can see the relevant parameters:

• Qcache_hits: the number of cache hits;

• Qcache_inserts: the number of insertions in the cache, each cache adds 1. Note that this is not the cache quantity;

Enable query cache

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 every time the memory block of the query cache is updated, it needs to be locked.

Turn off query cache

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

Applicable scenarios

Used for scenarios where the same statement is submitted frequently and the table data does not change very frequently, such as some static pages or a certain block in the page Information that changes infrequently.

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

Not applicable scenarios

The query cache strictly requires that the two SQL requests must be exactly the same, including SQL statements, connected databases, protocol versions, character sets and other factors will all affect . The following are several scenarios where query caching is not applicable:

• Subquery;

• SQL called in procedures, functions, triggers, and events , or refer to these results;

• When the query involves some special functions, 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.

• Queries similar to SELECT...LOCK IN SHARE MODE, SELECT...FOR UPDATE, SELECT..INTO OUTFILE/DUMPFILE, SELECT * FROM... WHERE autoincrement_col IS NULL;

• The SELECT execution plan uses temporary tables;

• Queries that do not reference any table, such as SELECT 1 2;

• The query generated warnings;

• The SQL_NO_CACHE keyword exists in the SELECT statement;

• involves partition tables.

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of What is the principle of mysql database query caching. For more information, please follow other related articles on the PHP Chinese website!