MySQL database query steps and Cache principles

We know that the database query function is frequently used, soMySQL databaseHow is the query performed? This article introduces the MySQL database query steps andCacheNext, let's take a look at this part.

When MySQL receives the query statement sent by the client, it first checks whether the result of this statement is cached in the cache block. If yes, it checks the permission, if you can pass the permission check, the result set in the cache block is directly returned, which is called hit cache. In this case, the Qcache_hits variable value is increased.

If the cache of this statement cannot be found (the value of the Com_select variable is increased), proceed to the next step:

1. the MySQL parser splits query statements into identifiers and creates a "Resolution tree ", the parser uses MySQL syntax to parse and verify the validity of the query statement identifier and whether the identifier appears in the appropriate position. It also checks whether the quotation marks in the string are closed.

2. The pre-processor checks whether the tables and columns in the "Resolution Tree" exist, and whether the column aliases are confused. Finally, the pre-processor checks the permissions, these checking features are not available in the parser and can only be done through the pre-processor.

3. If the previous two steps have passed the test, the MySQL optimizer will optimize the "Resolution Tree" and generate the execution plan with the lowest execution cost based on the predicted execution cost. Finally, execute this plan, store the query results, and return the result set to the client.

Using the above two variable values, we can use this formula to calculate the cache hit rate: Qcache_hits/(Qcache_hits_Com_select). We can check whether we can benefit from the cache through the hit rate. The question here is: what is the best hit rate? If there is no definite value, it depends on the situation. If the query statements (such as group by and COUNT) hit the results that require filtering a large amount of data ), even if the hit rate is low, it is also a good hit rate.

Any query statement that does not retrieve data from the cache block is called cache miss, which may result in the following reasons:

1. the query statement sent cannot be cached. The query statement cannot be cached for two reasons: the statement contains uncertain values, such as CURRENT_DATE ,. The result set is too large to be saved in the cache. the Qcache_not_cached variable value will be added to the results of these two causes. We can check the value of this variable to check the cache of our query statement.

2. the query statement sent has not been sent before (sent for the first time), so no cache exists.

3. The result set of the query statement sent exists in the cache. However, due to insufficient memory, MySQL has to clear some previous caches, to free up space for other new cache results. Similarly, data changes may cause cache invalidation. For example, update, delete, and insert ). If the cache is invalid due to data variables, we can check the value of the Com _ * variable to check how many query statements have changed the data. These variables include Com_update, Com_delete, and so on.

This article introduces the query steps and Cache principles of the MySQL database!

Source: http://52226777.blog.163.com /.