Detailed description of MySQL query Cache

Detailed description of MySQL query Cache

Query cache stores the text returned to the client using the SELECT syntax query. When the same request occurs again, data is retrieved from the query cache instead of being queried again. The query cache is shared Session, so the request of one client may be the same as that of another client.

When the server frequently receives the same request and the table data in the database changes frequently, the query cache is very useful and can greatly improve the access efficiency of applications. Many Web servers use this principle to dynamically generate pages based on database content.

The query cache does not return expired data. When the table data in the database changes, the related query cache is automatically cleared. However, the query cache is not valid when the data in the same table is updated in multiple mysqld server instances.

Query cache is invalid for data stored in multiple database partition tables. If the query involves multiple database partition tables, the query cache automatically becomes invalid.

To disable the query cache, set query_cache_size to 0 at startup.

The query cache improves the system performance under certain conditions, but it does not mean that the system performance will be improved under any circumstances. In some cases, the system performance may even be reduced. For example

1) there is a lot of disagreement about hierarchical query cache settings. Some people think it may be beyond the benefit of enabling it. The query cache size is usually valid when it is set to dozens of megabytes, but it is not necessarily set to several hundred megabytes.

2) query cache is very effective when the server is overloaded. The efficiency of using the query cache for a complex query composed of multiple SELECT statements is certainly very efficient. However, if data is inserted frequently, if you do not use the cache, the query cache is much more efficient.

Query cache execution Principle

For the following two types of queries, the query cache uses two different forms of queries, because the query cache strictly compares whether the two queries have identical bytes. For different strings, different data tables, different protocol versions, different character sets will be treated as different queries.

SELECT * FROM tbl_nameSelect * from tbl_name

Query cache is not suitable in the following situations

1) a query is a subquery of an external query.

2) queries executed in Stored Procedures, triggers, or functions

If the data table changes, such as adding/modifying/deleting the table structure or table data, the query cache becomes invalid and the cached data is automatically cleared from the cache.

When the query cache contains the following functions, the query cache is not valid.

Query situations where the cache is not applicable

1) query user-defined functions or stored procedures.

2) query user variables or locally stored program variables.

3) it involves mysql, INFORMATION_SCHEMA, and performance-schema tables in the database.

4) query involves database partition tables.

5) the query type is as follows:

SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATESELECT ... INTO OUTFILE ...SELECT ... INTO DUMPFILE ...SELECT * FROM ... WHERE autoincrement_col IS NULL

6) temporary tables are used for query.

7) No data table is found.

8) the query contains warnings.

9) The table to be queried has specific permissions.

Query cache and no query Cache

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

Configure query Cache

When query_cache_size is set to a non-zero value, the minimum capacity is 40 kb, which is determined by its system structure. When you use an application, you must set it to a large value based on the actual situation.

This article permanently updates the link address: