Query Buffer mechanism of MySQL database

MySQLThe database providesQuery BufferMechanism. Using this query buffer mechanism, MySQL stores SELECT statements and query results in the buffer. Later, the same SELECT statements are case-sensitive.) The results will be read directly from the buffer. To save query time and improve SQL query efficiency. This article mainly introduces the query buffer mechanism of the MySQL database. Next let's take a look at this part.

You can check whether query_cache_size is set properly by adjusting the following parameters:

Qcache inserts

Qcache hits

Qcache lowmem prunes

Qcache free blocks

Qcache total blocks

The value of Qcache_lowmem_prunes is very large, which indicates that the buffer is insufficient frequently. The value of Qcache_hits is very large, which indicates that the query buffer is frequently used. In this case, the value of Qcache_hits is not large, this indicates that your query repetition rate is very low. In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.

Qcache_free_blocks. If this value is very large, it indicates that many fragments in the buffer zone query_cache_type specify whether to use the query buffer.

If it is set:

Query_cache_size = 32 M

Query_cache_type = 1

Obtain the following status values:

Qcache queries in cache 12737 // indicates the number of cached items.

Qcache inserts 20649006

Qcache hits 79060095 // it seems that the repeat query rate is quite high.

Qcache lowmem prunes 617913 // There are so many times when the cache is too low.

Qcache not cached 189896

Qcache free memory 18573912 // The remaining cache space.

Qcache free blocks 5328 // this number seems to be a little large, with a lot of fragments

Qcache total blocks 30953

If the memory permits, you can set query_cache_size to a greater value.

For more information about the MySQL database query and cache mechanism, please visit http://database.51cto.com/mysql/. you will surely be able to get the result!