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

This article mainly introduces "what is the process of SQL query statement execution". In daily operation, I believe that many people have doubts about the process of SQL query statement execution. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the process of SQL query statement execution?" Next, please follow the editor to study!

An example of a SQL statement:

SELECT * FROM `test` WHERE `id` = 1

When we enter a SQL statement and return a result, how does the statement execute within the MySQL?

MySQL basic architecture

The following is a schematic diagram of the basic architecture of MySQL (the picture is from the network). From the following figure, we can see more clearly the execution process of SQL statements in various functional modules of MySQL.

From the above figure, MySQL can be divided into two parts: the Server layer and the storage engine layer.

The Server layer includes connectors, query caching, analyzers, optimizers, executors, etc., covering most of the core service functions of MySQL, as well as all built-in functions (such as date-time functions, string functions, mathematical functions, encryption functions, etc.). All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, and so on.

The storage engine layer is responsible for data storage and extraction. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on. The most commonly used storage engine today is InnoDB, which has been the default storage engine since MySQL version 5.5.5. When performing create table table building, if you do not specify the engine type, the default is InnoDB. Use engine=memory in the create table statement to specify that the table is created using the memory engine. Different storage engines have different ways to access table data and support different functions. We will discuss the knowledge of the engine in a later article.

As you can see from the figure above, different storage engines share a Server layer, that is, the part from the connector to the actuator. Let's look at the role of each component.

Server layer 1, connector

The first step in using the database is to connect to the database first, and the connector is the reception at this time. Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections.

Connection command:

Mysql mysql-h host address-u user name-p

After typing the command, you need to enter the password in the interactive conversation. Although the password can also be written directly after-p on the command line, it may lead to your password being compromised and it is not recommended to type it directly.

The following picture shows my computer operation (mac+ mamp):

If you enter the account number or password incorrectly, it will prompt (1045):

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The screenshot is as follows:

When the user name and password authentication is passed, the connector will go to the permissions table to find out the permissions you have. After that, the permission judgment logic in this connection will depend on the permissions read by this login.

Screenshot of successful connection:

From the figure above, we can see some version number information about MySQL (Server version: 5.7.23 MySQL Community Server (GPL)).

When a user successfully establishes a connection, even if you modify the permissions of the user with the administrator account, the permissions of the existing connection will not be affected. After the modification is complete, only the newly created connection will use the new permission settings.

After the connection is complete, if you have no subsequent action, the connection is idle, which can be seen in the show processlist; command. The following figure shows the results of show processlist.

Where, when the Command column has the line displayed as "Sleep", it means that there is now a free connection in the system.

If the client does not move for too long, the connector will automatically disconnect it. This time is controlled by the parameter wait_timeout, and the default value is 8 hours.

View the default connection timeout:

Show variables like 'wait_timeout'

You can use naming to modify this parameter value:

Set wait_timeout = 28801; / / Custom duration

The results are as follows:

If the client sends a request again after the connection is disconnected, it will receive an error warning: Lost connection to MySQL server during query. At this point, if you want to continue, you need to reconnect and execute the request.

In the database, persistent connection means that after a successful connection, the client always uses the same connection if there are persistent requests from the client. A short connection means that the connection is disconnected after a few queries are executed, and a new one is established next time.

The process of establishing a connection is usually complicated, and it is recommended to minimize the action of establishing a connection, that is, to use a long connection as much as possible.

If all long connections are used, sometimes MySQL takes up a lot of memory, because the memory temporarily used by MySQL during execution is managed in the connection object. These connection resources are released only when the connection is disconnected. If persistent connections accumulate for a long time, it may take up too much memory and be forcibly killed by the system, which will cause MySQL to restart abnormally.

How to solve this problem?

Disconnect the long connection regularly. After using it for a period of time, or after it is judged in the program that a large memory-consuming query has been executed, disconnect, and then reconnect when you want to query.

In the case of MySQL version 5.7 or later, you can reinitialize the connection resources by performing mysql_reset_connection after each large operation. This process does not require reconnection and redo permission verification, but restores the connection to the state it was when it was just created.

2. Query cache

After the connection is established, you can execute the select statement. The execution logic comes to the second step: query caching.

After MySQL gets a query request, it goes to the query cache to see if the statement has been executed before. Previously executed statements and their results may be cached directly in memory in the form of key-value pairs. Key is the statement of the query and value is the result of the query. If your query can find key directly in this cache, then the value will be returned directly to the client.

If the statement is not in the query cache, the execution phase continues later. After the execution is completed, the execution results are stored in the query cache. If the query hits the cache, MySQL does not need to perform subsequent complex operations to return the results directly, which can be very efficient.

