Introduction to MySQL query cache (1)

Introduction to MySQL query cache (1)

The MySQL query cache and the Oracle query cache are different. The Oracle query cache caches the execution plan, while the MySQL query cache does not cache the execution plan but the entire result set. The benefit of caching the entire result set is self-evident, but because the result set is cached, the query must be identical. The consequence is that the average hit rate is generally not too high. Query cache is the most effective when the data volume of some small applications or data tables is small.

As a new feature, what are the features and limitations of MySQL query cache? One by one:

1. the cache mechanism is transparent to applications. The query result set in the cache can be obtained only by changing the semantics of the query statement in the application. If you do not useQuery_cache_wlock_invalidate = onWhen the MySQL lock table is about to be written, the query can still obtain the result set from the cache even if the table is in the lock status or is waiting to be updated;

2. Only the entire query result set is cached, that is, subqueries, inline views, and some union queries are not cached;

3. The Caching mechanism works at the packet level. The second item only caches the entire query result set because it is limited to this mechanism. Because there is no extra conversion and processing, the cache result set can be returned very quickly;

4. cache processing is performed before resolution query. One reason to ensure high cache performance is that the query cache first queries whether the query cache already exists before executing the query resolution. If the query cache already exists, returns the result set directly.

5. the query must be completely the same. Because query parser is not performed before the query cache exists, the query is not normalized ), therefore, the cache search process is performed in byte order (byte by byte ). To be more specific, different comments, extra spaces, and case-insensitive characters are not pointed to the same cache result set during each query.

6. Only select statements are cached. The insert, delete, and update operations do not need to be cached. The show command and stored procedure (including the SELECT statement in the Stored Procedure) do not enter the cache result set.

7. Do Not Display spaces and comments at the beginning of the query statement. If the first subtitle is not "S" during cache search, the query cache result set will be stopped. Items 1 and 6 have already been explained;

8. prepared statement and cursors cannot be queried. (?)

9. transactions may not be supported. (?)

10. The query results must be completely consistent before they can enter the cache result set. For example, functions in the query statement that dynamically change the query result set, such as UUID, Rand, and connection_id, do not enter the cache result set;

11. The query cache is invalid at the granularity level. When a table is modified, all the cache related to the modified table will immediately become invalid (Invalidation ).

12. fragmentation is easily caused by long-time query cache, which is similar to fragment in Windows disk management. fragments generated by long-time query cache have a certain impact on execution efficiency. The query cache fragments can be viewed as the available memory of the query cache (Qcache_free_memory) Block (Qcache_free_blocks).Flush query CacheCommand to remove this situation.

13. Set the memory used for querying the cache of an appropriate size. For some of the reasons mentioned above, the query cache mechanism of MySQL cannot increase the memory demand infinitely, therefore, it is economical to set an appropriate query cache memory value. You can viewQcache_free_memoryAndQcache_lowmem_prunes.

14. query cache running mode. By default, after the cache is enabled, the MySQL cache mechanism is effective globally. If you only want to cache specific query statements, you canQuery_cache_typeSet to "demand" and add it to the query statement:SQL _cacheFor example:Select SQL _cache domoloseohelper from domolo where author = 'tianchunfeng '.

The above describes some basic features of MySQL query cache. How can I monitor the running status of MySQL query cache? For example, you can monitor the cache hit rate and adjust the cache size.

You can use the following command:

Mysql> show status like 'qcache % ';

Output:

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 1        |

| Qcache_free_memory      | 16766912 |

| Qcache_hits             | 3        |

| Qcache_inserts          | 1        |

| Qcache_lowmem_prunes    | 0        |

| Qcache_not_cached       | 6        |

| Qcache_queries_in_cache | 1        |

| Qcache_total_blocks     | 4        |

+-------------------------+----------+

For details, refer:The MySQL query cache documentation.

 

The next article introduces the optimization of MySQL query cache and the application environment.

References:

Http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

Http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Http://www.cyberciti.biz/tips/how-does-query-caching-in-mysql-works-and-how-to-find-find-out-my-mysql-query-cache-is-working-or-not.html