How to enable the query statement caching for MySQL

The programs we write may require a large number of database operations. Databases are often the cause of bottlenecks.

Opening query statement caching is also a strategy to optimize MySQL.

MySQL is a common database for our development programs. It has the ability to cache common query statements in memory, so that when a given query statement is invoked again, MySQL will immediately return it from the cache. However, in most MySQL, this feature is disabled by default. To enable it, add the following code to your MySQL configuration options file, which is named MY.CNF and used to declare variables and the behavior of your MySQL server (see http://dev.mysql.com/doc/refman/5.1/en/ option-files.html).

To enable MySQL-enabled query statement caching:

Here, we set the query statement cache to 64MB:

The code is as follows	Copy Code
[Mysqld] query_cache_size=64m

The size of the current query statement cache can be viewed through the show variables command for MySQL:

The code is as follows	Copy Code
Mysql>show VARIABLES; ... | Query_cache_size |555-55554 | Query_cache_type | On ...

We need to continually experiment with setting the size of the query statement cache. A cache that is too small means that the cached query statement will soon expire. Too much caching means that searching for a cache can take a relatively long time, and that using memory for caching is better than using some other means, like having more Web server processing, memcache or operating system file caching.

Working principle

The working principle of query caching can be summarized as follows:
A result set and SQL statement that caches a select operation or preprocessing query (note: 5.1.17 begins to support);
The new SELECT statement or preprocessing query statement, first to query the cache, to determine whether there is a set of records available, to determine the standard: and cached SQL statements are exactly the same, case-sensitive;

The query cache is not able to cache its recordset for what query statements, roughly the following categories:
The Sql_no_cache parameter is added to the query statement; www.111cn.net
The query statement contains functions that obtain values, including custom functions such as: Curdate (), Get_lock (), RAND (), Convert_tz, etc.
Query to System database: MySQL, information_schema
Use a session-level variable or a local variable in a stored procedure in a query statement;
The statement with lock in SHARE MODE and for update is used in the query statement
Similar to select in query statement ... Into the statement of the exported data;
Transaction ISOLATION LEVEL is: Serializable case, all query statements can not be cached;
Query operations on temporary tables;
A query statement that has a warning message;
A query statement that does not involve any table or view;
A user only has Column Level permission query statement;

Advantages and disadvantages of query caching:

Do not need to do any parsing and execution of SQL statements, of course, parsing must be through the first, directly from query cache to obtain the results;
The judgment rules of query caching are not intelligent enough, which increases the threshold of query caching and reduces its efficiency.
Query cache will increase the cost of checking and cleaning the recordset in query cache, and there is a table with SQL statement caching, each table has only one corresponding global lock;