Mysql database cache function Summary

Source: Internet
Author: User

Mysql cache function analysis:

1. The key Generation Principle of the mysql cache function is to generate a unique key based on the select statement according to certain hash rules. The select result generates a value, that is, key => value.Therefore, for the cache, select statements are case sensitive and contain spaces. The two select statements must be completely consistent to obtain the same cache.

2. After the cache is generated, as long as the table involved in the select statement has any data changes (insert, update, delete, etc ),All related caches will be deleted.Therefore, the introduction of mysql cache makes sense only when there are few table changes in data. For tests in this area, see Query Cache.

  Therefore, the cache function of mysql is only applicable to the following scenarios: tables with less data changes and more select statements.

So. In a complex system, the basic method for using the mysql cache function is as follows:

  Configure query_cache_type and rewrite the program.

Query_cache_type 0 indicates that no buffer is used. 1 indicates that the buffer is used, and 2 indicates that the buffer is used as needed.

Setting 1 indicates that the buffer is always valid. If no buffer is required, use the following statement:

SELECT SQL _NO_CACHE * FROM my_table WHERE...

If it is set to 2, you can use the following statement to enable buffering:

SELECT SQL _CACHE * FROM my_table WHERE...

  So, the simplest and most reliable way is to set query_cache_type to 2, and then use:

  SELECT SQL _CACHE * FROM...

  SELECT.

Mysql cache debugging notes]

1. You can use the following command to enable the select cache function of mysql:

Set global query_cache_size = 102400000;

Because when query_cache_size is 0 by default, the cache function is not enabled.

2 debugging:

View cache settings:

Show variables like '% query_cache % ';

Performance monitoring:

Show status like '% Qcache % ';

3. mysql cache cleanup:

You can use the flush query cache statement to clear query cache fragments to improve memory usage. This statement does not remove any queries from the cache.

The reset query cache statement removes all queries from the query cache. The flush tables statement also performs the same job.

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.