Detailed explanation of the simple use of MySQL query cache

SHOW VARIABLES LIKE 'have_query_cache'; 

SHOW VARIABLES LIKE 'query_cache_type'; 

SHOW VARIABLES LIKE 'query_cache_size'; 

SHOW STATUS LIKE 'Qcache%'; 

3. Enable query cache

MySQL query cache is disabled by default. You need to manually configure the parameter query_cache_type to enable query cache. query_cache_type
There are three possible values ​​for this parameter:

1. Configure in usr/my.cnf (the configuration file may also be in /etc/my.cnf)

2. Restart the service

service mysql restart

3. Testing

4. Query Cache SELECT Options

There are two options related to query caching that can be specified in the SELECT statement:

• SQL_CACHE : Caches query results if they are cacheable and the value of the query_cache_type system variable is ON or DEMAND .
• SQL_NO_CACHE : The server does not use the query cache. It neither checks the query cache, nor checks if the result is already cached, nor caches the query results.

SELECT SQL_CACUE id,name FROM customer;
SELECT SQL_NO_CACHE id,name FROM customer;

5. Query cache failure

1. Inconsistent SQL statements

To hit the cache, the query SQL statement must be consistent

SQL1: select count(*) from tb_item;
SQL2: select count(*) from tb_Item;

2. The query statement has some uncertain values

SQL1: select * from tb_item where updatetime < now() limit 1;
SQL2:select user();
SQL3:select database();

3. Do not use any table query statements

select 'A';

4. Query tables in mysql, information_schema or performance_schema databases

select * from information_schema.engines;

5. Queries executed within stored functions, triggers, or event bodies

As title

6. Table changes lead to cache deletion

If a table changes, all cached queries using that table become invalid and are removed from the cache. This includes queries that use MERGE to tables that are mapped to the changed table. A table can be altered by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

The above is a summary of the simple use of MySQL query cache

This is the end of this article about the simple use of MySQL query cache. For more information about the use of MySQL query cache, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!