Detailed explanation of the use of MySQL select cache mechanism

MySQL Query Cache is on by default. To some extent, it can improve the query effect, but it may not be the best solution. If there are a lot of modifications and queries, the cache failure caused by the modifications will cause a lot of overhead to the server. You can control the cache switch through query_cache_type [0 (OFF) 1 (ON) 2 (DEMAND)].

It should be noted that MySQL query cache is case sensitive, because the query cache is mapped in memory with a HASH structure. The basis of the HASH algorithm is the characters that make up the SQL statement, so any change to the SQL statement will be re-cached. This is also the reason why SQL statement writing standards should be established in project development.

1. When to cache

a) The mysql query cache content is the result set of select. The cache uses the complete SQL string as the key and is case-sensitive, space-sensitive, etc. That is, the two SQL statements must be completely consistent to result in a cache hit.

b) The prepared statement will never cache the result, even if the parameters are exactly the same. It is said that it will be improved after 5.1.

c) If the where condition contains certain functions, they will never be cached, such as current_date, now, etc.

d) If functions like date return data in hours or days, it is best to calculate it first and then pass it in.
select * from foo where date1=current_date -- will not be cached
select * from foo where date1='2008-12-30' -- cached, correct approach

e) A result set that is too large will not be cached (< query_cache_limit)

2. When to invalidate

a) Once any row of table data is modified, all caches related to the table will become invalid immediately.

b) Why not be smarter and determine whether the modified content is cache content? Because analyzing cache contents is so complex, the server needs to pursue maximum performance.

3. Performance

a) Cache may not always improve performance in all situations

When there are a large number of queries and a large number of modifications, the cache mechanism may cause performance degradation. Because each modification will cause the system to perform cache invalidation operations, resulting in considerable overhead.

In addition, access to the system cache is controlled by a single global lock, and a large number of queries will be blocked until the lock is released. So don't simply assume that setting up cache will definitely lead to performance improvements.

b) Large result sets will not be cached.

A result set that is too large will not be cached, but MySQL does not know the length of the result set in advance, so it can only wait until the cache is added to the critical value query_cache_limit after the reset set, and then simply discard the cache. This is not an efficient operation. If Qcache_not_cached in mysql status is too large, you can explicitly add SQL_NO_CACHE control to the SQL with potential large result sets.
query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

4. Memory pool usage

MySQL query cache uses memory pool technology to manage memory release and allocation by itself instead of through the operating system. The basic unit used by the memory pool is a variable-length block, and a result set cache strings these blocks together through a linked list. Because when storing the result set, you don't know how big the result set will eventually be. The minimum length of a block is query_cache_min_res_unit, and the last block of a resultset will perform a trim operation.

Query Cache plays a very important role in improving database performance.

The setup is also very simple, just two lines need to be written in the configuration file: query_cache_type and query_cache _size, and MySQL's query cache is very fast! And once a hit is found, it is sent directly to the client, saving a lot of CPU time.

Of course, non-SELECT statements have an impact on the cache and may cause the data in the cache to expire. Partial table modification caused by an UPDATE statement will invalidate all buffered data for the table, which is a measure that MySQL does not take in order to balance performance. Because, if each UPDATE needs to check the modified data and then withdraw part of the buffer, the complexity of the code will increase.

query_cache_type 0 means no cache is used, 1 means cache is used, and 2 means it is used as needed.

Setting 1 means that buffering is always effective. If buffering is not needed, you need to use the following statement:

The code is as follows

SELECT SQL_NO_CACHE * FROM my_table WHERE ...

If it is set to 2, you need to enable buffering, you can use the following statement:

The code is as follows

SELECT SQL_CACHE * FROM my_table WHERE ...

Use SHOW STATUS to view the buffer status:

The code is as follows

mysql> show status like 'Qca%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 8 |
| Qcache_inserts | 545875 |
| Qcache_hits | 83951 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2343256 |
| Qcache_free_memory | 33508248 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 18 |
+-------------------------+----------+
8 rows in set (0.00 sec)

If you need to calculate the hit rate, you need to know how many SELECT statements the server executed:

The code is as follows

mysql> show status like 'Com_sel%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 2889628 |
+---------------+---------+
1 row in set (0.01 sec)

In this case, MySQL hit 83,951 of the 2,889,628 queries, and only 545,875 were INSERT statements. Therefore, the sum of the two is far from the total query of 2.8 million, so we know that the buffer type used in this example is 2.

In the example of type 1, the value of Qcache_hits will be much larger than Com_select

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.