mysql query cache

Query Cache

当前版本是否支持Query Cache

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL8.0.

SHOW VARIABLES LIKE 'have_query_cache';

When using a standard MySQL binary, this value is always YES, even if query caching is disabled.

query cache相关变量

SHOW VARIABLES LIKE 'query_cache%';

query_cache_type

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0

disables the query cache, as does setting query_cache_type=0. By default, the query cache is

disabled. This is achieved using a default size of 1M, with a default for query_cache_type of 0.

# 默认query_cache_size=1048576=1M
# 下面query_cache_size会变成0    
SET GLOBAL query_cache_size = 40000;

If the query cache size is greater than 0, the query_cache_type variable influences how it works.

This variable can be set to the following values:

• A value of 0 or OFF prevents caching or retrieval of cached results. (值为0或OFF阻止缓存或检索缓存的结果。)

• A value of 1 or ON enables caching except of those statements that begin with SELECT

SQL_NO_CACHE. (值1或ON启用缓存，但以SELECT SQL_NO_CACHE开头的语句除外)

• A value of 2 or DEMAND causes caching of only those statements that begin with SELECT

SQL_CACHE.(值为2或DEMAND导致仅缓存以SELECT SQL_CACHE开头的那些语句)

If query_cache_size is 0, you should also set query_cache_type variable to 0

For example, a client can disable useof the query cache for its own queries like this:

SET SESSION query_cache_type = OFF;

query_cache_limit

To control the maximum size of individual query results that can be cached, set the

query_cache_limit system variable. The default value is 1MB.

You can set the maximum size that can be specified for the query cache at runtime with the ​​​SET​​​​ statement by using the ​​--maximum-query_cache_size=​​​32M​​​​ option on the command line or in the configuration file

query_cache_min_res_unit

The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.

Qcache

To monitor query cache performance, use SHOW STATUS to view the cache status variables

SHOW STATUS LIKE 'Qcache%';

FLUSH QUERY CACHE (碎片整理)

Defragment the query cache to better utilize its memory. FLUSH QUERY CACHE does not remove

any queries from the cache, unlike FLUSH TABLES or RESET QUERY CACHE.

RESET QUERY CACHE  (清空缓存)

Removes all query results from the query cache.

FLUSH TABLES (清空缓存)

FLUSH TABLES;

FLUSH TABLES WITH READ LOCK;

Closes all open tables, forces all tables in use to be closed, and flushes the query cache and

prepared statement cache. FLUSH TABLES also removes all query results from the query cache, like

the RESET QUERY CACHE statement