How a select statement is executed in MySQL

1. Analyzing MySQL from a macro perspective

First, let’s look at a classic picture ( MySQL logical architecture diagram )

The client in the above picture can be directly understood as PHP, Java, etc. Next, you will see connections and thread handling. This part is not unique to MySQL, and most clients and servers have a similar structure.

Therefore, generally speaking, MySQL can be divided into two layers: Server layer and storage engine layer.

The server layer mainly includes important modules such as connection layer, query cache, analyzer, optimizer, executor, etc. This layer also includes the MySQL core API part, such as commonly used formatting time, encryption, etc.

Everyone is familiar with the storage engine because I have asked you more than once about the differences between Innodb and Myisam storage engines during interviews.

So have you ever thought about why MySQL has so many storage engines?

All technologies originate from current problems, and MySQL is no exception.

The architecture of MySQL's storage engine is plug-in-based, which means it can be switched at will and is not fixed. In addition, the storage engine of MySQL version 5.5 is already Innodb by default.

2. How many difficulties does it take to execute a SQL statement?

MySQL detailed architecture diagram

There is also a familiar stranger query cache module in the figure, which no longer exists in MySQL 8.0.

First, we will take a general look at how a SQL statement works in this architecture diagram.

2-1 Connector

mysql -u root -p connect to the database command. After executing it, you will need to enter the password. Once the classic TCP handshake is complete, the connector comes into play.

If the code is incorrect, it returns Access denied for user 'root'@'localhost' (using password: YES, error code 1045).

If the connection information is correct, the user's permissions will be obtained based on the user access permission table you entered. It must be clear here that after you log in successfully, even if others modify your permissions, your permissions will not change until the connection is disconnected.

After you complete the connection, if you do not do anything, execute show processlist and you will see a sleep, indicating an empty connection.

Do you know how long it will take for MySQL to be automatically disconnected if no operations are performed after a successful connection?

You can execute show variables like 'wait_timeout'; to view the time.

In MySQL, if there is no special description, all times are in seconds. According to the time conversion, we can know that the empty connection lasts for 8 hours.

2-2 Query Cache

What you need to note is that MySQL 8.0 has been cancelled. This issue has been mentioned more than once. Especially those friends who are using MySQL versions below 8.0 should pay attention! When you switch to 8.0, you may encounter this problem and don’t know how to solve it.

Why MySQL 8.0 Cancels the Query Cache Module

The design of this module uses the query statement as the key and the result as the value for caching. Once the table is updated, all previous caches will be cleared. It's as painful as when the code you worked hard to write is overwritten by others after you submit it.

MySQL versions below 8.0 provide a parameter query_cache_type = enmand to control whether to use query cache. After setting, the default select statement will not be cached.

If it does work for some scenarios, you can add sql_cache after the select keyword.

If a select statement has been cached before, the result set will be returned directly here, while a select statement that has not been cached will be more difficult and will have to continue its long journey.

2-3 Analyzer

Before MySQL 8.0, it will determine whether to cache before entering the analyzer. After MySQL 8.0, the connector will directly enter the analyzer after successful verification.

The analyzer, according to the literal meaning, is to analyze what the SQL statement to be executed is and what to do.

For example, execute select * from user where id = 1

MySQL first determines that this is a query statement based on select, and then identifies user as the table name and id as the field name. This process is called lexical analysis.

The next step is to check whether the SQL syntax is correct and perform syntax analysis. If the syntax is incorrect, you will see the error "You have an error in your SQL syntax". Typically, the error will be found in use near.

2-4 Optimizer

At this point, MySQL knows what you want to do, but it has to choose the best way to execute it.

What do optimizers optimize?

For example : which index to choose when there are multiple indexes, and the connection order when multiple tables are associated.

Now are you wondering, since the optimizer will optimize the join order of multi-table associations, do you not have to consider the join order when writing SQL statements?

Of course not. If you can help MySQL do less work, do it as little as possible. It is still a principle to use small tables to drive large tables.

2-5 Actuator

This SQL statement will be actually executed only after what is to be done and how to do it is determined. Permission verification is performed first. If there is no permission, a permission error is directly returned. Otherwise, the interface provided by the corresponding engine is used according to the storage engine defined in the table.

Execution Flowchart

The above picture contains all the knowledge points in the text and is also the general execution flow chart of the entire MySQL.

This concludes this article on how a select statement is executed in MySQL. For more information about how to execute MySQL select statements, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!