MySQL query cache settings improve MySQL query performance and cache mysql

MySQL query cache settings improve MySQL query performance and cache mysql

First, let's take a look at the MSYQL logical framework: The image comes from high-performance mysql


 

If QueryCache is used, the server will retrieve the results from the query cache when the query receives the same query as before, instead of re-analyzing and executing the same query. This greatly improves the query performance.

 

To enable the query cache, follow these steps:

Although you set mysql to allow query cache, if you set the query cache size to 0, this is no different from not allowed.

Therefore, the query cache function can be enabled only after several steps are configured.

The following shows the most common settings for querying cache.


I. query_cache_type query Cache

In general, we will set query_cache_type to ON. By default, it should be ON

1. Mysql> select @ query_cache_type;

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

| @ Query_cache_type |

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

| ON |

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

In this way, when we execute select id, name from tableName;, the query cache will be used. When query_cache_type is enabled, if you do not want to use the cache, specify selectsql_no_cache id, name from tableName;

Of course, you can also disable query cache: 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 will not discuss this here. We will demonstrate common settings.

Ii. Set the system variable have_query_cache to check whether the cache is available.
1. Mysql> show variables like 'have _ query_cache ';

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

| Variable_name | Value |

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

| Have_query_cache | YES |

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

As shown above, the query cache settings are available.

Iii. System variable query_cache_size

Indicates the size of the query cache, that is, the size of memory allocated to the query cache. If the size is 0,

So the first step and the second step do not play a role, but there is still no effect.

1. Mysql> select @ global. query_cache_size;

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

| @ Global. query_cache_size |

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

| 1, 16777216 |

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

The above is the default setting for mysql, and some versions seem to be 0 by default, so you have to set it yourself.

Set @ global. query_cache_size = 100000. Here is about 1 MB.

Check select @ global. query_cache_size again;

The new size is displayed, indicating that the setting is successful.




Iv. query_cache_limit controls the maximum value of cache query results

For example, if the query result is large, it is also cached? This is obviously impossible.

MySql can set a maximum cache value to limit the cache. When the number of cached results exceeds this value, no cache will be performed. The default value is 1 MB, indicating that the query results will not be cached if the value exceeds 1 MB.

1. Mysql> select @ global. query_cache_limit;

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

| @ Global. query_cache_limit |

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

| 1, 1048576 |

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

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

Now, you can go to the query cache in four steps. The specific value size and query method are set as needed !.