Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the principle of MySQL database caching, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

What are the steps of MySQL database query

When MySQL receives the query statement sent by the client, it will first check whether the result of this statement is cached in the cache block, and if so, check the permission. If it can pass the permission check, it will directly return the result set in the cache block. We call it hitting the cache. In this case, the value of the Qcache_hits variable will be increased.

If the cache for this statement cannot be found in the cache (which increases the value of the Com_select variable), proceed to the next step:

1. The MySQL parser decomposes the query statement into identities and builds a "parsing tree". The parser uses MySQL syntax to parse and verify that the identity of the query statement is valid and whether it appears in the appropriate location. It also checks whether the quotation marks in the string are closed.

2. The preprocessor will check whether the tables and columns in the "parse tree" exist, whether the aliases of the columns are confused, and finally check the permissions, which are not available in the parser and can only be done through the preprocessor.

3. If the first two steps pass the test, MySQL's optimizer optimizes the parse tree and generates the execution plan with the lowest execution cost according to the predicted execution cost. Finally, the plan is executed, the query results are stored, and the result sets are returned to the client.

With the above two variable values, we can use this formula to calculate the cache hit ratio: Qcache_hits/ (Qcache_hits_Com_select), and use the hit ratio to see if we can benefit from the cache. There is a question here: how much is the best hit rate? If there is no definite value, it depends on the situation. If you hit the query statements that need to filter a large amount of data to get the results (for example, GROUPBY,COUNT, etc.), even if the hit rate is very low, it is also a good hit rate value.

What are the principles of MySQL database caching

1. The query statement sent is not cacheable for two reasons: the statement contains uncertain values, such as CURRENT_DATE. The resulting result set is too large to be saved to the cache. The result of both causes will increase the value of the Qcache_not_cached variable. We can check the cache of our query statement by looking at the value of this variable.

2. The query statement sent has not been sent before (sent for the first time), so there will be no cache.

3. The result set of the sent query statement previously existed in the cache, but due to insufficient memory, MySQL had to clear some of the previous cache to make room for other new cache results. Similarly, changes in data can also cause cache invalidation. For example (update, delete, insert). If the cache is invalidated by a variable of the data, we can confirm how many query statements have changed the data by looking at the value of the Com_* variable: Com_update,Com_delete, and so on.

After reading the above, do you have any further understanding of the principle of MySQL database caching? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.