The following articles mainly describe the MySQL cache function, analyze the MySQL cache function, and describe the MySQL cache debugging notes and MySQL cache cleanup content, the following is a detailed description of the article. I hope you will find some gains.
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, all related caches will be deleted as long as the table involved in the select statement has any data changes such as insert, update, and delete operations. 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, how to use the MySQLcache 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 MySQL cache function is not enabled.
2 debugging:
View cache settings:
- show variables like '%query_cache%';
Performance monitoring:
- show status like '%Qcache%';
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.
The above content is an introduction to the MySQL cache function. I hope you will find some gains.