But in most cases I would advise you not to use query caching, because query caching tends to do more harm than good.

Query cache invalidation is very frequent, as long as there is an update to a table, all query caches on that table will be emptied. So it is very likely that you have struggled to save the results and have been fully updated by an update before you use it. For databases with high update pressure, the hit rate of the query cache will be very low. If there is a static table that takes a long time to update, then the query on this table is suitable for query caching.

Fortunately, MySQL also provides this "on-demand" approach. You can set the parameter query_cache_type to DEMAND so that query caching is not used for default SQL statements. For the statement for which you are sure you want to use the query cache, you can specify it explicitly with SQL_CACHE, such as the following statement:

Mysql > SELECT SQL_CACHE * FROM `test` WHERE id=1

It is important to note that MySQL version 8.0 removes the entire query cache function directly, which means that it has not been available since 8.0.

3. Analyzer

If you miss the query cache, you will start to actually execute the statement. First of all, MySQL needs to know what you want to do, so you need to parse the SQL statement.

The analyzer will first do a "lexical analysis". What you enter is a SQL statement made up of multiple strings and spaces, and MySQL needs to identify what the string is and what it represents.

An example of a SQL statement:

SELECT * FROM `test` WHERE id=1

MySQL is identified by the keyword "SELECT" you typed, which is a query statement. It also recognizes the string "test" as "table name test" and the string "id" as "column id".

After recognition, it is necessary to do "grammatical analysis". According to the results of lexical analysis, the parser will determine whether the SQL statement you enter satisfies the MySQL grammar according to the grammar rules.

If your sentence is incorrect, you will receive an error reminder of "You have an error in your SQL syntax". For example, the following sentence where has an extra character "1".

Mysql > SELECT SQL_CACHE * FROM `test` WHERE1 id=1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id=1' at line 1

General syntax errors will indicate the first location of the error, so you should pay attention to the contents of the "use near" attachment.

As shown in the figure:

The parser constructs a parsing tree (internal data structure) that only ensures that there are no syntax errors (parsing), such as checking whether the identifier is valid and whether the statement is closed. It also includes rewriting the query, determining the reading order of the table, selecting the appropriate index, and so on.

4. Optimizer

After the analyzer, MySQL will know what you are going to do. It has to be processed by the optimizer before starting execution.

The optimizer decides which index to use when there are multiple indexes in the table, or the join order of each table when a statement has multiple table associations (join). For example, you execute the following statement, which executes the join of two tables:

Mysql > SELECT * FROM `test1` JOIN `test2` USING (id) WHERE test1.a=1 AND test2.t=2

You can first take the ID value of the record of axi1 from the table test1, then associate it with the table test2 according to the ID value, and then judge whether the value of t in T2 is equal to 2. You can also first extract the ID value of the record of tweak 2 from the table test2, then associate it to test1 according to the ID value, and then determine whether the value of an in test1 is equal to 1. The logical results of the two execution methods are the same, but the efficiency of execution will be different, and the role of the optimizer is to decide which scheme to use.

After the optimizer phase is complete, the execution plan for this statement is determined and then enters the executor phase. As for how the optimizer chooses the index, the optimizer will be sorted out separately in later articles.

5. Actuator

MySQL knows what you want to do through the parser and what to do through the optimizer, so it enters the executor phase and starts executing the statement.

At the beginning of execution, you should first determine whether you have permission to execute a query on this table T. If not, an error without permission will be returned, as shown below (in the project implementation, if the query cache is hit, permission verification will be done when the query cache returns the result. The query also calls precheck to verify permissions before the optimizer.

Mysql > SELECT * FROM `test` WHERE id=1

For example, in the table test in our example, the id field does not have an index, then the execution flow of the executor is as follows:

(1) call the InnoDB engine API to get the first row of the table and determine whether the ID value is 1. If not, skip it. If so, store the row in the result set.

(2) call the engine interface to take "next row" and repeat the same judgment logic until the last row of the table is fetched.

(3) the executor returns the recordset composed of all the rows that meet the conditions in the above traversal process to the client as a result set.

At this point, the statement execution is complete.

For indexed tables, the same logic is performed. The interface "fetch the first line that meets the condition" is called for the first time, and then the interface "next line that meets the condition" is looped, which are already defined in the engine.

You will see a rows_examined field in the database's slow query log indicating how many lines were scanned during the execution of the statement. This value is accumulated each time the executor calls the engine to get the data row.

In some scenarios, the executor is called once, and multiple lines are scanned inside the engine, so the number of rows scanned by the engine is not exactly the same as the rows_examined. The supplement will be explained later in the internal mechanism of the storage engine.

At this point, the study of "what is the process of executing SQL query statements" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.