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

This article shows you what the execution process of the MySQL query statement is, the content is concise and easy to understand, and it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The execution process of query request

It is well known that query requests are the most frequently used in MySQL database applications. Suppose we enter the SQL in code snippet 1 and request the MySQL server through the client, we will get a result set containing user. However, we don't know the process of MySQL, so let's take a look at what happens on the MySQL server before and after the query request.

Select * from user where userId=1

Code snippet 1

Figure 1 MySQL query request processing flow

As shown in figure 1, the whole picture consists of three parts, from top to bottom, the client (purple), MySQL Server layer (green), and MySQL storage engine layer (yellow).

It goes without saying that the client is mainly responsible for establishing a connection with the MySQL Server layer, sending query requests, and receiving the result set of the response.

MySQL Server layer, including connector, query cache, analyzer, optimizer, executor and so on. These components contain most of the main functions of MySQL, such as the most commonly used stored procedures, triggers, and views in this layer. There is also a general logging module bin log. L MySQL storage engine layer, which is mainly responsible for data storage and extraction. It supports multiple storage engines, such as InnoDB, MyISAM and so on. Commonly used is InnoDB, which has become the default storage engine for MySQL since MySQL version 5.5.5. The important thing is that the InnoDB engine includes its own logging module redo log, which will be highlighted later in the update statement.

The component structure of MySQL is described above, so here we briefly comb through the process of processing SQL statements, and then introduce each component one by one. As shown in figure 2, the process processing number is added to figure 1 to see how the components of MySQL handle SQL query requests.

1. Connector: when the client logs in to MySQL, the authentication and authority are judged.

two。 Query cache: when a query statement is executed, the cache will be queried first (removed after MySQL version 8.0).

3. Parser: assume that the SQL request will come to the parser without hitting the query cache. The parser is responsible for identifying what the SQL is going to do and checking that the syntax of the SQL is correct.

4. Optimizer: provides an optimized execution scheme for SQL.

5. Executor: distributes the statement to the corresponding storage engine for execution and returns data.

MySQL component definition

Figure 2 SQL request execution process

The SQL execution process is sorted out through a large picture above, and the corresponding components are described in detail here.

Connector

The client needs to access the MySQL Server through the connector, which is mainly responsible for identity authentication and authority authentication. In other words, it is responsible for the related authentication operations of the user logging into the database, such as verifying account password, permissions and so on. Under the premise that the user name and password is legal, the corresponding permission of the user is queried in the permission table, and the permission is assigned to the user. After the connection is complete, you can see the connection status in figure 3, and you can generate the query results in figure 3 through the command line "show processlist". In the content returned by the "Command" column, "Sleep" indicates that there is an idle connection in the same MySQL. And "Query" represents the connection being queried.

Figure 3 connection status

The connection status is mentioned above. Here, the five connection states are sorted into the following table for your reference.

Command thread is waiting for client to send data query connection thread is executing query locked thread is waiting for table lock release sorting result thread is sorting results sending data

Return data to the requester

MySQL divides connections in connectors into long connections and short connections.

Persistent connection means that the client requests to use the same connection all the time after the connection is successful.

A short connection means that the connection will be disconnected after each SQL request, and the connection will be re-established if there is another SQL request.

Because short connections repeatedly create connections that consume the same resources, long connections are chosen in most cases. However, in order to maintain a long connection, it will consume the system memory, and the occupied memory will not be released until the connection is disconnected. Here are two solutions:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Disconnect long connections periodically, disconnect every once in a while or after executing a large memory-consuming query to free up memory, and recreate the connection when querying.

MySQL 5.7 or later, reinitialize the connection by executing mysql_reset_connection. This process does not re-establish the connection, but frees up the occupied memory and restores the connection to the state where the connection was just created.

Query cache

The SQL statement can be executed after the connection to the database is established, but the cache is queried before execution to see if the statement has been executed before, and the result is cached in memory in the form of key-value.

Key is the SQL statement of the query, and Value is the result of the query. If the cache Key is hit, it will be returned directly to the client. If it is not hit, subsequent operations will be performed, and the result will still be cached after the execution of SQL to facilitate the next call.

Caching is not recommended for MySQL queries because there is a scenario where if an update is made to a table, the query cache for that table will be emptied. If the table is constantly used (update, query), the query cache will fail frequently, and it is meaningless to get the query cache. However, it can be applied to some data tables that are modified infrequently.

