Database mysql/mariadb Knowledge Point--query cache

Execution path of the query

1. SQL statements
2. Query cache
3. Parser
4. Parse tree
5. Pretreatment
6. Find the best query path
7. Query optimization SQL statements
8. Execution plan
9. API call Storage Engine
10. Call data, return results

Caches the result set and SQL statements for a select operation or preprocessing query, when there is a new SELECT statement or preprocessing query statement request, first to query the cache, to determine whether there is a set of records available, judging the criteria: The SQL statement with the cache, is exactly the same, case-sensitive.

Do not need to do any parsing and execution of SQL statements, of course, syntax parsing must pass the preceding, directly from the query cache to obtain the results of queries, improve query performance

The decision rule of query cache is not intelligent enough, which means that the threshold of query cache is improved and its efficiency is reduced. The use of query caching increases the overhead of checking and cleaning the recordset in query cache

Query cache

Query the cache, and download the cache of the query results, and if the query statements are identical, return the results in the cache directly;

We can use the following statement to see if the current service has query caching enabled:

MariaDB [(None)]> SHOW VARIABLES like '%query_cache% '; +------------------------------+----------+| Variable_name                | Value    |+------------------------------+----------+| have_query_cache             | YES      | | query_cache_limit |            1048576 |  | query_cache_min_res_unit     | 4096     | | query_cache_size             | 33554432 | | Query_cache_strip_comments   | OFF      | | query_cache_type             | On       | | query_cache_wlock_invalidate | OFF      |+------------------------------+----------+7 rows in Set (0.10 sec)

You can see that the value of Query_cache_type is on, that is, the query caching function has been turned on;

The value of Query_cache_type can be set to: On, off, DEMAND, respectively, started, disabled and on-demand cache, set in the file my.cnf;

VIM/ETC/MY.CNF[MYSQLD] Added: Query_cache_type = On

Have_query_cache indicates whether the current database supports caching capabilities; Yes

Query_cache_limit represents the maximum value of a single query cache, and if the query results exceed this size, the result is not cached even if the current result is specified; default is 1M

Query_cache_min_res_unit indicates that the cache is stored in memory of the smallest unit, the default is 4K, if the query results less than 4 K, will also occupy 4k of memory, so set up the conference memory space wasted, set too small, will be frequently allocated memory treatment by or frequent recovery

Query_cache_size indicates the total size of the query cache

Query_cache_wlock_invalidate indicates whether the queried table is locked by write lock and returns the structure using the cache

Only when the query statement is exactly the same, the cache can be hit, the query SQL statements are the same, the case is different;

MariaDB [(None)]> select * from Stu; MariaDB [(None)]> SELECT * from Stu;

When the query request is received, MySQL will hash the query statement, calculate the corresponding hash, through this hash value to find out if there is a corresponding cache, so, even if the query statement case is different, it will be considered to be different query statements; The hash code does not hit the corresponding cache, MySQL will put the corresponding hash value in the corresponding hash table, while the query results are stored in the corresponding cache, if the hash value of the query statement hit the corresponding cache entry, then return the response from the cache, if the cache corresponding table data changes, then the query cache, All caches related to the changed data table will be invalidated;

So, how can we flexibly use the cache, the following methods can be used;

When caching is turned on (query_cache_type=on), specifies that the corresponding query statement does not use the cache

MariaDB [(None)]> Select Sql_no_cache name from Stu;

You can also use cache when using cache on Demand (Query_cache_type=demand), where = = Specify the corresponding query statement

MariaDB [(None)]> Select Sql_cache name from Stu;

Viewing cache usage

MariaDB [(none)]> SHOW status like ' qcache% '; +-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        | | Qcache_free_memory      | 33536824 | | Qcache_hits             | 0        | | Qcache_inserts          | 0        | | Qcache_lowmem_prunes    | 0        | | qcache_not_cached |       | Qcache_queries_in_cache | 0        | | Qcache_total_blocks     | 1        |+-------------------------+----------+8 rows in Set (0.33 sec)

The meanings of each of these parameters are as follows:

Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.

Qcache_free_memory: Free memory in the cache.

Qcache_hits: Increases each time the query hits the cache

Qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.

Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The above free_blocks and free_memory can tell you which kind of situation)

Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().

Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.

Qcache_total_blocks: The number of blocks in the cache.

Based on the statistics above, and the relevant values of the cache settings, the correlation index of the query cache is calculated to determine whether the query cache is helpful to us. Everything is subject to actual production

Query Cache fragmentation rate = (qcache_free_blocks/qcache_total_blocks) * 100%

Query Cache Utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

Can be inferred from the above two formulas; if the number of blocks in the qcache_free_memory cache is larger, the higher the fragmentation rate, the smaller the Query_cache_min_res_unit value can be tried, or the cache fragmentation will be cleaned up in time.

To clean up cache fragmentation:

MariaDB [(None)]> FLUSH QUERY CACHE

If you want to clear a cache that already exists in the query cache, you can use

To remove the cache of all query results from the query cache

MariaDB [(None)]> RESET QUERY CACHE;

If the query cache utilization is too low, it indicates that the query_cache_size setting is too large to be suitably small if the cache utilization is very

High, while the value of Qcache_lowmem_prunes is large, the Query_cache_size setting is slightly smaller.

If you are not sure how large the adjustment is when adjusting the Query_cache_min_res_unit value, you can refer to the following formula

(query_cache_size-qcache_free_memory)/Qcache_queries_in_cache

Query Cache Hit Ratio = (qcache_hits/com_select) * 100%

Where Com_select represents the number of executions of the query statement (this description is not accurate; should be qcache_hits + qcache_inserts), you can use the following statement to get the number of query statements executed

Show status like "com_select%";

Optimizing Query Caching

Database mysql/mariadb Knowledge Point--query cache