Mysql database cache function Summary

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.