Shulou(Shulou.com)06/01 Report--

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

It should be noted that mysql query cache is case-sensitive, because Query Cache is mapped in memory with a HASH structure, and the HASH algorithm is based on the characters that make up the SQL statement, so any change in the sql statement is re-cache, which is why the writing specification of the sql statement should be established in the project development.

1. When to cache

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

B) 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 some functions are included in the where condition, they will never be cache, such as current_date, now, etc.

D) if a function such as date returns an hour or sky level, it is best to calculate it first and then pass it in.

Select * from foo where date1=current_date-- will not be cache

Select * from foo where date1='2008-12-30'-by cache, the right thing to do

E) result set that is too large will not be cache (

< query_cache_limit) 2. 何时invalidate a) 一旦表数据进行任何一行的修改，基于该表相关cache立即全部失效。 b) 为什么不做聪明一点判断修改的是否cache的内容？因为分析cache内容太复杂，服务器需要追求最大的性能。 3. 性能 a) cache 未必所有场合总是会改善性能 当有大量的查询和大量的修改时，cache机制可能会造成性能下降。因为每次修改会导致系统去做cache失效操作，造成不小开销。 另外系统cache的访问由一个单一的全局锁来控制，这时候大量>

Will be blocked until the lock is released. So don't simply assume that setting cache is bound to improve performance.

B) the cost of large result set will not be incurred by cache

A result set that is too large will not be cache, but mysql does not know the length of the result set in advance, so it can only be discarded simply after the cache is added to the critical value query_cache_limit by reset set. This is not an efficient operation. If the Qcache_not_cached in the mysql status is too large, you can explicitly add SQL_NO_CACHE control to the sql of the potentially large result set.

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 itself, rather than through the operating system. The basic unit used in the memory pool is a variable-length block, and a cache of a result set strands these block together through a linked list. Because you don't know how big the resultset will be when you store the resultset. The shortest length of the block is query_cache_min_res_unit, and the last block of the resultset performs the trim operation.

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

Its setup is also very simple, only need to write two lines in the configuration file: query_cache_type and query_cache_ size, and MySQL's query cache is very fast! And once hit, 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 cache. Partial table modifications caused by a UPDATE statement will invalidate all buffered data on the table, which is a measure that MySQL does not take to balance performance. Because if each UPDATE needs to check the modified data and then withdraw some of the buffering, it will increase the complexity of the code.

Query_cache_type 0 means not using buffering, 1 means using buffering, and 2 means using buffering as needed.

Setting 1 means that buffering is always valid, and 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 you set it to 2 and you need to enable buffering, you can use the following statement:

The code is as follows

SELECT SQL_CACHE * FROM my_table WHERE...

You can view buffering with SHOW 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 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 case, MySQL hits 83951 out of 2889628 queries, and only 545875 INSERT statements. Therefore, there is a big difference between them and 2.8 million of the total query, so we know that the buffer type used in this example is 2.

In the case of type 1, the value of Qcache_hits is much larger than that of Com_select.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.