MySQL query cache and MySQL Cache

MySQL query cache and MySQL Cache

MySQL query cache improves the performance of a single node in just a few ways. The cache is the first, because the memory performance is much higher than the disk performance. Cache is also a typical space-for-time policy. There are too many ways to implement cache, from static page cache to Server dynamic cache, to database-level cache, and so on. With the advent of big data, too many memory databases are generated. There are also a variety of cache tools. You can talk a lot at will, such as Redis, Memcached, Hbase, and so on. Even MySQL has a memory engine that stores data directly into the memory, therefore, cache is everywhere.

 

Http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e

 

There are also a variety of cache formats. The above mentioned is a relatively traditional cache. In fact, a variety of message queues are not a cache mechanism. One more familiar to our developers is Google's low-cost approach. It is said that Google was not equipped with hard disks on all machines to reduce costs and improve performance, instead, the memory is used directly to avoid data loss by the redundancy of multiple nodes.

 

The cache is also flawed. Since it is a cache, the target objects are some unchanged or slightly changed objects. If the data changes frequently, the cache is counterproductive.

 

MySQL query cache is a built-in caching mechanism of MySQL that can cache SQL statements. For example, we send select * from mysql. for such a user query, MySQL first searches for data in the memory and whether the data has expired. If no data exists or the data has expired, it searches for the data in the database, if data exists and does not expire, data is directly returned. The matching rule for SQL is very simple, that is, the comparison of strings. As long as the strings are the same, it is considered to be the same query. The same string does not mean that the SQL syntax is the same, but that the query SQL string is the same, and spaces do not work. The MySQL cache is effective for all SQL statements. That is to say, once the query cache is enabled, all SQL queries are enabled by default. We will have a lot of changed data, but we do not want to enable the query cache. This MySQL also reminds us that we should add SQL _no_cache to SQL, for example, select SQL _no_cache * from mysql. in this way, MySQL will bypass the cache and directly search for data from the database. Another thing to note is that, for example, if we use a function like now () in SQL, MySQL will not cache it for us.

 

Check whether the query cache is enabled.

 

The method for enabling query cache is also very simple. You can set the preceding query cache variable in the my. cnf configuration file. The following describes the meaning of each variable.

Whether query_caceh_type enables query cache. 0 indicates that query cache is not enabled. 1 indicates that query cache is always enabled (SQL _no_cache is not cached). 2 indicates that query cache is enabled as needed (SQL _cache is required for cache ). Query_cache_size the maximum memory size allocated to the cache

 

Query cache operations.

1. flush query cache; // clear the memory fragments in the query cache. 2. reset query cache; // removes all queries from the query cache. 3. flush tables; // close all open TABLES. At the same time, this operation clears the content in the query cache.

 

Although the cache can greatly improve the performance, it must be used with caution. If it is not used well, it will be counterproductive.