Shulou(Shulou.com)06/01 Report--

This article mainly explains "how the select statement is executed in MySQL". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and study and learn "how the select statement is executed in MySQL" together.

Frequently used select statements

In order to better run through the text, here is to list one of the simplest select query statements, for example: query the user information id 1001 in the user table, use the following SQL statement to query.

select * from user where user_id = 1001;

When we type the above SQL statement into MySQL's command line, how exactly does this SQL statement execute in MySQL? Next, let's take this SQL statement as an example and talk about how the select statement is executed in MySQL.

MySQL logical architecture

Before introducing the execution flow of select statements in MySQL, let's first look at MySQL's logical architecture, because the execution of any SQL statement cannot be separated from MySQL's logical architecture. In other words, the execution flow of SQL statements in MySQL is inseparable from MySQL's logical architecture.

In the figure above, we simply draw MySQL's logical architecture diagram, and give the logical hierarchy and the functions of each part in each layer. Logically, MySQL can be roughly divided into three layers: Server layer, storage engine layer and system file layer, while Server layer can be divided into network connection layer (connector) and data service layer (Server layer).

The Server layer contains the core components of MySQL such as connectors, query caches, analyzers, optimizers and executors. In addition, the Server layer also contains all built-in functions (such as date-time functions, encryption and decryption functions, aggregation functions, mathematical functions, etc.), storage engines, triggers, views, etc.

The storage engine layer is mainly responsible for interacting with the system file layer. The storage engine layer itself is a plug-in architecture design, supporting storage engines such as InnoDB, MyISAM, Archive, Memory, etc. In MySQL 5.5.5 and later, the default storage engine for MySQL is InnoDB.

The system file layer is primarily responsible for storing the actual data, which is stored as files on the server disk.

Next, let's talk about how a select statement executes in each part of MySQL's logical architecture.

How are connectors authorized?

First, let's see how MySQL's connectors validate when you type MySQL connections on the server command line. For example, we typed the following command on the server command line.

mysql -ubinghe -p

After executing "Enter," enter the password of the binghe account and connect to MySQL. At this point, the connection process needs to complete the classic TCP handshake operation (for knowledge related to TCP handshake, the small partners can refer to the interviewer: talk about the seven-layer network model and TCP three-way handshake and four-way disconnection?). After that, the connector begins to verify whether the identity of the connection is legitimate, the most direct is to verify that the username and password are correct.

If the username or password is incorrect, MySQL will prompt Access denied for user. If the username and password are correct, the connector queries MySQL's permissions table for the permissions the current connection has. After the permission is queried, as long as the connection is not disconnected, the permission operations involved in this connection will depend on the permission queried at this time.

In other words, after a user logs in to MySQL and successfully connects to MySQL, even if the administrator modifies the permissions of the current user, as long as the user does not disconnect MySQL, it will not be affected by the administrative modification permissions. When an administrator modifies permissions, it only works on newly created connections.

If a client does not perform any action for a long time after connecting to MySQL, the connector automatically disconnects the client. The specific length of disconnection is controlled by the MySQL parameter wait_timeout, which defaults to 8 hours. We can adjust the value of this parameter according to actual business needs, so that MySQL can meet our actual business scenarios.

Because the connection between the client and MySQL is more complex, this process is also more time-consuming, it will involve TCP handshake operations, but also query the current connection permission information. Often in the actual process of work, we will use the database connection pool way, the database connection cache, which means that we are using long connections to interact with MySQL.

However, there is also a problem with using a long connection to MySQL: sometimes it is found that the memory occupied by MySQL rises particularly fast, because the temporary memory used by MySQL during execution is managed in the connection object. These occupied resources are released only when the connection is broken. If the connection is not released for a long time, a large amount of temporary memory will occupy memory space. If it takes a long time, it may cause too much memory to be occupied, which will be "eliminated" by the operating system, giving people the feeling that MySQL has restarted unexpectedly.

We can solve this problem using the following solution:

Disconnect periodically or after performing a memory comparison query, and re-establish the connection to MySQL later. If you are using MySQL 5.7 or later, you can reinitialize MySQL resources by executing mysql_reset_connection. The reinitialization process does not reconnect MySQL and does not revalidate permissions. What is the purpose of query caching?

After logging in to MySQL, the client will establish a connection with MySQL. When executing the select statement at this time, it will first query whether the current select statement has been executed in the query cache. If the corresponding select statement has been executed before, the executed select statement and query result will be stored in the query cache in the form of key-value, where key is the query statement and value is the query result data.

If no corresponding data is found in the query cache, execution proceeds to subsequent query phases. When execution is complete, the results are cached in the query cache. If the subsequent query hits the cache, it will directly return the data in the query cache, and the performance is still quite high.

However, most of the time I don't recommend that my friends open the query cache. Why? The reason is simple: query cache failures occur so frequently that all query caches on a table are emptied whenever an update is made to that table. And in MySQL 8.0, the query cache function was directly removed (Ice River also proved this point when looking at MySQL source code).

What does the parser do with the select statement?

Parser mainly carries on lexical analysis and syntax analysis operation to select statement.

If the select statement misses the cache, it is first subjected to "lexical analysis" by the parser, at which point MySQL recognizes what each string in the select statement represents.

For example, MySQL recognizes this as a query statement by the "select" keyword, and also recognizes "user" as "data table name user" and "id" as "field name id." Next, it is necessary to "parse" and determine whether the select statement satisfies MySQL syntax according to syntax rules. If it is determined that the input SQL statement does not satisfy the syntax rules, MySQL will prompt the corresponding error message.

How does the optimizer optimize select statements?

After lexical analysis and syntax analysis of the select statement, it can only be executed after optimization by the optimizer. For example, if multiple indexes are used in our select statement, the optimizer will decide which index to use to query the data; for example, in the select statement, there are multi-table associated operations, and the optimizer will determine the join order of each table. The join order of the data tables is different, and the efficiency of execution will be greatly different. The optimizer will often choose to use the join order with high query efficiency.

If the select statement is optimized by the optimizer, it enters the execution phase.

How does the executor execute the select statement?

Enter the select statement in the execution phase. First, the executor will check the permissions of the current connection. The most direct way is to check whether the current connection has query permissions on the data table user. If the current connection does not have query permissions on the data table user, a no permissions error is returned. For example, the following error is returned.

ERROR 1142 (42000): SELECT command denied to user 'binghe'@'localhost' for table 'user'

If the current connection has query permissions on the data table user, execution continues. The data table is opened first, and the optimizer performs queries using the interfaces of the storage engine used to create the table. Here, let's take an example:

Assuming that we do not have an index on the id field, the flow performed by the executor is roughly as follows.

(1) Read the first row of data in the data table user through the storage engine, judge whether the id value of the current row is equal to 1001, if not equal to 1001, continue to read the next row of data; if equal to 1001, put the current row into the result set.

(2) Continue reading the next row of data through the storage engine, performing the same logical decision as (1), until all the data in the user table has been processed.

(3) After processing all the data, the executor will return the data in the result set to the client.

If there is an index on the id field, the overall logic performed is roughly the same as no index on the id field.

If slow query is enabled, when the select statement is executed, a rows_examined field is output in the slow query log, which indicates how many rows of data in the data table the select statement scans during execution. However, in some scenarios, the storage engine internally scans multiple rows once the executor is invoked, which causes the number of rows scanned by the storage engine not to be exactly the same as the number of rows identified by the rows_examined field.

Thank you for reading, the above is the "select statement in MySQL is how to execute" content, after the study of this article, I believe that we have a deeper understanding of how to execute the select statement in MySQL, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.