Query cache for MYSQL/MARIADB

Query cache:

Cached data: k/v, which is a key-value pair;

Key: The hash value of the query statement;

Value: Query result of query statement;

To determine whether the cache hits the standard:

By comparing the hash value of the whole query statement, the exact same is hit;

Some of the results of the query cannot be saved:

The database you are querying may contain sensitive information

A user-defined function (UDF) is included in the query statement;

storage functions;

user-defined variables;

Query requests initiated for temporary tables;

Queries that contain column-level authorizations;

Built-in functions for MySQL with indeterminate result values:

such as: Now (), current_date (), Current_time (), Current_User (), ...

Query cache related server variables in MySQL database:

mariadb [(none)]> show global variables like  ' query_cache% '; +---------------- --------------+---------+| variable_name                 | value   |+------------------------------+---------+|  query_cache_limit            | 1048576  | |  query_cache_min_res_unit     | 4096    | |  query_cache_size             | 0        | |  query_cache_strip_comments   | off     | |  query_cache_type             | on       | |  query_cache_wlock_invalidate | off     |+------------------------------+---------+6 rows in set  (0.00 sec) 

Query_cache_limit: Maximum number of bytes of query results that can be cached

Query_cache_min_res_unit: The smallest allocation unit of memory blocks in the query cache;

Query_cache_size: The total available space in memory for the query cache request;

Query_cache_strip_comments: Used to control whether the comment portion in the SQL query statement is removed and then stored as a key in the query cache.

Query_cache_type: Switch for cache function on or off:

Query_cache_wlock_invalidate: If a connection session has a write lock applied to a table, is it still possible to query from the cache and return the query results;

To view the cache server Status parameter, set the size of the memory space of the query cache request, which must be an integer multiple of 1024;

mariadb [(None)]> set @ @global. query_cache_size=134217728; query ok, 0 rows affected  (0.00 sec) mariadb [hellodb]> show  global status like  ' qcache% '; +-------------------------+-----------+| variable_name            | value     |+------ -------------------+-----------+| qcache_free_blocks      | 1          | |  qcache_free_memory      | 134198384 | |  Qcache_hits             | 1          | |  Qcache_inserts          | 1          | |  qcache_lowmem_prunes    | 0         | |  Qcache_not_cached       | 1          | |  qcache_queries_in_cache | 1         | |  Qcache_total_blocks     | 4          |+-------------------------+-----------+8 rows in set  (0.00 sec)

Qcache_free_blocks: Indicates how many blocks are currently remaining in the query cache;

Qcache_free_memory: Memory space that is still idle in the query cache;

Qcache_hits: Represents the hit count of query statements in the query cache. The larger the number, the better the caching effect.

Qcache_lowmem_prunes: This parameter records how many query requests are moved out of cache based on the LRU algorithm because of insufficient memory space, or if the value is large, the allocated memory space for the query cache is too small;

Qcache_not_cached: The number of query requests that are not cached, depending on the setting of the Query_cache_type variable;

Qcache_queries_in_cache: The number of results of query requests cached in the current query cache;

Qcache_total_blocks: The total number of blocks allocated in the current query cache;

Calculation of cache Hit ratio:

qcache_hits/(qcache_hits + qcache_inserts)

Determine whether to hit the cache by looking at the server parameter differences;

First, look at the contents of the table, then view the parameters, view the table contents, and view the parameters;

mariadb [hellodb]> select * from coc;+----+---------+----------+| id |  classid | courseid |+----+---------+----------+|  1 |        1 |        2 | |   2 |       1 |         5 | |   3 |       2 |         2 | |   4 |       2 |         6 | |   5 |       3 |         1 | |   6 |       3 |         7 | |   7 |        4 |        5 | |   8 |       4 |         2 | |   9 |       5 |         1 | |  10 |       5 |         9 | |  11 |       6 |         3 | |  12 |       6 |         4 | |  13 |       7 |         4 | |  14 |       7 |         3 |+----+---------+----------+14 rows in set  (0.00 sec) mariadb [hellodb]> show global status like  ' Qcache% '; +----------------- --------+-----------+| variable_name           |  value     |+-------------------------+-----------+| qcache_free_blocks       | 1         | |  qcache_free_memory      | 134198384 | |  Qcache_hits             | 0          | |  Qcache_inserts          | 1          | |  Qcache_lowmem_prunes    | 0          | |  qcache_not_cached       | 1         | |  qcache_queries_in_cache | 1         | |  Qcache_total_blocks     | 4          |+-------------------------+-----------+8 rows in set  (0.00 sec) MariaDB  [hellodb]> select * from coc;+----+---------+----------+| id |  classid | courseid |+----+---------+----------+|  1 |        1 |        2 | |   2 |       1 |         5 | |   3 |       2 |         2 | |   4 |       2 |        6 | |   5 |       3 |         1 | |   6 |       3 |         7 | |   7 |       4 |         5 | |   8 |       4 |         2 | |   9 |       5 |         1 | |  10 |       5 |         9 | |  11 |       6 |         3 | |  12 |       6 |        4 | |  13 |       7 |         4 | |  14 |       7 |         3 |+----+---------+----------+14 rows in set  (0.00 sec) mariadb [ hellodb]> show global status like  ' qcache% '; +-------------------------+----------- +| variable_name           | value      |+-------------------------+-----------+| qcache_free_blocks       | 1         | |  qcache_free_memory      | 134198384 | |  Qcache_hits             | 1          | |  Qcache_inserts          | 1          | |  Qcache_lowmem_prunes    | 0          | |  Qcache_not_cached       | 1          | |  qcache_queries_in_cache | 1         | |  Qcache_total_blocks     | 4          |+-------------------------+-----------+8 rows in set  (0.00 sec)

By qcache_hits parameter comparison, it can be found that after adding the cache through Qcache_inserts, the query is entered again, which is the result of extracting from the cache;

Note that if you write to the table (add, delete, change), MySQL automatically clears all cache entries in the query cache that are related to that table, so that the query request associated with this table must rebuild the cached content;

Query cache for MYSQL/MARIADB