How MySQL Select Statement is Executed

How is the MySQL Select statement executed?

I recently watched "MySQL 45 Lectures" by Mr. Ding Qi on Geek Time. I really thought it was well lectured. I would like to share with you some of the experience I gained in MySQL. Students who are interested can purchase related courses to study.

What I want to share today is the execution process of select and update.

The execution process of select

Without further ado, here is a great picture (drawn by myself).

First of all, we can see that the entire select statement consists of three modules, including the client and MySQL. MySQL consists of the server and storage engine sides. The server side includes connectors, query cache, analyzer, optimizer, executor and other parts. The storage engine side also includes multiple storage engines such as innodb, Myisam, memory, etc.

The functions of each module are as follows:

Connectors:

The connector's task is to establish a connection with the client, query permissions, maintain and manage connections, etc. When we log in using the command line, if the password or account is wrong, the connector will return an Access Deny error to us. After logging in to MySQL with the correct account and password, the connector will query the login permissions of the current account. All subsequent operations will depend on this permission.

Note that if we change the permissions of the account at this time, it will not affect existing connections, but only newly added connections.

If the client does not perform any operations after connecting to MySQL, the connector will disconnect the connection after the time set by the wait_timeout parameter. It is recommended to use long connections in the program, which can reduce the performance loss caused by establishing connections with the client and MySQL.

Query Cache:

When the client enters an SQL, if it hits the query cache, MySQL will directly return the result without performing the following series of analysis operations. If it does not hit, it will start the process of analyzer-optimizer-executor. There are two more points to note about query caching:

a. Query cache is no longer used in MySQL 8.0 because the query cache hit rate is generally low and the disadvantages outweigh the advantages.

b. In high-concurrency scenarios, it is recommended to turn off the query cache. When turning it off, use query_cache_type=off or query_cache_size=0.

c. If you must use this function, you can set the parameter query_cache_type to DEMAND. In this way, you can use select sql_cache * from table to force the use of query cache.

Analyzer:

If our query does not actually hit the query cache, then we have to enter the analyzer stage. The main functions of the analyzer are lexical analysis and syntax analysis, among which:

Lexical analysis refers to analyzing select, update, alter, column names, table names, library names, etc. based on SQL;

Syntax analysis refers to analyzing whether the SQL you wrote meets the MySQL syntax. If not satisfied, a prompt "you have an error xxx" will be given.

Optimizer:

The role of the optimizer is to select the best path for SQL execution. For example, when joining multiple tables, the optimizer will choose the most efficient solution to execute the join query. Here is an example. For example, we create a joint index of (a, b).

select * from table where a=1 and b=1;

select * from table where b=1 and a=1;

These two SQL statements are equivalent because the optimizer will transform the following statement into the above one, thereby applying the joint index for search queries.

Actuator:

After the SQL passes through the optimizer, it will enter the executor. Before executing the SQL, it is necessary to check the table permissions. If there is permission, it will use the interface provided by the storage engine according to the storage engine definition of the table, and finally return the data to the client and start waiting for new connections.

One thing to note here is that the executor is called once, and multiple rows are scanned inside the engine. Therefore, the number of rows scanned by the storage engine is not exactly the same as rows_examined.

Q&A

Q1: Why isn’t the permission check done before the optimizer?

A1: Sometimes, the tables to be operated by SQL statements are not just those in the SQL statement. For example, if there is a trigger, it can only be determined in the executor stage (process). Nothing can be done before the optimizer stage

Q2: Where are MySQL permissions verified?

A2: The login permission of the account is verified in the connector module. The operation permission of the table is divided into two cases. If the query cache is hit, it will be verified when the query cache returns the result, before the optimizer. If the index is not hit, the permission verification is verified in the executor module.

The above is the details of how the MySQL Select statement is executed. For more information about the MySQL Select statement, please pay attention to other related articles on 123WORDPRESS.COM!