MySQL Query cache summary _ MySQL

MySQL Query cache summary bitsCN.com

MySQL Query cache summary

You can use the following SQL statement to view the cache-related parameter status of the current query:

1

Show variables like '% query_cache % ';

The output result is similar to the following:

Query_cache_type

Query cache type, which has three values: 0, 1, and 2. If the value is 0, the query cache is not used. 1 indicates that the query cache is always used. 2 indicates the query cache is used as needed.

If query_cache_type is 1 and you do not want to use the data in the query cache, you can use the following SQL:

1

SELECT SQL _NO_CACHE * FROM my_table WHERE condition;

If the value is 2, you need to use the SQL _CACHE switch parameter to use the cache:

1

SELECT SQL _CACHE * FROM my_table WHERE condition;

Query_cache_size

By default, query_cache_size is 0, indicating that the memory reserved for the query cache is 0, so the query cache cannot be used. Therefore, we need to set the value of query_cache_size:

1

Set global query_cache_size = 134217728;

Note that the value above will not take effect if it is set too small. For example, I used the following SQL statement to set the query_cache_size:

1

Set global query_cache_size = 4000;

2

Show warnings;

The following result is returned:

Cache conditions

The query cache can be seen as the ing between SQL text and query results. If the SQL statement for the second query is the same as that for the first query (note that it must be the same, even if one more space or case is different) and the query cache is enabled, then the second query gets the result directly from the query cache. you can use the following SQL statement to view the number of cache hits (a cumulative value ):

1

Show status like 'qcache _ hits ';

In addition, even if identical SQL statements are used, different character sets, protocols, and so on will be considered different queries and cached separately.

Cache data failure time

When the table structure or data changes, the data in the query cache is no longer valid. These INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database results in invalid cache data. Therefore, the query cache is suitable for applications with a large number of identical queries and is not suitable for applications with a large number of data updates.

Clear query cache

You can use the following three SQL statements to clear the query cache:

1

FLUSH QUERY_CACHE;

2

RESET QUERY_CACHE;

3

Flush tables;

The first SQL statement clears the cache fragments. The second SQL statement removes all queries from the query cache. The third SQL statement disables all opened tables and clears the content in the query cache.

BitsCN.com