MySQL cache Research

The cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly retrieves the results from the cache without parsing and executing the SQL statement. If the table is changed, all cache queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, the query cache is not suitable for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, the query cache will save a lot of performance.

Rules for MySQL to check cache hits

1. When checking the cache, MySQL does not parse, refresh, or parameterize the statements. It precisely uses the query statements and other data sent from the client. As long as the characters are case sensitive, spaces, or comments are slightly different, the query cache considers this as a different query.

2. query cache does not store queries with uncertain results. Therefore, any query that contains uncertain functions (such as NOW () or CURRENT_DATE () will not be cached. Similarly, if CURRENT_USER () or CONNECTION_ID () are executed by different users, queries with different results will not be cached. In fact, the query cache does not cache queries that reference user-defined functions, storage functions, User-Defined variables, temporary tables, tables in the mysql database, or any table with column-level permissions.

3. the query must be identical (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

Enable query cache overhead

Before reading a query, you must check the cache.

If the query can be cached but not in the cache, saving the results after the results are generated will incur some additional overhead. Writing Data Query also has additional overhead because it must invalidate the data table in the cache.

These costs are relatively small, so the query cache is very good. However, you will see later that additional overhead may also increase. The most beneficial query from the cache may be the type that requires a lot of resources to produce results, but does not need a lot of space to save. Therefore, the cost for storage, return, and invalidation is low. Aggregate Query. For example, using COUNT () in a large table to produce small results is in line with this category.

Analyze and adjust an illustration of the query cache (found online)

 

View the cache status of Msyql

If the value of have_query_cache is yes, the cache is enabled.

Binlog_cache_size: the default size is 32768, that is, 32 K.

Max_binlog_cache_size: The default value is 18446744073709547520. This value is large enough. This parameter corresponds to binlog_cache_size, which indicates the Maximum cache size that binlog can use. If there are too many transactions in the system and the value of this parameter is set to small, an error is returned.

Query_cache_limit: Maximum capacity of a single Query result set that can be cached. The default value is 1 MB. The Query result set that exceeds this parameter will not be cached.

Query_cache_min_res_unit: sets the minimum memory size allocated for each Query Cache, that is, the minimum memory space occupied by each Query Cache.

Query_cache_size: Set the memory size used by the Query Cache. The default value is 0 and the size must be an integer multiple of 1024. If it is not an integer multiple, MySQL automatically adjusts and reduces the minimum size to a multiple of 1024.

Query_cache_type: the switch that controls the Query Cache function. It can be set to 0 (OFF), 1 (ON), and 2 (DEMAND). The meanings are as follows:

0 (OFF): disables the Query Cache function. Query Cache is not used in any situation.

1 (ON): Enable the Query Cache function. However, when the SQL _NO_CACHE prompt is used in the SELECT statement, the Query Cache is not used.

2 (DEMAND): Enable the Query Cache function, but the Query Cache is used only when the SQL _CACHE prompt is used in the SELECT statement.

Query_cache_wlock_invalidate: controls whether to invalidate the Query Cache related to the table when a write lock occurs on the table. If it is set to 1 (TRUE ), when the write lock is performed, all Query Cache related to the table will be invalidated. If it is set to 0 (FALSE), the Query Cache related to the table can still be read at the lock time.

Check Cache Usage

In this case, the query cache hit rate is 175/(175 + 4) = 97%. Because of the individual test database, fewer queries, fewer rows, and a high hit rate. Hey ~~

MySQL provides a series of Global statuses to record the current Status of the Query Cache, as follows:

Qcache_free_blocks: Number of memory blocks in the Query Cache that are currently in idle state

Qcache_free_memory: total Query Cache memory that is currently in idle state

Qcache_hits: Number of Query Cache hits

Qcache_inserts: the number of times that a new Query Cache is inserted into the Query Cache, that is, the number of times that a new Query Cache is not hit.

Qcache_lowmem_prunes: the number of times the old Query Cache needs to be deleted from the Query Cache to be used for new Cache objects when the Query Cache memory capacity is insufficient.

Qcache_not_cached: Number of SQL statements not cached, including SQL statements that cannot be cached and SQL statements that are not cached due to query_cache_type

Qcache_queries_in_cache: current number of SQL statements in the Query Cache

Qcache_total_blocks: Total number of blocks in Query Cache

Clear Cache

The previous Qcache_hits Qcache_inserts are cleared.