MySQL Query cache usage and performance details

MySQL Query Cache defaults to open. To a certain extent can improve the effect of the query, but not necessarily the best solution, if there are a large number of modifications and queries, due to the cache failure caused by the modification, the server will cause a lot of overhead, you can pass Query_cache_type "0 (off) 1 (ON) 2 (DEMAND ) to control the cached switch.

Note that the MySQL query cache is case sensitive, because query cache in memory is a hash structure to map, the hash algorithm is based on the composition of the SQL statement characters, so any changes in SQL statements cache, which is also the project development to The reason for establishing the code for writing SQL statements

1. When cache
A the MySQL query cache content is a select result set, cache uses the complete SQL string to do key, and is case-sensitive, the space and so on. That is, two SQL must be identical in order to cause a cache hit.
b Prepared statement will never cache the results, even if the parameters are exactly the same. It is said that it will be improved after 5.1.
c) Where conditions include certain functions that will never be cache, such as Current_date, now, and so on.
D. Functions such as date, if returned in hours or days, it is best to calculate the first and then pass in.
SELECT * from foo where date1=current_date--will not be cache
SELECT * from foo where date1= ' 2008-12-30 '-cache, correct approach
e) Too large result set will not be cache (< Query_cache_limit)

2. When invalidate
(a) Once the table data for any row of changes, based on the table related cache immediately complete failure.
b Why not make a smart point to judge whether the contents of the cache is modified? Because the analysis of cache content is too complex, the server needs to pursue maximum performance.

3. Performance
A cache may not always improve performance on all occasions
When there are a large number of queries and a large number of changes, the cache mechanism may cause performance degradation. Because each modification will cause the system to do cache failure operation, resulting in no small overhead.
In addition, the access of the system cache is controlled by a single global lock, when a large number of > queries will be blocked until the lock is released. So do not simply think that setting cache will definitely bring performance improvements.
b) Large result set will not be overhead by the cache
Too large result set will not be cache, but MySQL does not know beforehand the length of result set, so can only wait until the reset set after the cache added to the threshold Query_cache_limit will simply discard this cache. This is not an efficient operation. If qcache_not_cached is too large in MySQL status, you can explicitly add Sql_no_cache control to the potentially large result set's SQL.
Query_cache_min_res_unit = (query_cache_size–qcache_free_memory)/Qcache_queries_in_cache

4. Memory Pool Usage
MySQL Query cache uses the memory pool technology to manage its own memory release and allocation, rather than through the operating system. The base unit used by the memory pool is a variable-length block, and a result set's cache is strung together by a list of these blocks. Because it doesn't know how big this resultset ultimately is when you store the result set. The shortest block length is query_cache_min_res_unit, and the last block of ResultSet performs the trim operation.

Query Cache has a very important role to play in improving database performance.

The settings are also very simple, only need to write two lines in the configuration file: Query_cache_type and Query_cache _size, and the MySQL query cache is very fast! And once hit, send directly to the client, save a lot of CPU time.

Of course, non-SELECT statements have an effect on buffering, which may cause data in the buffer to expire. A partial table modification caused by an UPDATE statement will invalidate all buffered data on the table, a step that MySQL does not take to balance performance. Because if you need to check for modified data each time you UPDATE, then withdrawing a partial buffer will result in increased complexity of the code.

Query_cache_type 0 Delegates do not use buffering, 1 delegates use buffers, and 2 delegates are used as needed.

Setting 1 means that buffering is always valid, and if you do not need a buffer, you need to use the following statement:

The code is as follows	Copy Code
SELECT Sql_no_cache * from my_table WHERE ...

If set to 2, you need to turn on the buffer, you can use the following statement:

The code is as follows	Copy Code
SELECT Sql_cache * from my_table WHERE ...

Use Show status to view the buffering situation:

The code is as follows

Copy Code

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 executes:

The code is as follows	Copy Code
Mysql> Show status like ' com_sel% '; +---------------+---------+ | variable_name | Value | +---------------+---------+ | Com_select | 2889628 | +---------------+---------+ 1 row in Set (0.01 sec)

In this case, MySQL hits 83,951 of the 2,889,628 queries, and only 545,875 of the INSERT statements. Therefore, there is a big gap between the two and the total query for 2.8 million, so we know that the buffer type used in this example is 2.

In the case of type 1, the qcache_hits value would be much larger than the Com_select