For example: system configuration, or tables that are modified infrequently. The elimination strategy of cache is first-in, first-out, which is suitable for cases where the query is much larger than the modification, otherwise it is recommended to use Redis or other caching tools. Therefore, query caching is not recommended in most cases. After version 8.0 of MySQL, the query cache function has been deleted. Officials believe that this feature has fewer application scenarios, so it has been deleted.

If you do not need to use query caching in MySQL, you can also set the parameter query_cache_type to DEMAND, then query caching will not be used by default when executing SQL statements. If the cache is turned on, you can view the cache through the "show status like 'Qcache%'" command.

As shown in figure 4, several of the more frequently used state values are as follows:

Whether there is any new data added to Qcache_inserts, the Value will be added one for each item of data added.

Qcache_hits query statement whether hit the cache, each statement hit Value will be increased by one.

Qcache_free_memory cache idle size.

As shown in figure 4, cache status

Analyzer

If the query cache misses, the SQL request goes to the parser, which is used to identify the purpose of the execution of the SQL statement, which is roughly divided into two steps:

The first step is lexical analysis (Lexical scanner)

Mainly responsible for extracting keywords from SQL statements, such as: query table, field name, query condition and so on.

The second step, grammar rules (Grammar rule module)

The main purpose is to judge whether the SQL statement conforms to the syntax of MySQL.

In fact, to put it bluntly, lexical analysis (Lexical scanner) is to split the whole SQL sentence into words, while the grammar rules (Grammar rule module) generate the corresponding data structure according to the grammar rules defined by MySQL and store them in the object structure. The result is for the optimizer to generate the execution plan, and then call the storage engine interface to execute. Let's take a look at the following example, suppose there is such a SQL statement "select username from userinfo".

First, through lexical analysis, parsing character by character from left to right to get the four words as shown in Table 1.

Keyword non-keyword keyword selectusernamefromuserinfo

Table 1 parsing keywords

Then the syntax rules are parsed to determine whether the input SQL statement satisfies the MySQL syntax, and the syntax tree in figure 5 is generated. Of the four words generated by the SQL statement, two keywords are identified as select and from. According to the syntax of MySQL, there is a fields field between Select and from. Username; should be attached below, followed by the Tables field after from, and userinfo under it.

Figure 5 Syntax rules generate syntax tree

Optimizer

The role of the optimizer is to optimize the SQL to generate the most available execution plan. As shown in figure 6, the previously mentioned SQL parser generates the SQL syntax tree through syntax parsing and syntax rules. This syntax tree serves as input to the optimizer, which (the yellow part) contains two parts: logical transformation and cost optimization. After the optimization is completed, the SQL execution plan is generated as the output of the entire optimization process, which is handed over to the executor for execution on the storage engine.

Figure 6 location of the optimizer

As shown in the figure above, this section focuses on logical transformation and cost optimization in the optimizer.

Logical transformation

Logical transformation is carried out on the basis of relational algebra, and its purpose is to simplify and ensure that the results before and after the SQL change are consistent, that is, the logical change will not bring about the change of the result set. It mainly includes the following aspects:

Negative elimination: in the case of negative before the expression "and fetch" or "disjunctive", the relation condition should be split so as to eliminate the outer "NOT".

Equivalent constant transfer: take advantage of the transfer characteristics of the equivalence relationship in order to perform the "push-down" operation as soon as possible. The basic strategy of "push-down" is to always move the filter expression as close to the data source as possible.

Constant expression calculation: for the expression that can calculate the result immediately, calculate the result directly, and simplify the result and other conditions as early as possible.

The concept may be a little abstract in this way, but let's take a look at figure 7 to see how logical changes are implemented in SQL.

Fig. 7 logical transformation

As shown in figure 7, there are four steps from top to bottom:

1. For the existing SQL statement, first of all, through the "negative elimination", remove the "NOT" in the condition judgment. The statement is converted from the original "or" to "and", and the sign is changed by the greater than less than symbol. The blue part is the pre-modified SQL, and the red is the modified SQL.

two。 Equivalent passing, this step is easy to understand that the corresponding values in SQL are replaced by "t2.a=9" and "t2.b=5", respectively.

3. The next step is to evaluate the constant expression, which evaluates "5x 7" to "12".

4. The last part is the simplification of the constant expression after calculation, which will be "9"

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.