MySQL query cache settings improve MySQL query performance

First look at the MSYQL logical framework: Images from high-performance MySQL


If Querycache is used, when the query receives one and the same query, the server retrieves the results from the query cache instead of parsing and executing the same query again. This can greatly improve query performance.

To open the query cache, you can set it in several steps:

Although you set up MySQL to allow query caching, if you set the query cache size in order to 0, this is no different from allowing it.

So it has to be a few steps to actually open the query cache feature.

The following shows the most common settings query cache


First, query_cache_type How to use query caching

In general, we will set the Query_cache_type to on, which should be on by default

1. Mysql> SELECT @ @query_cache_type;

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

| @ @query_cache_type |

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

| On |

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

So when we execute select Id,name from TableName; This will use the query cache. In the case of Query_cache_type open, if you do not want to use the cache, you need to indicate the selectsql_no_cache id,name from TableName;

Of course, you can also disable query caching: mysql> set session Query_cache_type=off;

Of course you can also use the command to open the query cache:mysql> set session Query_cache_type=on;

We do not discuss this here, we demonstrate common settings.

Second, system variable Have_query_cache set whether the query cache is available
1. Mysql> Show variables like ' Have_query_cache ';

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

| variable_name | Value |

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

| Have_query_cache | YES |

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

The above display indicates that the set query cache is available.

Third, System variable Query_cache_size

Represents the query cache size, which is the allocation of memory size to the query cache, if you allocate a size of 0,

Then the first and second steps do not work, or there is no effect.

1. Mysql> SELECT @ @global. query_cache_size;

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

| @ @global. query_cache_size |

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

| 16777216 |

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

The above is the default MySQL settings, and some version as if the default is 0, then you have to set the next.

Sets the SET @ @global. query_cache_size=100000; Here is the setting around 1M.

Check again under SELECT @ @global. query_cache_size;

Shows that we set the new size to indicate that the setting was successful.




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 out of the question.

MYSQL can set a maximum cache value to limit the cache, and when you query the cache number results The data exceeds this value and is not cached. The default is 1M, which means that more than 1M query results are not cached.

1. Mysql> SELECT @ @global. query_cache_limit;

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

| @ @global. query_cache_limit |

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

| 1048576 |

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

This is the default value, if you need to modify it, just like setting the cache size, use set to re-specify the size.

OK, 4 steps to open the query cache, the size of the specific value and query the way this is set according to demand!.

MySQL query cache settings improve MySQL query performance