Exactly how the SQL query statement is executed

In this issue, the editor will bring you about how the SQL query sentence is executed. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

What parts does MySQL have?

1. Connectors: manage connections, rights verification. two。 Analyzer: lexical analysis, grammatical analysis. 3. Optimizer: perform plan generation, index selection. 4. Actuator: operates the storage engine and returns the results. 5. Storage engine: stores data and provides a read-write interface.

Connector

The first step is to connect to the MySQL database, which is to connect to the connector. Connectors are responsible for establishing connections with customers, obtaining permissions, maintaining and managing connections.

Mysql-h $ip-u root-p

Query cache

Once the connection is established, we can use the SELECT statement, and the execution logic comes to the second step: query caching. MySQL will now query the cache to see if this statement has been executed before, and if so, return it directly. After MySQL 8.0, this module has been removed.

Analyzer

If there is no query cache, from here MySQL will start to analyze what we are going to do, and we need to analyze our writing SQL statements. The analyzer will first do a lexical analysis to identify the string and the meaning it represents. Then we do syntax analysis to determine whether there are any errors in the SQL statement we wrote, and if there are any errors, we will throw an error.

Optimizer

After passing the parser, MySQL knows what you are going to do, and the optimizer will decide which scheme to use based on the table structure and the purpose of the statement.

Actuator

MySQL knows what we are going to do through the analyzer and the most efficient way to do it through the optimizer. So you can go to the executor and actually execute the SQL statement.

Select * from users where name = 'operator'

Assuming that there is no index on the name field in the users table, the executor calls the InnoDB engine API to take the first row to determine whether name is equal to operator. If not, skip it, and if so, put it in the result set. Then call the engine interface to fetch the next row, repeating the same logical judgment until the last row of the table is fetched. Finally, the result set is returned to the client.

The above is how the SQL query sentence shared by the editor is executed. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.