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

This article will explain in detail about the principle of Mysql implementation plan, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Mysql performance optimization, from a senior programmer's point of view, we can not debug our business SQL through debug like the java language, so how do we verify the optimized SQL semantics, then it is the execution plan.

The roles included in the execution plan:

ORM framework

Connection pool

Mysql Server

Storage engine

So what is the component responsible for generating the execution plan?

Answer: the optimizer module MySQL Query Optimizer, which is specially responsible for optimizing SELECT statements, provides the optimal execution plan-the optimal data retrieval method for Query by calculating and analyzing all kinds of system statistical information collected.

MySQL Query Optimizer is located on the Mysql Server side and is owned by Mysql and has nothing to do with the storage engine.

When MySQL Query Optimizer receives the Query passed by Query Parser (parser), it will decompose and analyze the Query according to the corresponding syntax of the MySQL Query statement. At the same time, it will also do a lot of other calculation and conversion work, such as constant conversion, invalid content deletion, constant calculation and so on.

MySQL Query Tree stores the process of data processing in a data structure of Tree type, indicating the steps that must be taken to complete Query, where the data source of each step is, and how to handle it. MySQL uses LEX and YACC syntax (lexical) parsing tools to generate MySQL Query Tree.

The client sends a Query request to MySQL and commands the parser module to complete the classification of the request. Forwarding the SELECT Query to MySQL Query Optimizer,MySQL Query Optimizer will first optimize the whole Query, carry out the budget of the constant expression, and convert it directly into a constant value. And simplify and transform the query conditions in Query, such as removing some useless or obvious conditions, structural adjustment and so on. Then analyze the Hint information in the Query, if any, to see if the Hint information can fully determine the execution plan of the Query. If there is no Hint or Hint information to fully determine the execution plan, the statistics of the objects involved will be read, the corresponding calculation and analysis will be carried out according to the Query, and finally the execution plan will be obtained.

In a more general summary, Mysql will generate an instruction tree (MySQL Query Tree) from the SQL statement passed by the client, and then execute the instruction tree through the storage engine. The final execution plan will contain all the information of the refactoring query, that is, the multi-table query of Mysql will always nest a loop from a table and trace back to complete all table associations, which is essentially a depth-first tree on the left.

What is the principle of Mysql implementation plan is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.