How is a SQL query statement executed?

                                                                                            

What are the parts of MySQL?

1. Connector: management connection, permission verification.
2. Analyzer: lexical analysis, syntax analysis.
3. Optimizer: execution plan generation, index selection.
4. Executor: operates the storage engine and returns results.

5. Storage engine: stores data and provides read and write interfaces.

Related learning recommendations: mysql video tutorial

##Connector

In the first step, we will first connect to the MySQL database, and now we will connect the connector. The connector is responsible for establishing a connection with the client, obtaining permissions, maintaining and managing the connection.

mysql -h $ip -u root -p

Query cache

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

Analyzer

If there is no query cache, MySQL will start to analyze what we want to do from here, and it needs to analyze the SQL statements we write. The analyzer will first do lexical analysis to identify the string and what it represents. Then perform syntax analysis to determine whether there are errors in the SQL statements we wrote. If there are errors, an error will be thrown.

Optimizer

After passing the analyzer, MySQL knows what you are going to do. At this time, the optimizer will decide which solution to use based on the table structure and statement purpose.

Executor

MySQL knows what we want to do through the analyzer, and knows how to do it most efficiently through the optimizer. Then you can enter 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, then the executor calls the InnoDB engine interface to get the first row and determines whether the name is equal to the operator. If not, skip it. If so, put it in the result. concentrated. Then call the engine interface to fetch the next row, and repeat 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 the detailed content of How is a SQL query statement executed?. For more information, please follow other related articles on the PHP Chinese website!