2 points first
Enabling cache also brings about overhead, mainly in the following aspects:
Before starting the query, you must check the cache.
If the query is cached but not in the result set, saving the data after the result is generated will incur a certain amount of overhead.
Writing data to the cache also brings about overhead
In some cases, the query cache will not be cached, even if you use SQL _CACHE
Mainly
Reference User-Defined Functions
Reference User-Defined variables
To use the surviving Process
The query contains some real-time system functions, such as now
Temporary table referenced
Although cache brings some overhead
However, caching is important to mysql.
Benefits and disadvantages
The following describes how to use memory in mysql cache.
The memory pool of the query cache is divided into variable-Size Blocks, each of which knows its size, and refers to the pointer of the forward and last logical blocks and physical blocks, when the server starts, it initializes the physical memory used by the query cache. The memory pool starts to take a quick start. Her size is set to the cache size minus the size of 40 kb required by the server. Each time the query results are cached, a cache block is allocated to the query results. However, the server cannot estimate the size of the results to be cached, which is at least equal to query_cache_min_res_unit, instead of generating the final result once in the cache and then sending it to the client, a cache is generated, which ensures that the space is not wasted or insufficient during allocation. Memory block allocation is slow. Because the server needs to check the available memory list and find the suitable size, the server will try to reduce the allocation times. When the results need to be cached, he will build a block with at least the minimum size and put the result in the block. If the block is full but the data is not completely saved, A new block is generated and the data is saved. After the data block is saved, if there is still a gap between the data blocks, the server will edit the data blocks and merge the space into the remaining space, fragments may be generated. Through compression, we can talk about how to merge a shard space into a valid data space.
For the transaction engine, such as innodb, the transaction will affect the cache, because dirty data will also invalidate the cache of other referenced data. This data will not be cached until the transaction is committed or rolled back.
Query_cache_min_res_unit: the minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4 KB)