Mysql--query-cache

Knowledge Preparation:

1. What is the query-cache of MySQL?

MySQL can cache the execution of the SELECT statement and the result set corresponding to the SELECT statement, and return the result directly the next time the same SELECT statement is invoked, instead of executing the SELECT statement again.

Due to the lack of this step of execution, the results are relatively quick to return. MySQL to ensure that the result set of the cache is consistent with the result set returned when the SELECT statement is executed again, so MySQL will judge the SELECT statement

The table used is not changed, if there is so cached result set is void, in order to get the correct result MySQL can only be the SELECT statement to execute again;

2, to pay attention to the place:

1, Query-cache is shared between the sessions of each session.

2, Query-cache set too big then its maintenance costs correspondingly become larger, this time it will affect performance.

3, in order to confirm Query-cache in the current business scenario is useful, the best way is to test, and over time, the previous useful query-cache may not always be useful;

4. Query-cache returns a result set from Query-cache only if the SELECT statement is exactly the same and the table does not change, note that the same refers to the byte sequence

, that is, select and select are treated as two different things, and select and select may be treated as two different things because they can be used.

Different character sets, which we cannot see in human eyes;

5, if a select query is a subquery of a query, then the results of this select will not be cached, that is, Query-cache will only cache the final result.

6. If a select is defined in stored function, event, trigger, then its result set will not be cached.

7, even if there is a query-cache result set available, the user will not have the right to return.

8, the table change statement includes: Insert, update,delete,truncate table,alter table,drop table,drop database.

9. If the select includes an indeterminate function then the result set is not cached.

Configuration Query-cache:

1, have_query_cache this variable used to detect whether the Query_cache is turned on, note that if the binary package is installed MySQL This value will always be yes;

2, if you want to close Query_cache then query_cache_size parameter Query_cache_type parameter to start the time set to 0.

3. Query_cache_limit control what is the maximum size of a result set.

4. Flush query cache and reset query cache are used to clear query_cache, and their two effects are equivalent.

5, if a query is Query-cache then qcache_hits this status will add 1, otherwise com_select will add 1.

Mysql--query-cache