Detailed explanation 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 large number of modifications and queries, cache failure caused by modifications will cause great overhead to the server. query_cache_type "0 (OFF) 1 (ON) 2 (DEMAND)" can be used to control the cache switch.

It should be noted that mysql query cache is case sensitive, because Query Cache is mapped by HASH structure in memory, and HASH algorithm is based on the characters that constitute SQL statements, so any change of sql statements re-cache, which is also the reason why sql statement writing specifications should be established in project development

1. When cache

a) mysql query cache content is the result set of select. cache uses the complete sql string as key and is case sensitive, space sensitive, etc. That is, two sql must be completely 1 to cause cache to hit.

b) prepared statement will never cache to the result, even if the parameter is exactly 1 sample. It is said that it will be improved after 5.1.

c) where conditions include functions that will never be cache, such as current_date, now, etc.

d) If a function such as date returns in hours or days, it is best to calculate it before passing it in.
select * from foo where date1=current_date-will not be used by cache
select * from foo where date1= '2008-12-30'--by cache, the correct way

e) Too large result set will not be cache ( < query_cache_limit)

2. When invalidate

a) 1 Once any 1 row modification of the table data is made, all relevant cache based on the table will immediately become invalid.

b) Why not make a smart 1 point to determine whether the content of cache is modified? Because analyzing cache content is too complicated, the server needs to pursue maximum performance.

3. Performance

a) cache may not always improve performance in all situations

The cache mechanism can cause performance degradation when there are a lot of queries and a lot of modifications. Because each modification will cause the system to do cache failure operation, causing a lot of overhead.

In addition, the access of system cache is controlled by a single 1 global lock, and a large number of > Will be blocked until the lock is released. So don't simply think that setting cache will definitely bring performance improvement.

b) Large result set will not be overhead of cache

Too large result set will not be cache, but mysql does not know the length of result set in advance, so reset set can only simply discard this cache after cache is added to the critical value query_cache_limit. This is not an efficient operation. If Qcache_not_cached in mysql status is too large, the control of SQL_NO_CACHE can be explicitly added to sql for potentially 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 pooling technology to manage memory release and allocation by itself rather than through the operating system. The basic unit used in the memory pool is the variable-length block, and an cache of result set strings these block through a linked list. Because when you store result set, you don't know how big this resultset will eventually be. The shortest length of block is query_cache_min_res_unit, and the last block of resultset performs trim operation.

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

It is also very simple to set up, only need to write two lines in the configuration file: query_cache_type and query_cache _size, and query cache of MySQL is very fast! Moreover, once a hit is made, it will be sent directly to the client, saving a lot of CPU time.

Of course, non-SELECT statements have an impact on buffering, and they may expire the data in the buffer. A partial table modification caused by an UPDATE statement invalidates all buffered data for that table, a measure that MySQL did not take to balance performance. Because every time UPDATE needs to check the modified data, and then unbuffering part of it will lead to increased code complexity.

query_cache_type 0 for no buffering, 1 for buffering, and 2 for use as needed.

Setting 1 means that buffering is always valid. 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 and buffering needs to be turned on, you can use the following statement:

The code is as follows

SELECT SQL_CACHE * FROM my_table WHERE ...

SHOW STATUS allows you to view the buffering situation:

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 ratio, you need to know how many SELECT statements the server has 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 example, MySQL hits 83,951 of 2,889,628 queries, and only 545,875 INSERT statements. Therefore, the sum of the two is quite different from the total query of 2.8 million, so we know that the buffer type used in this example is 2.

In the case of Type 1, Qcache_hits is much larger than Com_select