MySQL query cache settings improve MySQL query performance

Source: Internet
Author: User

MySQL query cache settings improve MySQL query performance
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. The query_cache_type query cache is generally used. We will set query_cache_type to ON. By default, it should be ON
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.

2. Set the system variable have_query_cache to check whether the cache is available. 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.

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.

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 !.

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.