MySQL Query cache

The query cache stores the SQL statements and the corresponding results that are sent to the client. When executing the same SQL statement, MySQL server retrieves the results directly from the query cache, eliminating the need to parse and execute the SQL statements. The query cache can be shared between sessions.

The query cache applies to environments where table modifications are not frequent and frequently perform the same query. Tables that are not suitable for tables that are frequently updated.

The query cache does not store expired data. When the table is modified, the data in the query cache is brushed out.

In MySQL 5.6.5, the query cache does not support partitioned tables, and queries that involve partitioned tables automatically cancel the query caching feature.

How the query cache works

The SQL request sent by the client is compared to the SQL in the query cache before parsing the SQL statement. The following two SQL statements are different in the query cache:

SELECT * from Tab1_name;

SELECT * from Tab1_name;

Must be the same SQL statement. Also, for other reasons, the same SQL query string may be treated as a different SQL statement. When querying a different database, using a different protocol version, or a different character set, the same SQL statement is cached separately.

The following scenarios are not available for query caching:

-Subqueries for out-of-query queries

Query inside-function, trigger, or event

When the query cache is hit, the value of the qcache_hits is increased by itself.

If the table changes, such as INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, O or drop database operation, all cached query results will become invalid. And is moved out of the query cache.
Queries to the view are also cached.

SQL statement on/OFF query caching feature:

SELECT Sql_cache ID, name from customer; # "Sql_cache" uses query cache for select Sql_no_cache ID, name from customer;# "Sql_no_cache" does not use query cache

To see if the MySQL server query cache is available:

Mysql> Show variables like ' have_query_cache '; +------------------+-------+| Variable_name    | Value |+------------------+-------+| Have_query_cache | YES   |+------------------+-------+

Close Query cache: It is recommended to close the query cache, there is a global lock efficiency is not high

Query_cache_type = 0query_cache_size = 0

When setting the query cache, the value of query_cache_size must not be less than 40k (used to allocate its own structure)

mysql> set global query_cache_size=4000; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> Show  warnings\g*************************** 1. Row **********  level:warning   code:1292message:truncated Incorrect query_cache_size value: ' 4000 ' ******* 2. Row ***************************  level:warning   

MySQL Query cache