MySQL open query caching method and query example

Turn on caching, set cache size, and implement the following:

1, modify the configuration file, Windows under the My.ini,linux is my.cnf;

On the last append to the configuration file:

The code is as follows	Copy Code
Query_cache_type = 1 Query_cache_size = 600000

Need to restart MySQL in effect;

Then adopt the second way;

b to open the cache, two ways:

A) Use the MySQL command:

The code is as follows	Copy Code
Set global query_cache_type = 1; Set global query_cache_size = 600000;

If you have an error:

Query cache is disabled; Restart the server with query_cache_type=1 ...

Input at the MySQL command line

Show variables like "%query_cache%" to see if the settings are successful, you can now use the cache;
Of course, if your datasheet has updated how to do, it does not matter MySQL default and this table has a relationship between the cache deleted, the next query will read the table directly and then cache

The following is a simple example:

The code is as follows

Copy Code

[mysql@csdba1850 ~]$ mysql-u root-p Enter Password: Welcome to the MySQL Monitor. Commands End With; Or/g. Your MySQL Connection ID is 3 Server version:5.0.45-community MySQL Community Edition (GPL) Type ' help, ' or '/h ' for help. Type '/C ' to clear the buffer. mysql> Set Global query_cache_size = 600000; Setting Cache memory Query OK, 0 rows Affected (0.00 sec) Mysql> Set Session Query_cache_type = on; Open Query Cache Query OK, 0 rows Affected (0.00 sec) mysql> Use test Reading table information for completion of table and column names you can turn off this feature to Get a quicker startup With-a Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | Animals | | person | +----------------+ 5 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) Qcache_hits indicates that the cumulative number of times a MySQL cache query hits the cache is an additive value. Mysql> show STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 0 | --0 times +---------------+-------+ 8 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) Mysql> show STATUS like ' qcache% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | Indicates that SQL obtains results directly in the cache and does not need to be parsed +---------------+-------+ 8 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) Mysql> show STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | The above SQL is also directly fetched from the cache to the result +---------------+-------+ 1 row in Set (0.00 sec) mysql> inserts into animals Select 9, ' Testsds '; After inserting the data, all the SQL caches associated with the table are emptied Query OK, 1 row Affected (0.00 sec) Records: 1 duplicates:0 warnings:0 mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 7 | +----------+ 1 row in Set (0.00 sec) mysql> show STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | Or is equal to 3, which means that the previous SQL was not directly derived from the cache +---------------+-------+ 1 row in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 7 | +----------+ 1 row in Set (0.00 sec) mysql> show STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 4 | +---------------+-------+ 1 row in Set (0.00 sec)

The above related content is the MySQL cache query and setup Introduction, hope you can have some harvest.

Add

First: The way Query_cache_type uses query caching
in general, we will set the Query_cache_type to on and by default it should be on

The code is as follows	Copy Code
Mysql> SELECT @ @query_cache_type; +--------------------+ | @ @query_cache_type | +--------------------+ | On | +--------------------+

Query_cache_type has 3 value 0 for turning off the query cache off,1 on behalf of the Open on,2 (DEMAND) is cached when there are Sql_cache keywords in the SQL statement, such as: Select Sql_cache user_name from Users where user_id = ' 100 ';
so that when we execute the select Id,name from tablename, the query cache is used.
① If you do not want to use caching when Query_cache_type is open, you need to indicate
Select Sql_no_cache id,name from tablename;
② when MySQL functions are used in SQL, It also does not cache
 
You can of course disable query caching: mysql> set session Query_cache_type=off,
Second: System variable have_query_cache set query cache available

The code is as follows	Copy Code
Mysql> Show variables like ' Have_query_cache '; +------------------+-------+ | variable_name | Value | +------------------+-------+ | Have_query_cache | YES |

+------------------+-------+
The above display indicates that setting the query cache is available.
Third: System variable Query_cache_size
Represents the query cache size, that is, allocating memory size to the query cache, if you allocate a size of 0,
So the first step and the second step do not work, or no effect.

The code is as follows	Copy Code
Mysql> SELECT @ @global. query_cache_size; +---------------------------+ | @ @global. query_cache_size | +---------------------------+ | 16777216 | +---------------------------+

The above is mysql6.0 set defaults, the previous version seems to default is 0, then you have to set up.
Sets the SET @ @global. query_cache_size=1000000; Here is set around 1M, more than 900 K.
Look again at the next

The code is as follows	Copy Code
SELECT @ @global. query_cache_size; +---------------------------+ | @ @global. query_cache_size | +---------------------------+ | 999424 | +---------------------------+

Shows that we set the new size to indicate a successful setting.
IV: Query_cache_limit control The maximum value of cached query results
For example: If the query result is large, also cache???? This is obviously impossible.
MYSQL can set a maximum cache value, when you query the number of cached results data exceeds this value will not
For caching. The default is 1M, which means that the 1M query results will not be cached.

The code is as follows	Copy Code
Mysql> SELECT @ @global. query_cache_limit; +----------------------------+ | @ @global. query_cache_limit | +----------------------------+ | 1048576 | +----------------------------+

This is the default value, and if you need to modify it, set it like a cache size, using the set
Re-specify the size.
OK, 4 steps to open the query cache, the size of the specific value and query the way this varies
To specify the situation.
MySQL Query cache related variables

The code is as follows

Copy Code

Mysql> Show variables like '%query_cache% '; +------------------------------+----------+ | variable_name | Value | +------------------------------+----------+ | Have_query_cache | YES | | Query_cache_limit | 1048576 | | Query_cache_min_res_unit | 4096 | | Query_cache_size | 16777216 | | Query_cache_type | On | | Query_cache_wlock_invalidate | Off | +------------------------------+----------+ 6 rows in Set (0.00 sec)

V: View the status of the cache

The code is as follows

Copy Code

Mysql> Show status like '%qcache% '; +-------------------------+----------+ | variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 11 | | Qcache_free_memory | 16610552 | | Qcache_hits | 10 | | Qcache_inserts | 155 | | Qcache_lowmem_prunes | 0 | | qcache_not_cached | 21 | | Qcache_queries_in_cache | 111 | | Qcache_total_blocks | 256 | +-------------------------+----------+ 8 rows in Set (0.00 sec)

MySQL provides a series of Global status to record the current state of Query Cache, as follows:
Qcache_free_blocks: The number of memory blocks in the Query Cache that is currently in an idle state
Qcache_free_memory: The total amount of Query Cache memory currently in the idle state
Qcache_hits:query Cache Hit Count
Qcache_inserts: The number of times a new query cache was inserted into query cache, that is, the number of missed hits
Qcache_lowmem_prunes: When query cache memory is not enough, the number of times you need to remove the old query cache from the new cache object
qcache_not_cached: No cache SQL number, including cache-unable SQL and SQL not cache due to Query_cache_type settings
Qcache_queries_in_cache: The number of SQL currently in Query cache
Total block number in Qcache_total_blocks:query Cache

Sixth: Check the query cache usage
The easiest way to check whether to benefit from the query cache is to check the cache hit rate
When the server receives the SELECT statement, the qcache_hits and com_select variables are cached by the query
The situation is incremented
The calculation formula for the query cache hit ratio is: qcache_hits/(qcache_hits + com_select).

The code is as follows	Copy Code
Mysql> Show status like '%com_select% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to cause memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small amounts of data.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that query_cache_size settings are too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit Ratio = 1.94%, hit ratio is very poor, may write more frequently, and may be some fragments.