Detailed MySQL Query cache

Query caching refers to the text returned to the client that is stored using the SELECT syntax to query. When the same request occurs again, the data is fetched from the query cache instead of being executed again. The query cache is a shared session, so one client request may get the same result as another client's request.

Query caching is useful when the server receives the same requests frequently and the table data in the database changes infrequently, and it can greatly improve the access efficiency of the application. Many Web servers use this principle to dynamically generate pages based on the content of the database.

The query cache does not return stale data, and the associated query cache is automatically purged when the table data in the database changes. However, query caching does not work when data from the same table is updated in multiple MYSQLD server instances.

The query cache is not valid for data stored in multiple database partition tables. If the query involves more than one database partition table, the query cache is automatically invalidated.

If you want to disable query caching, simply set the size of Query_cache_size to 0 at startup.

Query mitigation may improve the performance of the system in some cases, but it does not mean that the performance of the system can be improved in any situation, and in some cases it may even degrade the performance of the system. Such as

1) There is a large divergence in the Level setting query cache, which some people think might outweigh the benefits of enabling it. The size of the query cache is usually valid when set to dozens of megabytes, but not necessarily when set to hundreds of megabytes.

2) It is very effective to use query caching when the server is overloaded. The efficiency of using query caching for a complex query composed of multiple select is certainly very efficient, but if you insert data frequently, it is much more efficient to use the query cache without caching.

　　How query caching is performed

For the following two queries, the query cache is treated as two different forms of query because the query cache strictly compares the exact bytes of the two queries. For different strings, different data tables, different protocol versions, different character sets will be treated as different queries.

SELECT * FROM Tbl_name select * from Tbl_name

It is not appropriate to use the query cache in the following cases

1) query is a subquery of an external query

2) queries executed in a stored procedure, trigger, or function

If the data table changes, such as adding/modifying/deleting table structure or table data, it will invalidate the query cache and the cached data will be automatically purged from the cache.

The query cache is not valid when the query cache contains the following functions.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/78/F3/wKiom1aEzirD2jl1AABX2u2YMAk603.png "title=" Qqqq.png "alt=" Wkiom1aezird2jl1aabx2u2ymak603.png "/>

case where the query cache is not applicable

1) The query uses a user-defined function or stored procedure.

2) The query contains a user variable or a locally stored program variable.

3) related to Mysql,information_schema and Performance--schema tables in the database.

4) The query involves the database partition table.

5) The query is of the following type:

SELECT ... LOCK in SHARE MODE SELECT ... For UPDATE SELECT ... Into OUTFILE ... SELECT ... Into DumpFile ... SELECT * FROM ... WHERE Autoincrement_col is NULL

6) The query uses a temporary table.

7) The query does not use the data table.

8) The query contains a warning.

9) The user queries the table that has specific permissions.

　　Two ways of using query caching and not using query caching

SELECT Sql_cache ID, name from customer; SELECT Sql_no_cache ID, name from customer;

　　Configure Query caching

When the query cache Query_cache_size is set to a value other than 0, the minimum capacity is 40KB, which is determined by its system structure. How much is appropriate for the application, or depends on the actual situation.

This article is from the "This person's IT World" blog, be sure to keep this source http://favccxx.blog.51cto.com/2890523/1730405

Detailed MySQL Query cache